MySQL技术问询：统计近7天每日行数并补全无数据日期的0值

编写MySQL查询语句，统计过去7天内每天的唯一行数，数据表中无记录的日期需将计数填充为0。

示例数据 #

+----+----------------+---------------------+
| PK |   smsMessage   |       t_stamp       |
+----+----------------+---------------------+
| 0  | 'Alarm active' | 2022-09-19 22:23:56 |
| 1  | 'Alarm active' | 2022-09-19 22:23:41 |
| 2  | 'Alarm active' | 2022-09-19 22:23:42 |
| 3  | 'Alarm active' | 2022-09-19 22:23:56 |
| 4  | 'Alarm active' | 2022-09-19 22:23:25 |
| 5  | 'Alarm active' | 2022-09-20 22:23:57 |
| 6  | 'Alarm active' | 2022-09-20 22:23:40 | 
| 7  | 'Alarm active' | 2022-09-20 22:23:23 |
| 8  | 'Alarm active' | 2022-09-20 22:23:55 |
| 9  | 'Alarm active' | 2022-09-21 22:29:38 |
| 10 | 'Alarm active' | 2022-09-21 21:31:59 |
+----+----------------+---------------------+

期望输出 #

+-------+------------+
| count |   date     |
+-------+------------+
|   0   | 2022-09-15 |
|   0   | 2022-09-16 |
|   0   | 2022-09-17 |
|   0   | 2022-09-18 |
|   5   | 2022-09-19 |
|   4   | 2022-09-20 |
|   2   | 2022-09-21 |
+-------+------------+

核心思路是先生成过去7天的完整日期列表，再与数据表的日统计结果做左连接，通过COALESCE将空计数替换为0。

方案1：递归CTE生成日期（MySQL 8.0+） #

递归CTE是最简洁的方式，适合高版本MySQL：

WITH RECURSIVE date_range AS (
    -- 起始日期：当前日期前6天，覆盖过去7天
    SELECT CURDATE() - INTERVAL 6 DAY AS date
    UNION ALL
    -- 递归生成后续日期直至当前日期
    SELECT date + INTERVAL 1 DAY
    FROM date_range
    WHERE date < CURDATE()
),
daily_stats AS (
    -- 按日期分组统计唯一行数（以PK为唯一标识，可替换为其他字段）
    SELECT 
        DATE(t_stamp) AS date,
        COUNT(DISTINCT PK) AS count
    FROM your_table_name
    WHERE DATE(t_stamp) BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE()
    GROUP BY DATE(t_stamp)
)
-- 左连接补全所有日期，空计数填0
SELECT 
    COALESCE(ds.count, 0) AS count,
    dr.date
FROM date_range dr
LEFT JOIN daily_stats ds ON dr.date = ds.date
ORDER BY dr.date;

方案2：手动构造日期（兼容低版本MySQL） #

如果你的MySQL不支持CTE，直接手动列出7天日期：

SELECT 
    COALESCE(ds.count, 0) AS count,
    dr.date
FROM (
    SELECT CURDATE() - INTERVAL 6 DAY AS date UNION ALL
    SELECT CURDATE() - INTERVAL 5 DAY UNION ALL
    SELECT CURDATE() - INTERVAL 4 DAY UNION ALL
    SELECT CURDATE() - INTERVAL 3 DAY UNION ALL
    SELECT CURDATE() - INTERVAL 2 DAY UNION ALL
    SELECT CURDATE() - INTERVAL 1 DAY UNION ALL
    SELECT CURDATE()
) dr
LEFT JOIN (
    SELECT 
        DATE(t_stamp) AS date,
        COUNT(DISTINCT PK) AS count
    FROM your_table_name
    WHERE DATE(t_stamp) BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE()
    GROUP BY DATE(t_stamp)
) ds ON dr.date = ds.date
ORDER BY dr.date;

注意事项 #

1. 替换your_table_name为实际表名；
2. 若唯一行的判断依据不是PK，将COUNT(DISTINCT PK)中的PK替换为对应字段；
3. CURDATE()取的是当前系统日期，若需基于t_stamp的最大日期计算过去7天，可将CURDATE()替换为(SELECT MAX(DATE(t_stamp)) FROM your_table_name)。

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