Sql 基于前面行的值：当x&>2时重复1，当连续3行x=0时则重复0

我的任务是: 变量x和def分别是计数器和布尔值.两者都从零开始.

1. 如果在任何时间点x > 2，则从该点开始def = 1.
2. 现在，如果是def = 1，那么为了使它再次变为零，x必须在连续3个周期内等于0，否则def仍然是1.

我有SAS的背景，现在开始更广泛地使用PostgreSQL:

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;

据我所知，这可以用游标来实现，并试图使用这post来解决这个问题.然而，当我需要使用前一行中的值来计算当前值时，我陷入了这个步骤.以下是代码:

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$;