SQL中如何统计连续7天的滚动汇总？

真正可靠的连续7天滚动求和需先生成完整日期序列再左连接业务数据，用DATEDIFF确保日期差≤6天，且日期字段必须为DATE类型；PostgreSQL/MySQL 8.0+/SQL Server 2012+可用窗口函数，MySQL 5.7需自连接模拟。

用窗口函数加日期差实现连续7天滚动求和

仅靠SUM() OVER与ROWS BETWEEN 6 PRECEDING AND CURRENT ROW配合，只能依据行数进行滚动，并非基于真实日期滚动。一旦某天没有数据，就会出现遗漏或错位的情况。真正稳妥可靠的方案是：先生成完整的日期序列，接着与业务数据进行左连接，最后运用窗口函数来对齐日期差。

核心思路是把“连续7天”转化为“当前日期减去日期字段 ≤ 6 天”，再分组聚合。但要注意：必须确保每条记录的日期字段是 DATE 类型（不是 DATETIME），否则 DATEDIFF 计算可能因时分秒偏差出错。

• PostgreSQL / MySQL 8.0+ / SQL Server 2012+ 支持标准窗口语法，可直接用 SUM(val) OVER (PARTITION BY user_id ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)，但前提是 dt 每日必有记录
• 若存在日期缺失（比如用户周末不活跃），必须先用 GENERATE_SERIES（PostgreSQL）或递归 CTE（MySQL/SQL Server）补全日期维度
• SQLite 不支持窗口帧子句中的 ROWS BETWEEN 动态偏移，只能用相关子查询或自连接模拟，性能较差

MySQL 5.7 中绕过窗口函数限制的写法

MySQL 5.7 没有 SUM() OVER，也不能用 ROWS BETWEEN，得靠自连接 + 日期范围过滤。关键点在于：连接条件要写成 t1.dt >= DATE_SUB(t2.dt, INTERVAL 6 DAY) AND t1.dt ，而不是反过来——否则会把每个 t2 日作为起点去拉前7天，导致重复计数。

SELECT
t2.dt,
SUM(t1.val) AS rolling_7d_sum
FROM daily_metrics t2
LEFT JOIN daily_metrics t1
ON t1.user_id = t2.user_id
AND t1.dt >= DATE_SUB(t2.dt, INTERVAL 6 DAY)
AND t1.dt 
这个写法在数据量大时容易慢，建议在 (user_id, dt) 上建联合索引；另外注意 dt 字段不能为 NULL，否则 DATE_SUB(NULL, ...) 返回 NULL，整行被过滤掉。
PostgreSQL 中用 GENERATE_SERIES 补日期再聚合
当原始表缺失某些日期（比如节假日无订单），直接窗口函数会跳过这些天，导致滚动和“悬空”。必须先构造一个覆盖全周期的日期序列，再与业务表 LEFT JOIN，才能保证每天都有结果行。
示例中假设想统计 2024-01-01 到 2024-12-31 的每日滚动和：
SELECT
d.dt,
SUM(t.val) FILTER (WHERE t.dt IS NOT NULL) AS rolling_7d_sum
FROM (
SELECT generate_series('2024-01-01'::date, '2024-12-31'::date, '1 day')::date AS dt
) d
LEFT JOIN daily_metrics t
ON t.dt >= d.dt - INTERVAL '6 days'
AND t.dt 
FILTER (WHERE ...) 是 PostgreSQL 特有语法，比用 CASE WHEN 更简洁；如果兼容性要求高，就换成 SUM(CASE WHEN t.dt IS NOT NULL THEN t.val END)。
常见错误：把 DATEDIFF 单位搞错或忽略时区
在SQL Server或旧版MySQL里，DATEDIFF(day, t1.dt, t2.dt)看似能计算日期差，但其实返回的只是两个日期之间的日历天数差，时间部分是不考虑的。打个比方，如果 t1.dt 是 '2024-01-01 23:59:59'，t2.dt 是 '2024-01-02 00:00:01'，那么 DATEDIFF(day, ...) 依旧会返回1，这倒也没什么问题。可是呢，如果用 DATEDIFF(hour, ...) 再除以24，那就可能因为跨天进位而多算一天啦。

• 始终用 CAST(dt AS DATE) 或 DATE(dt) 先归一化时间字段
• 避免在 JOIN 条件里用函数包裹索引字段，比如 DATE(t.dt) = ... 会让索引失效
• 跨时区场景下，统一转成 UTC 再计算，不要依赖数据库服务器本地时区

连续7天滚动汇总真正难的不是写法，而是确认“7天”到底指自然日、工作日，还是按用户行为触发的时间窗——这些语义必须在数据清洗阶段就固化，不能拖到聚合SQL里临时判断。