1. 为什么“按月统计数量”是个高频又容易踩坑的需求做数据统计的人几乎都绕不开一个经典场景给一个起始时间和一个结束时间要求把这段时间内每个月的业务数量统计出来。比如订单系统要出月度报表、内容平台要统计每月新增用户、仓储系统要算每月出库单量。表面上看就是一句GROUP BY的事但真正动手写的时候你会发现坑一个接一个月份缺失怎么办跨年怎么处理时区、边界日期、月初月末算哪个月这些问题不解决报表数字对不上业务方追着问你就得加班返工。我自己在多个数据项目里反复做过这类统计从最早的 MySQL 到后来的 PostgreSQL、SQL Server甚至在一些嵌入式数据库里也实现过类似逻辑。每次重写都有新的体会因为不同数据库对日期函数的支持差异很大而“每月数量”这个需求又特别依赖日期处理能力。这篇文章就把我这些年积累的完整思路、可复用的 SQL 模板、以及那些只有踩过坑才知道的细节一次性讲清楚。不管你是刚入行的数据分析师还是写了几年 SQL 的后端开发都能从中找到可以直接抄作业的方案。核心关键词就三个时间区间、按月分组、数量统计。听起来简单但要把它们组合得健壮、高效、可移植需要一套系统的方法。下面我会从整体设计思路开始逐步拆解到具体实现最后给出常见问题的排查清单。2. 整体设计思路与方案选型2.1 先想清楚“月份”到底怎么定义在写任何 SQL 之前必须先和业务方确认一件事你说的“每月”是指自然月还是从起始时间开始每 30 天算一个周期绝大多数场景下都是自然月也就是从每月 1 号到月末。但即便是自然月也有细节要敲定起始时间如果是 1 月 15 号那 1 月这个月算不算通常的做法是只要该月与查询区间有交集就纳入统计数量只计算落在区间内的记录。这个定义直接决定了WHERE条件的写法。另一个容易忽略的点是时区。如果数据库存的是 UTC 时间而业务方看的是本地时间那月初月末的边界就会偏移。我遇到过最典型的问题是某条记录在 UTC 时间是 3 月 1 日 00:30但本地时间是 2 月 28 日 23:30按本地时间应该算 2 月按 UTC 却算 3 月。这种差异在跨月统计时会导致数字对不上。所以第一步永远是确认时间字段的存储时区和业务期望的统计时区是否一致。2.2 两种主流实现路径的取舍实现按月统计业界主要有两种思路。第一种是直接对原始表做分组聚合用日期函数把时间戳截断到月份然后GROUP BY这个月份字段。第二种是先生成一张月份序列再左连接原始数据这样即使某个月没有数据也能在结果里显示为 0。两种方案各有适用场景。直接分组聚合的优点是写法简单、性能好适合数据量大且业务方只关心有数据的月份。缺点是缺失月份不会出现在结果里如果报表需要连续月份展示就得在应用层补全。月份序列左连接的优点是结果完整天然支持补零缺点是写法复杂一些而且需要数据库支持生成序列的函数比如 PostgreSQL 的generate_series、MySQL 8.0 的递归 CTE。我的经验是如果报表要直接给业务方看优先用第二种如果只是中间计算步骤第一种就够了。还有一个折中方案先用第一种查出有数据的月份再用代码或临时表补全缺失月份。这个方案在数据库不支持序列生成时特别实用后面我会给出具体实现。2.3 性能考量索引和函数的关系这里有一个非常关键的优化点很多人会踩坑。假设你的 SQL 写成WHERE DATE_FORMAT(create_time, %Y-%m) BETWEEN 2024-01 AND 2024-12那么即使create_time上有索引数据库也无法使用因为你对字段做了函数运算。正确的做法是把条件写成范围查询WHERE create_time 2024-01-01 AND create_time 2025-01-01。这样索引就能正常生效数据量大时性能差距可能是几十倍。同样在GROUP BY里使用DATE_FORMAT或DATE_TRUNC是没办法避免的但至少要让WHERE条件走索引先把数据范围缩小再做分组。这个原则适用于所有数据库。我在一个千万级订单表上做过测试优化前查询要 8 秒优化后降到 0.3 秒差别就在这一点上。3. 核心细节解析与实操要点3.1 日期截断函数的跨数据库差异不同数据库把时间戳截断到月份的函数完全不同这是写可移植 SQL 最大的障碍。下面这张表是我整理的常用数据库对照建议收藏。数据库截断到月份的函数示例结果MySQLDATE_FORMAT(t, %Y-%m-01)2024-03-01PostgreSQLDATE_TRUNC(month, t)2024-03-01 00:00:00SQL ServerDATEFROMPARTS(YEAR(t), MONTH(t), 1)2024-03-01OracleTRUNC(t, MM)2024-03-01SQLitestrftime(%Y-%m-01, t)2024-03-01MySQL 的DATE_FORMAT返回的是字符串如果你后续要按日期排序字符串格式%Y-%m也能正确排序但如果是%Y-%m-01这种排序也没问题。不过要注意字符串和日期类型在比较时可能有隐式转换建议统一转成日期类型。PostgreSQL 的DATE_TRUNC返回的是 timestamp可以直接用于分组和排序非常方便。还有一个细节MySQL 从 8.0 开始支持DATE_FORMAT的替代方案但为了兼容 5.7我还是习惯用DATE_FORMAT。如果你用的是 MySQL 8.0也可以考虑用DATE(t)配合EXTRACT但写法更啰嗦。3.2 边界条件的精确处理时间区间的边界是另一个高频错误点。假设查询条件是“2024 年 1 月到 2024 年 3 月”那么正确的WHERE应该是WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-04-01 00:00:00注意右边用的是而不是并且结束时间是下个月的 1 号 0 点。这样做的好处是无论时间字段的精度是秒、毫秒还是微秒都不会漏掉 3 月 31 日 23:59:59.999 这样的记录。如果你写成 2024-03-31 23:59:59那么毫秒级的数据就会被漏掉。这个坑我在早期项目中踩过当时数据量小没发现后来数据量大了才暴露出来排查了很久。如果业务方给的结束时间是“2024-03-31”你需要自己把它转换成“2024-04-01”。在 SQL 里可以用DATE_ADD或DATEADD实现但更稳妥的做法是在应用层处理好再传进来避免 SQL 里再做日期运算。3.3 缺失月份补零的三种策略前面提到直接分组聚合不会显示没有数据的月份。补零有三种常见策略我按推荐程度排序。第一种是数据库原生序列生成。PostgreSQL 用generate_seriesMySQL 8.0 用递归 CTESQL Server 用master..spt_values或递归 CTE。这种方式最优雅结果直接在 SQL 里就完整了。缺点是依赖数据库特性迁移时可能要重写。第二种是应用层补全。SQL 只查出有数据的月份然后在代码里生成完整的月份列表用字典或 Map 合并。这种方式最灵活不依赖数据库特性适合多数据库兼容的项目。缺点是代码量稍多而且如果结果集很大内存占用要考虑。第三种是预建月份维度表。在数据仓库里建一张dim_month表存所有月份然后左连接。这种方式在数仓场景下很常见查询性能也好但需要额外的 ETL 维护。对于临时查询或小项目有点杀鸡用牛刀。我个人的选择是如果是 PostgreSQL 或 MySQL 8.0优先用第一种如果是老版本 MySQL 或需要跨库兼容用第二种。下面我会分别给出实现。4. 实操过程与核心环节实现4.1 基础版直接分组聚合先看最基础的写法适用于只需要有数据的月份的场景。以 MySQL 为例SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS cnt FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2025-01-01 00:00:00 GROUP BY DATE_FORMAT(create_time, %Y-%m) ORDER BY month;这段 SQL 的逻辑很直白先用WHERE把时间范围缩小注意这里用的是范围查询能走索引然后用DATE_FORMAT把时间截断到月份按这个分组计数最后按月份排序。结果里只会出现有订单的月份如果 2 月没有订单结果里就没有 2 月这一行。这里有个小优化GROUP BY和SELECT里的DATE_FORMAT表达式重复了MySQL 允许用别名分组但标准 SQL 不允许。为了兼容性我习惯写重复表达式或者用子查询先算出月份字段。子查询的写法在复杂场景下更清晰SELECT month, COUNT(*) AS cnt FROM ( SELECT DATE_FORMAT(create_time, %Y-%m) AS month FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2025-01-01 00:00:00 ) t GROUP BY month ORDER BY month;子查询的缺点是可能影响优化器对索引的使用但在大多数数据库里优化器会做谓词下推性能差异不大。我实测下来在千万级数据上两者差距在 5% 以内所以更推荐子查询写法因为可读性更好。4.2 进阶版PostgreSQL 生成完整月份序列PostgreSQL 的generate_series是补零的神器。完整写法如下WITH months AS ( SELECT TO_CHAR( GENERATE_SERIES( 2024-01-01::date, 2024-12-01::date, 1 month::interval ), YYYY-MM ) AS month ) SELECT m.month, COALESCE(COUNT(o.id), 0) AS cnt FROM months m LEFT JOIN orders o ON TO_CHAR(o.create_time, YYYY-MM) m.month AND o.create_time 2024-01-01 00:00:00 AND o.create_time 2025-01-01 00:00:00 GROUP BY m.month ORDER BY m.month;这里有几个关键点。GENERATE_SERIES生成从 2024 年 1 月到 12 月的每月第一天然后用TO_CHAR转成YYYY-MM格式。左连接时连接条件里也用了TO_CHAR这会导致无法使用索引所以我在连接条件里额外加了时间范围过滤让优化器能先用范围缩小数据。但即便如此TO_CHAR的转换仍然会发生在连接阶段性能不如直接分组。更好的做法是在连接条件里直接用月份的第一天做等值连接而不是字符串。比如WITH months AS ( SELECT GENERATE_SERIES( 2024-01-01::date, 2024-12-01::date, 1 month::interval ) AS month_start ) SELECT TO_CHAR(m.month_start, YYYY-MM) AS month, COALESCE(COUNT(o.id), 0) AS cnt FROM months m LEFT JOIN orders o ON o.create_time m.month_start AND o.create_time m.month_start INTERVAL 1 month GROUP BY m.month_start ORDER BY m.month_start;这个写法的优势是连接条件变成了范围比较如果create_time上有索引PostgreSQL 可以用索引来加速连接。我实测在 500 万行数据上第二种写法比第一种快 3 倍左右。这个技巧值得记住能用范围比较就不要用函数转换。4.3 MySQL 8.0 递归 CTE 实现补零MySQL 8.0 支持递归 CTE可以模拟generate_series。写法如下WITH RECURSIVE months AS ( SELECT 2024-01-01 AS month_start UNION ALL SELECT DATE_ADD(month_start, INTERVAL 1 MONTH) FROM months WHERE month_start 2024-12-01 ) SELECT DATE_FORMAT(m.month_start, %Y-%m) AS month, COUNT(o.id) AS cnt FROM months m LEFT JOIN orders o ON o.create_time m.month_start AND o.create_time DATE_ADD(m.month_start, INTERVAL 1 MONTH) GROUP BY m.month_start ORDER BY m.month_start;递归 CTE 的终止条件要小心WHERE month_start 2024-12-01确保最后生成的是 12 月 1 日不会多生成 2025 年 1 月。如果你把条件写成 2024-12-01就会多出一行 2025 年 1 月导致结果多一个零。这个细节很容易错我第一次写的时候就多了一个月排查了半天。另外MySQL 的递归 CTE 有默认的递归深度限制cte_max_recursion_depth默认是 1000对于月份序列来说完全够用不用担心。4.4 老版本 MySQL 的兼容方案如果用的是 MySQL 5.7 或更早版本没有递归 CTE也没有generate_series怎么办有两个办法。第一个是建一张月份辅助表一次性插入足够多年的月份然后左连接。这个方案最简单也最稳定。建表和插入的 SQL 如下CREATE TABLE dim_month ( month_start DATE PRIMARY KEY ); INSERT INTO dim_month (month_start) VALUES (2020-01-01), (2020-02-01), (2020-03-01), -- 省略中间月份 (2030-12-01);实际使用时可以用存储过程或脚本批量生成不用手写。有了这张表查询就变成SELECT DATE_FORMAT(m.month_start, %Y-%m) AS month, COUNT(o.id) AS cnt FROM dim_month m LEFT JOIN orders o ON o.create_time m.month_start AND o.create_time DATE_ADD(m.month_start, INTERVAL 1 MONTH) WHERE m.month_start 2024-01-01 AND m.month_start 2025-01-01 GROUP BY m.month_start ORDER BY m.month_start;这个方案的好处是查询性能极好因为dim_month表很小而且month_start是主键连接效率高。缺点是需要维护这张表如果年份范围不够了要记得扩展。我一般会一次性生成 50 年的月份基本一劳永逸。第二个办法是在应用层补全SQL 只查有数据的月份代码里生成完整月份列表再合并。这个方案我在 Python 和 Java 项目里都用过代码量不大而且不依赖数据库特性。以 Python 为例from datetime import datetime, timedelta def fill_months(start, end, data): result [] current start while current end: month_str current.strftime(%Y-%m) result.append({ month: month_str, cnt: data.get(month_str, 0) }) # 移动到下个月 if current.month 12: current current.replace(yearcurrent.year 1, month1) else: current current.replace(monthcurrent.month 1) return result这个函数接收起始月份、结束月份和 SQL 查出的字典返回补全后的列表。逻辑简单但要注意跨年时的月份递增上面代码里已经处理了。这种方案特别适合报表接口因为接口层本来就要做数据格式化顺手补全很方便。5. 常见问题与排查技巧实录5.1 月份数字对不上怎么快速定位这是最常见的问题业务方说“3 月的数字不对”你一看 SQL 觉得没问题。排查这类问题我有一套固定流程。第一步确认时间字段的时区。用SELECT global.time_zone, session.time_zone查数据库时区再对比业务期望时区。如果不一致要么在 SQL 里转换要么在应用层转换。第二步检查边界条件。把WHERE条件单独拿出来查一下区间内总共有多少条记录再和分组后的总和对比。如果总和对不上说明有记录被漏掉或重复计算。第三步抽查具体记录。找几条边界附近的记录看看它们被分到了哪个月是否符合预期。我遇到过一个典型案例某条记录的时间是2024-03-31 23:59:59但数据库存的是毫秒精度实际值是2024-03-31 23:59:59.500。如果WHERE条件写成 2024-03-31 23:59:59这条记录就被漏掉了。改成 2024-04-01后问题解决。这个坑我在不同项目里见过至少三次每次都是边界条件写错。5.2 性能突然变慢的排查思路按月统计的查询如果数据量大性能问题很常见。排查性能问题我一般先看执行计划。MySQL 用EXPLAINPostgreSQL 用EXPLAIN ANALYZE。重点看type列MySQL或Node TypePostgreSQL如果出现ALL或Seq Scan说明走了全表扫描索引没生效。这时候检查WHERE条件是否对字段做了函数运算如果有改成范围查询。另一个常见原因是GROUP BY的字段没有索引。虽然分组本身可以用临时表或排序实现但如果分组字段有索引数据库可以直接利用索引的有序性来加速。对于按月统计分组字段是计算出来的月份没法直接建索引。但可以通过建函数索引MySQL 8.0 支持或表达式索引PostgreSQL 支持来优化。比如 PostgreSQL 可以建CREATE INDEX idx_orders_month ON orders (DATE_TRUNC(month, create_time));这样分组时就能用上索引。不过函数索引会增加写入开销要根据读写比例权衡。5.3 跨年查询的月份排序问题如果查询区间跨年比如从 2023 年 11 月到 2024 年 2 月按%Y-%m格式排序是正确的因为年份在前。但如果只按%m排序就会出现 11、12、01、02 这样的顺序明显不对。所以永远要用%Y-%m或完整的日期来排序。这个错误在新手里很常见我早期也犯过后来养成习惯只要涉及月份排序一定带上年份。还有一个隐藏问题如果月份字段是字符串类型排序是按字典序%Y-%m格式下字典序和日期序一致所以没问题。但如果格式是%m-%Y字典序就乱了。所以格式选择也很重要推荐%Y-%m或%Y%m。5.4 常见问题速查表问题现象可能原因排查方法解决方案某月数字为 0 但实际有数据时区不一致查数据库时区和业务时区统一时区或在 SQL 中转换边界日期记录丢失结束条件用了检查WHERE条件改用下月第一天查询慢索引未生效看执行计划避免对字段做函数运算月份缺失直接分组聚合检查是否有补零逻辑用序列生成或应用层补全跨年排序错乱只按月份排序检查ORDER BY用%Y-%m格式排序递归 CTE 多出一行终止条件用了检查递归终止条件改用这张表建议保存下次遇到问题直接对照排查能省不少时间。5.5 几个只有踩过坑才知道的细节第一个细节COUNT(*)和COUNT(字段)在左连接补零时的区别。左连接时如果某个月没有匹配的记录COUNT(*)会返回 1因为左表有一行而COUNT(o.id)会返回 0。所以补零场景下必须用COUNT(o.id)或COUNT(具体字段)不能用COUNT(*)。这个坑我踩过当时结果里所有缺失月份都显示 1排查了好久才发现是COUNT(*)的问题。第二个细节如果时间字段允许为 NULLWHERE条件会自动过滤掉 NULL 记录这通常是期望行为。但如果业务方要求把 NULL 也算到某个月就需要额外处理比如用COALESCE给个默认时间。这种情况很少见但遇到了要知道怎么处理。第三个细节在大数据量下LEFT JOIN加范围条件可能导致性能问题因为每一行左表都要去右表做范围扫描。优化方法是先用子查询把右表按月份聚合好再左连接。这样右表的数据量从原始行数降到月份数连接效率大幅提升。写法如下WITH monthly AS ( SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS cnt FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2025-01-01 00:00:00 GROUP BY DATE_FORMAT(create_time, %Y-%m) ) SELECT m.month, COALESCE(mo.cnt, 0) AS cnt FROM months m LEFT JOIN monthly mo ON m.month mo.month ORDER BY m.month;这个写法把聚合下推到了子查询里外层只做简单的等值连接性能最好。我在千万级数据上测试比直接左连接快 5 倍以上。这个优化技巧值得记住先聚合再连接永远比先连接再聚合快。6. 不同业务场景下的变体实现6.1 按用户分组再按月统计有时候需求不是全局按月统计而是每个用户每月统计。比如统计每个用户每月的下单次数。这时候分组字段变成两个用户 ID 和月份。写法如下SELECT user_id, DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS cnt FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2025-01-01 00:00:00 GROUP BY user_id, DATE_FORMAT(create_time, %Y-%m) ORDER BY user_id, month;这个查询的结果里每个用户只会有有数据的月份。如果业务方要求每个用户都显示完整的 12 个月补零逻辑就复杂一些需要先查出所有用户再和月份序列做笛卡尔积然后左连接数据。这个场景在用户行为分析里很常见但实现起来要小心数据量膨胀。如果用户有 10 万月份有 12 个笛卡尔积就是 120 万行再左连接原始数据性能压力不小。这时候建议在应用层做补全或者只对活跃用户做补全。6.2 统计每月去重数量如果需求是“每月有多少个不同的用户下单”而不是总订单数就要用COUNT(DISTINCT user_id)。写法如下SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(DISTINCT user_id) AS user_cnt FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2025-01-01 00:00:00 GROUP BY DATE_FORMAT(create_time, %Y-%m) ORDER BY month;COUNT(DISTINCT)的性能比COUNT(*)差很多因为要去重。如果数据量大可以考虑用近似去重函数比如 PostgreSQL 的approx_count_distinct或 MySQL 的APPROX_COUNT_DISTINCT如果支持。但近似函数有误差要看业务方能否接受。我一般先问清楚精度要求如果要求精确就老老实实用COUNT(DISTINCT)同时确保WHERE条件走索引尽量减少扫描行数。6.3 按月统计金额总和数量统计换成金额统计逻辑一样只是把COUNT换成SUM。但要注意金额字段可能有 NULLSUM会自动忽略 NULL如果所有记录都是 NULL结果是 NULL 而不是 0。补零时要用COALESCE(SUM(amount), 0)。写法如下SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COALESCE(SUM(amount), 0) AS total_amount FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2025-01-01 00:00:00 GROUP BY DATE_FORMAT(create_time, %Y-%m) ORDER BY month;金额统计还有一个精度问题。如果金额字段是浮点数累加时可能有精度误差。建议用DECIMAL类型存储金额SUM的结果也是精确的。如果已经是浮点数可以在SUM外层用ROUND保留两位小数但要注意ROUND是四舍五入可能和业务方的期望有差异。这个细节在财务相关统计里特别重要一定要和业务方确认清楚。7. 我个人的实操心得与建议做了这么多年的数据统计我最大的体会是SQL 写得好不好一半看逻辑一半看对业务的理解。按月统计这个需求逻辑本身不复杂但业务上的细节特别多。比如“每月”的定义、时区的处理、边界日期的归属、缺失月份是否补零这些都不是 SQL 语法问题而是业务问题。我见过很多技术很强的人SQL 写得飞快但结果总是和业务方对不上原因就是没把业务细节问清楚。所以我的建议是动手写 SQL 之前先花十分钟和业务方确认这几件事时间字段的时区是什么统计区间是闭区间还是半开区间缺失月份要不要显示为 0数量是去重还是不去重金额要不要考虑精度把这几个问题问清楚后面能省掉大量返工时间。另一个心得是永远不要相信“这个查询很简单”。我早期接过一个需求业务方说“就是按月统计一下订单数”我十分钟写完交付结果被打回来三次。第一次是时区问题第二次是缺失月份没补零第三次是边界日期漏了记录。后来我养成了习惯任何统计需求都先写测试用例用几条边界数据验证确认无误再交付。这个习惯让我后来的返工率降低了 80% 以上。最后分享一个小技巧如果你不确定自己的 SQL 是否正确可以先用小范围数据手动算一遍。比如取 1 月到 3 月的数据手动数一下每个月的记录数再和 SQL 结果对比。这个方法虽然笨但最可靠。我在排查复杂问题时经常用尤其是涉及多个连接和分组的时候手动验证能快速定位问题所在。这个内容后续还可以这样扩展如果你用的是数据仓库比如 Hive 或 Spark SQL日期函数的写法又不一样但核心思路是一样的。另外如果统计维度更多比如按地区、按品类再按月就是多维分组原理相同只是分组字段更多。掌握了这个基础模式再复杂的统计需求都能拆解成类似的步骤。
