乾学院 53 号会员
1 回帖  •  58 浏览  • 
     ...

从 SQL 到 SPL：根据连续值生成计算列

 

某库表的字段 n 用于排序，x 字段是整数，有时会出现连续的 0。

n x
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

现在要新增计算列 def，要求是：def 初值为 0；如果当前行 x > 2，则 将 def 置为 1；遇到 3 个连续的 x=0 时，将当前 def 重置为 0；其他情况保持 def 与上一行相同。

n x def
1 0 0
2 1 0
3 2 0
4 3 1
5 4 1
6 2 1
7 1 1
8 0 1
9 1 1
10 0 1
11 0 1
12 0 0
13 0 0
14 1 0
15 2 0
16 3 1
17 4 1

SQL：

with cte as (
    select *
      ,(x > 2) exceeded_2
      ,(0 = all(array[     x
                      ,lag(x,1,0)over w1
                      ,lag(x,2,0)over w1
                     ]
                )
        ) as should_switch
    from have
    window w1 as (order by n) )
,cte2 as (
    select *,sum(should_switch::int)over(order by n) def_on_period 
    from cte
)
select n,x,(bool_or(exceeded_2) over w2)::int as def
from cte2
window w2 as (partition by def_on_period 
              order by n);

SQL要用多个窗口函数 + 多个子查询实现相对位置计算，代码复杂难懂。SPL 提供了表达相对位置的语法： https://try.esproc.com/splx?4OY


 　A
1 $select n, x, null as def from have.txt order by n
2 =A1.run(def=if( x>2:1, x[-2]+x[-1]+x==0:0; def[-1] ))

A1：加载数据，新增空的计算列。

A2：修改计算列，如果当前行 x>2，则 def 置为 1；如果连续 3 行都是 0，则 def 置为 0；否则 def 保持不变（置为上一行的 def）。[-1] 表示上一行。

问题来源：https://stackoverflow.com/questions/78128488/values-based-on-preceding-rows-when-x2-then-repeat-1-when-x-0-for-3-consecuti