按天聚合是什么
时间维度报表的基础操作：把时间戳截断到天或月，再按截断结果分组统计。写法上要先想清楚两件事——时间列上要能利用索引做范围过滤，以及分组键的格式是否与时间顺序一致。用 DATE(created_at) 或 date_trunc('day', created_at) 分组都很常见，但分组键被函数包住后无法直接使用原列索引做分组，所以通常配合 WHERE 先限定时间范围，把参与分组的数据量降下来。
基本信息
| MySQL 按天 | GROUP BY DATE(created_at) |
|---|---|
| PostgreSQL 按天 | GROUP BY date_trunc('day', created_at) |
| 按月 | MySQL 用 DATE_FORMAT(created_at,'%Y-%m')，PostgreSQL 用 date_trunc('month', …) |
| 索引要点 | 先用 WHERE 限定范围，再对结果分组 |
| 缺失日期 | 没有数据的日期不会出现在结果里，需要补零 |
| 时区 | 按用户所在时区统计要先做时区转换再截断 |
典型使用场景
- 日活与订单量趋势：按天统计活动与交易量
- 营收日报：按天汇总金额并与目标对比
- 存留分析：注册日与活跃日都截断到天再计算间隔
- 监控数据汇总：按天聚合指标写进汇总表
- 同比环比：截断到天或月后与上一周期对齐
- 异常排查：按天看指标突变，快速定位问题日期
示例
示例
-- MySQL：按天统计订单数与金额
SELECT DATE(created_at) AS d,
COUNT(*) AS order_cnt,
SUM(amount) AS total_amount
FROM orders
WHERE created_at >= '2024-05-01' # 先用范围过滤，缩小参与分组的数据
AND created_at < '2024-06-01'
GROUP BY DATE(created_at)
ORDER BY d;
-- PostgreSQL：按天
SELECT date_trunc('day', created_at) AS d, COUNT(*) AS order_cnt
FROM orders
WHERE created_at >= '2024-05-01' AND created_at < '2024-06-01'
GROUP BY 1 ORDER BY 1;粒度写法对照
| 粒度 | MySQL | PostgreSQL |
|---|---|---|
| 天 | DATE(created_at) | date_trunc('day', created_at) |
| 小时 | DATE_FORMAT(created_at,'%Y-%m-%d %H:00:00') | date_trunc('hour', created_at) |
| 月 | DATE_FORMAT(created_at,'%Y-%m') | date_trunc('month', created_at) |
| 周 | YEARWEEK 与周一对齐计算 | date_trunc('week', created_at) |
| 年 | YEAR(created_at) | date_trunc('year', created_at) |
缺失日期不会自动补零：按天统计的活动量曲线会出现断点，需要零值时可左连接一张日期维表，用 COALESCE 把 NULL 转成 0。
跨时区业务要统一口径：先按目标时区转换再截断，否则同一天的数据在两端统计结果不同。
不加 WHERE 范围直接按天分组，等于对全表做函数计算与排序，大表上代价很高；报表查询尽量限定最近一个周期。
使用要点
- 先用 WHERE 限定时间范围，再做截断分组
- 分组键格式统一用零填充写法，保证排序正确
- 需要连续日期序列时用日期维表左连接补零
- 报表口径写清楚：按自然日还是按业务日切分
- 按天聚合的结果落到汇总表，避免每次从明细现算
- 上线前用 EXPLAIN 确认 WHERE 上的时间条件走了索引