MySQL查询根据按月分组的激活日期统计累计用户数

Question

我有以下表格和数据:

CREATE TABLE tbl_users (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `activation_date` DATETIME
  );

INSERT INTO tbl_users (id, activation_date) VALUES
    (1, '2020-01-15' ),
    (2, '2020-02-13' ),
    (3, '2020-02-15' ),
    (4, '2020-03-01' ),
    (5, '2020-03-03' ),
    (6, '2020-05-01' ),
    (7, '2020-06-01' ),
    (8, '2020-07-15' ),
    (9, '2020-08-15' ),
    (10, '2020-08-15' ),
    (11, '2020-08-19' );

我正在寻找根据激活日期计算每月月底用户总数的最佳方法。对于上面的测试数据，输出应如下所示:

month   cumulative
1       1
2       3
3       5
4       5
5       6
6       7
7       8
8       11
9       11
10      11

我正在尝试:

SELECT MONTH(activation_date) as month, COUNT(*) as cumulative 
FROM tbl_users 
WHERE activation_date >= :start GROUP BY month

但我得到的是特定月份的值，而不是累积值。知道如何改进查询吗？或者我需要稍后在 php 中处理它？谢谢。

Answer 1

从 MySQL 8.0 开始，您可以使用窗口函数来完成此任务:

SELECT DISTINCT
    MONTH(activation_date) as month, 
    SUM(1) over (order by MONTH(activation_date) )as cumulative 
FROM tbl_users 
WHERE activation_date >= :start 
;

SQL editor online

结果:

+-------+------------+
| month | cumulative |
+-------+------------+
|     1 |          1 |
|     2 |          3 |
|     3 |          5 |
|     5 |          6 |
|     6 |          7 |
|     7 |          8 |
|     8 |         11 |
+-------+------------+