SQL按类别与日期分组统计时补全缺失日期并置计数为0的方法

你遇到的是分组统计时缺失无数据维度的问题，核心解决思路是先生成「所有分类+所有待统计日期」的全量组合，再左关联你原有统计结果，将空值填充为0即可，完整实现如下： #

实现步骤 #

1. 生成连续日期序列 #

首先生成你需要覆盖的时间范围内的所有连续日期，主流数据库都可以用递归CTE实现：

-- 递归生成2021-08-01到2021-08-31的所有日期，可替换为你需要的时间范围
WITH RECURSIVE date_range AS (
    SELECT '2021-08-01' AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY) 
    FROM date_range 
    WHERE date < '2021-08-31'
)

2. 生成全部分类列表 #

取出log表中所有去重的分类：

all_categories AS (
    SELECT DISTINCT category FROM log
)

3. 生成全量分类+日期组合 #

通过笛卡尔积得到每个分类对应全部待统计日期的完整组合：

full_combinations AS (
    SELECT c.category, d.date 
    FROM all_categories c
    CROSS JOIN date_range d
)

4. 关联统计结果补全空值 #

将全量组合左关联你原有的每日统计结果，没有数据的日期total自动填充为0：

daily_stats AS (
    SELECT 
        category, 
        COUNT(category) as total, 
        CAST(date AS DATE) as stat_date
    FROM log
    GROUP BY category, CAST(date AS DATE)
)
-- 最终查询
SELECT 
    fc.category,
    fc.date,
    COALESCE(ds.total, 0) AS total
FROM full_combinations fc
LEFT JOIN daily_stats ds 
    ON fc.category = ds.category 
    AND fc.date = ds.stat_date
ORDER BY fc.category ASC, fc.date ASC
LIMIT 100;

完整查询语句（可直接修改参数使用） #

WITH RECURSIVE date_range AS (
    SELECT '2021-08-01' AS date -- 替换为你的统计起始日期
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY) 
    FROM date_range 
    WHERE date < '2021-08-31' -- 替换为你的统计结束日期
),
all_categories AS (
    SELECT DISTINCT category FROM log
),
full_combinations AS (
    SELECT c.category, d.date 
    FROM all_categories c
    CROSS JOIN date_range d
),
daily_stats AS (
    SELECT 
        category, 
        COUNT(category) as total, 
        CAST(date AS DATE) as stat_date
    FROM log
    GROUP BY category, CAST(date AS DATE)
)
SELECT 
    fc.category,
    fc.date,
    COALESCE(ds.total, 0) AS total
FROM full_combinations fc
LEFT JOIN daily_stats ds 
    ON fc.category = ds.category 
    AND fc.date = ds.stat_date
ORDER BY fc.category ASC, fc.date ASC
LIMIT 100;

注意事项 #

• 上述语法为MySQL写法，其他数据库仅需调整日期运算语法即可：PostgreSQL写为date + INTERVAL '1 day'，SQL Server写为DATEADD(day, 1, date)
• 如果数据量较大，建议预先维护一张专门的日期维度表，直接查询维度表获取连续日期，性能远高于递归CTE
• 可按需给log表的date、category字段添加索引，提升统计查询效率

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