SQL中如何按日期字段按天或按月分组聚合？-SQL-PHP中文网

mysql中按天分组用group by date(created_at)，按月推荐group by year(created_at), month(created_at)或year_month(created_at)（8.0+），避免date_format导致索引失效。

sql中如何按日期字段按天或按月分组聚合？

MySQL里用 `DATE()` 或 `YEAR_MONTH()` 提取日期粒度

MySQL没有直接的“按月分组”语法，得靠函数把日期字段转成可分组的标量值。DATE() 会截掉时分秒，保留年月日，适合按天聚合；YEAR_MONTH(dt) 返回类似 202405 的整数，比拼接字符串更安全、可索引（如果字段有前缀索引且覆盖该表达式）。别用 CONCAT(YEAR(dt), '-', MONTH(dt)) —— 字符串比较慢，且无法利用日期索引。

常见错误是写 GROUP BY dt 却期望按天聚合：只要 dt 是 DATETIME 类型，哪怕值都是 2024-05-01 00:00:00 和 2024-05-01 14:22:03，也会被分成两组。

按天聚合：GROUP BY DATE(created_at)
按月聚合：GROUP BY YEAR(created_at), MONTH(created_at) 或 GROUP BY YEAR_MONTH(created_at)
注意：YEAR_MONTH() 在 MySQL 8.0+ 才支持，低版本只能用双字段组合

PostgreSQL 必须用 `DATE_TRUNC()`，不能用 `EXTRACT()` 直接分组

EXTRACT(YEAR FROM created_at) 只返回年份数字，丢失月份和日期信息，没法保证“同月数据归一组”——比如你 GROUP BY EXTRACT(YEAR FROM d), EXTRACT(MONTH FROM d) 看似合理，但 PostgreSQL 要求 SELECT 中所有非聚合字段必须出现在 GROUP BY 子句里，而 EXTRACT 表达式在 SELECT 和 GROUP BY 里写法稍有不同就报错：column "d" must appear in the GROUP BY clause。

正确做法是统一用 DATE_TRUNC('day', created_at) 或 DATE_TRUNC('month', created_at)，它返回一个带时区的 TIMESTAMP（如 '2024-05-01 00:00:00+08'），既可分组又可直接用于 SELECT 显示。

按天：GROUP BY DATE_TRUNC('day', created_at)
按月：GROUP BY DATE_TRUNC('month', created_at)
别名要一致：SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) FROM t GROUP BY DATE_TRUNC('month', created_at)

SQL Server 的 `FORMAT()` 效率低，优先用 `YEAR()`/`MONTH()` 组合

FORMAT(created_at, 'yyyy-MM') 写起来顺手，但它是字符串函数，每次调用都触发隐式转换和格式化开销，大数据量下明显拖慢查询。而且生成的字符串无法利用 created_at 列上的索引。

更优解是用确定性函数组合：YEAR(created_at) * 100 + MONTH(created_at) 得到 202405 这样的整数，或直接 GROUP BY YEAR(created_at), MONTH(created_at)。SQL Server 能识别这种组合为确定性表达式，部分场景下可走索引查找（尤其当有包含索引覆盖这两个字段时）。

避免：GROUP BY FORMAT(created_at, 'yyyy-MM')
推荐：GROUP BY YEAR(created_at), MONTH(created_at)
若需显示为 '2024-05'，在 SELECT 里用 CONVERT(CHAR(7), created_at, 120)（比 FORMAT 快）

跨数据库兼容写法：用日期运算“归零”再分组

如果代码要同时跑在 MySQL、PostgreSQL、SQL Server 上（比如 ORM 动态生成 SQL），硬套各数据库函数容易出错。一个较稳的退路是用算术归零：把日期减去当天/当月偏移，得到标准起始时间。

例如按月分组，核心思路是“把任意日期转成该月第一天的 0 点”。MySQL 和 PostgreSQL 都支持 date - INTERVAL (DAY(date) - 1) DAY，SQL Server 用 DATEFROMPARTS(YEAR(d), MONTH(d), 1)。但最简兼容写法其实是：在应用层先算好范围（如 2024-05-01 到 2024-06-01），然后用 BETWEEN + 循环查——虽然多几次查询，但逻辑清晰、无函数兼容风险。