PostgreSQL查询如何显示特定客户无订单日期的空行？

嘿，我一眼就发现了问题所在：你写的WHERE o.customerId = '1234'把左连接后本该保留的无订单日期行给过滤掉了！

为什么会这样？ #

LEFT JOIN确实会保留左表（你生成的日期序列表d）的所有行，但当某一天没有该客户的订单时，右表orders的所有字段都会是NULL。而WHERE o.customerId = '1234'这个条件会把o.customerId为NULL的行直接排除——毕竟NULL不等于任何值，包括'1234'，所以那些无订单的日期就从结果里消失了。

另外还有一个潜在的小问题：你原来生成日期序列的起始时间是所有订单的最早日期，而不是该客户的第一笔订单日期，这可能会导致结果里包含该客户还没下单的更早日期，这应该不是你想要的。

修正后的查询（推荐方案） #

把customerId的过滤条件移到LEFT JOIN的ON子句中，同时修正日期序列的起始时间为该客户的第一笔订单日期：

SELECT count(o.hist_create) AS daily_count, d.day AS date_column 
FROM ( 
    SELECT day::date FROM generate_series( 
        -- 只取该客户的第一笔订单日期作为起始
        (SELECT hist_create FROM orders WHERE customerId = '1234' ORDER BY hist_create LIMIT 1), 
        CURRENT_TIMESTAMP, 
        interval '1 day'
    ) day
) d 
LEFT JOIN orders o 
    ON date_trunc('day', o.hist_create) = d.day 
    AND o.customerId = '1234'  -- 在这里过滤客户，而不是WHERE子句
GROUP BY d.day 
ORDER BY d.day;

另一种可选方案（不推荐，但可以理解逻辑） #

如果一定要用WHERE子句，你需要允许o.customerId为NULL的情况，但同样要修正日期起始：

SELECT count(o.hist_create) AS daily_count, d.day AS date_column 
FROM ( 
    SELECT day::date FROM generate_series( 
        (SELECT hist_create FROM orders WHERE customerId = '1234' ORDER BY hist_create LIMIT 1), 
        CURRENT_TIMESTAMP, 
        interval '1 day'
    ) day
) d 
LEFT JOIN orders o 
    ON date_trunc('day', o.hist_create) = d.day 
WHERE o.customerId = '1234' OR o.customerId IS NULL  -- 保留无订单的NULL行
GROUP BY d.day 
ORDER BY d.day;

为什么推荐第一种方案？ #

第一种方案在连接阶段就只关联该客户的订单，避免了和其他客户订单的无效关联，逻辑更清晰，性能也更好。

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