[SQL ] 查詢當日每小時產量統計表
（針對 SQL 語言的問題，用這個標題。請用 Ctrl+Y 砍掉這行）
資料庫名稱：oracle sql
資料庫版本：
內容/問題描述：
主要問題有二
一 欄位一 時間區間以 當日每小時 做間隔,不知道有沒有時間函數可以處理?
目前想到 以 CASE 作條件輸出 ； WITH 做虛擬表格 查詢 ；
製作 VIEW 觀視表 (和WITH 一樣)
二 達成率希望做個別統計 EX: 07:00 產量/100 ； 08:00 產量/50
主要卡在問題一的時間區隔處理, 沒有其他資料表有相關欄位可以做 JOINT 或 子查詢
請大家幫幫忙
資料表 G_SN_TRAVEL 欄位 OUT_PROCESS_TIME (DATE 機台過站時間戳記)
希望輸出查詢統計表如下
欄位一 欄位二 欄位三
CLOCK_TIME QTY(產量) RATE(達成率)
07:00 XXX XX %
08:00 XXX XX %
09:00 XXX XX %
已有 方案 都可以完成 問題一
方案一
SELECT
(case to_char(A.OUT_PROCESS_TIME,'HH24')
when '07' then '07:00~07:59'
when '08' then '08:00~08:59'
when '09' then '09:00~09:59'
when '10' then '10:00~10:59'
when '11' then '11:00~11:59'
when '12' then '12:00~12:59'
when '13' then '13:00~13:59'
when '14' then '14:00~14:59'
when '15' then '15:00~15:59'
when '16' then '16:00~16:59'
when '17' then '17:00~17:59'
when '18' then '18:00~18:59'
when '19' then '19:00~19:59'
when '20' then '20:00~20:59'
end) AS TIME_CLOCK,
COUNT (A.OUT_PROCESS_TIME) AS QTY,
TO_CHAR ((COUNT (A.OUT_PROCESS_TIME) /120),'0.000') AS RATE
FROM SAJET.G_SN_TRAVEL A WHERE A.PROCESS_ID = '100032' AND
to_char(A.OUT_PROCESS_TIME,'YYYYMMDD') = TO_CHAR(SYSDATE,'YYYYMMDD')
GROUP BY
(case to_char(A.OUT_PROCESS_TIME,'HH24')
when '07' then '07:00~07:59'
when '08' then '08:00~08:59'
when '09' then '09:00~09:59'
when '10' then '10:00~10:59'
when '11' then '11:00~11:59'
when '12' then '12:00~12:59'
when '13' then '13:00~13:59'
when '14' then '14:00~14:59'
when '15' then '15:00~15:59'
when '16' then '16:00~16:59'
when '17' then '17:00~17:59'
when '18' then '18:00~18:59'
when '19' then '19:00~19:59'
when '20' then '20:00~20:59'
end)
ORDER BY
(case to_char(A.OUT_PROCESS_TIME,'HH24')
when '07' then '07:00~07:59'
when '08' then '08:00~08:59'
when '09' then '09:00~09:59'
when '10' then '10:00~10:59'
when '11' then '11:00~11:59'
when '12' then '12:00~12:59'
when '13' then '13:00~13:59'
when '14' then '14:00~14:59'
when '15' then '15:00~15:59'
when '16' then '16:00~16:59'
when '17' then '17:00~17:59'
when '18' then '18:00~18:59'
when '19' then '19:00~19:59'
when '20' then '20:00~20:59'
end) ASC
===============================
方案二
with
v_today(vday)
as (
SELECT to_char(sysdate,'YYYYMMDD')FROM dual
),
--select * from v_today
v_G_SN_TRAVEL(v_WORK_ORDER,v_SERIAL_NUMBER,v_OUT_PROCESS_TIME,v_vc_time)
as (
select a.WORK_ORDER ,a.SERIAL_NUMBER ,a.OUT_PROCESS_TIME
,to_char(a.OUT_PROCESS_TIME,'YYYYMMDDHH24MISS') as vc_time
from SAJET.G_SN_TRAVEL a
,v_today b
where 0=0
--and a.WORK_ORDER = 'MO20050015'
and a.PROCESS_ID = '100032'
and to_char(a.OUT_PROCESS_TIME,'YYYYMMDD') BETWEEN b.vday and b.vday
),
--select * from v_G_SN_TRAVEL
v_clock_07 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '070000' and '079999'
),
v_clock_08 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '080000' and '089999'
),
v_clock_09 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '090000' and '099999'
),
v_clock_10 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '100000' and '109999'
),
v_clock_11 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '110000' and '119999'
),
v_clock_12 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '120000' and '129999'
),
v_clock_13 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '130000' and '139999'
),
v_clock_14 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '140000' and '149999'
),
v_clock_15 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '150000' and '159999'
),
v_clock_16 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '160000' and '169999'
),
v_clock_17 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '170000' and '179999'
),
v_clock_18 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '180000' and '189999'
),
v_clock_19 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '190000' and '199999'
),
v_clock_20 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '200000' and '209999'
),
v_clock_21 (v_count_time)
as (
select COUNT(v_vc_time) from v_G_SN_TRAVEL where
to_char(v_OUT_PROCESS_TIME,'HH24MISS') BETWEEN '210000' and '219999'
),
sum_clock_data (v_itm,v_scd)
as (
select '07:00 ~ 07:59' as v_itm ,v_count_time from v_clock_07
union all
select '08:00 ~ 08:59' as v_itm ,v_count_time from v_clock_08
union all
select '09:00 ~ 09:59' as v_itm ,v_count_time from v_clock_09
union all
select '10:00 ~ 10:59' as v_itm ,v_count_time from v_clock_10
union all
select '11:00 ~ 11:59' as v_itm ,v_count_time from v_clock_11
union all
select '12:00 ~ 12:59' as v_itm ,v_count_time from v_clock_12
union all
select '13:00 ~ 13:59' as v_itm ,v_count_time from v_clock_13
union all
select '14:00 ~ 14:59' as v_itm ,v_count_time from v_clock_14
union all
select '15:00 ~ 15:59' as v_itm ,v_count_time from v_clock_15
union all
select '16:00 ~ 16:59' as v_itm ,v_count_time from v_clock_16
union all
select '17:00 ~ 17:59' as v_itm ,v_count_time from v_clock_17
union all
select '18:00 ~ 18:59' as v_itm ,v_count_time from v_clock_18
union all
select '19:00 ~ 19:59' as v_itm ,v_count_time from v_clock_19
union all
select '20:00 ~ 20:59' as v_itm ,v_count_time from v_clock_20
union all
select '21:00 ~ 21:59' as v_itm ,v_count_time from v_clock_21
)
select (v_itm)"Time Clock",(v_scd)"Output Qty" from sum_clock_data
--
從表象看起來我是個沒啥事的閒人╭(─╴─)╮
其實我的真實身份是............研究僧
研究如何將自己脫離去死團方法.........................Orz
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 61.216.86.175 (臺灣)
※ 文章網址: https://www.ptt.cc/bbs/Database/M.1595486385.A.D77.html
推
07/23 17:25,
4年前
, 1F
07/23 17:25, 1F
→
07/23 17:26,
4年前
, 2F
07/23 17:26, 2F
→
07/23 17:26,
4年前
, 3F
07/23 17:26, 3F
→
07/23 17:27,
4年前
, 4F
07/23 17:27, 4F
討論串 (同標題文章)
完整討論串 (本文為第 1 之 2 篇)：
Database 近期熱門文章
PTT數位生活區 即時熱門文章
