时间戳

首页 > 解决方案 > 在大型mysql表中找到每个不同的其他值出现次数最多的值的有效方法

mysql - 在大型mysql表中找到每个不同的其他值出现次数最多的值的有效方法

问题描述

我有一个大约 6100 万行的 mysql 表。我对这个问题感兴趣的两列是“foo_type”和“foo_id”。我想要做的是，对于“foo_type”的每个不同值，返回出现次数最多的 foo_id。

因此，我的结果将如下所示：

foo_type  |  foo_id
-------------------
Banana    |  127321
Apple     |  59871
etc

等，意思是“当 foo_type 是 'Apple' 时，foo_id 59871 比 foo_id 的任何其他值出现的次数更多。”

这两列都使用单个多列索引（仅涵盖这两列）进行索引。

最有效的方法是什么？谢谢

编辑：我事先知道 foo_type 的所有可能值是什么，如果有帮助的话。

标签： mysqlsqlcountgreatest-n-per-group

解决方案

您正在描述一个称为模式的统计概念。一些数据库有一个内置的聚合函数（例如Oracle），但没有MySQL。

您可以使用聚合和窗口函数解决此问题：

select *
from (
    select foo_type, foo_id, count(*) cnt, 
        rank() over(partition by foo_type order by count(*) desc) rn
    from mytable 
) t
where rn = 1

这需要 MySQL 8.0。在早期版本中：

select foo_type, foo_id, count(*) cnt
from mytable t
group by foo_type, foo_id
having count(*) = (
    select count(*)
    from mytable t1
    where t1.foo_type = t.foo_type
    group by t1.foo_id
    order by count(*) desc limit 1
)

推荐阅读