postgresql 判断是空的_PostgreSQL last_值忽略空值

我知道已经有人问过了,但为什么下面的解决方案不起作用呢?我想填补

value

最后一个非空值由

idx

我看到的:

idx | coalesce

-----+----------

1 | 2

2 | 4

3 |

4 |

5 | 10

(5 rows)

我想要的是:

idx | coalesce

-----+----------

1 | 2

2 | 4

3 | 4

4 | 4

5 | 10

(5 rows)

with base as (

select 1 as idx

, 2 as value

union

select 2 as idx

, 4 as value

union

select 3 as idx

, null as value

union

select 4 as idx

, null as value

union

select 5 as idx

, 10 as value

)

select idx

, coalesce(value

, last_value(value) over (order by case when value is null then -1

else idx

end))

from base

order by idx