使用SQL按30分钟间隔统计字符串类型时间列的数据计数

核心实现思路 #

要实现无记录时段计数为0的效果，不能直接对原表分组，必须先生成覆盖全天的完整30分钟时间槽，再关联原表统计：

• 生成指定日期从00:00开始、每30分钟一个的时间槽，全天共48个，覆盖00:00到次日00:00前的全部时段
• 筛选原表中Data = 'a'的记录，将字符串类型的timeAdded转为时间类型，匹配其所属的时间槽
• 用时间槽表左关联原表数据，分组统计每个时间槽内的记录数，无匹配记录的时段自动返回0

可直接运行的代码（MySQL 8.0+版本） #

注意：将代码中的your_table替换为你实际的业务表名即可

WITH RECURSIVE time_slots AS (
    -- 初始化当日第一个30分钟时段起点
    SELECT CAST('2022-01-05T00:00:00.0000' AS DATETIME) AS slot_start
    UNION ALL
    -- 递归生成后续每30分钟的时段，直到覆盖全天
    SELECT DATE_ADD(slot_start, INTERVAL 30 MINUTE)
    FROM time_slots
    WHERE slot_start < CAST('2022-01-05T23:30:00.0000' AS DATETIME)
)
SELECT
    'a' AS Data,
    DATE_FORMAT(slot_start, '%Y-%m-%dT%H:%i:%s.0000') AS Time,
    COUNT(t.Data) AS count
FROM time_slots s
LEFT JOIN your_table t
    ON t.Data = 'a'
    -- 转换timeAdded字符串为时间类型，匹配对应时段
    AND CAST(t.timeAdded AS DATETIME) >= s.slot_start
    AND CAST(t.timeAdded AS DATETIME) < DATE_ADD(s.slot_start, INTERVAL 30 MINUTE)
GROUP BY s.slot_start
ORDER BY s.slot_start;

结果匹配说明 #

对照提供的样例数据，返回结果和预期完全一致：

• 2022-01-05T00:00:00.0000时段：包含3条Data='a'的记录，count返回3
• 2022-01-05T00:30:00.0000到2022-01-05T10:30:00.0000的所有时段：无对应记录，count全返回0
• 2022-01-05T11:00:00.0000时段：包含2条Data='a'的记录，count返回2
• 所有Data='b'的记录会被过滤，不会计入统计结果

跨数据库适配提示 #

• PostgreSQL：将时间转换函数替换为TO_TIMESTAMP(timeAdded, 'YYYY-MM-DD"T"HH24:MI:SS.MS')，时间累加语法替换为slot_start + INTERVAL '30 minute'即可
• 不支持递归CTE的低版本数据库：可以提前创建一张辅助时间维表，预存所有需要统计的30分钟时间槽，再按照相同的左关联逻辑统计即可

内容的提问来源于stack exchange，提问作者Ziyad