PostgreSQL 13按月分组并统计当月及历史累计行数

原查询仅统计各月的当月数据量，要实现当月及所有之前月份的累计行数，可以借助PostgreSQL的窗口函数SUM() OVER()完成，以下是两种实现方式：

方式一：使用CTE（可读性更强） #

WITH monthly_counts AS (
    SELECT 
        TO_CHAR(plans.first_published_at, 'YYYY-MM') AS publication_date,
        COUNT(*) AS monthly_count
    FROM plans
    WHERE plans.first_published_at IS NOT NULL
    GROUP BY TO_CHAR(plans.first_published_at, 'YYYY-MM')
    ORDER BY publication_date
)
SELECT 
    publication_date,
    SUM(monthly_count) OVER (ORDER BY publication_date) AS cumulative_count
FROM monthly_counts;

方式二：子查询嵌套 #

SELECT 
    publication_date,
    SUM(monthly_count) OVER (ORDER BY publication_date) AS cumulative_count
FROM (
    SELECT 
        TO_CHAR(plans.first_published_at, 'YYYY-MM') AS publication_date,
        COUNT(*) AS monthly_count
    FROM plans
    WHERE plans.first_published_at IS NOT NULL
    GROUP BY TO_CHAR(plans.first_published_at, 'YYYY-MM')
) AS monthly_counts
ORDER BY publication_date;

逻辑说明 #

1. 先通过内层查询/CTE计算每个月的当月数据量，和原查询逻辑一致，同时按月份排序保证累计顺序正确。
2. 外层查询使用SUM(monthly_count) OVER (ORDER BY publication_date)：这个窗口函数会按月份先后顺序，将从第一行到当前行的所有monthly_count求和，得到累计值。如果需要明确指定窗口范围，可写成SUM(monthly_count) OVER (ORDER BY publication_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，效果与默认一致。

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