1. 项目概述：从业务报表到SQL思维的跃迁

在数据驱动的业务场景里，我们经常需要面对一类经典的报表需求：按月统计某个指标，并且不仅要看到每个月的独立数值，还要看到从起始月到当前月的累计值。比如，统计每个月的销售额，同时生成“截至本月累计销售额”；或者分析用户新增数量，并计算“年度累计新增用户数”。这种“月度统计+逐月累加”的需求，在财务分析、用户增长、运营监控等领域几乎无处不在。

乍一看，这似乎是个简单的分组统计问题，用个GROUP BY MONTH(date)就能搞定。但当你真正动手在MySQL里实现时，会发现里面有不少门道。不同的写法在逻辑清晰度、执行效率、可维护性以及应对复杂场景的能力上，有着显著的差异。有些写法虽然直观，但性能堪忧，数据量一大就慢如蜗牛；有些写法利用了高级特性，简洁高效，但对SQL功底要求较高；还有些写法则是在特定业务约束下的巧妙变通。

今天，我们就来彻底拆解这个高频需求。我将结合自己多年在数据仓库和业务系统开发中处理类似问题的经验，为你梳理出几种主流的实现方案。我们不会停留在简单的语法展示，而是会深入每种方法背后的执行逻辑、性能瓶颈和适用场景，并分享一些实际踩坑后总结出来的优化技巧。无论你是正在为月度报表发愁的开发者，还是希望提升SQL解决问题能力的数据分析师，这篇文章都能给你提供可以直接“抄作业”的实战代码和避坑指南。

2. 核心场景与需求深度解析

在深入代码之前，我们必须先厘清需求背后的业务逻辑和潜在的技术挑战。这有助于我们理解为什么一种写法比另一种更好，以及在什么情况下该选择哪种方案。

2.1 典型业务场景枚举

“按月统计并累加”的需求绝非千篇一律，细微的业务差异会导致实现逻辑的巨大不同。

场景一：财务流水累计这是最经典的场景。假设有一张sales表，记录每一笔订单的日期和金额。业务需要一份报表，展示2023年每个月的销售额，以及从2023年1月开始到当月的累计销售总额。这里的关键是，累计是跨月份的连续累加。

场景二：用户存量累计有一张users表，created_at记录注册时间。需要统计每月新增用户数，并计算“截至当月底的总用户数”。注意，这里的累计是“存量”概念，即到该月为止历史上所有注册的用户总和，用户不会减少（不考虑注销）。这与财务累计类似，但数据模型更简单。

场景三：消耗或递减类累计例如，记录用户每月积分消耗的points_consumption表。需要按月统计消耗积分，并计算“年度累计消耗”。虽然也是累加，但业务含义是“消耗”，累计值会一直增长。

场景四：带状态或分类的累计复杂场景来了。比如orders表，有订单日期、金额和状态（如‘已完成’、‘已取消’）。业务需要统计“每月完成的订单金额”及其累计值。这意味着过滤（WHERE status = 'completed'）和累加需要协同工作。更复杂的可能是按地区、产品线等多维度进行分组累加。

2.2 技术需求与挑战拆解

基于以上场景，我们可以抽象出共同的技术需求点：

1. 时间维度聚合：核心是按月（YEAR(date), MONTH(date)或DATE_FORMAT(date, ‘%Y-%m’)）进行分组（GROUP BY）。
2. 跨行计算：累加的本质是当前行与之前所有行的聚合值进行求和。这是一个典型的“窗口计算”或“行间计算”问题，需要SQL能够访问当前分组之外（之前）的数据。
3. 排序保证：累计计算严重依赖于时间顺序。如果月份顺序错乱，累加结果将毫无意义。因此，结果集必须严格按照年月升序排列。
4. 效率与性能：当源表数据量巨大（百万、千万级）时，不同的实现方式性能差异可达数量级。我们需要关注索引利用、中间结果集大小、是否产生重复计算等问题。
5. SQL可读性与维护性：代码是写给人看的。过于晦涩的技巧虽然可能高效，但会给后续维护者带来困难。需要在优雅和高效之间取得平衡。

理解了这些，我们就可以带着明确的目标去评估接下来的每一种写法：它是否能清晰、正确、高效地满足这些核心需求？

3. 方案一：自连接与子查询——最直观的“暴力破解”法

这是很多SQL初学者最容易想到的思路，符合人类最直接的思维模式：要计算某个月的累计值，那我就把之前所有月份的数据都找出来，再加一遍。

3.1 基础自连接写法

思路是让每个月的数据行（我们称之为主表a），去关联所有日期小于等于它的数据行（副表b），然后对副表b的统计值进行求和。

SELECT YEAR(a.order_date) as report_year, MONTH(a.order_date) as report_month, DATE_FORMAT(a.order_date, ‘%Y-%m’) as year_month, SUM(a.amount) as monthly_amount, SUM(b.amount) as cumulative_amount FROM sales a LEFT JOIN sales b ON YEAR(b.order_date) = YEAR(a.order_date) AND MONTH(b.order_date) <= MONTH(a.order_date) -- 如果需要按年分开累计，还需加上年份相等条件 -- AND YEAR(b.order_date) = YEAR(a.order_date) WHERE a.order_date >= ‘2023-01-01’ AND a.order_date < ‘2024-01-01’ GROUP BY YEAR(a.order_date), MONTH(a.order_date), DATE_FORMAT(a.order_date, ‘%Y-%m’) ORDER BY report_year, report_month;

原理解析： 这个查询创建了一个笛卡尔积的变体。对于主表a中的每一行（代表一个聚合后的月份），它会连接副表b中所有年份相同且月份小于等于它的行。在聚合时，SUM(a.amount)只对当前月份的数据进行求和（因为GROUP BY了a的日期），得到当月值。而SUM(b.amount)则对连接后所有b表的数据（即当前月及之前所有月的数据）进行求和，得到累计值。

注意：这里有一个关键细节，a和b都来自同一张表，且在JOIN前没有分组。这意味着如果sales表原始数据是订单级别的，那么连接操作会产生巨大的中间结果集（数据量的平方级膨胀），性能灾难的根源就在于此。

3.2 优化版：基于子查询预聚合

为了缓解性能问题，一个重要的优化是先对数据进行按月预聚合，然后在聚合结果上进行自连接。这样中间表的数据量就从订单行数变成了月份数（最多12行/年），性能提升立竿见影。

WITH monthly_sales AS ( SELECT YEAR(order_date) as yr, MONTH(order_date) as mon, DATE_FORMAT(order_date, ‘%Y-%m’) as year_month, SUM(amount) as mth_amount FROM sales WHERE order_date >= ‘2023-01-01’ AND order_date < ‘2024-01-01’ GROUP BY yr, mon, year_month ) SELECT a.yr, a.mon, a.year_month, a.mth_amount as monthly_amount, SUM(b.mth_amount) as cumulative_amount FROM monthly_sales a LEFT JOIN monthly_sales b ON b.yr = a.yr AND b.mon <= a.mon GROUP BY a.yr, a.mon, a.year_month, a.mth_amount ORDER BY a.yr, a.mon;

实操心得：

1. 务必使用CTE或子查询先聚合：这是我踩过最大的坑。直接在原始明细表上做自连接，一旦数据超过几万行，查询基本会超时。先GROUP BY月，将数据压缩到几十行，后续连接的成本几乎可以忽略不计。
2. 连接条件要小心：示例中使用了ON b.yr = a.yr AND b.mon <= a.mon。这实现了“按年独立累计”。如果你需要跨年连续累计（例如从2023年1月累计到2024年12月），连接条件需要转换为一个可比较的连续值，比如ON CONCAT(b.yr, LPAD(b.mon, 2, ‘0’)) <= CONCAT(a.yr, LPAD(a.mon, 2, ‘0’))。但更推荐使用日期字段本身比较，逻辑更清晰。
3. 索引是关键：在sales.order_date和sales.amount上建立复合索引，能极大加速预聚合子查询的速度。

方案一总结：

• 优点：逻辑非常直观，易于理解和解释，几乎所有版本的MySQL都支持。
• 缺点：即使经过优化，自连接仍然是一种“重量级”操作，尤其是在需要复杂条件或多维累加时，SQL语句会变得冗长且难以维护。
• 适用场景：数据量不大、对SQL版本无要求（如老旧系统）、需要快速写一个一次性查询的临时分析。

4. 方案二：用户变量——MySQL的“过程化”技巧

在MySQL 8.0引入窗口函数之前，用户变量（@var）是实现累加等高级计算的神器。它模拟了过程化编程中“变量”的概念，在结果集生成过程中逐行计算。

4.1 基础用户变量写法

其核心思想是，在查询过程中，用一个变量来保存上一行的累计值，并在当前行进行更新。

SELECT year_month, monthly_amount, (@cumulative := @cumulative + monthly_amount) AS cumulative_amount FROM ( SELECT DATE_FORMAT(order_date, ‘%Y-%m’) AS year_month, SUM(amount) AS monthly_amount FROM sales WHERE order_date >= ‘2023-01-01’ AND order_date < ‘2024-01-01’ GROUP BY year_month ORDER BY year_month -- 排序至关重要！ ) AS monthly_summary CROSS JOIN (SELECT @cumulative := 0) AS vars ORDER BY year_month;

原理解析：

1. 子查询monthly_summary先完成按月聚合，并必须按year_month排序。
2. CROSS JOIN (SELECT @cumulative := 0) AS vars初始化一个用户变量@cumulative为0。CROSS JOIN确保主查询的每一行都能访问到这个初始化后的变量。
3. 在主查询的SELECT列表中，(@cumulative := @cumulative + monthly_amount)会按行执行。对于第一行，@cumulative初始为0，加上第一行的monthly_amount，结果赋值给@cumulative并作为cumulative_amount输出。第二行时，@cumulative已经是第一行的累计值了，如此递推，实现累加。

4.2 处理多分组累计（如按年）

如果需要每年重新开始累计，写法会复杂一些，需要引入变量来记录和判断分组边界。

SELECT yr, mon, year_month, monthly_amount, cumulative_amount FROM ( SELECT yr, mon, year_month, monthly_amount, @cumulative := IF(@current_year = yr, @cumulative + monthly_amount, monthly_amount) AS cumulative_amount, @current_year := yr AS dummy_set_year FROM ( SELECT YEAR(order_date) as yr, MONTH(order_date) as mon, DATE_FORMAT(order_date, ‘%Y-%m’) as year_month, SUM(amount) as monthly_amount FROM sales WHERE order_date >= ‘2023-01-01’ GROUP BY yr, mon, year_month ORDER BY yr, mon ) AS m, (SELECT @cumulative := 0, @current_year := NULL) AS vars ) AS result;

注意事项与避坑指南：

1. 执行顺序的“黑盒”：用户变量的求值顺序依赖于MySQL优化器选择的执行计划，这在复杂查询中是不确定的。上述写法在大多数简单场景下有效，但并不能100%保证。MySQL官方文档也指出，在SELECT列表中同时使用和赋值用户变量，其顺序是不被保证的。
2. 排序是生命线：必须确保内层子查询的结果是按照累加维度（如年月）严格排序的。任何排序错误都会导致累计结果完全错乱。
3. 初始化位置：变量的初始化(SELECT @cumulative := 0)必须通过CROSS JOIN或子查询嵌入，确保在主要数据处理前完成。不能直接写在WHERE后面。
4. 避免在WHERE或GROUP BY中使用：在WHERE或GROUP BY子句中依赖用户变量的值，结果极不可预测。
5. 可读性差：对于不熟悉这种技巧的开发者，这段SQL如同天书，调试和维护成本高。

方案二总结：

• 优点：在MySQL 5.x时代，这是实现累加最高效的方法之一，通常比自连接性能更好，因为只需要一次扫描。
• 缺点：语法晦涩，逻辑依赖于执行顺序（有风险），可维护性差，官方不推荐用于关键业务逻辑。
• 适用场景：MySQL 8.0以下版本，对性能有较高要求，且查询逻辑相对简单的场景。对于新的关键业务，强烈不推荐使用。

5. 方案三：窗口函数——现代SQL的优雅解法

MySQL 8.0终于迎来了窗口函数这个强大的特性。它专门用于处理“相对于行的计算”，完美契合累计求和的需求。代码简洁、逻辑清晰、性能优异，是目前的首选方案。

5.1SUM() OVER()基础用法

窗口函数的核心是OVER()子句，它定义了一个“窗口”，函数在这个窗口上进行计算。

SELECT DATE_FORMAT(order_date, ‘%Y-%m’) AS year_month, SUM(amount) AS monthly_amount, SUM(SUM(amount)) OVER ( ORDER BY DATE_FORMAT(order_date, ‘%Y-%m’) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM sales WHERE order_date >= ‘2023-01-01’ AND order_date < ‘2024-01-01’ GROUP BY year_month ORDER BY year_month;

原理解析：

1. SUM(amount)是普通的聚合函数，与GROUP BY配合，计算出每个月的销售额monthly_amount。
2. SUM(SUM(amount)) OVER(...)是精髓所在。外层的SUM()是一个窗口聚合函数。OVER子句定义了窗口范围：
   • ORDER BY ...：指定了行之间的顺序，这是累计计算的基础。
   • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：指定了窗口的框架。UNBOUNDED PRECEDING表示从结果集的第一行开始，CURRENT ROW表示到当前行结束。这个框架定义了一个动态扩大的范围，从第一行到当前行，窗口函数就在这个范围内进行求和。
3. 因此，对于每一行（每个月），SUM(SUM(amount))计算的是从第一行到该行所有monthly_amount的总和，即累计值。

5.2 更简洁的写法与分区累计

实际上，对于“从开头到当前行”这种最常用的框架，可以省略ROWS BETWEEN子句，因为它是ORDER BY的默认框架（如果ORDER BY存在且未指定框架，则默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，对于整数排序，其效果与ROWS类似）。

SELECT YEAR(order_date) as report_year, MONTH(order_date) as report_month, SUM(amount) AS monthly_amount, SUM(SUM(amount)) OVER ( PARTITION BY YEAR(order_date) ORDER BY YEAR(order_date), MONTH(order_date) ) AS cumulative_amount_in_year FROM sales WHERE order_date >= ‘2023-01-01’ GROUP BY report_year, report_month ORDER BY report_year, report_month;

这里引入了PARTITION BY关键字。它的作用是将数据先按指定字段（如YEAR(order_date)）分成不同的区（partition），窗口函数（累计求和）会在每个分区内独立进行。这就轻松实现了“按年重新累计”的需求。

窗口函数方案的优势与细节：

1. 性能：现代数据库对窗口函数有深度优化。它通常只需要对数据扫描一次或有限次数，避免了自连接产生的巨大中间表，性能远超前两种方案，尤其是在大数据量下。
2. 清晰性：语义明确，SUM() OVER(ORDER BY ...)一看就知道是顺序累加，PARTITION BY清晰表达了分组逻辑。
3. 灵活性：窗口函数功能强大，除了SUM，还有ROW_NUMBER(),RANK(),AVG(),LEAD()/LAG()等，可以轻松解决一系列复杂的行间计算问题。
4. 执行顺序：窗口函数是在WHERE,GROUP BY,HAVING之后执行的，因此可以直接使用GROUP BY后的聚合结果（如SUM(amount)）进行窗口计算，非常符合直觉。

方案三总结：

• 优点：语法简洁优雅，逻辑清晰易懂，执行性能高，功能强大且灵活。
• 缺点：要求MySQL版本在8.0及以上。对于更复杂的滑动窗口或动态框架，学习曲线稍陡。
• 适用场景：只要你的MySQL是8.0+，这就是解决累计求和问题的标准答案和最佳实践。无论是简单累计还是复杂的分区、多维度累计，都应优先考虑窗口函数。

6. 方案四：应用层累加——灵活性的最后防线

有时候，我们可能受限于数据库版本（如无法使用窗口函数），或者累加逻辑极其复杂，掺杂了大量业务判断，用纯SQL实现变得非常困难且低效。这时，将“聚合”和“累加”分离，把累加逻辑放到应用程序代码中，也不失为一种务实的选择。

6.1 实现思路与示例（以Python为例）

思路很简单：

1. 用SQL高效地完成按月分组聚合，并确保结果按时间排序。
2. 将排序后的结果集（通常是列表或数组）返回给应用程序。
3. 在应用程序的内存中，遍历这个有序列表，手动计算累计值。

SQL部分（极简）：

SELECT DATE_FORMAT(order_date, ‘%Y-%m’) AS year_month, SUM(amount) AS monthly_amount FROM sales WHERE order_date >= ‘2023-01-01’ AND order_date < ‘2024-01-01’ GROUP BY year_month ORDER BY year_month;

Python应用程序部分：

import pymysql def get_monthly_cumulative(): connection = pymysql.connect(host=‘...’, user=‘...’, password=‘...’, database=‘...’) try: with connection.cursor() as cursor: sql = “””SELECT DATE_FORMAT(order_date, ‘%Y-%m’) AS year_month, SUM(amount) AS monthly_amount FROM sales WHERE order_date >= ‘2023-01-01’ AND order_date < ‘2024-01-01’ GROUP BY year_month ORDER BY year_month””” cursor.execute(sql) results = cursor.fetchall() cumulative = 0 final_results = [] for row in results: year_month, monthly_amount = row cumulative += monthly_amount final_results.append({ ‘year_month’: year_month, ‘monthly_amount’: monthly_amount, ‘cumulative_amount’: cumulative }) return final_results finally: connection.close()

6.2 优劣分析与决策点

优点：

• 绝对兼容：不依赖任何特定的数据库高级特性，兼容所有版本的MySQL乃至其他数据库。
• 逻辑无限灵活：累加逻辑完全由代码控制。你可以轻松实现“当年累计”、“跨年累计”、“遇到特定月份重置累计”、“只累计大于某阈值的月份”等任何复杂业务规则。
• 调试方便：在应用层调试逻辑比在SQL层调试要直观和方便得多，可以利用IDE的调试器、打印日志等。
• 分担数据库压力：将计算密集型任务转移到应用服务器，在某些场景下可以减轻数据库的CPU负载。

缺点：

• 网络与内存开销：需要将中间结果从数据库传输到应用端。如果月份数据量很大（比如几十年），会有额外的网络I/O和内存占用，但通常这个量级是可以接受的。
• 失去了数据库的计算优势：数据库引擎是为集合计算而优化的，特别是窗口函数，在数据库内部执行通常比在应用层循环更高效。
• 数据一致性风险：如果聚合后的数据在应用层处理过程中，源数据发生了变化，可能会导致计算结果“过期”。而纯SQL查询在事务隔离级别下能保证一致性。

方案四总结：

• 适用场景：
  1. 数据库版本老旧（如MySQL 5.1, 5.5），不支持窗口函数，且自连接/用户变量方案无法满足性能或复杂度要求。
  2. 累加逻辑异常复杂，涉及大量条件判断和业务规则，用SQL表达极其晦涩或不可能。
  3. 作为临时性、一次性的数据分析脚本，开发速度优先。
• 决策建议：默认优先使用窗口函数（方案三）。仅当窗口函数不可用，且其他SQL方案在可读性、性能上都有明显短板时，才考虑将累加逻辑上移到应用层。这是一个在能力、效率、复杂度之间的权衡决策。

7. 性能对比与选型指南

纸上得来终觉浅，我们通过一个简单的思维实验来对比一下这几种方案在“大数据量”下的表现。假设sales表有1亿条记录，需要统计近3年（36个月）的数据。

• 方案一（自连接-未优化）：直接在1亿条记录上做自连接，中间结果集理论上可能膨胀到无法想象的程度，查询几乎必然失败或超时。
• 方案一（自连接-预聚合优化）：先聚合得到36行中间结果，再对这36行进行自连接。性能瓶颈主要在初始的1亿行聚合扫描上，如果order_date有索引，这个聚合可以很快。后续连接成本极低。总体性能中等，主要消耗在聚合阶段。
• 方案二（用户变量）：同样需要先扫描1亿行进行聚合，得到36行结果。然后对这36行结果进行顺序扫描并计算变量。性能与优化后的自连接方案类似，但可能略好，因为避免了连接操作。但稳定性存疑。
• 方案三（窗口函数）：数据库优化器可以非常高效地处理这个查询。它通常也只需要对基表进行一次扫描完成聚合，然后在聚合后的36行结果上，通过内置的、高度优化的窗口计算引擎完成累加。这是性能最高的方案，尤其是当OVER()子句中的ORDER BY和PARTITION BY能利用到索引时。
• 方案四（应用层）：数据库端完成1亿行到36行的聚合（高效），然后传输36行数据到应用端（开销极小），应用端进行36次加法运算（开销极小）。性能接近于方案三的数据库聚合部分，额外增加了微小的网络和序列化开销。

综合选型决策矩阵：

最终建议：

1. 首选方案三（窗口函数）：只要你的MySQL版本是8.0或以上，无脑选择它。它是性能、可读性和功能性的完美结合，是现代SQL的标准写法。
2. 兼容性备选方案一（优化自连接）：如果数据库版本低于8.0，且累加逻辑不复杂，使用先预聚合再连接的写法。这是最安全、最易理解的兼容方案。
3. 谨慎使用方案二（用户变量）：仅在对性能有极致要求，且能完全掌控查询执行计划的边缘场景下考虑，并需要充分测试。不推荐用于核心业务逻辑。
4. 特殊情况考虑方案四（应用层）：当累加规则极其复杂，或者你需要将计算过程与业务代码深度集成时使用。也可以作为低版本数据库下，复杂累加需求的兜底方案。

8. 常见问题与排查技巧实录

在实际开发中，即使选择了正确的方案，也可能会遇到各种意想不到的问题。下面是我在多年实践中总结的一些典型坑点和解决技巧。

8.1 累计结果不正确或翻倍

问题现象：计算出的累计值远大于预期，像是重复累加了。排查思路：

1. 检查连接条件（方案一）：在自连接中，连接条件ON b.mon <= a.mon必须确保关联到的是同一年的之前月份。如果漏掉了年份条件AND b.year = a.year，就会把去年同月份的数据也累加进来。这是最常见的错误。
2. 检查聚合键（所有方案）：确保GROUP BY的子句是完备的。例如，SELECT year, month, SUM(amount) ... GROUP BY year, month。如果只GROUP BY month，那么不同年份的同一个月数据会被合并，导致基础月度值就错了，累计自然全错。
3. 检查数据本身：用最基础的月度聚合查询SELECT year, month, SUM(amount) FROM table GROUP BY year, month ORDER BY year, month验证你的月度数据是否正确。累计是建立在正确的月度值之上的。
4. 窗口函数框架（方案三）：确认OVER()子句中ORDER BY的字段是否能唯一确定行顺序。如果ORDER BY的字段有重复值（比如按year_month聚合，但year_month有重复），默认的RANGE框架会把相同值的所有行视为同一个“当前行”进行处理，可能导致累加逻辑与预期不符。此时可以改用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW来明确指定按物理行计算。

8.2 查询速度慢，如何优化？

针对方案一/二/三的通用优化：

1. 索引，索引，还是索引：在用于分组(GROUP BY)和排序(ORDER BY)的日期字段上建立索引。例如，ALTER TABLE sales ADD INDEX idx_order_date (order_date);。对于WHERE条件中的日期范围过滤，索引能极大加速数据定位。
2. 减少扫描范围：务必在WHERE子句中使用最精确的日期条件，避免全表扫描。使用>= ‘start-date’ AND < ‘end-date’的格式，优于BETWEEN或YEAR(date)=2023，因为前者能更好地利用索引。
3. 使用覆盖索引（方案三特优）：如果查询只涉及order_date和amount两个字段，可以创建复合索引(order_date, amount)。这样，数据库可以直接从索引中获取所需数据，无需回表，速度最快。

针对方案一（自连接）的特有优化：

• 务必先子查询聚合：如前所述，这是最大的性能开关。永远不要在巨大的明细表上直接做自连接。
• 使用CTE提高可读性：With子句（CTE）能让预聚合的逻辑更清晰，有时也能帮助优化器制定更好的计划。

8.3 如何处理NULL值和缺失月份？

问题：某个月份没有数据，结果集中就缺少这一行，导致累计序列中断。期望：希望结果集中包含所有连续的月份，即使数据为0。解决方案： 这需要先生成一个完整的日期维度表（包含所有需要的年月），再与你的数据表进行左连接。

WITH all_months AS ( -- 生成2023年所有月份序列 SELECT ‘2023-01-01’ as month_start UNION ALL SELECT ‘2023-02-01’ UNION ALL SELECT ‘2023-03-01’ -- ... 生成所有月份 UNION ALL SELECT ‘2023-12-01’ ), monthly_data AS ( SELECT DATE_FORMAT(order_date, ‘%Y-%m-01’) as month_start, SUM(amount) as monthly_amount FROM sales WHERE order_date >= ‘2023-01-01’ AND order_date < ‘2024-01-01’ GROUP BY DATE_FORMAT(order_date, ‘%Y-%m-01’) ) SELECT DATE_FORMAT(am.month_start, ‘%Y-%m’) as year_month, COALESCE(md.monthly_amount, 0) as monthly_amount, SUM(COALESCE(md.monthly_amount, 0)) OVER (ORDER BY am.month_start) as cumulative_amount FROM all_months am LEFT JOIN monthly_data md ON am.month_start = md.month_start ORDER BY am.month_start;

关键点：使用COALESCE(md.monthly_amount, 0)将NULL值转换为0，这样累计求和才能正确进行。这个技巧在生成完整业务报表时非常有用。

8.4 多维度分组累计怎么写？

需求：不仅要按时间累计，还要按部门、产品类别等维度分别累计。方案：窗口函数的PARTITION BY子句就是为此而生。

SELECT department_id, DATE_FORMAT(order_date, ‘%Y-%m’) as year_month, SUM(amount) as monthly_amount, SUM(SUM(amount)) OVER ( PARTITION BY department_id ORDER BY DATE_FORMAT(order_date, ‘%Y-%m’) ) as cumulative_amount_by_dept FROM sales WHERE order_date >= ‘2023-01-01’ GROUP BY department_id, year_month ORDER BY department_id, year_month;

PARTITION BY department_id保证了累计是在每个部门内部独立进行的。你可以添加多个字段到PARTITION BY中，实现更细粒度的分组累计。这是窗口函数相比其他方案在复杂场景下碾压性的优势，用自连接或用户变量来实现同样的功能，SQL语句会变得异常复杂和低效。