postgres查询-按特定列筛选的24小时时间序列,但仍返回每小时的行

xv8emn3q  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(464)

我正在处理一个查询,该查询返回给定日期的每小时时间序列,但我需要按另一个表上的特定列进行筛选,在我的示例中,该列是用户id。
这是我当前的查询,返回当天每小时的提交计数:

SELECT hours, count(s.id)
FROM generate_series(current_date, current_date + interval '23h', cast('1h' as interval)) hours
         left join submission s on hours = date_trunc('hour', s.submitted_date)
group by hours
order by hours;


但是,这些记录与用户id无关,因此当我使用这样的查询按用户id筛选时,它将只返回接收到该用户提交的小时数,而我需要它返回每小时的记录,就像上面的查询:

SELECT hours, count(s.id)
FROM generate_series(current_date, current_date + interval '23h', cast('1h' as interval)) hours
    left join submission s on hours = date_trunc('hour', s.submitted_date)
    left join form f on s.form_custom_id = f.custom_id
    left join "user" u on f.user_id = u.id
    where u.id = 4
group by hours
order by hours;


这是我的sql小提琴:http://www.sqlfiddle.com/#!17/a8d80/1型
任何帮助都将不胜感激!

nsc4cvqm

nsc4cvqm1#

要将用户上的筛选条件移动到 on 连接的一侧。因此,您需要从 user 表,因此考虑了过滤逻辑:

select hours, count(u.id)
from generate_series(current_date, current_date + interval '23h', cast('1h' as interval)) hours
left join submission s on hours = date_trunc('hour', s.submitted_date)
left join form f on s.form_custom_id = f.custom_id
left join "user" u on f.user_id = u.id and u.id = 4
group by hours
order by hours;
shyt4zoc

shyt4zoc2#

为了一个 left join ,除第一个表外,所有表上的筛选都需要在 on 条款:

select hours, count(s.id)
from generate_series(current_date, current_date + interval '23h', cast('1h' as interval)) hours left j oin
     submission s 
     on hours = date_trunc('hour', s.submitted_date) left join
     form f
     on s.form_custom_id = f.custom_id left join
     "user" u
     on f.user_id = u.id and u.id = 4
group by hours
order by hours;

否则 where 子句将筛选出不匹配的行,将外部联接转换为内部联接。

相关问题