SQL query: Find the element with the highest frequency-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW)

SQL query: Find the element with the highest frequency

P粉903969231 2023-09-12 10:36:13

440

I'm trying to write a SQL query to find the mode, i.e. the element that occurs more often than other elements. For example:

2,2,1,1---->在这里，输出应该为空（1和2都出现了两次）

    3,3,3----->在这里，输出也应该为空（没有第二个元素）

    3,3,1----->在这里，输出应该是3。（3的出现次数大于1的出现次数）

Here are the 3 conditions used to find it. How can I implement it?

P粉903969231

reply all(1)

P粉1627736262023-09-13 00:50:40 1 floor

You can count the number of values to find the most frequent value, and you can also filter based on the number of values:

select x.*
from (select val,
             count(*) as cnt,
             row_number() over (order by count(*) desc ) as seqnum,
             count(*) over () as num_vals
             count(*) over (partition by count(*)) as cnt_cnt
      from table 
      group by val
     ) x
where cnt_cnt = 1 and seqnum = 1 and num_vals > 1;

Actually, you can achieve this using the having clause and order by:

select val
from (select val, count(*) as cnt,
             count(*) over () as num_values
      from table 
      group by val
     ) v
where num_values > 1
order by cnt desc;