如何从最大计算中获得完整的行？

Question

我确实和GROUP BY搏斗过我可以处理的基本知识是:如何在不破坏分组的情况下访问我在group by中命名的不同列？请注意，group by只是我自己的想法，也许还有其他更好的方法。不过，它必须在甲骨文中起作用。

以下是我的例子：

create table xxgroups (
  groupid int not null primary key,
  groupname varchar2(10)
);
insert into xxgroups values(100, 'Group 100');
insert into xxgroups values(200, 'Group 200');

drop table xxdata;
create table xxdata (
  num1 int,
  num2 int,
  state_a int,
  state_b int,
  groupid int,
    foreign key (groupid) references xxgroups(groupid)
);
-- "ranks" are 90, 40, null, 70:
insert into xxdata values(10, 10, 1, 4, 100);
insert into xxdata values(10, 10, 0, 4, 200);
insert into xxdata values(11, 11, 0, 3, 100);
insert into xxdata values(20, 22, 5, 7, 200);

任务是为每个distinct (num1, num2)创建一个结果行，并从state_a和state_b中打印出计算“排名”最高的groupname。

请注意，前两行具有相同的nums，因此只应选择较高的排名-- groupname为“组200”。

我想，我对基本的group by有了很大的了解。

SELECT xd.num1||xd.num2 nummer, max(ranking.goodness)
FROM xxdata xd
    , xxgroups xg
    ,( select state_a, state_b, r as goodness
       from dual
       model return updated rows
       dimension by (0 state_a, 0 state_b) measures (0 r)
       rules (r[1,4]=90, r[3,7]=80,r[5,7]=70, r[4,7]=60, r[0,7]=50, r[0,4]=40)
       order by goodness desc
      ) ranking
WHERE xd.groupid=xg.groupid
  and ranking.state_a (+) = xd.state_a
  and ranking.state_b (+) = xd.state_b
GROUP BY xd.num1||xd.num2
ORDER BY nummer
;

其结果是我所需要的90%：

nummer   ranking
----------------
1010     90
1111      
2022     70

百分之百完美

nummer   groupname
-------------------
1010     Group 100
1111     Group 100
2022     Group 200

• 棘手的部分是，我想在结果中的groupname。而且我不能将它包括在select中，因为这样我就必须把它也放入group by中--我不想这样做(这样我就不会从所有组中选择最佳的排名条目)。
• 在我的解决方案中，使用model表计算“秩”。我相信还有其他的解决办法。关键是，这是一个非平凡的计算，我不想做两次。
• 我从其他示例中了解到，可以使用第二个查询返回原始行以获得groupname，但我无法在这里看到如何做到这一点，同时又不重复我的排名计算。
• 一个很好的建议是将group by替换为LIMIT 1/ORDER BY goodness，并将此计算select用作筛选子select。但是a)甲骨文中没有LIMIT，我怀疑rownum<=1在子选择中会起什么作用；( b)无论如何，我都不能将我的大脑围绕在它周围。也许有办法？

Answer 1

您可以使用FIRST聚合修饰符来有选择地将您的函数应用于一个组的一个行的子集--这里是一个单行(SQLFiddle演示)：

SELECT xd.num1||xd.num2 nummer, 
       MAX(xg.groupname) KEEP (DENSE_RANK FIRST 
                               ORDER BY ranking.goodness DESC) grp,
       max(ranking.goodness)
FROM xxdata xd
    , xxgroups xg
    ,( select state_a, state_b, r as goodness
       from dual
       model return updated rows
       dimension by (0 state_a, 0 state_b) measures (0 r)
       rules (r[1,4]=90, r[3,7]=80,r[5,7]=70, r[4,7]=60, r[0,7]=50, r[0,4]=40)
       order by goodness desc
      ) ranking
WHERE xd.groupid=xg.groupid
  and ranking.state_a (+) = xd.state_a
  and ranking.state_b (+) = xd.state_b
GROUP BY xd.num1||xd.num2
ORDER BY nummer;

您的分析方法也可以工作，但是由于我们已经在这里使用聚合，所以我们最好使用FIRST修饰符一次得到所有列。

Answer 2

我以前确实搜索过，但现在我找到了这个答案，我可以接受它来回答我的问题。这里的甲骨文解决方案是over、partition by和order by以及row_number()。

select *
from ( select data.*, row_number()
         over (partition by nummer order by goodness desc) as seqnum
from (
SELECT xd.num1, xd.num2 nummer, xg.groupname, ranking.goodness
FROM xxdata xd
    , xxgroups xg
    ,( select state_a, state_b, r as goodness
       from dual
       model return updated rows
       dimension by (0 state_a, 0 state_b) measures (0 r)
       rules (r[1,4]=90, r[3,7]=80,r[5,7]=70, r[4,7]=60, r[0,7]=50, r[0,4]=40)
      ) ranking
WHERE xd.groupid=xg.groupid
  and ranking.state_a (+) = xd.state_a
  and ranking.state_b (+) = xd.state_b
ORDER BY nummer
) data )
where seqnum = 1
;

结果是

    10         10 Group 100          90          1
    11         11 Group 100                      1
    20         22 Group 200          70          1

这很美。

现在我必须试着去理解over在select中非常出色地做了什么.