0

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 |
+----+--------+-------------+
1
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.