需求:计算c4的逻辑:当 c2 = 1,则 c4 = 1;否则 c4 = (上一个 c4 + 当前的 c3) / 2
1、用枚举方式:
--准备数据
create or replace view test2(c1,c2,c3,c4) as values
(1,1,6,1),
(1,2,23,null),
(1,3,8,null),
(1,4,4,null),
(2,1,32,1),
(2,2,9,null),
(2,3,15,null),
(2,4,8,null);
select * from test2;
set spark.sql.shuffle.partitions=4;
select c1, c2, c3,
case when c2=1 then 1
else (lag(c4) over (partition by c1 order by c2)+c3)/2
end as c4
from test2;
--使用枚举的方式
--计算当c2=2时 ,c4=?
create or replace temporary view test_c2 as
select c1, c2, c3,
if(c2=2,(lag(c4) over (partition by c1 order by c2)+c3)/2,c4) as c4
from test2;
--计算当c2=3时 ,c4=?
create or replace temporary view test_c3 as
select c1, c2, c3,