mysql - SQL累积前几天的结果，同时获取过去24小时内的记录数

Question

我编写了这个 SQL 查询，它返回过去 24 小时内每小时创建的记录数。该查询在第一天工作正常，但从第二天开始，它会将第一天的记录数与当前天数相加。

这是我当前的查询:

 select h.hr, count(e.eventID) as cnt
from (
    select 0 hr union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 union all select 21 union all select 22 union all select 23
) h
left join events e 
    on e.timeStamp > now() - interval 24 hour
        and hour(e.timeStamp) = h.hr
group by h.hr

我的数据集:

+---------+----------+---------------------+---------------------+
| eventID | personID | timeStamp           | dateModified        |
+---------+----------+---------------------+---------------------+
|       1 |        8 | 2019-12-28 12:26:49 | 2019-12-28 12:26:50 |
|       2 |        0 | 2019-12-28 12:26:51 | 2019-12-28 12:26:52 |
|       3 |        0 | 2019-12-28 12:27:11 | 2019-12-28 12:27:12 |
|       4 |        0 | 2019-12-28 12:27:12 | 2019-12-28 12:27:12 |
|       5 |        0 | 2019-12-28 12:28:20 | 2019-12-28 12:28:20 |
|       6 |        0 | 2019-12-28 12:28:21 | 2019-12-28 12:28:21 |
|       7 |        0 | 0000-00-00 00:00:00 | 2019-12-28 12:28:21 |
|       8 |        2 | 2019-12-28 12:30:02 | 2019-12-28 12:30:02 |
|       9 |        0 | 2019-12-28 12:30:03 | 2019-12-28 12:30:03 |
|      10 |        1 | 2019-12-30 05:38:02 | 2019-12-30 05:38:01 |
|      11 |        0 | 2019-12-30 05:38:05 | 2019-12-30 05:38:03 |
|      12 |        0 | 2019-12-30 05:41:42 | 2019-12-30 05:41:41 |
|      13 |        1 | 2019-12-30 05:41:41 | 2019-12-30 05:41:41 |
|      14 |        1 | 2019-12-30 05:43:11 | 2019-12-30 05:43:11 |
|      15 |        0 | 2019-12-30 05:43:13 | 2019-12-30 05:43:11 |
|      16 |        8 | 2019-12-30 05:44:08 | 2019-12-30 05:44:08 |
|      17 |        0 | 2019-12-30 05:44:10 | 2019-12-30 05:44:08 |
|      18 |        1 | 2019-12-30 05:48:06 | 2019-12-30 05:48:06 |
|      19 |        0 | 2019-12-30 05:48:08 | 2019-12-30 05:48:07 |
|      20 |        1 | 2019-12-30 06:09:58 | 2019-12-30 06:09:57 |
|      21 |        0 | 2019-12-30 06:10:00 | 2019-12-30 06:09:58 |
|      22 |        0 | 2019-12-30 06:11:22 | 2019-12-30 06:11:20 |
|      23 |        1 | 2019-12-30 06:11:20 | 2019-12-30 06:11:20 |
|      24 |        1 | 2019-12-30 06:13:30 | 2019-12-30 06:13:30 |
|      25 |        0 | 2019-12-30 06:13:32 | 2019-12-30 06:13:30 |
|      26 |        0 | 0000-00-00 00:00:00 | 2019-12-30 06:13:42 |
|      27 |        0 | 0000-00-00 00:00:00 | 2019-12-30 06:14:00 |
|      28 |        1 | 2019-12-30 06:14:55 | 2019-12-30 06:14:54 |
|      29 |        0 | 2019-12-30 06:14:57 | 2019-12-30 06:14:55 |
|      30 |        0 | 0000-00-00 00:00:00 | 2019-12-30 06:14:59 |
|      31 |        8 | 2019-12-30 06:16:22 | 2019-12-30 06:16:22 |
|      32 |        0 | 2019-12-30 06:16:24 | 2019-12-30 06:16:22 |
|      33 |        0 | 0000-00-00 00:00:00 | 2019-12-30 06:16:27 |
|      34 |        8 | 2019-12-30 06:17:56 | 2019-12-30 06:17:56 |
|      35 |        0 | 2019-12-30 06:17:58 | 2019-12-30 06:17:56 |
|      36 |        1 | 2019-12-30 06:18:32 | 2019-12-30 06:18:31 |
|      37 |        0 | 2019-12-30 06:18:33 | 2019-12-30 06:18:31 |
|      38 |        0 | 0000-00-00 00:00:00 | 2019-12-30 06:18:37 |
|      39 |        8 | 2019-12-30 06:21:23 | 2019-12-30 06:21:23 |
|      40 |        0 | 2019-12-30 06:21:25 | 2019-12-30 06:21:23 |
|      41 |        0 | 2019-12-30 06:21:33 | 2019-12-30 06:21:32 |
|      42 |        0 | 2019-12-30 06:21:34 | 2019-12-30 06:21:32 |
|      43 |        1 | 2019-12-30 06:39:58 | 2019-12-30 06:39:57 |
|      44 |        0 | 2019-12-30 06:40:00 | 2019-12-30 06:39:59 |
|      45 |        1 | 2019-12-30 06:40:29 | 2019-12-30 06:40:29 |
|      46 |        0 | 2019-12-30 06:40:31 | 2019-12-30 06:40:29 |
|      47 |        1 | 2019-12-30 06:42:06 | 2019-12-30 06:42:05 |
|      48 |        0 | 2019-12-30 06:42:07 | 2019-12-30 06:42:05 |
|      49 |        1 | 2019-12-30 06:44:21 | 2019-12-30 06:44:20 |
|      50 |        0 | 2019-12-30 06:44:22 | 2019-12-30 06:44:21 |
|      51 |        1 | 2019-12-30 06:45:35 | 2019-12-30 06:45:34 |
|      52 |        0 | 2019-12-30 06:45:36 | 2019-12-30 06:45:34 |
|      53 |        1 | 2019-12-30 06:46:27 | 2019-12-30 06:46:27 |
|      54 |        0 | 2019-12-30 06:46:28 | 2019-12-30 06:46:27 |
|      55 |        1 | 2019-12-30 06:50:40 | 2019-12-30 06:50:39 |
|      56 |        0 | 2019-12-30 06:50:41 | 2019-12-30 06:50:39 |
|      57 |        8 | 2019-12-30 08:10:52 | 2019-12-30 08:10:57 |
|      58 |        0 | 2019-12-30 08:10:59 | 2019-12-30 08:10:58 |
|      59 |        1 | 2019-12-30 09:47:06 | 2019-12-30 09:47:07 |
|      60 |        0 | 2019-12-30 09:47:08 | 2019-12-30 09:47:07 |
|      61 |        1 | 2019-12-30 11:43:17 | 2019-12-30 11:43:17 |
|      62 |        0 | 2019-12-30 11:43:18 | 2019-12-30 11:43:18 |
|      63 |        1 | 2019-12-30 11:43:35 | 2019-12-30 11:43:35 |
|      64 |        0 | 2019-12-30 11:43:36 | 2019-12-30 11:43:35 |
|      65 |        1 | 2019-12-30 11:44:05 | 2019-12-30 11:44:05 |
|      66 |        0 | 2019-12-30 11:44:06 | 2019-12-30 11:44:06 |
|      67 |        1 | 2019-12-30 12:26:47 | 2019-12-30 12:26:47 |
|      68 |        0 | 2019-12-30 12:26:49 | 2019-12-30 12:26:48 |
|      69 |        0 | 2019-12-30 12:40:34 | 2019-12-30 12:40:33 |
|      70 |        1 | 2019-12-30 12:40:33 | 2019-12-30 12:40:34 |
|      71 |        8 | 2019-12-31 04:52:29 | 2019-12-31 04:52:28 |
|      72 |        0 | 2019-12-31 04:52:31 | 2019-12-31 04:52:29 |
|      73 |        0 | 2019-12-31 04:53:50 | 2019-12-31 04:53:47 |
|      74 |        8 | 2019-12-31 04:53:48 | 2019-12-31 04:53:47 |
|      75 |        8 | 2019-12-31 04:54:14 | 2019-12-31 04:54:13 |
|      76 |        0 | 2019-12-31 04:54:16 | 2019-12-31 04:54:14 |
|      77 |        8 | 2019-12-31 04:54:40 | 2019-12-31 04:54:38 |
|      78 |        0 | 2019-12-31 04:54:41 | 2019-12-31 04:54:39 |
|      79 |        0 | 2019-12-31 04:55:11 | 2019-12-31 04:55:09 |
|      80 |        8 | 2019-12-31 04:55:10 | 2019-12-31 04:55:10 |
+---------+----------+---------------------+---------------------+

我哪里出错了？

这是我的时间戳格式，以防有人需要它:2019-12-31 04:55:10

P.S:我的sql不支持递归CTE，所以我不得不使用union 24次。

Answer 1

我没有测试数据，但这个冷正是你所需要的:

 select h.hr, count(e.eventID) as cnt
from (
    select 0 hr union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 union all select 21 union all select 22 union all select 23
) h
left join (select * from events e where e.timeStamp > now() - interval 24 hour) e
        on hour(e.timeStamp) = h.hr
group by h.hr

如果

select * from events e where e.timeStamp > now() - interval 24 hour

只为您提供过去 24 小时的记录

如果您想要最后一天的事件:

select h.hr, count(e.eventID) as cnt
from (
    select 0 hr union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 union all select 21 union all select 22 union all select 23
) h
left join (select * from events e where e.timeStamp > cast(now()  as date)) e
        on hour(e.timeStamp) = h.hr
group by h.hr