找到每组其他行的最大值

Question

i有一个简单的表格( ID(( GRP_ID(。

create table tst as
select 1 grp_id, 1 id from dual union all
select 1 grp_id, 1 id from dual union all
select 1 grp_id, 2 id from dual union all
select 2 grp_id, 1 id from dual union all
select 2 grp_id, 2 id from dual union all
select 2 grp_id, 2 id from dual union all
select 3 grp_id, 3 id from dual; 

使用分析函数找到每个组的最大值很简单。

select grp_id, id,
max(id) over (partition by grp_id) max_grp
from tst
order by 1,2;
    GRP_ID         ID    MAX_GRP
---------- ---------- ----------
         1          1          2 
         1          1          2 
         1          2          2 
         2          1          2 
         2          2          2 
         2          2          2 
         3          3          3 

，但目标是找到不包括当前行的值的最大值。

这是预期的结果(列MAX_OTHER_ID(：

   GRP_ID         ID    MAX_GRP MAX_OTHER_ID
---------- ---------- ---------- ------------
         1          1          2            2 
         1          1          2            2 
         1          2          2            1 
         2          1          2            2 
         2          2          2            2  
         2          2          2            2 
         3          3          3              

请注意，在GRP_ID = 2中存在最大值上的平局，因此max_other_id保持不变。

我确实管理了这两个步骤解决方案，但是我想知道是否有一个更简单，更简单的解决方案。

with max1 as (
select grp_id, id,
row_number() over (partition by grp_id order by id desc) rn
from tst
)
select GRP_ID, ID, 
case when rn = 1 /* MAX row per group */ then
  max(decode(rn,1,to_number(null),id)) over (partition by grp_id)
else
   max(id) over (partition by grp_id)
end as max_other_id   
from max1
order by 1,2

;

Answer 1

我希望窗口函数支持多个范围规格，例如：

max(id) over (
        partition by grp_id 
        order by id 
        range between unbounded preceding and 1 preceding
        or range between 1 following and unbounded following
        )

，但不幸的是它们没有。

作为解决方法，您可以在不同范围上使用该功能两次避免使用该功能，并在此拨打coalesce。

select grp_id,
    id,
    coalesce(
            max(id) over (
                partition by grp_id
                order by id 
                range between 1 following and unbounded following
                )
            , max(id) over (
                partition by grp_id 
                order by id 
                range between unbounded preceding and 1 preceding
                )
            ) max_grp
from tst
order by 1,
    2

cocece从开箱即用，因为作为窗口功能调用的结果，订购子句将是给定窗口中的最大值，或者是空值。

演示-http：//rextester.com/sdxvf13962

Answer 2

SELECT GRP_ID,ID, (SELECT Max(ID)  FROM TEST A WHERE A.ROWID<>B.ROWID AND A.GRP_ID=B.GRP_ID) maX_ID FROM TEST B;

通过共同查询获得了预期的结果！希望这会有所帮助。