如何用Lag/Lead等窗口函数计算订阅量期初期末数并生成报表

咱们先明确下需求：你需要基于每月的new（新增用户）和churn（流失用户）数据，生成包含Start、Net、End的计算表，规则是：

• Start = 上月的End值，第一个月的Start为0
• Net = new - churn
• End = Start + Net

先把你的输入和预期输出整理成更清晰的表格：

输入数据 #

预期输出（补全后） #

实现方案：用窗口函数搞定 #

核心思路是结合累积求和窗口函数和Lag窗口函数，一步到位计算出所有字段，不用写循环或者自关联，效率很高。

具体SQL代码（以PostgreSQL为例，其他数据库逻辑一致） #

WITH monthly_metrics AS (
    -- 先计算每个月的Net值
    SELECT 
        month,
        year,
        new,
        churn,
        new - churn AS Net
    FROM your_input_table
)
SELECT 
    month,
    year,
    -- 取上月的End作为本月Start，第一行用0填充
    COALESCE(LAG(End) OVER (ORDER BY year, month), 0) AS Start,
    new,
    churn,
    Net,
    -- 累积求和计算End：从第一个月到当前月的Net总和
    SUM(Net) OVER (ORDER BY year, month) AS End
FROM monthly_metrics
ORDER BY year, month;

代码解释 #

1. CTE部分：先通过monthly_metrics计算出每个月的Net，这一步很直观，就是新增减流失。
2. 主查询部分：
   • SUM(Net) OVER (ORDER BY year, month)：这个窗口函数会按年月顺序，计算从第一行到当前行的Net总和，正好对应End的规则——因为End是上月End加本月Net，本质就是所有历史Net的累积和。
   • COALESCE(LAG(End) OVER (ORDER BY year, month), 0)：LAG(End)会抓取当前行的上一行的End值（也就是上月的End），作为本月的Start。第一行没有上一行，所以用COALESCE把NULL替换成0，满足第一个月Start=0的要求。
3. 排序：最后按year和month排序，保证结果的时间顺序正确。

简化版（不用CTE） #

如果你的数据库不支持CTE，也可以把逻辑合并成一个查询，可读性稍差但功能一样：

SELECT 
    month,
    year,
    COALESCE(LAG(SUM(new - churn) OVER (ORDER BY year, month)) OVER (ORDER BY year, month), 0) AS Start,
    new,
    churn,
    new - churn AS Net,
    SUM(new - churn) OVER (ORDER BY year, month) AS End
FROM your_input_table
ORDER BY year, month;

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