如何在这种特定情况下显示空行

Question

我正在尝试使用我在这里找到的各种示例构建查询，但到目前为止收效甚微：

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 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'
GROUP  BY d.day 
ORDER  BY d.day;

因此，在这里，我想每天计算特定客户的订单，从第一个订单的时间戳到今天，虽然这有效，但它不会获取该客户没有订单的日期。我希望看到类似以下内容：

number | date 
     15| 06-12-2017 
     0 | 07-12-2017
     9 | 08-12-2017
     11| 09-12-2017

但我得到：

number | date 
     15| 06-12-2017 
     9 | 08-12-2017
     11| 09-12-2017

我以为加入会解决这个问题，似乎这个解决方案对其他人有效，也许不像我那样以我的方式定位客户？

hist_create的数据类型为 timestamp 。

Answer 1

问题是您正在筛选订单表中的字段，这违背了使用外部联接的目的。改为在联接中包含该条件：

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 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'
GROUP  BY d.day 
ORDER  BY d.day;

Answer 2

@fauxmosapien指出了您对LEFT JOIN的主要问题。

但是由于您的列hist_create具有数据类型timestamp，因此可以像这样进一步改进查询：

SELECT count(o.hist_create) AS daily_count, d.day::date AS date_column 
FROM  (
   SELECT generate_series(min(hist_create), now()::timestamp, interval '1 day') AS day
   FROM   orders
   ) d
LEFT   JOIN orders o ON date_trunc('day', o.hist_create) = d.day
                    AND o.customerId = '1234'  -- why is the number quoted?
GROUP  BY d.day 
ORDER  BY d.day;

原始查询生成一系列timestamptz值(因为这是CURRENT_TIMESTAMP返回的值(强制转换为date，然后必须强制转换为timestamp才能在LEFT JOIN中hist_create匹配项。

相反，请创建一个timestamp系列并直接匹配。仅转换为输出的date(或以您喜欢的任何方式格式化值以显示to_char()(。使用EXPLAIN ANALYZE进行测试以验证其性能是否稍好。它还最大限度地减少了在时间戳和日期/时间戳之间进行转换的偷偷摸摸的极端情况错误。更多详情：

• 在PostgreSQL中生成两个日期之间的时间序列