如何用递归CTE基于上一行结果计算SQL表的services列？

问题场景 #

现有如下SQL表结构及数据：

create table my_table (
    month varchar(2),
    services integer null,
    s_registered integer,
    s_terminated integer
);

insert into my_table(month, services, s_registered, s_terminated) values
    ('01', 395, 14, 14),
    ('02', null, 5, 9),
    ('03', null, 19, 15),
    ('04', null, 30, 11),
    ('05', null, 13, 15),
    ('06', null, 8, 11),
    ('07', null, 10, 17),
    ('08', null, 13, 17),
    ('09', null, 5, 10),
    ('10', null, 10, 19),
    ('11', null, 7, 22),
    ('12', null, 18, 13);

需要计算每行的services列值，规则为：当前行services = 上一行services + s_registered - s_terminated。使用LAG()窗口函数仅能计算出第二行的有效值，后续行因依赖的上一行值为null无法得到正确结果，因此需要通过递归CTE实现连续计算。

解决方法 #

递归CTE包含锚点成员（初始数据）和递归成员（迭代计算）两部分，具体实现如下：

WITH recursive_services AS (
    -- 锚点成员：取第一个月的初始数据，作为计算起点
    SELECT 
        month,
        services,
        s_registered,
        s_terminated,
        CAST(month AS INT) AS month_num
    FROM my_table
    WHERE month = '01'

    UNION ALL

    -- 递归成员：按月份顺序关联上一行数据，计算当前月的services
    SELECT 
        t.month,
        rs.services + t.s_registered - t.s_terminated AS services,
        t.s_registered,
        t.s_terminated,
        CAST(t.month AS INT) AS month_num
    FROM my_table t
    JOIN recursive_services rs 
        ON CAST(t.month AS INT) = rs.month_num + 1
)
SELECT month, services, s_registered, s_terminated
FROM recursive_services
ORDER BY month_num;

代码说明 #

1. 锚点成员：筛选出month='01'的行，这是整个计算的起始基准，同时将月份字符串转为整数month_num，方便按顺序关联后续数据。
2. 递归成员：通过month_num + 1的关联条件，将原表的后续月份数据与递归CTE的上一行结果绑定，用上一行的services值推导当前行的计算结果。
3. 最终查询按month_num排序，输出完整的12个月计算结果，例如：
   • 02月：395 + 5 - 9 = 391
   • 03月：391 + 19 - 15 = 395
   • 以此类推，直到12月的最终值。

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