SQL如何进行按周分组统计_利用DATE_PART或TRUNC函数

SQL按周分组统计：避开跨年“坑”，别让数据“串周”

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

按周统计数据，听起来简单，做起来却是个“暗藏玄机”的活儿。你猜最常见的坑是什么？就是跨年时，去年的第52周和今年的第52周，在数据库眼里被当成了同一周，数据就这么稀里糊涂地混在了一起。今天咱们就来聊聊，在PostgreSQL、Oracle和MySQL这几个主流数据库里，如何干净利落地完成按周分组，确保每一周的数据都“泾渭分明”。

PostgreSQL里用DATE_PART按周分组，注意周起始日和年份边界

PostgreSQL默认遵循ISO标准，将一周视为从周一到周日。但问题在于，它的DATE_PART('week', ...)函数只返回一个1到53的纯数字，压根不提年份。如果直接GROUP BY DATE_PART('week', created_at)，那么2023年的最后一周和2024年的第一周，只要周序号相同，就会被合并统计，这显然不是我们想要的结果。

正确的姿势是什么？必须把年份和周数“绑定”在一起。比如这样：

SELECT
  DATE_PART('year', created_at)::INT AS year,
  DATE_PART('week', created_at)::INT AS week_num,
  COUNT(*)
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;

不过，更稳妥、也更符合国际惯例的写法，是直接使用ISO周格式：TO_CHAR(created_at, 'IYYY-IW')。这个格式符能自动处理恼人的跨年问题。举个例子，2023年12月31日（周一）在ISO标准下属于2024年的第1周，这个函数就会准确地返回“2024-01”。

• 切记别用'YYYY-WW'：这个格式是基于日历年计算周数的，2023-12-31会被标记为“2023-52”，但实际上它已经属于ISO 2024年的第一周了。
• 除了TO_CHAR，DATE_PART('isoyear', ...)和DATE_PART('isoweek', ...)这对组合拳语义更清晰，是TO_CHAR的绝佳替代。
• 如果你的业务偏偏约定周日是一周的开始，那也别慌。只需要在计算前，先将日期减去一天：created_at - INTERVAL '1 day'，然后再取ISO周即可。

Oracle中用TRUNC按周分组，关键是指定'IW'或'WW'

Oracle提供了非常直接的周处理函数：TRUNC。这里的关键在于第二个参数怎么选。用TRUNC(date_col, 'IW')，它会将日期截断到所在周的周一（ISO标准周）。而TRUNC(date_col, 'WW')则是截断到当年1月1日所在周的周日。绝大多数情况下，都应该使用'IW'，否则在跨年边界上，周的定义会出现断裂，导致统计失真。

比如，要统计每周的订单量，可以这么写：

SELECT
  TRUNC(order_date, 'IW') AS week_start,
  COUNT(*)
FROM sales
GROUP BY TRUNC(order_date, 'IW')
ORDER BY 1;

• 这样做的好处是，TRUNC(order_date, 'IW')返回的是一个具体的周一日期，这个值本身就可以用来排序，也方便进行日期范围查询。
• 一定要避开'W'这个参数：它只返回当月内的周序号（1-5），而且每个月都会重置，完全无法用于跨月或跨年的聚合分析。
• 如果前端展示需要“2024-W05”这样的格式，可以用TO_CHAR(order_date, 'YYYY-"W"IW')来转换。

MySQL没有原生ISO周函数，YEARWEEK()参数必须设为1

MySQL的情况有点特殊，它没有原生的ISO周函数。常用的YEARWEEK(date)函数，其默认行为（mode=0）是以周日为周起始日，并且按日历年计算周数。这就意味着，2023年12月31日（周一）会被算作2023年的第52周——这与ISO标准（属于2024年第1周）是冲突的。

所以，在MySQL里进行安全的按周分组，必须显式地传入参数1：即YEARWEEK(date, 1)。这个模式代表周一为起点，并遵循ISO的周规则。

标准的安全写法如下：

SELECT
  YEARWEEK(order_time, 1) AS yw,
  COUNT(*)
FROM t_order
GROUP BY yw
ORDER BY yw;

• 千万不要省略第二个参数：YEARWEEK(order_time)等价于YEARWEEK(order_time, 0)，其结果在跨年时是不可靠的。
• 如果需要把年份和周数拆分开来使用，可以用FLOOR(YEARWEEK(order_time, 1) / 100)取年份，用YEARWEEK(order_time, 1) % 100取周数。
• 另外要注意，MySQL还有个WEEK()函数，它只返回周数而不包含年份，绝对不能单独用于需要跨年分组的场景。

跨数据库兼容写法？别硬套，优先按目标库规范写

最后，聊聊很多人关心的“跨数据库兼容”问题。实话实说，试图写一套在所有数据库里都能通用的“周分组”SQL，往往是费力不讨好。比如，想用EXTRACT(WEEK FROM ...)在PostgreSQL、SQLite和SQL Server之间通用？PostgreSQL支持，SQLite不支持，而SQL Server的DATEPART(week, ...)又是以周日为起点且没有ISO模式。强行抽象，只会埋下难以察觉的Bug。

那怎么办呢？这里有几个更务实的思路：

• 应用层聚合：干脆把原始的日期字段拉到应用程序内存中，利用编程语言本身更完善的日期库（比如Python的dt.isocalendar()）来计算周信息并进行分组。
• 建立中间层：在ETL（数据抽取、转换、加载）阶段，就统一计算好一个周标识字段，例如week_key CHAR(7)，其值为“2024-W05”这样的格式。后续所有分析SQL，直接对这个字段进行分组即可，一劳永逸。
• 最易忽略的时区问题：所有操作前，务必确认你的时间字段已经转换到了业务所在的时区。否则，UTC时间下的“周一”，可能对应你本地时间的周日，统计结果会差之千里。

说到底，处理日期和时间，永远是数据工作中最需要细心和明确业务规则的部分。按周分组虽是小功能，但背后涉及的规则选择，直接决定了数据的准确性与可靠性。