有时候，我们可能有这样的场景，需要将销量按月统计，并且按月逐月累加。写惯了GROUP BY,按月统计倒是小case,但是逐月累加实现起来，要稍微麻烦一点。下面就整理几种写法，以备不时之需。

建表及模拟数据
12345678910111213141516171819202122232425
-- 创建表CREATE TABLE `sales` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `sales_date` date NOT NULL,  `sales_amount` decimal(10,2) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 插入模拟数据INSERT INTO `sales` (`sales_date`, `sales_amount`) VALUES("2023-01-01", 1500.00),("2023-01-02", 1800.00),("2023-01-05", 2200.00),("2023-02-01", 1200.00),("2023-02-03", 1800.00),("2023-03-01", 2500.00),("2023-03-05", 2800.00),("2023-03-08", 3200.00),("2023-04-01", 2100.00),("2023-04-03", 1900.00),("2023-04-05", 2600.00),("2023-05-01", 3100.00),("2023-05-02", 3400.00),("2023-06-01", 3800.00),("2023-06-06", 4200.00);

该表包含三个字段：id、sales_date、sales_amount。id为自增长主键，sales_date为销售日期，sales_amount为销售额。插入了15条模拟数据，涵盖了2023年1月至6月的销售数据

一、自连接和子查询

首先在内部查询中计算出每个月份的销售总额和月份；接着在外部查询中使用自连接和子查询计算每个月份的累计销售额


(资料图)

123456789101112131415
SELECT t1.month, t1.monthly_sales,       SUM(t2.monthly_sales) AS cumulative_salesFROM (  SELECT DATE_FORMAT(sales_date, "%Y-%m") AS month,         SUM(sales_amount) AS monthly_sales  FROM sales  GROUP BY month) t1JOIN (  SELECT DATE_FORMAT(sales_date, "%Y-%m") AS month,         SUM(sales_amount) AS monthly_sales  FROM sales  GROUP BY month) t2 ON t1.month >= t2.monthGROUP BY t1.month;
二、子查询
12345678910111213
SELECT month, monthly_sales,       (SELECT SUM(monthly_sales)         FROM (          SELECT DATE_FORMAT(sales_date, "%Y-%m") AS month, SUM(sales_amount) AS monthly_sales          FROM sales          GROUP BY month        ) t2        WHERE t2.month <= t1.month) AS cumulative_salesFROM (  SELECT DATE_FORMAT(sales_date, "%Y-%m") AS month, SUM(sales_amount) AS monthly_sales  FROM sales  GROUP BY month) t1;

此处使用了两个子查询，第一个子查询用于获取每个月份的总销售额和月份，第二个子查询用于计算累加值。在内部子查询中，通过<=操作符将当前月份以及之前所有月份的销售额相加，从而得到累加值

三、子查询+变量
1234567
SELECT month, monthly_sales, @cumulative := @cumulative + monthly_sales AS cumulativeFROM (  SELECT DATE_FORMAT(sales_date, "%Y-%m") AS month, SUM(sales_amount) AS monthly_sales  FROM sales  GROUP BY month) t1CROSS JOIN (SELECT @cumulative := 0) t2;

此处使用了两个子查询，第一个子查询用于获取每个月份的总销售额和月份，第二个子查询用于初始化变量@cumulative。在外部查询中，通过CROSS JOIN将两个子查询连接起来，并且使用变量@cumulative来计算累加值。

四、用户变量和子查询

在内部查询中先对销售日期进行排序，然后使用用户变量@cumulative来记录每个月份的累加值。在最终的查询结果中，输出月份、当月销售额以及累加值

123456789
SELECT month, monthly_sales,       (@cumulative := @cumulative + monthly_sales) AS cumulative_salesFROM (  SELECT DATE_FORMAT(sales_date, "%Y-%m") AS month,          SUM(sales_amount) AS monthly_sales  FROM sales  GROUP BY month  ORDER BY sales_date ASC) t1, (SELECT @cumulative := 0) t2;

此处使用了两个子查询，第一个子查询用于获取每个月份的总销售额和月份，并按销售日期升序排序；第二个子查询用于初始化用户变量@cumulative。在外部查询中，通过,连接两个子查询，并使用用户变量@cumulative来计算每个月份的累加值。

五、表达式（CTE）和窗口函数

使用MySQL 8.0引入的通用表表达式（CTE）和窗口函数，可以将累加值计算放在CTE中完成

123456789
WITH monthly_sales AS (  SELECT DATE_FORMAT(sales_date, "%Y-%m") AS month,          SUM(sales_amount) AS monthly_sales  FROM sales  GROUP BY month)SELECT month, monthly_sales,       SUM(monthly_sales) OVER (ORDER BY month) AS cumulative_salesFROM monthly_sales;

此处使用了CTE来计算每个月份的总销售额和月份，并在外部查询中使用窗口函数SUM() OVER()对月份进行累加。

SUM() OVER()

使用MySQL 8.0引入的LATERAL关键字，以及OVER ORDER BY子句，按月份求和，再用SUM() OVER()进行累加，并分别输出月份、当月销售金额和累计销售金额

12345678
SELECT  month, monthly_sales,       SUM(monthly_sales) OVER (ORDER BY month) AS cumulative_salesFROM (  SELECT DATE_FORMAT(sales_date, "%Y-%m") AS month,         SUM(sales_amount) AS monthly_sales  FROM sales  GROUP BY month) t1;

此处使用LATERAL关键字和OVER ORDER BY子句对每个月份进行分组，并计算每个月份的销售总额和sum() over()

目前大概就知道这几种方式，各位有没有其他更好的方法呢？

推荐内容