sql - 如何从最大计算中获取完整行？

Question

我确实遇到了 GROUP BY - 再次。我可以处理的基础知识，但就是:如何访问我在 group by 中命名的不同列，而不破坏我的分组？请注意 group by这只是我自己的想法，可能还有其他人做得更好。不过，它必须在 Oracle 中工作。

这是我的例子:

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) 创建一个结果行并打印出 groupname来自 state_a 的最高计算“等级”和 state_b .

Note that the first two rows have the same nums and thus only the higher ranking should be selected -- with the groupname being "Group 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

100% 完美将是

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

棘手的部分是，我想要 groupname结果中。我不能将它包含在 select 中，因为那样我就不得不把它放进 group by以及 - 我不想要的(然后我不会从所有组中选择最佳排名条目)

在我的解决方案中，使用 model表来计算“排名”。我确定还有其他解决方案。关键是，这是一个非平凡的计算，我不想做两次。

我从其他示例中知道可以使用第二个查询返回原始行以到达 groupname ，但我看不出我怎么能在这里，
不重复我的排名计算。

一个不错的建议是更换 group by与 LIMIT 1/ORDER BY goodness并使用此计算选择作为过滤子选择。但是 a) 没有 LIMIT在 Oracle 中，我怀疑是 rownum<=1会在子选择中进行，并且 b) 无论如何我都无法将大脑环绕在它周围。也许有办法？

Answer 1

您可以使用 FIRST 聚合修饰符有选择地将您的函数应用于组的行的子集——这里是单行( SQLFiddle demo ):

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修改器一次性获取所有列。