SQL如何按小时/日/周等时间范围使用group by分组统计

需求背景 #

需要从stats表完成检出数据统计，具体要求如下：

• 分类维度：统计汽车、摩托车、公交车等所有detection_class的检出数量
• 粒度支持：可按小时、日、周固定时间粒度拆分结果
• 范围限定：仅统计指定start_time到end_time区间内的检出数据

原有实现问题 #

原有SQL仅支持全时间范围按检测类别统计总检出量，逻辑为内层按track_id去重，外层按detection_class分组计数，代码如下：

SELECT detection_class, count(*) 
FROM (
      SELECT detection_class 
      FROM stats 
      where  source_id = "6d10586f61f64d71b1467a79093974ff" 
             AND detection_time <=end_time 
             AND detection_time >= start_time 
       group by track_id) as count 
 group by detection_class

该写法无法按时间周期拆分统计结果，需要调整分组逻辑。

改造后SQL（支持多粒度切换） #

核心调整逻辑：在内层去重时，先根据目标统计粒度将detection_time格式化为统一的时间分组桶，外层同时按时间桶、检测类别两个维度分组计数即可。

以下以MySQL语法为例，支持通过参数切换统计粒度：

-- 可直接替换粒度格式化规则实现不同周期统计：
-- 1. 按小时统计：DATE_FORMAT(detection_time, '%Y-%m-%d %H:00:00')
-- 2. 按日统计：DATE_FORMAT(detection_time, '%Y-%m-%d')
-- 3. 按周统计（周一为周起始）：DATE_FORMAT(detection_time, '%Y-%u')
SELECT 
    time_bucket,
    detection_class,
    count(*) AS detect_count
FROM (
    SELECT 
        detection_class,
        -- 替换下面一行的格式化规则即可切换统计粒度
        DATE_FORMAT(detection_time, '%Y-%m-%d %H:00:00') AS time_bucket
    FROM stats 
    WHERE source_id = "6d10586f61f64d71b1467a79093974ff" 
          AND detection_time >= '传入的start_time参数'
          AND detection_time <= '传入的end_time参数'
    -- 若需要按单时间粒度内的track_id去重，将GROUP BY改为 GROUP BY time_bucket, track_id
    GROUP BY track_id
) AS t
GROUP BY time_bucket, detection_class
ORDER BY time_bucket ASC, detection_class ASC;

若使用PostgreSQL数据库，时间桶生成逻辑可替换为date_trunc('粒度单位', detection_time) AS time_bucket，粒度单位直接传'hour'/'day'/'week'即可，无需手动编写格式化字符串。

注意事项 #

• 原SQL全局按track_id去重的逻辑下，若同一个track_id跨多个时间周期出现，会被计入该track_id首次被检出的时间桶；如果业务要求每个时间周期内的track_id单独去重统计，修改内层GROUP BY字段即可。
• 时间范围过滤条件建议将detection_time >= start_time写在前面，若detection_time字段建有索引，可大幅提升大时间范围查询的效率。

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