基于PostgreSQL实现SAS风格的前序行依赖def变量计算

需求说明 #

变量x为计数器，def为布尔值（用0/1表示），初始值均为0：

• 当任意时刻x > 2时，def从此刻起设为1；
• 若def = 1，需连续3个周期x = 0才能将def重置为0，否则def保持1。

背景与问题 #

具备SAS开发背景，需迁移至PostgreSQL实现上述逻辑。已完成SAS实现代码，尝试通过游标实现PostgreSQL版本，但卡在调用前序行值进行当前行计算的步骤，已完成建表语句及未完成的PL/pgSQL函数代码，寻求完整解决方案。

附原代码 #

SAS实现代码 #

data have;
input n x;
datalines;
1 0
2 1
3 2
4 3
5 4
6 2
7 1
8 0
9 1
10 0
11 0
12 0
13 0
14 1
15 2
16 3
17 4
;
run;


data want(drop=prev_: cur_m def rename=def_new=def);
set have;
retain  prev_def prev_cur_m;
if x > 2 then def = 1;
else if prev_def = 1 and x > 0 then def = 1;
else def = 0;

if _n_ = 1 then cur_m = -1;
else if def = 1 then cur_m = -1;
else if prev_def = 1 and def = 0 then cur_m = 0;
else if -1 < prev_cur_m < 3 and x > 0 then cur_m = 0;
else if -1 < prev_cur_m < 3 and x = 0 then cur_m + 1;
else cur_m = -1;

if _n_ = 1 then def_new = 0;
else if def = 1 then def_new = 1;
else if -1 < cur_m < 3 then def_new = 1;
else def_new = 0;

output;
prev_def = def;
prev_cur_m = cur_m;
run;

PostgreSQL已完成代码 #

create table have (
    n int,
    x int
) ;
insert into have (n,x)
values (1,0),(2,1),(3,2),(4,3),(5,4),(6,2),
       (7,1),(8,0),(9,1),(10,0),(11,0),(12,0),
       (13,0),(14,1),(15,2),(16,3),(17,4);

create or replace function calc_dpd()
returns table (
    n int,
    x int,
    def int,
    cure_m int,
    def_new int )
language plpgsql as $f$
declare rec record;
begin
    for rec in (select * from have order by n) loop 
        if rec.n = 1 then 
            def = 0;
            cure_m = 0;
            def_new = 0;
        end if;
        if x > 2 then 
            def = 1;
        end if;
--  here I need to test if def from previous row = 1 and current def = 0;
        select rec.n, rec.x
        into n,x;
        return next;
    end loop;
end $f$;

完整解决方案 #

在PostgreSQL的PL/pgSQL中，通过声明持久化变量保存前一行状态（对应SAS的retain语句），即可实现依赖前序行的计算逻辑。以下是补全并修正后的函数：

create or replace function calc_dpd()
returns table (
    n int,
    x int,
    def int,
    cur_m int,
    def_new int
)
language plpgsql as $f$
declare
    rec record;
    -- 持久化前一行状态，对应SAS的retain变量
    prev_def int := 0;
    prev_cur_m int := -1;
begin
    for rec in (select * from have order by n) loop 
        declare
            -- 当前行临时计算变量
            cur_def int := 0;
            cur_cur_m int := -1;
            cur_def_new int := 0;
        begin
            -- 计算当前def值
            if rec.x > 2 then
                cur_def := 1;
            elsif prev_def = 1 and rec.x > 0 then
                cur_def := 1;
            else
                cur_def := 0;
            end if;

            -- 计算当前cur_m值
            if rec.n = 1 then
                cur_cur_m := -1;
            elsif cur_def = 1 then
                cur_cur_m := -1;
            elsif prev_def = 1 and cur_def = 0 then
                cur_cur_m := 0;
            elsif prev_cur_m > -1 and prev_cur_m < 3 and rec.x > 0 then
                cur_cur_m := 0;
            elsif prev_cur_m > -1 and prev_cur_m < 3 and rec.x = 0 then
                cur_cur_m := prev_cur_m + 1;
            else
                cur_cur_m := -1;
            end if;

            -- 计算当前def_new值
            if rec.n = 1 then
                cur_def_new := 0;
            elsif cur_def = 1 then
                cur_def_new := 1;
            elsif cur_cur_m > -1 and cur_cur_m < 3 then
                cur_def_new := 1;
            else
                cur_def_new := 0;
            end if;

            -- 赋值并返回当前行结果
            n := rec.n;
            x := rec.x;
            def := cur_def;
            cur_m := cur_cur_m;
            def_new := cur_def_new;
            return next;

            -- 更新前一行状态变量，供下一行计算使用
            prev_def := cur_def;
            prev_cur_m := cur_cur_m;
        end;
    end loop;
end $f$;

核心要点 #

1. 状态持久化：用prev_def和prev_cur_m在循环外保存前一行的计算结果，替代SAS的retain功能；
2. 变量隔离：循环内部声明临时变量cur_def、cur_cur_m、cur_def_new，避免跨循环的变量污染；
3. 逻辑对齐：严格遵循SAS原代码的分支判断顺序，确保计算结果与SAS完全一致；
4. 调用方式：执行select * from calc_dpd();即可得到与SASwant表相同的输出。

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