PostgreSQL last_value忽略null [英] PostgreSQL last_value ignore nulls

问题描述

我知道已经有人问过这个问题，但是为什么下面的解决方案不起作用？我想用 idx 排序的最后一个非空值填充 value 。

I know this already been asked, but why doesn't the solution below work? I want to fill value with the last non-null value ordered by 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

基本
的订单

推荐答案

要了解您的解决方案为何不起作用，请按照窗口框架中的顺序查看输出：

To see why your solution doesn't work, just look at the output if you order by the ordering in your window frame:

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, value from base
order by case when value is null then -1
                                                 else idx
                                                 end;
 idx | value
-----+-------
   3 |
   4 |
   1 |     2
   2 |     4
   5 |    10

last_value（）窗口函数将选择当前帧中的最后一个值。在不更改任何框架默认值的情况下，这将是当前行。

The last_value() window function will pick the last value in the current frame. Without changing any of the frame defaults, this will be the current row.

这篇关于PostgreSQL last_value忽略null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！