Querying last non-null values of time-series table in Postgres

Question

I have a time-series table which looks like the following:

time                |   a     | b        | c       | d
--------------------+---------+----------+---------+---------
2016-05-15 00:08:22 |         |          |         |         
2016-05-15 01:50:56 |         |          | 26.8301 |
2016-05-15 02:41:58 |         |          |         |            
2016-05-15 03:01:37 |         |          |         |            
2016-05-15 04:45:18 |         |          |         |         
2016-05-15 05:45:32 |         |          | 26.9688 |
2016-05-15 06:01:48 |         |          |         |         
2016-05-15 07:47:56 |         |          |         | 27.1269
2016-05-15 08:01:22 |         |          |         |            
2016-05-15 09:35:36 | 26.7441 | 29.8398  |         | 26.9981
2016-05-15 10:08:53 |         |          |         |         
2016-05-15 11:08:30 |         |          |         |         
2016-05-15 12:14:59 |         |          |         |         
2016-05-15 13:33:36 | 27.4277 | 29.7695  |         |                            
2016-05-15 14:36:36 | 27.4688 | 29.6836  |         |            
2016-05-15 15:37:36 | 27.1016 |          |         |

I want to return last non-null values of every column:

like this (best option):

time                | column   | value
--------------------+--------- +-------
2016-05-15 15:37:36 | a        | 27.1016
2016-05-15 14:36:36 | b        | 29.6836
2016-05-15 05:45:32 | c        | 26.9688
2016-05-15 09:35:36 | d        | 26.9981

like this:

column   | value
-------- +-------
a        | 27.1016
b        | 29.6836
c        | 26.9688
d        | 26.9981

or at least like this:

a       | b        | c       | d
--------+----------+---------+---------
27.1016 | 29.6836  | 26.9688 | 26.9981

Thanks!

@Miro . . . I'm confused by your data. If this time-series data, why aren't the rows in time order? Don't you want the most recent value? — Gordon Linoff
– Gordon Linoff, Commented Dec 1, 2019 at 21:53
You are totally right - there was a mistake in my data. It was supposed to be ordered. Fixing.... — Meir Tseitlin
– Meir Tseitlin, Commented Dec 2, 2019 at 2:29

Gordon Linoff · Accepted Answer · 2019-11-30 22:15:38Z

2

You can unpivot and select the last row:

select distinct on (v.which) t.time, v.which, v.val
from t cross join lateral
     (values (a, 'a'), (b, 'b'), (c, 'c'), (d, 'd')) v(val, which)
where v.val is not null
order by v.which, t.time desc;

answered Nov 30, 2019 at 22:15

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

4 Comments

forpas Over a year ago

This will return the last non nulls chronologically which is not what the OP wants. Check the expected output.

Meir Tseitlin Over a year ago

There was a problem with the data - the intension is correct..... Sorry about it...

Meir Tseitlin Over a year ago

This solution is neat, but for some reason, Postgres is not reusing my partial indices on data when executing it (CREATE INDEX "idx_nonnull_a" ON t (time, "a") WHERE "a" IS NOT NULL). Any idea?

Gordon Linoff Over a year ago

@Miro . . . I don't think tis is going to use any indexes. To use indexes will be much more complicated, if that is at all possible.

jorge is not ai · Accepted Answer · 2019-11-30 22:26:48Z

I suggest another answer, but I see now that @GordonLinoff answer is better.

with src as (
  select '0' as pos,    1    as a, 2    as b, null as c
  union all select '1', null as a, null as b, 7    as c
  union all select '2', 2    as a, null as b, 3    as c
  union all select '3', null as a, null as b, null as c
  union all select '4', null as a, 4    as b, null as c
),
n as (
 select row_number() over() as rn, src.* from src
)

(select last_value(pos) over (order by rn desc) as timestamp, 'a' as column, last_value(a) over (order by rn desc) as value 
from n
where a is not null
limit 1)
union all
(select last_value(pos) over (order by rn desc) as timestamp, 'b' as column, last_value(b) over (order by rn desc) as value 
from n
where b is not null
limit 1)
union all
(select last_value(pos) over (order by rn desc) as timestamp, 'c' as column, last_value(c) over (order by rn desc) as value 
from n
where c is not null
limit 1)

timestamp | column | value
:-------- | :----- | ----:
2         | a      |     2
4         | b      |     4
2         | c      |     3

db<>fiddle here

Collectives™ on Stack Overflow

Querying last non-null values of time-series table in Postgres

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related