PostgreSQL统计上周按天分组销售数据 无销售日补0失效问题

问题根因 #

coalesce函数仅能对结果集中已存在行的空值做替换，无法生成原本不存在的记录行。直接从销售关联表做分组聚合的写法，会导致没有销售记录的星期不会出现在分组结果中，自然无法通过coalesce补0。

修复逻辑 #

先构造上周完整7天的基准日期序列，再通过左连接关联按天聚合的销售统计结果，未匹配到销售数据的日期会返回空值，此时用coalesce即可正常补0。

修正后SQL代码 #

WITH last_week_days AS (
    SELECT
        day_series AS order_date,
        to_char(day_series, 'Dy') AS day
    FROM generate_series(
        date_trunc('week', CURRENT_DATE) - INTERVAL '7 days',
        date_trunc('week', CURRENT_DATE) - INTERVAL '1 day',
        INTERVAL '1 day'
    ) AS day_series
),
daily_sales AS (
    SELECT
        DATE(s.order_time) AS stat_date,
        COUNT(o.menu_item_id) AS qty_sold,
        SUM(m.price - m.cost) AS total_profit
    FROM sales_order s
    INNER JOIN order_item o ON o.sales_order_id = s.id
    INNER JOIN menu_item m ON m.id = o.menu_item_id
    WHERE s.order_time >= date_trunc('week', CURRENT_DATE) - INTERVAL '7 days'
      AND s.order_time < date_trunc('week', CURRENT_DATE)
    GROUP BY DATE(s.order_time)
)
SELECT
    lwd.day,
    COALESCE(ds.qty_sold, 0) AS qty_sold,
    COALESCE(ds.total_profit, 0) AS total_profit
FROM last_week_days lwd
LEFT JOIN daily_sales ds ON lwd.order_date = ds.stat_date
ORDER BY lwd.order_date ASC;

注意事项 #

• 原SQL的时间过滤条件date_trunc('week', now())::date - 5范围不准确，会遗漏上周的部分日期，修正后用明确的上周一至上周日的闭开区间，不会漏算或多算数据
• 不要直接按星期缩写字符串排序：不同语言环境下星期缩写的字符串排序规则和实际周顺序不一致，按基准日期排序可以保证结果始终按周一到周日的顺序返回
• 必须使用基准日期表左连接销售统计结果，才能保留完整7天的所有行，这是coalesce能正常补0的前提

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