PostgreSQL按月统计上月已存在ID数量的动态查询实现

你可以通过CTE先提取全表所有月份的去重ID集合，再通过自关联匹配上月ID的方式实现全量动态统计，不需要绑定当前日期，具体SQL如下：

WITH monthly_id AS (
    -- 提取每个月出现过的去重ID，统一将日期转为当月第一天
    SELECT DISTINCT 
        date_trunc('month', date) AS month,
        id
    FROM your_table_name -- 替换为实际表名
)
SELECT 
    curr.month AS date,
    COUNT(DISTINCT curr.id) AS count
FROM monthly_id curr
-- 左关联上个月的同ID记录，匹配成功即为两个月都出现的ID
LEFT JOIN monthly_id prev 
    ON curr.id = prev.id 
    AND curr.month = prev.month + INTERVAL '1 month'
GROUP BY curr.month
ORDER BY curr.month;

逻辑说明 #

• 首先通过CTE对全表数据做预处理，每个ID只要当月出现过就仅保留一条记录，避免重复计算
• 自关联时通过curr.month = prev.month + INTERVAL '1 month'动态匹配每个月对应的上月时间范围，不需要硬编码和当前日期相关的条件
• 最早的月份没有对应上月数据，关联匹配不到返回的统计值自然为0，完全符合你给出的样例输出要求

你原来的语句问题在于子查询的时间范围绑定了current_date，只能统计和当前日期临近的两个月数据，改用上述自关联方案即可覆盖所有存在数据的月份。

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