how to aggregate adjacent rows?

user_10636332 0
Viewed 25

for example

1 A 1 E 1 
2 A 1 E 1
3 A 2 Q 1
4 D 3 P 2
5 B 2 Q 3
6 A 1 R 2

I want to receive group by 2nd column, in 3rd column return first value, in 4th column return last value , in 5th column return sum of 5th column values

A 1 Q 3 (A, 1 because its first value, Q because its last value, 3 because 1+1+1 from first 3 rows)
D 3 P 2
B 2 Q 3
A 1 R 2 (its not aggregate with first 3 rows, because not adjacent for them)

I tried smth like count(2nd) over (partition by 2nd order by 3rd) but its absolutely opposite my goal.

aggregate-functions window-functions sql
1 Answers

This is a variation of a gaps & islands problem, the key is to first partition into groups which you can do by subtracting a seqential number per partition from an overall sequential number.

You have not provided any table or column names so I've just used c1 - c5.

with g as (
  select *,
    Row_Number() over (order by c1) 
      - Row_Number() over(partition by c2 order by c1) gp
  from t
), fl as (
  select *,
    First_Value(c3) over(partition by gp order by c1) f, 
    First_Value(c4) over(partition by gp order by c1 desc) l
  from g
)
select c2, f, l, Sum(c5) s
from fl
group by c2, f, l
order by Min(c1);

See Example Fiddle

enter image description here

user_153326500
Related