（针对 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
),