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

简介

在数据库中，我们经常需要对某些数据进行统计分析。其中，按照时间进行统计是常见的需求之一。本文将会介绍在 MySQL 数据库中，如何按月进行统计，并且实现每月的统计值逐月累加的几种写法。

1. 创建测试表

首先，我们需要创建一个用于测试的数据表。假设我们有一个表名为 sales，该表记录了每天的销售额。

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE,
    amount DECIMAL(10, 2)
);

2. 插入测试数据

接下来，我们需要向 sales 表中插入一些测试数据。我们模拟了一个销售周期为一个月的情况，即每个月的销售数据。

INSERT INTO sales (date, amount) VALUES 
    ('2022-01-01', 100.00),
    ('2022-01-02', 120.00),
    ('2022-01-03', 80.00),
    ... (省略)
    ('2022-01-31', 150.00),
    ('2022-02-01', 200.00),
    ('2022-02-02', 180.00),
    ('2022-02-03', 210.00),
    ... (省略)
    ('2022-02-28', 190.00),
    ... (省略)
    ('2022-12-01', 300.00),
    ... (省略)
    ('2022-12-31', 350.00);

3. 按月统计销售额

接下来，我们将介绍几种不同的方法来实现按月统计销售额。

3.1 GROUP BY + MONTH()

一种常见的方法是使用 GROUP BY 和 MONTH() 函数来按月分组。

SELECT MONTH(date) AS month, SUM(amount) AS total_amount
FROM sales
GROUP BY MONTH(date)
ORDER BY month;

运行以上查询语句，将会按月统计销售额，并按月份的顺序排序结果。

示例运行结果：

+-------+--------------+
| month | total_amount |
+-------+--------------+
|     1 |    3980.00   |
|     2 |    6250.00   |
|     3 |    5100.00   |
|  ...  |     ...      |
|    12 |    8470.00   |
+-------+--------------+

3.2 按月累加统计值

上面的方法仅仅统计了每个月的销售额，接下来我们介绍两种不同的方式来实现按月累加统计值。

3.2.1 使用子查询

首先，我们可以使用子查询的方式来实现按月累加统计值。

SELECT 
    t1.month, 
    SUM(t2.amount) AS cumulative_total
FROM
    (SELECT 
        MONTH(date) AS month,
        amount
    FROM sales) AS t1
JOIN
    (SELECT 
        MONTH(date) AS month,
        amount
    FROM sales) AS t2
ON 
    t1.month >= t2.month
GROUP BY 
    t1.month
ORDER BY 
    t1.month;

以上查询语句中，我们使用了两个子查询 t1 和 t2，分别代表了原始表的两个副本。在 JOIN 条件中，我们使用了 t1.month >= t2.month 来保证按月累加。通过对 t2.amount 使用 SUM() 函数，得到了每个月的累加销售额。

示例运行结果：

+-------+--------------------+
| month | cumulative_total   |
+-------+--------------------+
|     1 |    3980.00         |
|     2 |   10230.00         |
|     3 |   15330.00         |
|  ...  |       ...          |
|    12 |   88380.00         |
+-------+--------------------+

3.2.2 使用变量

另一种方式是使用 MySQL 中的变量来实现按月累加统计值。

SET @cumulative_total := 0;
SELECT 
    month,
    (@cumulative_total := @cumulative_total + total_amount) AS cumulative_total
FROM
    (SELECT 
        MONTH(date) AS month,
        SUM(amount) AS total_amount
    FROM sales
    GROUP BY MONTH(date)
    ORDER BY month) AS t1;

在以上查询语句中，我们使用了变量 @cumulative_total 来保存累计值，并且通过 := 来逐月累加总销售额。

示例运行结果：

+-------+--------------------+
| month | cumulative_total   |
+-------+--------------------+
|     1 |    3980.00         |
|     2 |   10230.00         |
|     3 |   15330.00         |
|  ...  |       ...          |
|    12 |   88380.00         |
+-------+--------------------+