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.
1条答案
按热度按时间sd2nnvve1#
您可以先找出每天每个类别的计数,然后将结果加入数列:
See fiddle。