SQL 月度、季度、年度报表 SQL 实战

date_trunc是postgresql中精准分组月度/季度/年度数据的首选函数：月用’date_trunc(‘month’, order_time)’返回当月首日时间戳，季用’quarter’自动对齐季度初，年用’year’保留时间上下文；注意timestamptz需先at time zone转换时区，且group by必须与select表达式严格一致。

怎么用 DATE_TRUNC 快速切分月度/季度/年度数据（PostgreSQL）

PostgreSQL 里最稳的日期分组方式就是 DATE_TRUNC，它直接把时间截断到指定粒度，不用拼字符串、不依赖时区转换逻辑。但很多人一上来就写 DATE_TRUNC('month', created_at)，结果发现 1 月 1 日的数据被归到上一年的 12 月——这是因为 DATE_TRUNC 返回的是「截断后的时间起点」，比如 DATE_TRUNC('month', '2024-01-15'::date) 返回的是 2024-01-01，不是字符串。

• 月度分组：用 DATE_TRUNC('month', order_time)，返回 2024-01-01 00:00:00 这类带时间的 timestamp，排序和分组都可靠
• 季度分组：写 DATE_TRUNC('quarter', order_time)，它会自动对齐到每季度第一天（如 2024-01-01、2024-04-01）
• 年度分组：用 DATE_TRUNC('year', order_time)，别手贱写 EXTRACT(YEAR FROM ...)，后者只返回数字，丢失了时间上下文，没法做范围查询或跨年对比
• 注意时区：如果字段是 timestamptz，DATE_TRUNC 默认按数据库时区处理；想按用户本地时间切分，得先 AT TIME ZONE 'Asia/Shanghai' 再截断

GROUP BY 里混用日期函数容易报错的三种情况

mysql 和 SQL Server 用户常在这儿翻车：SELECT 里写了 YEAR(order_date)，GROUP BY 却只写 order_date，数据库直接报错或结果错乱。核心原则就一条：SELECT 列里所有非聚合字段，必须完整出现在 GROUP BY 中，且表达式要一字不差。

• 错误示范：SELECT YEAR(created_at), count(*) FROM orders GROUP BY created_at → MySQL 8.0+ 会拒绝执行，因为 YEAR(created_at) 和 created_at 不是同一个表达式
• 正确写法：SELECT YEAR(created_at), COUNT(*) FROM orders GROUP BY YEAR(created_at)，或者更推荐用 DATE_FORMAT(created_at, '%Y-%m')（MySQL）保持格式统一
• SQL Server 注意：DATEPART(quarter, order_date) 返回整数，但多个季度可能跨年，光按这个分组会把 2023-Q4 和 2024-Q4 混在一起，得补上 YEAR(order_date)
• 别偷懒用 SELECT * 配合 GROUP BY，尤其在报表场景下，字段多、别名多，极易漏掉隐式依赖的列

按自然月统计时，BETWEEN 和 >= AND 的边界陷阱

写月度报表最常出问题的是时间范围条件。用 BETWEEN '2024-01-01' AND '2024-01-31' 看似合理，但如果 order_time 是 timestamp 类型，而最后一条记录是 2024-01-31 23:59:59.999，就可能因精度丢失被漏掉。

• 安全写法永远是左闭右开：order_time >= '2024-01-01' AND order_time
• 季度同理：order_time >= '2024-01-01' AND order_time ，比算 <code>DATEADD 或拼字符串靠谱得多
• 如果字段是 DATE 类型（无时间部分），BETWEEN 可以用，但建议统一风格，避免团队里有人用有人不用，查 bug 时来回切换理解成本高
• 别信“数据库会自动对齐”，不同引擎对 BETWEEN 的实现细节有差异，PostgreSQL 和 sqlite 处理微秒的方式就不一样

报表 SQL 性能卡在日期函数上？先看索引是否失效

加了 YEAR(created_at) 或 DATE_FORMAT(created_at, '%Y-%m') 后查询变慢，大概率是索引没用上。函数作用于索引字段时，B-tree 索引基本作废，除非你建的是函数索引。

• PostgreSQL 可建函数索引：CREATE INDEX idx_orders_month ON orders (DATE_TRUNC('month', created_at))
• MySQL 8.0+ 支持函数索引，但只限于确定性函数，DATE_FORMAT(created_at, '%Y-%m') 可以，NOW() 就不行
• SQL Server 建计算列索引更稳妥：ALTER table orders ADD order_month AS DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1)，再给这个列建索引
• 最省事的方案：业务写入时就存一个 order_month 字段（值为 '2024-01'），维护成本低，查询快，还方便做分区表

日期函数看着简单，但每个数据库对它的优化策略、精度处理、时区行为都不一样。写报表时别急着堆函数，先确认字段类型、时区设置、索引状态，比调参有用得多。