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 做个记录,你们看看还有没有更好的方法呀

SELECT
	CONTROL_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
	(
	SELECT
		B.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 
FROM
	H_CHARGE_STATISTIC B 
WHERE
	B.CONTROL_NUMBER = '000002' 
	AND B.START_TIME IS NOT NULL 
	AND B.END_TIME IS NOT NULL 
	AND B.TX_POWER != 0.00
	AND B.RX_POWER != 0.00
	AND (( 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 BY
	CONTROL_NUMBER