Home > front end >  Count per day per category using generate_series()
Count per day per category using generate_series()

Time:11-30

I am trying to generate using generate_series() for each day and each category, the count, in a given date range.

Table Posts:

id date category_id
1 2022-01-01 1
2 2022-01-01 1
3 2022-01-02 1
4 2022-01-02 2

Table Categories:

id code
1 WEB
2 MOBILE
3 DESKTOP

Expected Results :

day code count
2022-01-01 WEB 2
2022-01-01 MOBILE 0
2022-01-01 DESKTOP 0
2022-01-02 WEB 1
2022-01-02 MOBILE 1
2022-01-02 DESKTOP 0
2022-01-03 WEB 0
2022-01-03 MOBILE 0
2022-01-03 DESKTOP 0
2022-01-04 WEB 0
2022-01-04 MOBILE 0
2022-01-04 DESKTOP 0
2022-01-05 WEB 0
2022-01-05 MOBILE 0
2022-01-05 DESKTOP 0

So far I have :

SELECT day::date, code, count(p.id)
FROM generate_series('2022-01-01'::date, '2022-01-05'::date, '1 DAY') AS day
CROSS JOIN categories c
LEFT JOIN posts p ON p.category_id = c.id
WHERE date BETWEEN '2022-01-01' AND '2022-01-05'
GROUP BY (day, code)
ORDER BY day;

The results is not quite there, I have some intuition that I should join on a sub-query but I'm not sure.

Link to fiddle

Thanks for your help.

CodePudding user response:

You can first find the counts for each category per day, and then join the results onto the series:

select d::date, c.code, coalesce(t.c, 0) 
from generate_series('2022-01-01'::date, '2022-01-05'::date, '1 day') d 
cross join categories c 
left join (select p.date, p.category_id, count(*) c 
   from posts p group by p.date, p.category_id) t 
on c.id = t.category_id and t.date = d::date

See fiddle.

  • Related