PostgreSQL use value from previous row if missing

Question

I have a following query:

WITH t as (
  SELECT date_trunc('hour', time_series) as trunc 
  FROM generate_series('2013-02-27 22:00'::timestamp, '2013-02-28 2:00', 
                       '1 hour') as time_series
  GROUP BY trunc
  ORDER BY trunc
)
SELECT DISTINCT ON(trunc) trunc, id
FROM t
LEFT JOIN (
   SELECT id, created, date_trunc('hour', created) as trunc_u
   FROM event
   ORDER BY created DESC
) u
ON trunc = trunc_u

which yields the following result:

"2013-02-27 22:00:00";
"2013-02-27 23:00:00";2
"2013-02-28 00:00:00";5
"2013-02-28 01:00:00";
"2013-02-28 02:00:00";

Table event has id, created and some other columns, but only those are relevant here. The query above gives me id of last event generated per given trunc time period (thanks to DISTINCT ON I get a nice aggregation per period).

Now, this query yields NULL if no events happened in given time period. I would like it to return the previous available id, even if it is from different time period. I.e.:

"2013-02-27 22:00:00";0
"2013-02-27 23:00:00";2
"2013-02-28 00:00:00";5
"2013-02-28 01:00:00";5
"2013-02-28 02:00:00";5

I am sure I am missing some easy way to accomplish this. Any advice?

dani herrera · Accepted Answer · 2013-02-28 20:45:26Z

4

You ca mix a self join and windows functions

Simplifying I take this table with this sample values:

create table t ( a int, b int);    
insert into t values 
( 1, 1),
( 2, Null),
( 3, Null),
( 4, 2 ),
( 5, Null),
( 6, Null);

In your query a is trunc_u and b is your id. The query is:

with cte as (    
    select 
      t1.a, 
      coalesce( t1.b, t2.b, 0) as b,
      rank() OVER 
       (PARTITION BY t1.a ORDER BY t2.a DESC) as pos
    from t t1 
    left outer join t t2
      on t2.b is not null and
         t2.a < t1.a    
)
select a, b
from cte
where pos = 1;

And results:

| A | B |
---------
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |

answered Feb 28, 2013 at 20:45

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pajton Over a year ago

Thanks! Though I think I'll use Igor's solution, because it's simpler.

Ihor Romanchenko · Accepted Answer · 2013-02-28 20:50:33Z

3

Try:

WITH t as (
  SELECT time_series as trunc 
    FROM generate_series('2013-02-27 22:00'::timestamp, '2013-02-28 2:00', 
                         '1 hour') as time_series
)
SELECT DISTINCT ON(t.trunc) t.trunc, e.id
  FROM t
  JOIN event e
    ON e.created < t.trunc 
 ORDER BY t.trunc, e.created DESC

If it is too slow - tell me. I will give you a faster query.

edited Feb 28, 2013 at 20:50

answered Feb 28, 2013 at 20:45

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

2 Comments

pajton Over a year ago

This is exactly what I was looking for, thanks! Speed is not much of a problem here, cause it is for reports, not often generated.

Tom Over a year ago

@Ihor what will be a faster query? I need exactly something like this, but for. +50 million rows on a regular basis ;) THX!

Collectives™ on Stack Overflow

PostgreSQL use value from previous row if missing

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related