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

前言

分析问题之前先给出问题的答案：2次，送给急需要知道答案又不求甚解的朋友。

这个问题之前听过类似的，一直没有当回事，今天在解题的时候发现了这道题，于是动脑筋想了一下，从12点位置时分秒3个表针重合开始，第一次应该在1点5分之后，那是分针转了一圈快追上时针了，再稍微走一点就能追上，然后秒针再转过来就完成了第一次重合，同理在2点10分之后也有一次，在3点15之后还有一次，这样算下来12小时之内有11次，那么一天24小时就有22次。

正在为自己的想法得意时，查了一下参考答案发现我被幼稚的想法打败了，实际上一天24小时内，时分秒针只重合了2次，原因就是我设想从12点开始到1点5分，分针转了一圈快追上时针了，此刻时针与分针确实会有一次相遇，但是此时的秒针却没办法跟他们相逢，因为三个表针是联动的，针对于每个精确到秒的时间，三个针都有固定的位置，不是想重合就能重合的。

在1点5分附近的情况就是，时针和分针快要重合了，然后秒针匆匆赶来，然后时针和分针重合了，秒针还差一点才能到，然后秒针继续走，但是秒针走会继续带动分针和时针运动，然后秒针赶到了分针时针相遇的附近，却发现它俩已经“分手”了，秒针只能大步流星的一个个越过它们俩，期待着下次它们仨能相遇在一处。

时针和分针的相遇

在考虑时分秒三针重合情况之前，我们可以先想一下一天24小时内，分针和时针相遇了多少次，其实这才是我刚才想的那个答案22次，知道了次数之后我们还想知道具体的时间，可不可以算出来呢？当然可以！

接下来我们以一种通俗的方式来解这个问题，那就是列方程式求解，首先将时间作为连续值来看待，我们设时针的角速度是ω，因为时针走1格，分针会走1圈，也就是12格，所以分针的角速度是12ω，分针转了一圈追上时针用的是t，时针和分针转过角度差为1圈，也就是2π，那么此时可以列出方程：

$$12\omega t-\omega t=2\pi$$

关于角速度ω的值，因为1圈的角度是2π，转一圈需要花的时间是12小时，所以ω=2π/12小时，带入方程得到t=12小时/11，同理如果分针转两圈追上时针，那么方程式为：

$$12\omega t-\omega t=4\pi$$

可以求得t=24小时/11，由此我们就得到了，时针分针相遇时刻与分针转的圈数i的关系：

$$t=i\ast 12小时/11$$

代码实现

有了上面的分析，我们可以写代码计算一下一天之中时针和分针相遇具体时刻，因为开着lua编辑器，顺手就用lua写了，代码如下：

function print_meet(id, meet)
    local h = math.floor(meet)
    local t = meet - h;
    local ts = t * 3600;
    local m = ts // 60;
    local s = ts - m * 60;
    print(string.format("%02dth meet, time = %02d:%02d:%05.2f", id, h, m, s));
end

for i=1,24 do
    print_meet(i, i * 12 / 11)
end

运行结果

01th meet, time = 01:05:27.27
02th meet, time = 02:10:54.55
03th meet, time = 03:16:21.82
04th meet, time = 04:21:49.09
05th meet, time = 05:27:16.36
06th meet, time = 06:32:43.64
07th meet, time = 07:38:10.91
08th meet, time = 08:43:38.18
09th meet, time = 09:49:05.45
10th meet, time = 10:54:32.73
11th meet, time = 12:00:00.00
12th meet, time = 13:05:27.27
13th meet, time = 14:10:54.55
14th meet, time = 15:16:21.82
15th meet, time = 16:21:49.09
16th meet, time = 17:27:16.36
17th meet, time = 18:32:43.64
18th meet, time = 19:38:10.91
19th meet, time = 20:43:38.18
20th meet, time = 21:49:05.45
21th meet, time = 22:54:32.73
22th meet, time = 24:00:00.00
23th meet, time = 25:05:27.27
24th meet, time = 26:10:54.55

分析

从上面的结果来看，处于一天内的时间相遇时刻只有前22次，12点之后第一次相遇是在01:05:27.27，此时虽然时针和分针相遇，但是秒针大概在27秒的位置，离他们还很远，同理分针时针第二次相遇时刻02:10:54.55，秒针也没有跟他们在一起，但是有两次例外，那就是12:00:00.00和24:00:00.00，这两次时针、分针、秒针完全重合了，所以我们也得到了本文标题中的答案。

总结

1. 时分秒针在一天之内重合2次
2. 从连续的时间来看，时针和分针在一天之内重合22次
3. 有一种现实情况就是表盘上的时间是离散的，不连续的，最小的时间间隔是1秒，此时我们计算的第一次相遇时间01:05:27.27是不存在的，01:05:27的时候分针在时针之前，而01:05:28的时候分针在时针之后，它们也错过了，所以时针和分针考虑离散的情况，一天之后也只是重合2次。

效果如下

Slider滑块轴的默认绑定输出值是number 且取值范区间是【0-100】

根据需求 比如我这里是需要展示一天24小时，且只能展示半点和整点。可以把时间轴等分成48份，这里max取值上限可以设为48，步长为1

重点是自定义的格式化处理函数

formatTooltip (val) {
      let hour = 0
      let min = 0
      let ms = val * 1800
      hour = parseInt(ms / 3600)
      if (hour < 10) {
        hour = '0' + hour.toString()
      } else {
        hour = hour.toString()
      }
      min = ms % 3600
      if (min === 0) {
        min = '00'
      } else {
        min = '30'
      }
      let time = hour + ':' + min
      console.log(time)
      return time
    }

因为参数val是刻度上的一份，代表半小时时长，化成毫秒计算需乘1800。计算小时整除向下取整，计算分钟整除求余，除尽为整点，除不尽为半点。这里注意不满10小时要在前面补0。

2021/3/23补充
:marks写法