如何用单条MariaDB/MySQL查询统计每日末系统用户总数？

问题背景 #

现有MariaDB用户表结构及数据如下：

+----+----------+----------------------+
| ID | Username | RegistrationDatetime |
+----+----------+----------------------+
|  1 | A        |  2022-01-03 12:00:00 |
|  2 | B        |  2022-01-03 14:00:00 |
|  3 | C        |  2022-01-04 23:00:00 |
|  4 | D        |  2022-01-04 14:00:00 |
|  5 | E        |  2022-01-05 14:00:00 |
+----+----------+----------------------+

需要通过单条SQL查询得到每日结束时的系统用户总数，预期结果：

+------------+-------+
|   Date     | Count |
+------------+-------+
| 2022-01-03 |     2 |
| 2022-01-04 |     4 |
| 2022-01-05 |     5 |
+------------+-------+

补充说明：用户可能注销或删除，不能通过时间段max(ID)统计，ID字段存在间隙。

解决方案 #

假设用户表名为users，以下是适配需求的SQL语句：

基础版（无用户注销/删除场景） #

SELECT
    DATE(reg_date) AS Date,
    SUM(COUNT(*)) OVER (ORDER BY DATE(reg_date)) AS Count
FROM (
    SELECT RegistrationDatetime AS reg_date
    FROM users
) AS daily_regs
GROUP BY DATE(reg_date)
ORDER BY Date;

考虑用户注销/删除场景 #

若表中有标识用户有效性的字段（比如IsActive，1代表有效，0代表注销/删除），则添加筛选条件：

SELECT
    DATE(reg_date) AS Date,
    SUM(COUNT(*)) OVER (ORDER BY DATE(reg_date)) AS Count
FROM (
    SELECT RegistrationDatetime AS reg_date
    FROM users
    WHERE IsActive = 1 -- 仅统计有效用户
) AS daily_regs
GROUP BY DATE(reg_date)
ORDER BY Date;

原理说明 #

1. 内层子查询：提取目标用户的注册时间，若需过滤无效用户，在此步骤添加条件。
2. 分组统计：按注册日期的日期部分分组，计算每日新增的有效用户数量。
3. 窗口函数累计求和：通过SUM(COUNT(*)) OVER (ORDER BY DATE(reg_date))实现从最早日期到当前日期的累计用户总数，即每日结束时的系统用户总量。

扩展：包含无注册用户的日期 #

如果需要显示没有新用户注册的日期（仍展示当日累计总数），可以生成连续日期范围后关联用户数据：

-- 生成指定范围的连续日期
WITH date_range AS (
    SELECT '2022-01-03' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_range
    WHERE dt < '2022-01-05'
),
daily_reg_counts AS (
    SELECT
        DATE(RegistrationDatetime) AS reg_date,
        COUNT(*) AS daily_count
    FROM users
    WHERE IsActive = 1
    GROUP BY DATE(RegistrationDatetime)
)
SELECT
    dr.dt AS Date,
    COALESCE(SUM(drc.daily_count) OVER (ORDER BY dr.dt), 0) AS Count
FROM date_range dr
LEFT JOIN daily_reg_counts drc ON dr.dt = drc.reg_date
ORDER BY dr.dt;

内容的提问来源于stack exchange，提问作者tim