ORACLE 将数据按照一天中的24小时进行次数的统计
ORACLE 将数据按照一天中的24小时进行次数的统计
#首先我的数据表是这样的
表中有开始时间(start_time)和结束时间(end_time) , 我要做的是查询一个日期时间段内(例如2019-10-01 00:00:00至2019-10-31 23:59:59) 的数据 然后再按照一天24小时,统计每条数据的开始和结束的时间段在24小时中的分布情况,最后已表中的000002为分组字段,生成一条统计数据 ,然后在前端用echers展示,结果如下:
解决方案:
(1).按照日期和控制器编号查询出要处理的数据
(2).由于开始和结束时间 有可能两个都在统计的时间段内,也有可能其中一个在要统计的时间段内,所以要再将"(1)"中的数据的开始和结束时间处理下,截掉不在统计时间段内的时间:
`CASE WHEN B.START_TIME < TO_DATE( '2019-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ) THEN TO_DATE( '2019-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ) ELSE B.START_TIME END`
CASE WHEN B.END_TIME > TO_DATE( '2019-11-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss' ) THEN TO_DATE( '2019-11-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss' ) ELSE B.END_TIME END
(3) 因为我是要按照24小时来统计,所以我这里将时间又处理了下,只取24小时制的小时数,使用:
TO_CHAR(START_TIME,'HH24')
(4) 然后我开始从0-23点挨个统计,使用的方法如下,这是统计数据在0点-1点占用的次数,以此类推
COUNT( CASE WHEN ( A.START_TIME <= 00 AND A.END_TIME >= 00 ) THEN 1 END ) AS count_0,
下面是全部的完整sql 做个记录,你们看看还有没有更好的方法呀
SELECTCONTROL_NUMBER,COUNT( CASE WHEN ( A.START_TIME <= 00 AND A.END_TIME >= 00 ) THEN 1 END ) AS count_0,COUNT( CASE WHEN ( A.START_TIME <= 01 AND A.END_TIME >= 01 ) THEN 1 END ) AS count_1,COUNT( CASE WHEN ( A.START_TIME <= 02 AND A.END_TIME >= 02 ) THEN 1 END ) AS count_2,COUNT( CASE WHEN ( A.START_TIME <= 03 AND A.END_TIME >= 03 ) THEN 1 END ) AS count_3,COUNT( CASE WHEN ( A.START_TIME <= 04 AND A.END_TIME >= 04 ) THEN 1 END ) AS count_4,COUNT( CASE WHEN ( A.START_TIME <= 05 AND A.END_TIME >= 05 ) THEN 1 END ) AS count_5,COUNT( CASE WHEN ( A.START_TIME <= 06 AND A.END_TIME >= 06 ) THEN 1 END ) AS count_6,COUNT( CASE WHEN ( A.START_TIME <= 07 AND A.END_TIME >= 07 ) THEN 1 END ) AS count_7,COUNT( CASE WHEN ( A.START_TIME <= 08 AND A.END_TIME >= 08 ) THEN 1 END ) AS count_8,COUNT( CASE WHEN ( A.START_TIME <= 09 AND A.END_TIME >= 09 ) THEN 1 END ) AS count_9,COUNT( CASE WHEN ( A.START_TIME <= 10 AND A.END_TIME >= 10 ) THEN 1 END ) AS count_10,COUNT( CASE WHEN ( A.START_TIME <= 11 AND A.END_TIME >= 11 ) THEN 1 END ) AS count_11,COUNT( CASE WHEN ( A.START_TIME <= 12 AND A.END_TIME >= 12 ) THEN 1 END ) AS count_12,COUNT( CASE WHEN ( A.START_TIME <= 13 AND A.END_TIME >= 13 ) THEN 1 END ) AS count_13,COUNT( CASE WHEN ( A.START_TIME <= 14 AND A.END_TIME >= 14 ) THEN 1 END ) AS count_14,COUNT( CASE WHEN ( A.START_TIME <= 15 AND A.END_TIME >= 15 ) THEN 1 END ) AS count_15,COUNT( CASE WHEN ( A.START_TIME <= 16 AND A.END_TIME >= 16 ) THEN 1 END ) AS count_16,COUNT( CASE WHEN ( A.START_TIME <= 17 AND A.END_TIME >= 17 ) THEN 1 END ) AS count_17,COUNT( CASE WHEN ( A.START_TIME <= 18 AND A.END_TIME >= 18 ) THEN 1 END ) AS count_18,COUNT( CASE WHEN ( A.START_TIME <= 19 AND A.END_TIME >= 19 ) THEN 1 END ) AS count_19,COUNT( CASE WHEN ( A.START_TIME <= 20 AND A.END_TIME >= 20 ) THEN 1 END ) AS count_20,COUNT( CASE WHEN ( A.START_TIME <= 21 AND A.END_TIME >= 21 ) THEN 1 END ) AS count_21,COUNT( CASE WHEN ( A.START_TIME <= 22 AND A.END_TIME >= 22 ) THEN 1 END ) AS count_22,COUNT( CASE WHEN ( A.START_TIME <= 23 AND A.END_TIME >= 23 ) THEN 1 END ) AS count_23
FROM(SELECTB.CONTROL_NUMBER,TO_CHAR( CASE WHEN B.START_TIME < TO_DATE( '2019-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ) THEN TO_DATE( '2019-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ) ELSE B.START_TIME END, 'HH24' ) AS START_TIME,TO_CHAR( CASE WHEN B.END_TIME > TO_DATE( '2019-10-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss' ) THEN TO_DATE( '2019-10-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss' ) ELSE B.END_TIME END, 'HH24' ) AS END_TIME
FROMH_CHARGE_STATISTIC B
WHEREB.CONTROL_NUMBER = '000002' AND B.START_TIME IS NOT NULL AND B.END_TIME IS NOT NULL AND B.TX_POWER != 0.00AND B.RX_POWER != 0.00AND (( B.START_TIME BETWEEN TO_DATE( '2019-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ) AND TO_DATE( 2019-10-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss' ) ) OR ( B.END_TIME BETWEEN TO_DATE( '2019-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ) AND TO_DATE( '2019-10-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss' ) ) )) A
GROUP BYCONTROL_NUMBER
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！