PostgreSQL last_value ignore nulls

Question

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

我看到的：

 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

Answer 1

您想要的是lag(ignore nulls)。这是使用两个窗口函数来完成您想要的事情的一种方法。第一个定义了NULL值的分组，第二个分配值：

select idx, value, coalesce(value, max(value) over (partition by grp))
from (select b.*, count(value) over (order by idx) as grp
      from base b
     ) b
order by idx;

您也可以通过使用数组而无需子征。基本上，以最后一个元素不计算NULL S：

select idx, value, 
       (array_remove(array_agg(value) over (order by idx), null))[count(value) over (order by idx)]
from base b
order by idx;

这是一个db＆lt;>小提琴。

Answer 2

好吧，last_value在这里对我没有意义，除非您可以向我指出。查看示例您需要最后一个非价值，您可以通过以下方式获得它：我正在组成一个带有空的组和以前的非零值，以便我可以获得第一个非价值。

with base as (
select 1    as idx , 2    as value   union
select 2    as idx, -14    as value    union
select 3    as idx , null as value   union
select 4    as idx , null as value   union
select 5    as idx , 1   as value
)
Select idx,value,
first_value(value) Over(partition by rn) as new_val
from(
select idx,value
    ,sum(case when value is not null then 1 end) over (order by idx) as rn
  from   base
) t

这是代码

http://sqlfiddle.com/#！15/fcda4/2

Answer 3

查看为什么解决方案不起作用，只需在窗框中订购订购，请查看输出：

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((窗口函数将在当前帧中选择最后一个值。没有更改任何帧默认值，这将是当前行。