postgresql 使用generate_series()每天每个类别的计数

vojdkbi0 于 2022-12-03 发布在 PostgreSQL

关注(0)|答案(1)|浏览(151)

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.

postgresql

来源：https://stackoverflow.com/questions/74621559/count-per-day-per-category-using-generate-series

1条答案

按热度按时间

sd2nnvve1#

您可以先找出每天每个类别的计数，然后将结果加入数列：

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。

赞(0）回复(0）举报 2022-12-03

我来回答

postgresql 使用generate_series()每天每个类别的计数

1条答案

相关问题

热门标签

最新问答