Re: Sum raw with the same continuous flags

2013/6/1 Shenli Zhu <zhushenli(at)gmail(dot)com>

> Hi, there are 2 columns, flag(boolean) and num(integer),in a table.
> Table is like
> | flag | num |
> |------+-----|
> | 1 | 2 | \ 5
> | 1 | 3 | /
> | 0 | 1 | \ 7
> | 0 | 6 | /
> | 1 | 4 | \ 9
> | 1 | 5 | /
> | ... | ... |
> I want to sum up the raw with the same continuous flags. E.g. flag in 1st
> and
> 2nd row are both 1, 3rd and 4th are both 0, 5th and 6th are both 1. So
> the table becomes
> | flag | num |
> |------+-----|
> | 1 | 5 |
> | 0 | 7 |
> | 1 | 9 |
>
> Can I do this in SQL or PL/pgSQL? Any suggestions are welcome.
>

WITH data(flag,num) AS (VALUES
(true,2),(true, 3),
(false,1),(false,6),
(true,4),(true,5))
SELECT flag,
sum(num) AS sum_num
FROM (
SELECT flag,num,
sum(grp_flag) OVER (ORDER BY rn) AS grp
FROM (
SELECT flag,num,
row_number() OVER() AS rn,
CASE WHEN lag(flag) OVER () = flag THEN NULL ELSE 1 END AS
grp_flag
FROM data
) s1
) s2
GROUP BY grp,flag
ORDER BY grp;

You should introduce some explicit ordering column into your table though,
as results will
change otherwise based on your DB activity.

Inspired by this answer: http://stackoverflow.com/a/10624628/1154462

--
Victor Y. Yegorov