Every N seconds, data about the user's state is written to the table. For example,

How to show start and end of state in different columns. Anything like this:

CodePudding user response：

In my opinion, you can use a recursive statement and partition windows function

Demo

with recursive
  cte_r as (
    select
      user_id,
      state_id,
      date_time_state,
      row_number() over () as rn
    from
      test),
  cte as (
    select
      user_id,
      state_id,
      date_time_state,
      rn,
      1 as grp
    from cte_r
    where rn = 1
    union all
    select
      cr.user_id,
      cr.state_id,
      cr.date_time_state,
      cr.rn,
      case
        when cr.user_id = c.user_id and cr.state_id = c.state_id 
          then grp
        else grp   1
      end
    from cte c,
         cte_r cr
    where c.rn = cr.rn - 1
  )
select
  user_id,
  state_id,
  min(date_time_state) as start_date_time_state,
  max(date_time_state) as end_date_time_state
from
  cte
group by user_id, state_id, grp
order by 3

CodePudding user response：

You can use a self-join:

with cte as (
   select s.*, coalesce(sum(case when s1.user_id = s.user_id and s1.state_id != s.state_id then 1 end), 0) rid
   from states s left join states s1 on s.date_time_state >= s1.date_time_state
   group by s.user_id, s.state_id, s.date_time_state order by s.date_time_state
)
select c.rid, c.user_id, c.state_id, min(c.date_time_state), max(c.date_time_state) 
from cte c group by c.rid, c.user_id, c.state_id;