sql写法:看着有点复杂,仔细梳理,发现逻辑是极为简单的,如果有什么不懂,请给小编留言,小编尽量给你们解答
间隔是三十分钟的:
SELECT COUNT(DISTINCT tmp.xlh) totalNum, tmp.newTime
FROM (SELECT T.xlh xlh,
TO_CHAR(T.zhczsj, 'yyyymmddhh24mi') oldTime, -- 原来的时间,这个时间主要是要与现在的时间做对比
CASE
WHEN TO_CHAR(T.zhczsj, 'mi') < 30 THEN
TO_CHAR(T.zhczsj, 'yyyymmdd') || TO_CHAR(T.zhczsj, 'hh24') || '30'
ELSE
TO_CHAR(T.zhczsj, 'yyyymmdd') ||
to_char(to_number(TO_CHAR(T.zhczsj, 'hh24'))+1) || '00'
END AS newTime -- 时间段伪列
FROM vio_veh_viorec T
WHERE T.zhczsj >= trunc(sysdate) and T.zhczsj < trunc(sysdate) + 1
ORDER BY T.zhczsj ASC) tmp
GROUP BY tmp.newTime
ORDER BY tmp.newTime DESC
间隔是五分钟的:
SELECT COUNT(DISTINCT tmp.xlh) totalNum, tmp.newTime
FROM (SELECT T.xlh xlh,
TO_CHAR(T.zhczsj, 'yyyymmddhh24mi') oldTime,
-- 原来的时间
CASE
WHEN SUBSTR(TO_CHAR(T.zhczsj, 'mi'), 2, 1) < 5 THEN
TO_CHAR(T.zhczsj, 'yyyymmddhh24') ||
SUBSTR(TO_CHAR(T.zhczsj, 'mi'), 1, 1) || 0
ELSE
TO_CHAR(T.zhczsj, 'yyyymmddhh24') ||
SUBSTR(TO_CHAR(T.zhczsj, 'mi'), 1, 1) || 5
END AS newTime -- 时间段伪列
FROM vio_veh_viorec T
WHERE T.zhczsj >= trunc(sysdate) and T.zhczsj < trunc(sysdate) + 1
ORDER BY T.zhczsj ASC) tmp
GROUP BY tmp.newTime
ORDER BY tmp.newTime DESC