按计数(*)排列一组项目

Question

我在使用Oracle分析函数时遇到一些问题，需要帮助。下面是一个通用示例：

create table test (item varchar2(10), value varchar2(10));
insert into test values ('item1','value1');
insert into test values ('item1','value1');
insert into test values ('item1','value1');
insert into test values ('item1','value1');
insert into test values ('item1','value1');
insert into test values ('item1','value2');
insert into test values ('item1','value2');
insert into test values ('item3','value2');
insert into test values ('item3','value2');
insert into test values ('item3','value2');
insert into test values ('item5','value1');
insert into test values ('item5','value1');
insert into test values ('item5','value1');
insert into test values ('item5','value1');
insert into test values ('item5','value1');
insert into test values ('item5','value1');
insert into test values ('item5','value1');
insert into test values ('item5','value2');
insert into test values ('item5','value2');
insert into test values ('item5','value2');
select item, value, count(*) c, 
       sum(count(*)) over () total, 
       sum(count(*)) over (partition by item) total_by_item,
       dense_rank() over (order by count(*) desc) dense_rank
  from test 
 group by item, value 
 order by 5 desc;

查询结果为：

ITEM       VALUE       C      TOTAL  TOTAL_BY_ITEM DENSE_RANK
---------- ---------- -- ---------- -------------- ----------
item5      value1      7         20             10          1
item5      value2      3         20             10          3
item1      value2      2         20              7          4
item1      value1      5         20              7          2
item3      value2      3         20              3          3

如何获得按TOTAL_by_ITEM排序的项目？所以它看起来是这样的：

ITEM       VALUE       C      TOTAL  TOTAL_BY_ITEM WHAT_I_NEED
---------- ---------- -- ---------- -------------- -----------
item5      value1      7         20             10           1
item5      value2      3         20             10           1
item1      value2      2         20              7           2
item1      value1      5         20              7           2
item3      value2      3         20              3           3

是否可以在没有其他联接或子查询的情况下实现这一点？我觉得这是可能的。我自然认为它必须是这样的：dense_rank（count（*））over（partition-by-item），就像我用来获得第5列的分析SUM一样，但它不起作用。

Answer 1

我不认为这是您要搜索的内容，但只是为了参考而不需要子查询，您可以使用MODEL子句获得相同的结果：

select item, value, c, total, total_by_item, what_i_need
from test 
group by item, value
model
  dimension by (row_number() over (order by null) d)
  measures (
    item, value,
    count(*) c,
    sum(count(*)) over () total,
    sum(count(*)) over (partition by item) total_by_item,
    1 what_i_need
  )
  rules (
    what_i_need[any] = dense_rank() over (order by total_by_item[cv()] desc)
  )
order by 5 desc;

我认为，如果没有子查询，就无法实现它。