表数据如下:
不知道怎么把里面每个时间分为对应的时间段然后统计这个时间段里的数据,例如2018-12-21 15:25:00划分到2018-12-21 15这个时间段,并统计这个时间段里volume相关值,类似于下面的Excel表格
我写了一个SQL,但是应该是没有正确分组,始终做不出来,希望大神赐教,感谢!
SELECT
case TO_CHAR(TIMESTAMP,'hh24')
when '00' then '0:00:00--- 1:00:00'when '01' then '0:00:00--- 1:00:00'when '02' then '0:00:00--- 1:00:00'when '03' then '0:00:00--- 1:00:00'
when '04' then '0:00:00--- 1:00:00'when '05' then '0:00:00--- 1:00:00'when '06' then '0:00:00--- 1:00:00'when '07' then '0:00:00--- 1:00:00'
when '08' then '0:00:00--- 1:00:00'when '09' then '0:00:00--- 1:00:00'when '10' then '0:00:00--- 1:00:00'when '11' then '0:00:00--- 1:00:00'
when '12' then '0:00:00--- 1:00:00'when '13' then '0:00:00--- 1:00:00'when '14' then '0:00:00--- 1:00:00'when '15' then '0:00:00--- 1:00:00'
when '16' then '0:00:00--- 1:00:00'when '17' then '0:00:00--- 1:00:00'when '18' then '0:00:00--- 1:00:00'when '19' then '0:00:00--- 1:00:00'
when '20' then '0:00:00--- 1:00:00'when '21' then '0:00:00--- 1:00:00'when '22' then '0:00:00--- 1:00:00'when '23' then '0:00:00--- 1:00:00' end as dd,
SUM (volume1) AS volume1,
SUM (volume2) AS volume2,
SUM (volume3) AS volume3,
SUM (volume4) AS volume4,
SUM (volume5) AS volume5,
SUM (VOLUME) AS VOLUME
FROM
TBL_RADAR_STATISTICSDATA
WHERE
TIMESTAMP BETWEEN TO_DATE (
'2018-01-01 10:10:10',
'yyyy-mm-dd hh24:mi:ss'
)
AND TO_DATE (
'2019-01-01 10:10:10',
'yyyy-mm-dd hh24:mi:ss'
)
group by to_char(TIMESTAMP,'hh24')
order by to_char(TIMESTAMP,'hh24');