基于起始时间和结束时间的数据进行小时计数

Question

在表格中，数据以时间戳格式存在，但我以时间（start_at）和时间（end_at）的格式共享它。

表格结构：

id, start_at, end_at
1,  03:00:00, 06:00:00
2,  02:00:00, 05:00:00
3,  01:00:00, 08:00:00
4,  08:00:00, 13:00:00
5,  09:00:00, 21:00:00
6,  13:00:00, 16:00:00
6,  15:00:00, 19:00:00

对于结果，我们需要计算在start_at和end_at时间之间活动的id数量。

hours, count
0,       0
1,       1
2,       2
3,       3
4,       3
5,       2
6,       1
7,       1
8,       1
9,       2
10,      2
11,      2
12,      2
13,      3
14,      2
15,      3
16,      2
17,      2
18,      2
19,      1
20,      1
21,      0
22,      0
23,      0

Answer 1

要么

WITH RECURSIVE
cte AS (
    SELECT 0 `hour`
    UNION ALL
    SELECT `hour` + 1 FROM cte WHERE `hour` < 23
)
SELECT cte.`hour`, COUNT(test.id) `count`
FROM cte 
LEFT JOIN test ON cte.`hour` >= HOUR(test.start_at)
              AND cte.`hour` < HOUR(test.end_at)
GROUP BY 1
ORDER BY 1;

要么

WITH RECURSIVE
cte AS (
    SELECT CAST('00:00:00' AS TIME) `hour`
    UNION ALL
    SELECT `hour` + INTERVAL 1 HOUR FROM cte WHERE `hour` < '23:00:00'
)
SELECT cte.`hour`, COUNT(test.id) `count`
FROM cte 
LEFT JOIN test ON cte.`hour` >= test.start_at 
              AND cte.`hour` < test.end_at 
GROUP BY 1
ORDER BY 1;

第一个查询以时间格式返回小时列，而第二个查询以数值形式返回此列。选择对您来说安全的变体。

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5a77b6e3158be06c7a551cb7e64673de