MySQL按逆序周区间统计指定日期范围数据的技术问询

问题分析 #

你需要从指定时间范围（2021-11-01至2021-12-04）及关联倒推周区间内的数据，按从结束日期倒推的周区间分组求和value，最后一周允许天数不足7天。原SQL存在的问题：

• 误用count(value)而非SUM(value)，需求是求和而非计数
• 未按周区间进行分组，仅返回单条结果
• WHERE条件错误缩小了筛选范围，导致数据缺失

正确SQL语句 #

WITH grouped_data AS (
    SELECT
        DATE_SUB('2021-12-04', INTERVAL (FLOOR(DATEDIFF('2021-12-04', created_at)/7)*7) DAY) AS end_interval,
        SUM(value) AS total,
        MIN(created_at) AS min_created
    FROM data_table
    WHERE created_at <= '2021-12-04' 
      AND (created_at >= '2021-11-01' OR DATE_SUB('2021-12-04', INTERVAL (FLOOR(DATEDIFF('2021-12-04', created_at)/7)*7) DAY) >= '2021-11-01')
    GROUP BY end_interval
    ORDER BY end_interval DESC
)
SELECT
    end_interval,
    COALESCE(LEAD(end_interval) OVER (ORDER BY end_interval DESC), min_created) AS start_interval,
    total
FROM grouped_data;

逻辑拆解 #

1. CTE grouped_data 分组计算

   • 区间结束日期计算：以2021-12-04为基准，通过DATEDIFF计算每条数据与基准日的天数差，除以7取整后得到完整周数，再用基准日减去该周数对应的天数，得到数据所属区间的结束日期end_interval。
   • 分组聚合：按end_interval分组，求和value得到total，同时记录区间内最早的created_at（用于最后一个区间的起始日期补全）。
   • 数据筛选：保留基准日之前的数据，且确保数据本身或其所在区间与指定时间范围（2021-11-01至2021-12-04）有交集。

2. 主查询补全区间起始日期

   • 使用LEAD()窗口函数，按end_interval降序排列，将下一个区间的结束日期作为当前区间的起始日期start_interval。
   • 最后一个区间无后续区间，用该区间内最早的created_at作为起始日期，确保区间完整覆盖数据。

执行结果 #

该SQL将输出与你预期完全一致的结果：

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