Postgresql – How to get the last non-null value that came before the current row

postgresqlpostgresql-10window functions

Say I have the following table:

create table tab (ts integer, a integer);

insert into tab values (1, 1), (2, 2), (3, null), (4, null), (5, 3);

When i query the data there may be some nulls:

select * from tab;

+----+--------+
| ts |   a    |
+----+--------+
|  1 | 1      |
|  2 | 2      |
|  3 | (null) |
|  4 | (null) |
|  5 | 3      |
+----+--------+

Now I like to replace the null values with the first non-null value that comes before it. Like so:

+----+--------+
| ts |   a    |
+----+--------+
|  1 | 1      |
|  2 | 2      |
|  3 | 2      |
|  4 | 2      |
|  5 | 3      |
+----+--------+

I've tried using several variations of the FIRST_VALUE and LAST_VALUE window functions but they always only return first value overall, the last value overall or the current value.

SELECT
  *,
  first_value(a) OVER (
    ORDER BY CASE WHEN a IS NULL THEN NULL ELSE ts END NULLS LAST
  )
FROM
  tab
ORDER BY
  ts ASC;

-- OR

SELECT
  *,
  first_value(a) OVER (
    ORDER BY CASE WHEN a is NULL then 0 else 1 END DESC, ts
  )
FROM
  tab
ORDER BY
  ts ASC;

+----+--------+-------------+
| ts |   a    | first_value |
+----+--------+-------------+
|  1 | 1      |           1 |
|  2 | 2      |           1 |
|  3 | (null) |           1 |
|  4 | (null) |           1 |
|  5 | 3      |           1 |
+----+--------+-------------+

Best Answer

You can use a subquery:

select
  t1.ts,
  case when a is null then
    (select a 
     from tab t2 
     where t2.ts < t1.ts and a is not null 
     order by a desc
     limit 1)
  else a end as a
from
  tab t1;

ts |  a
-: | -:
 1 |  1
 2 |  2
 3 |  2
 4 |  2
 5 |  3

db<>fiddle here

Related Question