使用generate_series()时LEFT JOIN缺失数据行问题排查

核心问题 #

你的查询没出现订单量为0的行，是因为连接顺序和连接类型错误：

• 你先LEFT JOIN orders，再用默认的INNER JOIN关联products。当某产品某年份没有订单时，orders表对应的行是NULL，此时products.id = o.product_id的匹配条件不成立，INNER JOIN会直接过滤掉这些行，自然出不来0订单的记录。
• 另外，你没有生成「所有年份 × 所有产品」的完整组合，这是统计全量维度的基础。

修正后的查询语句 #

SELECT 
    date_trunc('year'::text, years) AS year,
    p.id AS product_id,
    COALESCE(count(o.id), 0) AS total_orders
FROM generate_series(
    (SELECT MIN(date) FROM orders),
    (SELECT MAX(date) FROM orders),
    '1 year'::interval
) years
-- 生成所有年份和所有产品的笛卡尔积，确保每个产品每个年份都有一行
CROSS JOIN products p
-- 左连接订单表，匹配年份和产品ID
LEFT JOIN orders o 
    ON date_trunc('year'::text, years) = date_trunc('year'::text, o.date)
    AND p.id = o.product_id
GROUP BY year, p.id
ORDER BY year, p.id;

关键改动说明 #

• CROSS JOIN products：把年份序列和产品表做交叉连接，得到所有可能的「年份-产品」组合，这是能统计0订单的前提。
• 调整LEFT JOIN关联条件：直接用产品表ID和订单表product_id关联，不依赖orders的中间结果，确保即使没有订单，产品行也能保留。
• COALESCE处理空值：当某年份某产品无订单时，count(o.id)返回NULL，用COALESCE转换为0。

执行上述语句后，就能得到你预期的2012-01-01 | 2 | 0这类行。

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