sql - Postgresql 串行每日记录计数
问题描述
我正在尝试获取从 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
)
我得到了正确的系列,没有跳过任何日期。
解决方案
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
推荐阅读
- python - 如何仅使用 numpy 预处理文本
- amazon-web-services - 如何使用子模块在 aws 控制台中手动触发代码构建
- c# - C# net core Odata web api json格式异常
- ruby-on-rails - rspec中的Bcrypt Invalid Hash,同时测试用户是否应该散列用户的密码
- swift - 通过苹果应用商店应用内购买访问托管内容的问题:““内容”文件夹不存在。
- css - 使用第 n 个子选择器更改背景图像。(SASS)
- vba - VBA LongVar = range.find 测试变量是否为空
- php - 在 CodeIgniter 中正确使用 form_validation
- html - 绝对位置上的打字机动画问题
- python - 即使列仅包含整数,熊猫也总是将列转换为对象