（針對 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
),