CODEWITHSUNDEEP
sqldatabasepostgresql
Omid S.
Omid S.

Reputation: 761

extracting sequential state changes from a table's data

I'm trying to write a query to select continues blocks based on one field to turn it into state change records . basically I need to select only one "v1" if same value comes multiple times while ordered based on id. let's assume the following table : 

| id | v1 | v2 | v3 |
---------------------
|1   | a  |  b | b  |
|2   | a  |  e | b  |
|3   | z  |  b | b  |
|4   | z  |  o | p  |
|5   | a  |  h | t  |
|6   | a  |  i | k  |
|7   | b  |  g | p  |

desired results : 

|time sequence    | state  |
----------------------------
|1                | a      |
|2                | z      |
|3                | a      |
|4                | b      |

any body has any ideas?

Upvotes: 1

Views: 40

Answers (1)

klin
klin

Reputation: 121634

Use a window function, lag() in this case.

This query finds starts of series:

select id, v1, v1 is distinct from lag(v1) over (order by id) as start
from my_table

 id | v1 | start 
----+----+-------
  1 | a  | t
  2 | a  | f
  3 | z  | t
  4 | z  | f
  5 | a  | t
  6 | a  | f
  7 | b  | t
(7 rows)

Select only rows with start = true and add row numbers:

select row_number() over (order by id) as sequence, v1 as state
from (
    select id, v1, v1 is distinct from lag(v1) over (order by id) as start
    from my_table
    ) s
where start

 sequence | state 
----------+-------
        1 | a
        2 | z
        3 | a
        4 | b
(4 rows)

Upvotes: 2

Related Questions