如何在PostgreSQL中获取含0计数的每日用户创建量？

要显示计数为0的日期，核心思路是先生成完整的日期序列，再将用户表数据左连接到这个序列上，这样没有用户注册的日期也会保留，计数显示为0。以下是具体实现：

完整查询示例 #

针对你指定的时间范围（2022-01-11 至今），可以用如下查询：

WITH date_series AS (
    SELECT generate_series(
        DATE_TRUNC('day', '2022-01-11'::DATE),
        DATE_TRUNC('day', NOW()),
        INTERVAL '1 day'
    ) AS day
)
SELECT
    ds.day,
    COUNT(u.id) AS signup
FROM date_series ds
LEFT JOIN users u ON DATE_TRUNC('day', u.created_at) = ds.day
GROUP BY ds.day
ORDER BY ds.day;

关键说明 #

• generate_series：生成从起始日期到当前日期的每日时间戳序列，确保所有日期都被包含。
• CTE（WITH子句）：将生成的日期序列封装为临时数据集date_series，方便后续关联。
• LEFT JOIN：以日期序列为主表，左连接用户表，即使某一天没有用户注册，该日期也会保留在结果中。
• COUNT(u.id)：只统计用户表中存在的id（非NULL值），没有数据时会返回0，避免直接用COUNT(*)导致无数据日期计数为1的问题。

简化写法（无需CTE） #

如果不需要CTE，也可以直接在FROM子句中使用generate_series：

SELECT
    ds.day,
    COUNT(u.id) AS signup
FROM generate_series(
    DATE_TRUNC('day', '2022-01-11'::DATE),
    DATE_TRUNC('day', NOW()),
    INTERVAL '1 day'
) AS ds(day)
LEFT JOIN users u ON DATE_TRUNC('day', u.created_at) = ds.day
GROUP BY ds.day
ORDER BY ds.day;

为什么之前的查询不生效？ #

你之前的查询直接从users表分组，只有存在用户注册记录的日期才会出现在结果集中，没有数据的日期自然不会被返回。必须先构造完整的日期范围，再关联用户数据才能得到包含0的统计结果。

内容的提问来源于stack exchange，提问作者Omkar