按月统计活跃学生累计数：含新增流失及 attrition/retention 计算需求

核心思路 #

通过构造完整的月度时间序列，结合窗口函数实现单SQL完成所有统计指标计算，涵盖新增、流失、净增减、月初/末学生数及留存/流失率。

完整SQL示例（SQL Server） #

WITH MonthSequence AS (
    -- 生成目标年份的所有月度起始日期，这里以2024年为例
    SELECT DATEFROMPARTS(2024, 1, 1) AS MonthDate
    UNION ALL
    SELECT DATEADD(MONTH, 1, MonthDate)
    FROM MonthSequence
    WHERE MonthDate < DATEFROMPARTS(2024, 12, 1)
),
MonthlyMetrics AS (
    SELECT
        ms.MonthDate,
        -- 当月新增学生数
        COUNT(DISTINCT CASE WHEN DATEFROMPARTS(YEAR(s.DateEntered), MONTH(s.DateEntered), 1) = ms.MonthDate THEN s.StudentID END) AS Total_New,
        -- 当月流失学生数
        COUNT(DISTINCT CASE WHEN DATEFROMPARTS(YEAR(s.DateInactive), MONTH(s.DateInactive), 1) = ms.MonthDate THEN s.StudentID END) AS Total_Quit,
        -- 当月净增减
        COUNT(DISTINCT CASE WHEN DATEFROMPARTS(YEAR(s.DateEntered), MONTH(s.DateEntered), 1) = ms.MonthDate THEN s.StudentID END)
        - COUNT(DISTINCT CASE WHEN DATEFROMPARTS(YEAR(s.DateInactive), MONTH(s.DateInactive), 1) = ms.MonthDate THEN s.StudentID END) AS Net_Gain
    FROM MonthSequence ms
    LEFT JOIN Students s ON 
        DATEFROMPARTS(YEAR(s.DateEntered), MONTH(s.DateEntered), 1) = ms.MonthDate
        OR DATEFROMPARTS(YEAR(s.DateInactive), MONTH(s.DateInactive), 1) = ms.MonthDate
    GROUP BY ms.MonthDate
)
SELECT
    FORMAT(MonthDate, 'yyyy-MM') AS YearMonth,
    Total_New,
    Total_Quit,
    Net_Gain,
    -- 当月初学生数：累计上月及之前的净增减，第一个月初始为0（若有历史数据可替换为初始值）
    COALESCE(SUM(Net_Gain) OVER(ORDER BY MonthDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS Total_Count_Start,
    -- 当月末学生数
    COALESCE(SUM(Net_Gain) OVER(ORDER BY MonthDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) + Net_Gain AS Total_Count_End,
    -- 流失率：当月流失数/当月初学生数，NULLIF避免除以0
    ROUND(CAST(Total_Quit AS DECIMAL(10,2)) / NULLIF(COALESCE(SUM(Net_Gain) OVER(ORDER BY MonthDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0), 0), 4) AS Attrition_Rate,
    -- 留存率：(当月末数-当月新增数)/当月初学生数
    ROUND(CAST((COALESCE(SUM(Net_Gain) OVER(ORDER BY MonthDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) + Net_Gain - Total_New) AS DECIMAL(10,2)) / NULLIF(COALESCE(SUM(Net_Gain) OVER(ORDER BY MonthDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0), 0), 4) AS Retention_Rate
FROM MonthlyMetrics
ORDER BY MonthDate;

关键指标说明 #

• Total_New：统计当月DateEntered的唯一学生ID数量，用DISTINCT避免重复统计（若学生表无重复则可省略）
• Total_Quit：统计当月DateInactive的唯一学生ID数量
• Net_Gain：直接通过新增减流失得到
• Total_Count_Start：利用SUM() OVER()窗口函数累计之前所有月份的净增减，第一个月默认初始学生数为0，若有历史数据可将COALESCE的第二个参数替换为历史期末数
• Total_Count_End：月初数加当月净增减
• Attrition_Rate：流失率保留4位小数，用NULLIF处理月初数为0的情况避免报错
• Retention_Rate：按公式计算，同样处理除数为0的情况

适配其他数据库说明 #

• MySQL：将DATEFROMPARTS替换为STR_TO_DATE(CONCAT(YEAR(date_col), '-', MONTH(date_col), '-01'), '%Y-%m-%d')，递归CTE改为用数字序列生成月份
• PostgreSQL：用DATE_TRUNC('month', date_col)替代日期拼接逻辑，递归CTE语法一致

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