id、sales_date、sales_amount。id为自增长主键，sales_date为销售日期，sales_amount为销售额。插入了15条模拟数据，涵盖了2023年1月至6月的销售数据
有时候，我们可能有这样的场景，需要将销量按月统计，并且按月逐月累加。写惯了GROUP BY,按月统计倒是小case,但是逐月累加实现起来，要稍微麻烦一点。下面就整理几种写法，以备不时之需。
建表及模拟数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
-- 创建表
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月的销售数据
一、自连接和子查询
首先在内部查询中计算出每个月份的销售总额和月份；接着在外部查询中使用自连接和子查询计算每个月份的累计销售额
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SELECT t1.month, t1.monthly_sales,
SUM(t2.monthly_sales) AS cumulative_sales
FROM (
SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month,
SUM(sales_amount) AS monthly_sales
FROM sales
GROUP BY month
) t1
JOIN (
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.month
GROUP BY t1.month;
|
二、子查询
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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_sales
FROM (
SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales
FROM sales
GROUP BY month
) t1;
|
此处使用了两个子查询，第一个子查询用于获取每个月份的总销售额和月份，第二个子查询用于计算累加值。在内部子查询中，通过<=操作符将当前月份以及之前所有月份的销售额相加，从而得到累加值
三、子查询+变量
|
SELECT month, monthly_sales, @cumulative := @cumulative + monthly_sales AS cumulative
FROM (
SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales
FROM sales
GROUP BY month
) t1
CROSS JOIN (SELECT @cumulative := 0) t2;
|
此处使用了两个子查询，第一个子查询用于获取每个月份的总销售额和月份，第二个子查询用于初始化变量@cumulative。在外部查询中，通过CROSS JOIN将两个子查询连接起来，并且使用变量@cumulative来计算累加值。
四、用户变量和子查询
在内部查询中先对销售日期进行排序，然后使用用户变量@cumulative来记录每个月份的累加值。在最终的查询结果中，输出月份、当月销售额以及累加值
|
SELECT month, monthly_sales,
(@cumulative := @cumulative + monthly_sales) AS cumulative_sales
FROM (
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中完成
|
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_sales
FROM monthly_sales;
|
此处使用了CTE来计算每个月份的总销售额和月份，并在外部查询中使用窗口函数SUM() OVER()对月份进行累加。
SUM() OVER()
使用MySQL 8.0引入的LATERAL关键字，以及OVER ORDER BY子句，按月份求和，再用SUM() OVER()进行累加，并分别输出月份、当月销售金额和累计销售金额
|
SELECT month, monthly_sales,
SUM(monthly_sales) OVER (ORDER BY month) AS cumulative_sales
FROM (
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()
目前大概就知道这几种方式，各位有没有其他更好的方法呢？
