MySQL中按月统计并逐月累加统计值的几种写法

2023-10-25 21:14

短信预约 -IT技能 免费直播动态提醒

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

本月第一天

-- 本月第一天SELECT DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE()) + 1 DAY); -- 本月第一天SELECT CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m'), '-01');

建表及模拟数据

-- 创建表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月的销售数据

一、自连接和子查询

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

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;

二、子查询

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;

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

三、子查询+变量

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来记录每个月份的累加值。在最终的查询结果中，输出月份、当月销售额以及累加值

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中完成

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()进行累加，并分别输出月份、当月销售金额和累计销售金额

##月统计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;##日统计SELECT  day, monthly_sales,       SUM(monthly_sales) OVER (ORDER BY day) AS cumulative_salesFROM (  SELECT DATE_FORMAT(sales_date, '%Y-%m-%d') AS day,         SUM(sales_amount) AS monthly_sales  FROM sales  GROUP BY day) t1;