0
315
ViewsPostgres query - 24 hour time series filtered by specific column, but still return row for each hour
I'm working on a query that returns an hourly time series for a given day, but I need to filter by a particular column on another table, which in my case is the user id.
This is my current query, which returns the submission count for every hour of the current day:
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;
However, these records are irrespective of user id, so when I filter by user id with a query like this it will only return the hours for which a submission for that user was received when instead I need it to return records for every hour much like the query above:
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;
Here's my SQL Fiddle: http://www.sqlfiddle.com/#!17/a8d80/1
Any help would be greatly appreciated!
2 answers
Answer question0
You want to move the filtering condition on the user to the on side of the join. Accordingly, you need to count on a column from the user table, so the filtering logic is taken into account:
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;
0
For a left join, the filtering on all but the first table needs to be in the on clause:
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;
Otherwise, the where clause turns filters out the non-matching rows, turning the outer join into an inner join.