如何调整SQL语句获取过去3周（周六至周五）的统计数据？

问题分析 #

原SQL的WHERE条件仅限定了单周的时间范围，因此只能返回上周数据。要获取过去3周（周六至周五为一个周期）的数据，需调整时间范围至覆盖3个完整周期，同时修正分组逻辑以匹配周周期统计需求。

修正方案 #

方案1：按每周周期（周六至周五）分组统计 #

如果需要按周维度统计（每个组对应一周的周六到周五），使用以下SQL：

SELECT
    -- 将每条数据映射至所属周的周六作为周起始标识
    DATE_SUB(timestamp, INTERVAL (WEEKDAY(timestamp) + 2) % 7 DAY) AS week_start,
    -- 聚合该周内的所有id（去重后拼接，可根据需求调整）
    GROUP_CONCAT(DISTINCT id) AS ids,
    COUNT(timestamp) AS nametotal
FROM amount_data
WHERE
    -- 时间范围：3周前的周六 至 最近的周五
    timestamp >= DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 7 DAY), INTERVAL 21 DAY)
    AND timestamp <= DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 7 DAY), INTERVAL 1 DAY)
GROUP BY week_start
ORDER BY week_start;

方案2：按每日timestamp分组统计（保留原分组逻辑，扩展时间范围） #

如果需要保留按timestamp（或日期）分组的逻辑，仅扩展时间范围至过去3周，使用以下SQL：

SELECT
    DATE(timestamp) AS date, -- 若timestamp含时分秒，建议转为日期维度分组
    GROUP_CONCAT(DISTINCT id) AS ids,
    COUNT(timestamp) AS nametotal
FROM amount_data
WHERE
    timestamp >= DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 7 DAY), INTERVAL 21 DAY)
    AND timestamp <= DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 7 DAY), INTERVAL 1 DAY)
GROUP BY date
ORDER BY date;

关键逻辑说明 #

1. 时间范围计算：
   • DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) -7 DAY)：计算当前周的周六（DAYOFWEEK中周日为1，周六为7，因此该表达式可得到本周六的日期）。
   • 减去21天得到3周前的周六，减去1天得到最近的周五，从而覆盖过去3周的完整周期。
2. 周分组标识：
   • DATE_SUB(timestamp, INTERVAL (WEEKDAY(timestamp) + 2) %7 DAY)：将任意日期映射至其所在周的周六（WEEKDAY中周一为0，周六为5，通过计算偏移量实现）。
3. id字段处理：
   • 原SQL中直接SELECT id不符合GROUP BY规则（非聚合列），因此使用GROUP_CONCAT(DISTINCT id)聚合该分组内的所有id，若不需要展示id可直接移除该字段。

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