Question 90751 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: increment column value in select statement based on previous values

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

event_id timestamp
1 2022-11-28 00:00:00
1 2022-11-28 00:00:10
2 2022-11-28 00:00:20
2 2022-11-28 00:00:30
2 2022-11-28 00:00:40
3 2022-11-28 00:00:50
3 2022-11-28 00:01:10
1 2022-11-28 00:01:20
2 2022-11-28 00:01:30
2 2022-11-28 00:01:40
3 2022-11-28 00:01:50
3 2022-11-28 00:02:10
3 2022-11-28 00:02:20
4 2022-11-28 00:02:30

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:

event_id timestamp
1 2022-11-28 00:00:00
1 2022-11-28 00:00:10
2 2022-11-28 00:00:20
2 2022-11-28 00:00:30
2 2022-11-28 00:00:40
3 2022-11-28 00:00:50
3 2022-11-28 00:01:10
4 2022-11-28 00:01:20
5 2022-11-28 00:01:30
5 2022-11-28 00:01:40
6 2022-11-28 00:01:50
6 2022-11-28 00:02:10
6 2022-11-28 00:02:20
7 2022-11-28 00:02:30

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search