2013/6/1 Shenli Zhu <zhushenli@xxxxxxxxx>
--
Victor Y. Yegorov
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
