I have data like,
| index | flag |
|---|---|
| 0 | F |
| 1 | T |
| 2 | T |
| 3 | T |
| 4 | F |
| 5 | F |
| 6 | T |
And I want to write a Postgres query to convert this to,
| index | flag | group |
|---|---|---|
| 0 | F | 0 |
| 1 | T | 1 |
| 2 | T | 1 |
| 3 | T | 1 |
| 4 | F | 2 |
| 5 | F | 2 |
| 6 | T | 3 |
Where the group increments every time the flag changed between true and false.
How can I do this?
With LAG() and SUM() window functions:
SELECT "index", flag, SUM(r::int) OVER (ORDER BY "index") "group"
FROM (
SELECT *, flag <> LAG(flag, 1, flag) OVER (ORDER BY "index") r
FROM tablename
) t
See the demo.
Results:
| index | flag | group |
|---|---|---|
| 0 | f | 0 |
| 1 | t | 1 |
| 2 | t | 1 |
| 3 | t | 1 |
| 4 | f | 2 |
| 5 | f | 2 |
| 6 | t | 3 |