sql - 窗口函数first_value的异常行为

我有两列-订单号，值。表值构造函数：

(1, null)
,(2, 5)
,(3, null)
,(4, null)
,(5, 2)
,(6, 1)

我需要得到

(1, 5) -- i.e. first nonnull Value if I go from current row and order by OrderNo
,(2, 5)
,(3, 2) -- i.e. first nonnull Value if I go from current row and order by OrderNo
,(4, 2) -- analogous
,(5, 2)
,(6, 1)

这是我认为应该有效的问题。

;with SourceTable as (
    select *
        from (values
            (1, null)
            ,(2, 5)
            ,(3, null)
            ,(4, null)
            ,(5, 2)
            ,(6, 1)
        ) as T(OrderNo, Value)
)
select
       *
       ,first_value(Value) over (
           order by
               case when Value is not null then 0 else 1 end
               , OrderNo
           rows between current row and unbounded following
       ) as X
   from SourceTable
order by OrderNo

问题是它返回的结果集与SourceTable完全相同。我不明白为什么。例如，如果第一行被处理（OrderNo=1），我希望X列返回5，因为frame应该包括所有行（当前行和未绑定的后续行），它首先按值排序-非空，然后按OrderNo排序。所以帧中的第一行应该是OrderNo=2。显然不是这样，但我不明白为什么。
如果有人解释第一帧是如何构建的，我将不胜感激。我需要这个用于SQL Server和Postgresql。
非常感谢

最佳答案

如果按case when Value is not null then 0 else 1 end, orderno

 orderno | value | x
---------+-------+---
       2 |     5 | 5
       5 |     2 | 2
       6 |     1 | 1
       1 |       |
       3 |       |
       4 |       |
(6 rows)

对于orderno=1，它后面的帧中没有不为空的内容。
相反，我们可以使用count作为子查询中的窗口函数将订单分组。然后，我们将max用作该组上的窗口函数（这是任意的，min也可以工作），以获取该组中的一个非空值：

with SourceTable as (
    select *
        from (values
            (1, null)
            ,(2, 5)
            ,(3, null)
            ,(4, null)
            ,(5, 2)
            ,(6, 1)
        ) as T(OrderNo, Value)
)
select orderno, order_group, max(value) OVER (PARTITION BY order_group) FROM (

    SELECT *,
       count(value) OVER (ORDER BY orderno DESC) as order_group
   from SourceTable
   ) as sub
order by orderno;
 orderno | order_group | max
---------+-------------+-----
       1 |           3 |   5
       2 |           3 |   5
       3 |           2 |   2
       4 |           2 |   2
       5 |           2 |   2
       6 |           1 |   1
(6 rows)

关于sql - 窗口函数first_value的异常行为，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58418463/