这个 FIRST_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 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

出了什么问题以及为什么？

Default RANGE / ROWS for FIRST_VALUE（对于任何其他分析函数）是BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

如果你添加IGNORE NULLS, then NULL构建范围时不考虑值。

The RANGE变成BETWEEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCEPT FOR THE NULL ROWS（这不是有效的OVER条款）。

自从你的txt那是NULL有高id的，它们首先被选择，并且它们的范围是空的，因为没有非NULL它们之间的行和UNBOUNDED PRECEDING

你应该改变ORDER BY or RANGE您的查询的子句。

改变ORDER BY将行与NULLid 到窗口的末尾，以便非NULL值（如果有）将始终首先被选择，并且RANGE肯定会从该值开始：

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分区中的行：

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