I am using Postgresql 13 and I have a table that looks something like this:

I need to get monotonically increasing values for the event_id column based on the timestamp order. So the above table will become something like:

Ideally I'd need this done in a single select statement, I tried a bunch of different approaches but nothing led me even close to what I need. Any suggestion? Thanks

CodePudding user response：

You can assign a unique row number to each "chunk" of similar event_ids:

with cte as (
   select (select sum(case when t1.event_id != t.event_id then 1 else 0 end) 
           from tbl t1 where t1.timestamp <= t.timestamp) k, t.* 
   from tbl t
)
select t3.r, v.value#>>'{}' from (select row_number() over (order by (
     select max(v.value::text) from json_array_elements(t2.js) v)) r, t2.* from (
       select t1.event_id, t1.k, json_agg(t1.timestamp) js from cte t1 
       group by t1.event_id, t1.k) t2) t3 
cross join json_array_elements(t3.js) v

See fiddle.

CodePudding user response：

Try the following using a running sum of a flag that is set to 1 whenever event_id is changed over the increasing of timestamp.

select sum(flag) over (order by timestamp)  1 as event_id,
       timestamp 
from
(
  select *,
  case 
   when lag(event_id, 1, event_id)
        over (order by timestamp) <> event_id
   then 1 else 0
  end as flag
  from table_name 
) T
order by timestamp 

See a demo.