CODEWITHSUNDEEP
postgresql
Timur Mingulov
Timur Mingulov

Reputation: 2519

Filling gaps in postgresql

I have Actions table, which has rows ordered by time

|   time   | session   |
|----------|-----------|
| 16:10:10 | session_1 |
| 16:13:05 | null      |
| 16:16:43 | null      |
| 16:23:12 | null      |
| 16:24:01 | session_2 |
| 16:41:32 | null      |
| 16:43:56 | session_3 |
| 16:51:22 | session_4 |

I want to write a select which will put previous meaningful value instead of nulls

How to get this result with postgresql?

|   time   | session   |
|----------|-----------|
| 16:10:10 | session_1 |
| 16:13:05 | session_1 |
| 16:16:43 | session_1 |
| 16:23:12 | session_1 |
| 16:24:01 | session_2 |
| 16:41:32 | session_2 |
| 16:43:56 | session_3 |
| 16:51:22 | session_4 |

Upvotes: 0

Views: 411

Answers (1)

uncaught_exception
uncaught_exception

Reputation: 1078

update  Actions a
set session  = (
    select session 
      from Actions 
     where time = (
            select max(time) from Actions b 
             where b.time < a.time and session is not null
      )
) where session is null;

I tried this with 'time' as int and 'session' as int [easier to add data].

drop table Actions;    
create table Actions (time int, session int);    
insert into Actions values (1,10),(2,null),(3,null),(4,2),(5,null),(6,3),(7,4);    
select * from Actions order by time;    
update  Actions a ...;    
select * from Actions order by time;

enter image description here

EDIT

Response to your modified question.

select  a1.time, a2.session 
from    Actions a1
        inner join 
        Actions a2
        on a2.time = (
         select max(time) from Actions b 
             where b.time <= a1.time and session is not null
        )

Upvotes: 2

Related Questions