null - 使用 Redshift 将空值替换为最后一个非空值

Question

我有一个叫做数量的表:

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

我想选择它，以便我可以用以前的非空值填充空格:

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

我在 i686-pc-linux-gnu 上使用 PostgreSQL 8.0.2，由 GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1499 编译

我怎么能做到这一点？

谢谢!

Answer 1

就像是last_value(quantity ignore nulls) over (order by date rows unbounded preceding)
它是一个窗口函数，返回指定窗口中的最后一个值