sql - Recursive sum in Redshift -

sql - Recursive sum in Redshift -

create table testit ( id int, v1 int, v2 int, result int); insert testit (id, v1, v2, result) values (1, 1, 2, 1 ) , (2, 4, 3, 4 ) , (3, 6, 7, 6 ) , (4, null, 10, 13) , (5, null, 12, 25) ;

given first 3 columns id, v1, v2, want write query returns 'result' column:

v1 if v1 not null the (recursive) sum of preceding rows of v1 , v2 ig v1 null (or alternatively: lastly value of v1 , sum of v2 between first row v1 null , preceding row)

is possible? sqlfiddle link

the next query gets desired result. 3 different queries homecoming next result sets joined union all:

if v1 of current row not null

if v1 of current row null , v1 of previous row not null

if v1 of current row null , v1 of previous row null

select t_main.id, t_main.v1, t_main.v2, results.result testit t_main inner bring together ( select id, result testit v1 not null union select t1.id, max(t2.v1+t2.v2) sum_result testit t1 inner bring together testit t2 on t2.id = t1.id-1 , t2.v1 not null t1.v1 null grouping t1.id union select to1.id, max(to3.v1+to3.v2+to1.v2) testit to1 inner bring together testit to2 on to2.id = to1.id-1 , to2.v1 null inner bring together ( select t1.id t1_id, max(t3.id) t3_id testit t1 inner bring together testit t2 on t2.id = t1.id-1 , t2.v1 null inner bring together testit t3 on t3.id < t1.id , t3.v1 not null t1.v1 null grouping t1.id ) max_id on to1.id = max_id.t1_id inner bring together testit to3 on max_id.t3_id = to3.id grouping to1.id ) results on t_main.id = results.id order t_main.id;

performance-wise query may not best approach, there many self-joins, there quite few business rules well.

sql fiddle

sql postgresql amazon-redshift