MySQL跨天时间区间数据分组统计查询问题

原代码核心错误 #

• 时段过滤逻辑完全失效：HOUR(sd.timestamp) >= 20 AND HOUR(sd.timestamp) < 4 是恒不成立的判断，时间字段的小时值范围是0-23，不可能同时满足大于等于20、小于4，直接过滤掉了跨夜班次0-4点的所有数据
• 班次归属逻辑硬编码：DATE(sd.timestamp - INTERVAL(LEAST(20, 4)) HOUR) 偏移量固定为4小时，无法适配非跨夜、全天统计的场景
• 递归序列长度写死：硬编码value < 29只能生成30个周期，无法适配周、自定义长度的统计需求
• 周期表排序逻辑错误：period CTE中ORDER BY value DESC会导致周期顺序倒排，关联时容易出现匹配错位

通用修正代码（支持全部三类场景） #

核心思路是把排班规则、统计区间全部参数化，通过生成完整的班次周期表做范围关联，避免硬编码小时判断，同时解决跨夜数据归属问题。

-- 以下参数根据实际统计需求修改即可
SET @stat_start = '2022-06-01 00:00:00'; -- 统计范围起始时间
SET @stat_end = '2022-07-01 00:00:00';   -- 统计范围结束时间
SET @shift_start = 20;                   -- 每日班次开始小时（20即20:00）
SET @shift_end = 4;                      -- 每日班次结束小时（4即次日04:00，非跨夜班次填当日时间即可）
SET @cross_night = @shift_end < @shift_start; -- 自动识别是否为跨夜班次

WITH RECURSIVE seq AS (
    SELECT 0 AS num
    UNION ALL
    SELECT num + 1
    FROM seq
    -- 自动计算需要生成的周期数，跨夜场景多补1天覆盖最后一个班次的凌晨时段
    WHERE num < TIMESTAMPDIFF(DAY, @stat_start, @stat_end) + IF(@cross_night, 1, 0)
),
shift_period AS (
    SELECT
        DATE(@stat_start + INTERVAL num DAY) AS stat_date,
        @stat_start + INTERVAL num DAY + INTERVAL @shift_start HOUR AS period_start,
        @stat_start + INTERVAL num DAY + INTERVAL @shift_end HOUR + INTERVAL IF(@cross_night, 1, 0) DAY AS period_end
    FROM seq
    -- 过滤超出统计范围的无效班次
    HAVING period_start < @stat_end + INTERVAL IF(@cross_night, 1, 0) DAY
)
SELECT
    sp.stat_date,
    SUM(IFNULL(sd.need_calc_field, 0)) AS total_count -- 替换为实际需要聚合的字段
FROM shift_period sp
LEFT JOIN sensor_data sd
    ON sd.timestamp >= sp.period_start
    AND sd.timestamp < sp.period_end
-- 不需要展示无数据的边缘日期可以打开下面的过滤条件
-- WHERE sp.stat_date >= DATE(@stat_start) AND sp.stat_date < DATE(@stat_end)
GROUP BY sp.stat_date
ORDER BY sp.stat_date;

三类典型场景参数配置 #

• 跨夜班次统计（2022年6月1日-7月1日，每日20:00至次日04:00）：
  @stat_start='2022-06-01'、@stat_end='2022-07-01'、@shift_start=20、@shift_end=4
• 非跨夜班次统计（2022年6月1日-6月30日，每日04:00至20:00）：
  @stat_start='2022-06-01'、@stat_end='2022-07-01'、@shift_start=4、@shift_end=20
• 全天统计（2022年6月1日-6月30日，每日00:00至23:59）：
  @stat_start='2022-06-01'、@stat_end='2022-07-01'、@shift_start=0、@shift_end=24

调整说明 #

• 废弃原有的HOUR()函数过滤逻辑，改用班次周期范围匹配，不会漏算跨夜时段数据，同时可以命中timestamp字段的索引，查询性能更高
• 所有规则参数抽离，不需要修改核心查询逻辑，仅调整参数即可适配周、月、任意自定义时间区间，以及跨夜/不跨夜/全天的排班规则
• 递归序列长度自动计算，不需要硬编码天数，跨夜场景自动补全最后一个班次的凌晨时段
• 采用左关联方式聚合，无数据的日期也会返回0值，不会出现日期缺漏

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