获取当前年度每个月的用户计数，即使计数为0

Question

我需要得到当前年度每个月的新订户数量。

DB Structure:Subscriber(subscriber_id, create_timestamp, ...)

预期结果:

date       | count
-----------+------
2021-01-01 | 3
2021-02-01 | 12
2021-03-01 | 0
2021-04-01 | 8
2021-05-01 | 0

我写了下面的查询:

SELECT
DATE_TRUNC('month',create_timestamp)
AS  create_timestamp,
COUNT(subscriber_id) AS count
FROM subscriber
GROUP BY DATE_TRUNC('month',create_timestamp); 

可以工作，但不包括计数为0的月份。它只返回表中存在的那些。如:

"2021-09-01 00:00:00"   3
"2021-08-01 00:00:00"   9

Answer 1

第一个子查询用于按年检索每个月的行，然后与另一个子查询进行LEFT JOIN，子查询用于按月检索total_count。COALESCE()用于将NULL值替换为0。

-- PostgreSQL (v11)
SELECT t.cdate
, COALESCE(p.total_count, 0) total_count
FROM (select generate_series('2021-01-01'::timestamp, '2021-12-15', '1 month') as cdate) t
LEFT JOIN (SELECT DATE_TRUNC('month',create_timestamp) create_timestamp
, SUM(subscriber_id) total_count
FROM subscriber
GROUP BY DATE_TRUNC('month',create_timestamp)) p
ON t.cdate = p.create_timestamp

请从url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=20dcf6c1784ed0d9c5772f2487bcc221查看

Answer 2

获取当前年度每个月的新订户数量

SELECT month::date, COALESCE(s.count, 0) AS count
FROM   generate_series(date_trunc('year', LOCALTIMESTAMP)
, date_trunc('year', LOCALTIMESTAMP) + interval '11 month'
, interval '1 month') m(month)
LEFT   JOIN (
SELECT date_trunc('month', create_timestamp) AS month
, count(*) AS count
FROM   subscriber
GROUP  BY 1
) s USING (month);

db<此处小提琴>

假设每一行都是一个"新订阅者"。因此，count(*)是最简单和最快的。

:

• 连接generate_series()上的计数查询并检索Null值为'0'
• 在PostgreSQL中生成两个日期之间的时间序列