正如所解释的在之前的回答中 https://stackoverflow.com/q/30920975/266304,您可以使用 SSSSS 格式模型来操作日期值的时间部分。在这里应用该方法,然后您可以按调整后的值进行查询和分组:
SELECT
TO_CHAR(TRUNC(sess_start_date)
+ FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)/96,
'YYYY-MM-DD HH24:MI:SS') time_start,
COUNT (sess_id) how_many
FROM
t.sessions,
t.users
WHERE
t.users.user_id = t.sessions.sess_user_id
AND user_type !='X'
AND sess_start_date>=TRUNC(ADD_MONTHS(SYSDATE,-12))
GROUP BY
TRUNC(sess_start_date) + FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)/96;
如果您想要不参考日期的时间段,您可以这样做:
SELECT
TO_CHAR(TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)*900,
'SSSSS'),'HH24:MI') time_start,
COUNT (sess_id) how_many
FROM
t.sessions,
t.users
WHERE
t.users.user_id = t.sessions.sess_user_id
AND user_type !='X'
AND sess_start_date>=TRUNC(ADD_MONTHS(SYSDATE,-12))
GROUP BY
TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)*900, 'SSSSS');
要获得所有天的同一时间段内的平均计数,您可以将它们与内联视图结合起来:
SELECT
TO_CHAR(period_start, 'HH24:MI') as period_start,
AVG (how_many) avg_how_many
FROM (
SELECT
TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)*900,
'SSSSS') period_start,
TO_CHAR(TRUNC(sess_start_date)
+ FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)/96,
'YYYY-MM-DD HH24:MI:SS') time_start,
COUNT (sess_id) how_many
FROM
t.sessions,
t.users
WHERE
t.users.user_id = t.sessions.sess_user_id
AND user_type !='X'
AND sess_start_date>=TRUNC(ADD_MONTHS(SYSDATE,-12))
GROUP BY
TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)*900, 'SSSSS'),
TRUNC(sess_start_date) + FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)/96
)
GROUP BY
period_start
ORDER BY
period_start;