如何使用SQL获取出现频次最高的键值组合？

嘿，这个需求我经常碰到，核心逻辑其实很清晰——先统计每个*(key, value)*组合的出现频次，再找出每个key对应的最高频次，最后筛选出匹配这个最高频次的组合就行。下面给你几种不同的SQL实现思路，你可以根据自己用的数据库平台来适配：

不管用哪种SQL变体，本质都逃不开这三步：

• 第一步：统计所有*(key, value)*组合的出现次数
• 第二步：计算每个key对应的最大出现次数
• 第三步：把频次等于最大值的组合筛选出来

方法1：窗口函数（推荐，现代SQL首选） #

如果你的数据库支持窗口函数（比如PostgreSQL、SQL Server、MySQL 8.0+），这是最简洁的写法。用RANK()或者ROW_NUMBER()给每个key下的组合按频次排序：

WITH combo_counts AS (
    SELECT 
        key, 
        value, 
        COUNT(*) AS occurrence_count,
        -- RANK()会保留并列第一的所有组合，ROW_NUMBER()只会取一个
        RANK() OVER (PARTITION BY key ORDER BY COUNT(*) DESC) AS rank_num
    FROM your_log_table
    GROUP BY key, value
)
SELECT key, value, occurrence_count
FROM combo_counts
WHERE rank_num = 1;

这里用RANK()的好处是，如果某个key下有多个组合频次相同（比如假设a有两个组合都出现2次），所有并列第一的都会被返回；要是你只想取其中一个，换成ROW_NUMBER()就行。

方法2：子查询关联（兼容老版本SQL） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用嵌套子查询来实现：

SELECT t.key, t.value, t.occurrence_count
FROM (
    -- 先统计所有组合的出现次数
    SELECT key, value, COUNT(*) AS occurrence_count
    FROM your_log_table
    GROUP BY key, value
) t
JOIN (
    -- 再找出每个key对应的最大频次
    SELECT key, MAX(occurrence_count) AS max_count
    FROM (
        SELECT key, value, COUNT(*) AS occurrence_count
        FROM your_log_table
        GROUP BY key, value
    ) t1
    GROUP BY key
) t2 
ON t.key = t2.key AND t.occurrence_count = t2.max_count;

逻辑很直白：先算出所有组合的频次，再得到每个key的最大频次，最后把两者关联起来，筛选出匹配的组合。

方法3：HAVING子句动态匹配 #

还有一种更“紧凑”的写法，用HAVING子句在分组时动态计算当前key的最大频次：

SELECT t1.key, t1.value, COUNT(*) AS occurrence_count
FROM your_log_table t1
GROUP BY t1.key, t1.value
HAVING COUNT(*) = (
    SELECT MAX(cnt)
    FROM (
        SELECT COUNT(*) AS cnt
        FROM your_log_table t2
        WHERE t2.key = t1.key
        GROUP BY t2.value
    ) t3
);

这个方法的核心是，在HAVING里针对每个key，先算出它所有value的出现次数，再取最大值，然后筛选出等于这个最大值的组合。

这些思路都是通用的，你可以根据自己的数据库特性做微调——比如有些平台对GROUP BY的字段要求更严格，或者有内置函数可以简化统计逻辑。

内容的提问来源于stack exchange，提问作者jasonslyvia