此FIRST_VALUE查询有什么问题? [英] What's wrong with this FIRST_VALUE query?

问题描述

查询如下:

with t
as (
  select 450 id, null txt , 3488 id_usr from dual union all
  select 449   , null     , 3488        from dual union all
  select  79   , 'A'      , 3488        from dual union all
  select  78   , 'X'      , 3488        from dual 
)
select id
     , txt
     , id_usr
     , first_value(txt ignore nulls) over (partition by id_usr order by id desc) first_one
  from t

并返回:

ID  TXT     D_USR   FIRST_ONE
450         3488    
449         3488    
79  A       3488    A
78  X       3488    A

这是预期的:

ID  TXT     ID_USR  FIRST_ONE
450         3488    A
449         3488    A
79  A       3488    A
78  X       3488    A

怎么了?为什么?

推荐答案

FIRST_VALUE的默认RANGE / ROWS(与任何其他分析函数一样)为BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Default RANGE / ROWS for FIRST_VALUE (as for any other analytical function) is BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

如果添加IGNORE NULLS，则在构建范围时不会考虑NULL值.

If you add IGNORE NULLS, then NULL values are not taken into account when building the range.

RANGE变为BETWEEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCEPT FOR THE NULL ROWS(这不是有效的OVER子句).

The RANGE becomes BETWEEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCEPT FOR THE NULL ROWS (it's not a valid OVER clause).

由于txt的txt具有较高的id，因此将首先选择它们，并且它们的范围为空，因为它们和UNBOUNDED PRECEDING之间没有非NULL行.

Since your txt's that are NULL have high id's, they are selected first, and their ranges are empty, since there are no non-NULL rows between them and UNBOUNDED PRECEDING

您应该更改查询的ORDER BY或RANGE子句.

You should change either ORDER BY or RANGE clause of your query.

更改ORDER BY会将具有NULL id的行放在窗口的末尾，这样始终会首先选择非NULL值(如果有的话)，并且RANGE一定会从该位置开始值:

Changing ORDER BY puts the rows with NULL id's to the end of the window so that a non-NULL value (if any) will be always selected first, and the RANGE will guaranteedly start from that value:

with t
as (
  select 450 id, null txt , 3488 id_usr from dual union all
  select 449   , null     , 3488        from dual union all
  select  79   , 'A'      , 3488        from dual union all
  select  78   , 'X'      , 3488        from dual 
)
select id
     , txt
     , id_usr
     , first_value(txt) over (partition by id_usr order by NVL2(TXT, NULL, id) DESC) first_one
  from t

更改RANGE重新定义范围以包括分区中的所有非NULL行:

Changing RANGE redefines range to include all non-NULL rows in the partition:

with t
as (
  select 450 id, null txt , 3488 id_usr from dual union all
  select 449   , null     , 3488        from dual union all
  select  79   , 'A'      , 3488        from dual union all
  select  78   , 'X'      , 3488        from dual 
)
select id
     , txt
     , id_usr
     , first_value(txt IGNORE NULLS) over (partition by id_usr order by id DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_one
  from t

这篇关于此FIRST_VALUE查询有什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！