单表SQL查询如何补全无数据日期的计数为0？

我注意到你的原查询是按星期几（day）分组，导致同一星期几的不同日期被合并，但你的期望输出是每一天的独立记录（即使星期几重复），缺失日期的计数显示为0。在PostgreSQL中，我们可以通过生成目标时间范围内的完整日期序列，再和原表的每日计数做左连接来实现，完全不需要额外实体表，用纯SQL就能搞定。

具体实现步骤 #

1. 生成完整日期序列：用generate_series函数生成你查询范围内的所有日期（可以选择原数据的起止日期，或者固定的“过去10天”范围）；
2. 统计每日产品数量：对原表按日期分组，统计每个有记录日期的产品数量；
3. 左连接补全缺失值：将完整日期序列和每日统计结果左连接，用COALESCE把缺失的计数替换为0，最后提取日期对应的星期几。

完整SQL代码（匹配你的期望输出） #

WITH date_range AS (
    -- 生成符合查询条件的所有日期（从原数据最早记录到最晚记录）
    SELECT generate_series(
        (SELECT MIN(date_created)::date FROM table1 WHERE user_id = 1 AND date_created >= now() - interval '10 days'),
        (SELECT MAX(date_created)::date FROM table1 WHERE user_id = 1 AND date_created >= now() - interval '10 days'),
        interval '1 day'
    )::date AS full_date
),
daily_product_counts AS (
    -- 按日期统计每个用户的产品数量
    SELECT 
        date_created::date AS record_date,
        COUNT(product) AS count
    FROM table1
    WHERE user_id = 1 AND date_created >= now() - interval '10 days'
    GROUP BY record_date
)
SELECT
    TO_CHAR(d.full_date, 'Dy') AS day,
    COALESCE(dpc.count, 0) AS count
FROM date_range d
LEFT JOIN daily_product_counts dpc 
    ON d.full_date = dpc.record_date
ORDER BY d.full_date;

关键细节说明 #

• generate_series：PostgreSQL内置的序列生成函数，这里用来创建连续的日期序列，相当于临时的“日期表”，不需要你手动创建实体表；
• COALESCE：专门处理左连接后出现的NULL值（也就是没有产品记录的日期），直接替换为0，正好满足你的计数需求；
• 按日期排序：最后按full_date排序，保证结果是按时间顺序排列的，和你期望的输出结构完全一致。

如果需要固定时间范围为“过去10天”（而非原数据的起止日期），只需修改date_range部分：

WITH date_range AS (
    SELECT generate_series(
        (now() - interval '10 days')::date,
        current_date,
        interval '1 day'
    )::date AS full_date
),
-- 后续部分和上面一致

这样就能完美得到你想要的输出啦！

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