Skip to content
Advertisement

Using window function in redshift to aggregate conditionally

I have a table with following data: input data

Link to test data: http://sqlfiddle.com/#!15/dce01/1/0

I want to aggregate the items column (using listagg) for each group in gid in sequence as specified by seq column based on the condition that aggregation ends when pid becomes 0 again for a group.

i.e. for group g1, there would be 2 aggregations; 1 for seq 1-3 and another for sequence 4-6; since for group g1, the pid becomes 0 for seq 4.

I expect the result for the given example to be as follows (Please note that seq in result is the min value of seq for the group where the pid becomes 0): result

Advertisement

Answer

I understand your question as a gaps and island problem, where you want to group together adjacent rows having the same gid untiil a pid having value 0 is met.

Here is one way to solve it using a window sum to define the groups: basically, a new island starts everytime a pid of 0 is met. The rest is just aggregation:

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement