SQL中如何实现按周统计的滚动平均_窗口函数日期处理-SQL-PHP中文网

SQL中DATE_TRUNC('week')在PostgreSQL和BigQuery中支持并默认截至周一，但MySQL、SQL Server、SQLite不支持，MariaDB 10.4+仅部分支持且周起始日可能为周日；按周滚动均值须先GROUP BY聚合到周粒度再开窗，避免直接在明细行上用ROWS BETWEEN；需注意ISO周与业务周差异、索引优化及周维度表应含week_start/week_end字段。

sql中如何实现按周统计的滚动平均_窗口函数日期处理

SQL中`DATE_TRUNC('week')`在不同数据库的兼容性问题

PostgreSQL和BigQuery支持DATE_TRUNC('week')直接截取到周一，但MySQL、SQL Server、SQLite不认这个写法，硬用会报错function DATE_TRUNC does not exist或语法错误。MariaDB 10.4+才开始部分支持，且默认周起始日可能是周日而非周一。

实操建议：

PostgreSQL/BigQuery：直接用DATE_TRUNC('week', order_date)，注意BigQuery里要写成DATE_TRUNC(order_date, WEEK(MONDAY))才能确保从周一算起
MySQL：改用DATE_SUB(order_date, INTERVAL WEEKDAY(order_date) DAY)（WEEKDAY()返回0=周一，所以减去天数后得到当周周一）
SQL Server：用DATEADD(DAY, 2-DATEPART(WEEKDAY, order_date), order_date)，再配合SET DATEFIRST 1确保周一为每周第一天，否则结果可能偏移
别依赖YEARWEEK()或WEEK()函数——它们返回的是数字，跨年时（如2024-12-30是2025年第1周）会导致分组错乱

用窗口函数计算滚动周均值时，`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`为什么不对

按周统计的本质是先聚合到“周粒度”，再对周粒度序列做滚动平均。如果直接在原始明细表上套ROWS BETWEEN 2 PRECEDING AND CURRENT ROW，窗口会按原始行序（比如按时间戳排序）滑动，而不是按“周”滑动——可能某一周有100单，下一周只有2单，窗口就卡在行数上，完全偏离业务语义。

正确做法是两步走：

先用GROUP BY把数据聚合成周汇总表（含week_start和weekly_amount）
再在这个汇总表上开窗口：AVG(weekly_amount) OVER (ORDER BY week_start ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
务必确认week_start字段类型是DATE（非TIMESTAMP），否则某些引擎（如Trino）排序可能因毫秒级差异打乱周顺序
若需固定滚动3周（含当前周），不要用RANGE——它依赖值域，而周日期是离散的，RANGE BETWEEN INTERVAL '14' DAY PRECEDING AND CURRENT ROW在跨月/跨年时容易漏周

处理跨年周时`ISO week`与自然周的混淆陷阱

2024年12月30日属于ISO标准的2025年第1周（因为ISO规定包含当年至少4天的周才算该年周），但业务上你很可能希望它归入2024年最后一周统计。混用会导致2024年少一周、2025年多一周，滚动平均曲线突兀断层。

关键判断点：

查清业务定义：“第1周”是指1月1日所在周？还是第一个完整周一至周日？还是ISO标准？
PostgreSQL中TO_CHAR(date, 'IYYY-IW')返回ISO年周，TO_CHAR(date, 'YYYY-WW')返回日历年周——二者在每年开头/结尾最多差2周
MySQL中YEARWEEK(date, 1)（mode=1表示周一为起点、周数从1开始）比YEARWEEK(date, 3)（ISO模式）更贴近常规业务理解
生成周维度表时，别只算“年+周数”，必须带week_start和week_end两个DATE字段，后续JOIN和排序都靠它，不靠字符串拼接

性能瓶颈常出现在窗口函数前的周分组阶段

当原始表超千万行，又没对日期字段建索引，GROUP BY DATE_SUB(order_date, INTERVAL WEEKDAY(order_date) DAY)这类表达式无法走索引，全表计算周起始日再分组，耗时飙升。

提速方案：

在日期列上建函数索引（PostgreSQL/MySQL 8.0.13+）：CREATE INDEX idx_order_week ON orders ((DATE_SUB(order_date, INTERVAL WEEKDAY(order_date) DAY)))
提前物化周字段：ALTER TABLE orders ADD COLUMN order_week DATE; UPDATE orders SET order_week = DATE_SUB(order_date, INTERVAL WEEKDAY(order_date) DAY); 然后对order_week建普通索引
避免在窗口函数中重复计算周：不要写AVG(amount) OVER (ORDER BY DATE_SUB(order_date, INTERVAL WEEKDAY(order_date) DAY) ...)，先算好再开窗
如果只要最近12周滚动均值，加WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 84 DAY)提前过滤，别让窗口函数扛全量数据

最易被忽略的是：周边界计算必须和业务对齐，哪怕多一行WHERE过滤或一个索引，也比后期调窗口逻辑省力得多。