sql - 如何在postgres中获取每个日期的数据计数
问题描述
我正在尝试获取数据以填充多线图。表作业具有列 id、created_at 和 partner_id。我想每天显示每个 partner_id 的工作总和。我当前的查询有 2 个问题。1)它缺少很多工作。2) 如果那天有一行,它只包含给定日期的条目。我当前的查询是 start 是一个整数,表示我们正在寻找数据的前几天:
SELECT d.date, count(j.id), j.partner_id FROM (
select to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
AS date
FROM generate_series(0, #{start}, 1)
AS offs
) d
JOIN (
SELECT jobs.id, jobs.created_at, jobs.partner_id FROM jobs
WHERE jobs.created_at > now() - INTERVAL '#{start} days'
) j
ON (d.date=to_char(date_trunc('day', j.created_at), 'YYYY-MM-DD'))
GROUP BY d.date, j.partner_id
ORDER BY j.partner_id, d.date;
这将返回如下记录:
[{"date"=>"2019-06-21", "count"=>3, "partner_id"=>"099"},
{"date"=>"2019-06-22", "count"=>1, "partner_id"=>"099"},
{"date"=>"2019-06-21", "count"=>3, "partner_id"=>"075"},
{"date"=>"2019-06-23", "count"=>1, "partner_id"=>"099"}]
我想要的是这样的:
[{"date"=>"2019-06-21", "count"=>3, "partner_id"=>"099"},
{"date"=>"2019-06-22", "count"=>1, "partner_id"=>"099"},
{"date"=>"2019-06-21", "count"=>3, "partner_id"=>"075"},
{"date"=>"2019-06-22", "count"=>0, "partner_id"=>"075"},
{"date"=>"2019-06-23", "count"=>0, "partner_id"=>"075"},
{"date"=>"2019-06-23", "count"=>1, "partner_id"=>"099"}]
因此,对于查询中的每一天,即使该计数为 0,我也会为每个合作伙伴输入一个条目。即使计数为 0,我如何调整查询以填充数据?
解决方案
使用LEFT JOIN
. 您也不需要这么多子查询,也不需要将日期转换为字符串然后再转换回日期:
SELECT d.date, count(j.id), j.partner_id
FROM (SELECT to_char(dte, 'YYYY-MM-DD') AS date , dte
FROM generate_series(current_date - {start} * interval '1 day', current_date, interval '1 day') gs(dte)
) d LEFT JOIN
jobs j
ON DATE_TRUNC('day', j.created_at) = d.dte
GROUP BY d.date, j.partner_id
ORDER BY j.partner_id, d.date;
推荐阅读
- react-native - 世博会文件不可读
- mysql - MySQL 写入可能不会更新查询缓存
- azure - 探测状态代码不匹配。收到的状态码与预期的 200-399 不同
- .htaccess - htacess:将所有http重定向到https,除了任何以开头的url
- haskell - 代数数据类型的 Haskell 映射函数
- python - 使用 RadioButtons 作为变量错误
- sql - 具有和的成本查询 >
- css - CSS调整大小:拖动垂直边框?
- javascript - 在网站中自定义 Braintree Drop-in UI
- google-chrome-devtools - Chrome任务管理器CPU一栏含义