sql - Postgresql 串行每日记录计数

Question

我正在尝试获取从 1 月 1 日至今的记录总数，而不跳过日期并为没有记录的日期返回 0。
我尝试了以下操作:orders 是一个示例表，orderdate 是一个时间戳列

with days as (
  select generate_series(
    date_trunc('day','2020-01-01'::timestamp),
    date_trunc('day', now()),
    '1 day'::interval
  ) as day
)
select
  days.day,
  count(orders.id)
from days
left outer join orders on date_trunc('day', orders.orderdate) = days.day
where orders.orders_type='C'
group by 1

问题是日期被跳过。
但是如果我执行:

select generate_series(
    date_trunc('day','2020-01-01'::timestamp),
    date_trunc('day', now()),
    '1 day'::interval
  )

我得到了正确的系列，没有跳过任何日期。

Answer 1

where条件应该属于on left join的条款，即这个:

from days
left outer join orders on date_trunc('day', orders.orderdate) = days.day
where orders.orders_type='C'

应该写成:

from days
left outer join orders 
    on  date_trunc('day', orders.orderdate) = days.day
    and orders.orders_type='C'

笔记:

你在这里实际上不需要 cte，你可以放 generate_series()直接在from条款

日期连接条件可以优化为索引友好的表达式，避免 date_trunc()

表别名使查询更易于读写

您可以将查询编写为:

select d.day, count(o.id)
from generate_series(date '2020-01-01', now(), '1 day') as d(day)
left outer join orders o
    on  o.orderdate >= d.day
    and o.orderdate <  d.day + '1 day'::interval
    and o.orders_type='C'
group by d.day