Oracle 中的 MAX(COUNT(*))

MAX(COUNT(*)) in Oracle

提问by antohoho

I need to take MAX of my count but it does not work, Oracle does not allow to deep aggregation..

我需要取 MAX 的计数，但它不起作用，Oracle 不允许深度聚合..

this is results without MAX:

这是没有 MAX 的结果：

187 1   2
187 3   1
159 1   1
159 3   1
159 2   8
188 2   9
188 1   2
188 3   1
187 2   9



select tt.token_id, tt.tag_variable_type_id, max(count(*)) as usage
from tokens tt
left outer join token_xref ttx on ttx.token_id = tt.token_id
where tag_variable_type_id in (1, 2, 3) and ttx.template_id = 52
group by tt.token_id, tt.tag_variable_type_id

I also tried to put it in having max(count(*)); but did not work

我也尝试将其放入 max(count(*)); 但没有用

Is there any way to get MAX of the COUNT() in one query and no inner select

有没有办法在一个查询中获得 COUNT() 的 MAX 而没有内部选择

I expect after MAX

我期待在 MAX 之后

187 2 9
159 2 8
188 2 9

EDIT

编辑

I have one more concern using analytic query instead of just guess which row to exclude if I have duplicates

我还有一个使用分析查询的问题，而不是仅仅猜测如果我有重复要排除哪一行

MIN(tag_variable_type_id) KEEP (DENSE_RANK LAST ORDER BY usage) AS tag_variable_type_id

I assigned to my variable type "Priority" using decode function, so my guess would be MIN(priority) KEEP (DENSE_RANK LAST ORDER BY usage) AS priority, see query

我使用解码函数分配给我的变量类型“优先级”，所以我的猜测是 MIN(priority) KEEP (DENSE_RANK LAST ORDER BY usage) AS priority，请参阅查询

But I am again loosing my tag_variable_type_id..

但我又失去了我的 tag_variable_type_id ..

anyway to keep it? I decode it back, but might be better way

无论如何要保留它？我解码回来，但可能是更好的方式

 decode (MIN(priority) KEEP (DENSE_RANK LAST ORDER BY usage), 1,2, 2,1, 3,3) as typevar,



select token_id,    
   MIN(priority) KEEP (DENSE_RANK LAST ORDER BY usage) AS priority,
   MAX(usage) AS usage 
from ( select tt.token_id, tt.tag_variable_type_id,  
              decode(
                tt.tag_variable_type_id,
                1, 2, 
                2, 1, 
                3, 3  
              ) as priority,
              count(*) as usage 
       from tag_tokens tt 
       left outer join template_token_xref ttx on ttx.token_id = tt.token_id 
       where tag_variable_type_id in (1, 2, 3) and ttx.template_id = 52 
       group by tt.token_id, tt.tag_variable_type_id)
group by token_id;

采纳答案by Andrew not the Saint

Try

尝试

select token_id, 
   MIN(tag_variable_type_id) KEEP (DENSE_RANK LAST ORDER BY usage) AS tag_variable_type_id,
   MAX(usage) AS usage from ( select tt.token_id, tt.tag_variable_type_id, count(*) as usage from tokens tt left outer join token_xref ttx on ttx.token_id = tt.token_id where tag_variable_type_id in (1, 2, 3) and ttx.template_id = 52 group by tt.token_id, tt.tag_variable_type_id ) group by token_id ;

Note1: MIN (tag_variable_type_id) is not necessarily correct, I just assume that it's functionally redundant, only required by the Oracle SQL syntax

注1：MIN(tag_variable_type_id)不一定正确，我只是假设它在功能上是多余的，只有Oracle SQL语法需要

Note2: There are other ways of writing the query without analytical functios, but this way is probably the most efficient

注2：还有其他不带分析函数的查询方式，但这种方式可能是最有效的

回答by Nicolas

You need to match lines from your original result set with another query on the same result set that gets the max of usage.

您需要将原始结果集中的行与获得最大使用率的同一结果集中的另一个查询相匹配。

We'll be using a WITHclause for clarity :

WITH为了清楚起见，我们将使用一个子句：

WITH result_count as (select tt.token_id, tt.tag_variable_type_id, count(*) as usage
    from tokens tt
       left outer join token_xref ttx on ttx.token_id = tt.token_id
       where tag_variable_type_id in (1, 2, 3) and ttx.template_id = 52
       group by tt.token_id, tt.tag_variable_type_id )
select result_count.token_id, tag_variable_type_id, max_usage
   from result_count   join  -- original result set
     (select token_id, max(usage) max_usage
        from result_count
        group by token_id) result_max on  -- result set with max usage
         result_count.token_id = result_max.token_id AND
         usage = max_usage ;

Now, if there are several tag_variable_type_idthat reach the max, you'll get several lines for one token_id; if you only need one you'll have to add an arbitrary condition.

现在，如果有几个tag_variable_type_id达到最大值，你会得到几行token_id；如果您只需要一个，则必须添加任意条件。

回答by Noel

ROW_NUMBERcan be used to assign unique row number to each row.

ROW_NUMBER可用于为每一行分配唯一的行号。

select token_id,tag_variable_type_id,usage
  from(
       select tt.token_id token_id,
              tt.tag_variable_type_id tag_variable_type_id,
              count(*) as usage,
              row_number() over (partition by tt.token_id order by count(*) desc) as r
         from tokens tt left outer join token_xref ttx
              on ttx.token_id = tt.token_id
        where tag_variable_type_id in (1, 2, 3)
          and ttx.template_id = 52
        group by tt.token_id, tt.tag_variable_type_id
        )
 where r= 1;

• order by count(*) descensures highest count gets row number 1 and next highest row number 2 and so on.
• partition by tt.token_idensures this numbering is reset for each group of token_id.

• order by count(*) desc确保最高计数获得行号 1 和下一个最高行号 2，依此类推。
• partition by tt.token_id确保为每组 token_id 重置此编号。

Use this row number in outer query to filter only those rows with row number 1.

在外部查询中使用此行号仅过滤那些行号为 1 的行。

回答by mustaccio

So you'll just need to wrap another select around what you already have:

所以你只需要在你已经拥有的东西周围再选择一个：

select token_id, max(usage) from (
 select tt.token_id, tt.tag_variable_type_id, max(count(*)) as usage
 from tokens tt
 left outer join token_xref ttx on ttx.token_id = tt.token_id
 where tag_variable_type_id in (1, 2, 3) and ttx.template_id = 52
 group by tt.token_id, tt.tag_variable_type_id
) 
group by token_id