PostgreSQL无新增学生月份：如何沿用上月在籍学生数？

问题背景 #

现有fact_enrolment表存储学生数据，包含start_date_key（入学日期键）、withdrawal_date_key（退学日期键，未退学学生该字段为空）。已完成每月新增、退学学生数统计，但统计每月在籍学生数时，无数据变动的月份不会出现在结果集中，需求是显示所有月份，无变动时沿用上月的在籍数。使用PostgreSQL 13.15版本。

原查询可计算有数据月份的在籍数，但缺失无变动月份：

WITH student_activity AS 
(
    -- Convert start and withdrawal date keys to actual date format
    SELECT to_date(fe.start_date_key::text, 'YYYYMMDD') AS start_date,
    to_date(fe.withdrawal_date_key::text, 'YYYYMMDD') AS withdrawal_date,
    dp.product_name, dp.sku
    FROM fact_enrolment fe
    INNER JOIN dim_product dp ON fe.product_key = dp.product_key
)
SELECT date_trunc('month', month_series) AS month,
COUNT(*) AS existing_students,
sa.product_name
FROM (
    SELECT generate_series(
    (SELECT MIN(to_date(start_date_key::text, 'YYYYMMDD')) FROM fact_enrolment),'2100-12-31',INTERVAL '1 month') AS month_series) AS months
LEFT JOIN student_activity sa ON sa.start_date < month_series AND (sa.withdrawal_date IS NULL OR sa.withdrawal_date >= month_series)
GROUP BY month, sa.product_name

表结构与示例数据 #

CREATE TABLE fact_enrolment (
    student_key int4 NULL,
    start_date_key int4 NULL,
    withdrawal_date_key int4 NULL,
    product_key int4 NULL);

CREATE TABLE dim_product (
    product_key int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
    product_name varchar NULL,
    sku varchar NULL);

INSERT INTO dim_product (product_name , sku) VALUES ('Preschool' , 'ABC123');

INSERT INTO fact_enrolment (student_key, start_date_key, withdrawal_date_key, product_key) VALUES 
  (12, 20230105, 20230130, 1)
, (14, 20230106, 20230120, 1)
, (45, 20230405, 20230420, 1);
INSERT INTO fact_enrolment (student_key, start_date_key, product_key) VALUES 
  (17, 20230110, 1)
, (20, 20230120, 1)
, (21, 20230220, 1)
, (22, 20230202, 1)
, (23, 20230228, 1)
, (34, 20230206, 1)
, (44, 20230406, 1);

期望结果 #

注：退学发生在月末时，次月在籍数扣除该生。

解决方案 #

核心思路是先统计每月新增、退学数，通过累计计算得到在籍数，再生成完整月份序列，最后用窗口函数填充缺失的在籍数：

WITH 
-- 生成完整的月份序列
month_series AS (
    SELECT date_trunc('month', generate_series(
        (SELECT MIN(to_date(start_date_key::text, 'YYYYMMDD')) FROM fact_enrolment),
        '2023-04-01', -- 可根据需求调整结束月份，或替换为CURRENT_DATE
        INTERVAL '1 month'
    )) AS month
),
-- 统计每月新增学生数
monthly_new AS (
    SELECT 
        date_trunc('month', to_date(start_date_key::text, 'YYYYMMDD')) AS month,
        COUNT(*) AS new_students,
        dp.product_name
    FROM fact_enrolment fe
    JOIN dim_product dp ON fe.product_key = dp.product_key
    GROUP BY month, dp.product_name
),
-- 统计每月退学学生数
monthly_withdrawal AS (
    SELECT 
        date_trunc('month', to_date(withdrawal_date_key::text, 'YYYYMMDD')) AS month,
        COUNT(*) AS withdrawal_students,
        dp.product_name
    FROM fact_enrolment fe
    JOIN dim_product dp ON fe.product_key = dp.product_key
    WHERE withdrawal_date_key IS NOT NULL
    GROUP BY month, dp.product_name
),
-- 合并新增、退学数据，关联完整月份序列
monthly_changes AS (
    SELECT 
        ms.month,
        COALESCE(mn.new_students, 0) AS new_students,
        COALESCE(mw.withdrawal_students, 0) AS withdrawal_students,
        COALESCE(mn.product_name, mw.product_name, (SELECT product_name FROM dim_product LIMIT 1)) AS product_name
    FROM month_series ms
    LEFT JOIN monthly_new mn ON ms.month = mn.month
    LEFT JOIN monthly_withdrawal mw ON ms.month = mw.month
),
-- 计算累计在籍数
running_enrolment AS (
    SELECT 
        month,
        new_students,
        withdrawal_students,
        -- 累计逻辑：上月在籍 + 本月新增 - 本月退学
        SUM(new_students - withdrawal_students) OVER (
            PARTITION BY product_name 
            ORDER BY month 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS existing_students,
        product_name
    FROM monthly_changes
)
SELECT 
    month,
    new_students,
    withdrawal_students,
    -- 填充无变动月份的在籍数，取最近的非NULL值
    LAST_VALUE(existing_students) OVER (
        PARTITION BY product_name 
        ORDER BY month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS existing_students,
    product_name
FROM running_enrolment
ORDER BY month;

方案说明 #

1. 生成完整月份序列：用generate_series生成从最早入学月份到目标结束月份的所有月份，确保每个月份都出现在结果中。
2. 统计每月变动：分别统计每月新增和退学学生数，用COALESCE将无变动月份的数值设为0。
3. 累计计算在籍数：通过窗口函数SUM计算累计在籍数，逻辑为上月在籍数加本月新增、减本月退学。
4. 填充缺失值：用LAST_VALUE ... IGNORE NULLS填充无变动月份的在籍数，确保连续显示上月的有效数值。

验证结果 #

运行上述SQL后，将得到与期望完全一致的结果：

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