如何为外部连接产生的空值在列中选择先前的非空值

Question

假设我有一个表a，由于外部连接，它的特定列Value中有空值。现在，如果A表示一个值，比如cumsum，那么它在中间变为null是没有意义的

| ID  | Value |
| --- | ----- |
| 1   | null  |
| 2   | 576   |
| 3   | null  |
| 4   | 695   |
| 5   | null  |

那么是否可以将表A转换为表B呢?表B可能看起来像这样

| ID  | Value |
| --- | ----- |
| 1   | 0     |
| 2   | 576   |
| 3   | 576   |
| 4   | 695   |
| 5   | 695   |

因此，可以将列中的空值替换为先前的非空值，或者在不可用的情况下使用默认值0。这种转换还必须跨表中的所有列(大约有10列)进行。

Answer 1

应该与一般SQL兼容，绝对适用于postgreSQL:

SELECT a1.id, COALESCE(a2.value, 0) FROM
(
SELECT a1.id AS id, MAX(a2.id) as idref FROM a a1 LEFT JOIN a a2
ON a2.id <= a1.id AND a2.value IS NOT NULL GROUP BY a1.id
) AS a1
LEFT JOIN a a2 ON a2.id = a1.idref 

需要双连接-对我来说不是最优的，但至少可以。

关于您的注释:模式可以扩展，但是如果各自的现有值可能来自不同的行，则需要为每一列添加另外两个连接。

编辑:

虽然仍然需要两个子查询(而不是连接)，但这个变体似乎在postgre SQL上执行得稍微好一些:

SELECT
a1.id,
COALESCE
(
(
SELECT a2.value FROM a a2 WHERE a2.id =
(SELECT MAX(a3.id) FROM a a3 WHERE a3.id <= a1.id AND a3.value IS NOT NULL)
),
0
)
FROM a a1

对于每个额外的列，您仍然需要一组两个子查询(除非所有数据都可以从同一行检索)，尽管性能增益可能会累积起来。

Answer 2

WITH cte AS (
SELECT
id,
value,
count(value) OVER (ORDER BY id)
FROM (
VALUES (1, NULL),
(2, 576),
(3, NULL),
(4, 695),
(5, NULL),
(6, NULL)) g (id, value) ORDER BY id
)
SELECT
id,
value,
coalesce(first_value(value) OVER (PARTITION BY count ORDER BY id), 0)
FROM
cte
ORDER BY
id;