Replace nulls with the last non-null value using Redshift

Question

I have a table called quantities:

+----------+----------+
| date     | quantity |
+----------+----------+
| 30/11/17 | 90       |
+----------+----------+
| 01/12/17 |          |
+----------+----------+
| 02/12/17 |          |
+----------+----------+
| 03/12/17 | 1622     |
+----------+----------+
| 04/12/17 |          |
+----------+----------+
| 05/12/17 | 9092     |
+----------+----------+
| 06/12/17 |          |
+----------+----------+
| 07/12/17 |          |
+----------+----------+
| 08/12/17 | 2132     |
+----------+----------+
| 09/12/17 |          |
+----------+----------+
| 10/12/17 | 2889     |
+----------+----------+

And I want to select it so that I can fill in the blanks with the previous non-null value:

+----------+----------+
| date     | quantity |
+----------+----------+
| 30/11/17 | 90       |
+----------+----------+
| 01/12/17 | 90       |
+----------+----------+
| 02/12/17 | 90       |
+----------+----------+
| 03/12/17 | 1622     |
+----------+----------+
| 04/12/17 | 1622     |
+----------+----------+
| 05/12/17 | 9092     |
+----------+----------+
| 06/12/17 | 9092     |
+----------+----------+
| 07/12/17 | 9092     |
+----------+----------+
| 08/12/17 | 2132     |
+----------+----------+
| 09/12/17 | 2132     |
+----------+----------+
| 10/12/17 | 2889     |
+----------+----------+

I am using PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1499

How could I achieve this?

Thanks!

score 20 · Accepted Answer · edited Dec 01 '17 at 18:44

20

something like last_value(quantity ignore nulls) over (order by date rows unbounded preceding)

it's a window function that returns the last value in the specified window

edited Dec 01 '17 at 18:44

Henry

697
1
6
16

answered Dec 01 '17 at 11:23

AlexYes

4,088
2
15
23

working as expected, it's been very helpful. thank you – Raj Kamuni Apr 06 '20 at 08:18

score -1 · Answer 2 · answered Dec 01 '17 at 11:27

You may use something on the lines of:

select date,(select t.quantity from [tablename] t where t.quantity is not null and t.date <= t1.date
order by t.date desc limit 1) from [tablename] t1;

t.quantity is not null: It makes sure you don't get null values in result set.

t.date <= t1.date: It ensures the last known value is picked.

Replace nulls with the last non-null value using Redshift

2 Answers2