SQL实现按周或季度动态分组_日期函数的深度应用-SQL-PHP中文网

mysql中week()默认以周日为起点且第1周含1月1日，易致跨年周错乱；应改用week(date,1)或yearweek(date,1)，后者返回202401格式避免年份混淆。

sql实现按周或季度动态分组_日期函数的深度应用

MySQL里用`WEEK()`分组为什么总少一周或多一周

因为WEEK()默认按周日为一周起点，且第1周定义为“包含1月1日的那一周”，和日常说的“周一到周日”或“自然周”不一致。比如2024-01-01是周一，但WEEK('2024-01-01')返回1，而WEEK('2023-12-31')返回53——这天其实属于2024年第1周，但年份还是2023。

实操建议：

用WEEK(date, 1)：第二个参数1表示周一为每周第一天，且第1周必须包含4个及以上周一（ISO标准），更符合业务习惯
别只靠WEEK()分组，得配合YEARWEEK(date, 1)——它返回形如202401的整数，能避免跨年周错乱
如果要“显示为2024-W01”这种格式，用CONCAT(YEAR(date), '-W', LPAD(WEEK(date, 1), 2, '0'))，但注意：跨年日期仍可能归属前一年，所以优先用YEARWEEK()

PostgreSQL中`date_trunc('week')`返回的是周日还是周一

默认是周一。PostgreSQL的date_trunc('week', date)会截断到**该周的周一零点**（时区按当前session设置）。比如date_trunc('week', '2024-01-06'::date)（周六）返回2024-01-01，不是2024-01-07。

常见错误现象：

误以为截断后是周日，结果分组时把周一数据丢进上一周
没考虑时区，服务器用UTC、应用用东八区，导致date_trunc结果偏移一天
对timestamptz字段直接date_trunc('week')，结果按UTC算，而非本地时间

正确做法：显式转时区再截断，例如date_trunc('week', (created_at AT TIME ZONE 'Asia/Shanghai')) AT TIME ZONE 'Asia/Shanghai'

SQL Server里`DATEPART(week, ...)`和`DATEPART(iso_week, ...)`的区别

DATEPART(week, ...)用的是SQL Server默认的周计算规则（由SET DATEFIRST控制，默认7=周日），而DATEPART(iso_week, ...)强制走ISO 8601标准：周一为每周第一天，第1周是包含当年第一个周四的那周。

使用场景：

做BI报表、跨系统对账时，必须用iso_week，否则和MySQL/PostgreSQL的YEARWEEK(..., 1)或PostgreSQL的date_trunc('week')结果不一致
DATEPART(week, ...)在老系统遗留逻辑里可能出现，但别在新需求里用——它依赖会话级设置，不可靠
组合年份要用YEAR(date) + DATEPART(iso_week, date)，但注意：12月最后一周可能属于下一年，所以推荐用FORMAT(date, 'yyyy-MM-dd')配合窗口函数推导ISO周边界

按季度分组时，`QUARTER()`函数在不同数据库里的陷阱

MySQL和PostgreSQL的QUARTER(date)都返回1~4，看起来安全，但问题出在“季度起止时间”的语义模糊上：业务常说的Q1是1月1日到3月31日，但有些分析场景需要按“财务季度”（比如财年从4月开始）或“滚动季度”（最近90天）。

容易踩的坑：

直接GROUP BY QUARTER(date) + YEAR(date)，结果2024-Q1包含2024-01-01 ~ 2024-03-31，但若某条记录是2024-03-31 23:59:59.999，毫秒精度下可能因时区或类型隐式转换被归入下季度
SQL Server没有QUARTER()函数，得写(MONTH(date) + 2) / 3，但整除在T-SQL里是向下取整，MONTH=1时(1+2)/3 = 1，没问题；可一旦date是datetime2且含纳秒，表达式依然有效，但可读性差
真正稳健的做法是生成季度边界：比如MySQL用MAKEDATE(YEAR(date), 1) + INTERVAL (QUARTER(date)-1) QUARTER算起始日，再+ INTERVAL 1 QUARTER - INTERVAL 1 DAY算结束日