窗口函数first_value的异常行为 [英] Unexpected behavior of window function first_value
问题描述
我有2列-订单号,值。表值构造函数:
I have 2 columns - order no, value. Table value constructor:
(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)
这是我认为应该可以使用的查询。
This is query that I think should work.
;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,因为框架应该包含所有行(当前行和未绑定的行),并且它按Value排序-首先是非null,然后是OrderNo。因此,框架中的第一行应为OrderNo = 2。显然,它不是那样工作的,但我不明白为什么。
The issue is that it returns exactly same resultset as SourceTable. I don't understand why. E.g., if first row is processed (OrderNo = 1) I'd expect column X returns 5 because frame should include all rows (current row and unbound following) and it orders by Value - nonnulls first, then by OrderNo. So first row in frame should be OrderNo=2. Obviously it doesn't work like that but I don't get why.
如果有人解释了第一帧的结构,我将不胜感激。我需要SQL Server和Postgresql。
Much appreciated if someone explains how is constructed the first frame. I need this for SQL Server and also Postgresql.
非常感谢
推荐答案
很容易看出为什么如果按的情况对结果进行排序(如果Value不为null则为0,否则1结束,orderno
It's pretty easy to see why first_value doesn't work if you order the results by 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,框架中没有任何内容
For orderno=1, there's nothing after it in the frame that would be not-null.
相反,我们可以使用count作为子查询的窗口函数将订单分为几组。然后,我们使用max作为该组上的窗口函数(这是任意的,min也可以工作)来获得该组中的一个非空值:
Instead, we can arrange the orders into groups using count as a window function in a sub-query. We then use max as a window function over that group (this is arbitrary, min would work just as well) to get the one non-null value in that group:
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)
这篇关于窗口函数first_value的异常行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!