CODEWITHSUNDEEP
postgresql
user3002273
user3002273

Reputation:

Postgres query for grouping series by boolean flag

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?

Upvotes: 1

Views: 48

Answers (1)

forpas
forpas

Reputation: 164139

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

Upvotes: 0

Related Questions