PostgreSQL中如何获取累计客户数？是否支持类似Python的循环实现？

需求说明 #

需要统计从2023-01-01到每个月1号的累计注册客户数，例如统计2023-01-01至2023-02-01、2023-01-01至2023-03-01……等时间段内的唯一注册客户总数。

期望输出格式 #

解决方案 #

PostgreSQL 无需用循环实现，直接通过生成日期序列结合聚合函数就能高效完成统计，以下提供两种常用方案：

方案一：生成截止日期序列 + 子查询统计

适合客户可能重复注册（需去重）的场景：

WITH target_dates AS (
    -- 生成从2023-02-01到目标结束月的每个月1号，这里以2023-12-01为例
    SELECT generate_series(
        '2023-02-01'::date,
        '2023-12-01'::date,
        '1 month'::interval
    ) AS end_date
)
SELECT
    -- 格式化日期范围字符串
    TO_CHAR('2023-01-01'::date, 'MM/DD') || '-' || TO_CHAR(end_date, 'MM/DD') AS date,
    -- 统计截止到当前end_date的唯一客户数
    (SELECT COUNT(DISTINCT customer) FROM mytable WHERE enrollment_dt <= end_date) AS count
FROM target_dates
ORDER BY end_date;

方案二：按月统计新增 + 累计求和

如果客户不会重复注册（每个customer仅注册一次），可使用更高效的窗口函数实现累计：

WITH monthly_new AS (
    -- 统计每个月的新增注册客户数
    SELECT
        DATE_TRUNC('month', enrollment_dt) + INTERVAL '1 month' AS end_date,
        COUNT(customer) AS new_count
    FROM mytable
    WHERE enrollment_dt >= '2023-01-01'
    GROUP BY DATE_TRUNC('month', enrollment_dt)
    ORDER BY end_date
),
-- 补充目标时间段内的所有月份（避免无新增的月份被遗漏）
full_months AS (
    SELECT generate_series(
        '2023-02-01'::date,
        '2023-12-01'::date,
        '1 month'::interval
    ) AS end_date
)
SELECT
    TO_CHAR('2023-01-01'::date, 'MM/DD') || '-' || TO_CHAR(f.end_date, 'MM/DD') AS date,
    -- 用窗口函数累计求和
    COALESCE(SUM(m.new_count) OVER (ORDER BY f.end_date), 0) AS count
FROM full_months f
LEFT JOIN monthly_new m ON f.end_date = m.end_date
ORDER BY f.end_date;

代码说明 #

1. generate_series：PostgreSQL 内置函数，用于生成连续的日期/数值序列，此处用来生成每个统计周期的截止日期。
2. COUNT(DISTINCT customer)：确保统计唯一客户数，避免重复统计。
3. 窗口函数SUM() OVER()：在方案二中实现累计求和，性能优于子查询，前提是客户无重复注册情况。
4. TO_CHAR：格式化日期为指定字符串样式，匹配期望的输出格式。

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