Оффлайн
- Регистрация
- 1 Мар 2015
- Сообщения
- 1,481
- Баллы
- 155
Problem description & analysis:
The field n of a certain database table is used for sorting, and the x field is an integer, sometimes with consecutive 0s.
Task: Now we need to add a calculated column def, which requires the initial value of def to be 0; If the current row x>2, set def to 1; When encountering three consecutive x=0, reset the current def to 0; In other cases, keep def the same as the previous row.
Code comparisons:
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 requires multiple window functions and multiple subqueries to implement relative position calculation, and the code is complex and difficult to understand.
SPL: SPL provides the syntax for expressing relative positions:
??
A1： Load data and add an empty calculated column.
A2： Modify the calculated column, if the current row x>2, set def to 1; If three consecutive rows are 0, then def is set to 0; Otherwise, the def remains unchanged (set to the previous row’s def). [-1] represents the previous row.
— Free Trial Available, Download Now!
The field n of a certain database table is used for sorting, and the x field is an integer, sometimes with consecutive 0s.
Task: Now we need to add a calculated column def, which requires the initial value of def to be 0; If the current row x>2, set def to 1; When encountering three consecutive x=0, reset the current def to 0; In other cases, keep def the same as the previous row.
Code comparisons:
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 requires multiple window functions and multiple subqueries to implement relative position calculation, and the code is complex and difficult to understand.
SPL: SPL provides the syntax for expressing relative positions:
??
A1： Load data and add an empty calculated column.
A2： Modify the calculated column, if the current row x>2, set def to 1; If three consecutive rows are 0, then def is set to 0; Otherwise, the def remains unchanged (set to the previous row’s def). [-1] represents the previous row.
— Free Trial Available, Download Now!