mysql - 为 A 列的每个值选择 B 列的 N 个最频繁的值

Question

使用如下 MySQL 表:

id | colA | colB
...| 1    | 13
...| 1    | 13
...| 1    | 12
...| 1    | 12
...| 1    | 11
...| 2    | 78
...| 2    | 78
...| 2    | 78
...| 2    | 13
...| 2    | 13
...| 2    | 9

对于 colA 中的每个值，我想找到 colB 中 N 个最常见的值。

N=2 的示例结果:

colA | colB
1    | 13
1    | 12
2    | 78
2    | 13

我能够使用以下方法获得 colA 和 colB 及其频率的所有独特组合:

SELECT colA, colB, COUNT(*) AS freq FROM t GROUP BY colA, colB ORDER BY freq DESC;

结果示例:

colA | colB | freq
1    | 13   | 2
1    | 12   | 2
1    | 11   | 1
2    | 78   | 3
2    | 13   | 2
2    | 9    | 1

但是我很难为 colA 中的每个值而不是整个表应用 LIMIT。

这基本上就像 How to select most frequent value in a column per each id group? ，仅适用于 MySQL 而不是 PostgreSQL。

我目前使用的是 MariaDB 10.1。

Answer 1

如果可以的话，使用窗口函数:

SELECT colA, colB, freq
FROM (SELECT colA, colB, COUNT(*) AS freq,
             DENSE_RANK() OVER (PARTITION BY colA ORDER BY COUNT(*) DESC) as seqnum
      FROM t
      GROUP BY colA, colB 
     ) ab
WHERE seqnum <= 2;

请注意，您可能需要 DENSE_RANK()、RANK() 或 ROW_NUMBER()，具体取决于您希望如何处理关系。如果有 5 个 colB 值具有两个最高排名，则 DENSE_RANK() 将返回所有五个。

如果您想要恰好两个值，请使用ROW_NUMBER()。