mysql - MySQL 中输入计数最高的子查询

Question

我见过很多类似的问题，但无法将它们应用到我的情况中。我花了很多时间来解决这个问题，所以我很感激你们能给我的任何帮助。

背景:我有一个 Mosaic Plot我试图用数据驱动的两个输入属性相对于另一个的映射计数频率。数据来源于一条mysql语句。还有一些简单的过滤被应用。对于我正在使用的数据集，有很多小值，所以数据变得有点——杂乱无章。

目标:我的目标是从每个输入中取出最常出现的 N 个项目，然后将剩余的项目集中到“其他”容器中。 (最坏情况下降)。我相当确定我将不得不使用复杂的子查询来执行此操作。但是，我很难完全理解子查询语法。

注意:我可以处理生成计数或非计数的 SQL 结果，但由于性能限制和涉及按摩的工作，我可能更喜欢基于计数的输出稍后再说。

Example:   N=2
input1    input2
a           w
a           w
b           w
b           w
b           w
c           x
d           x
b           y
a           z

Output #1 (Preferred):
input1     input2    count
a           w          2
b           w          3
other       x          2
b           other      1
a           other      1

or 

Output #2:
input1     input2    
a           w
a           w
b           w
b           w
b           w
other       x
other       x
b           other
a           other

这是我的原始查询:

SELECT input1,input2 from `table` 
where attr3 != 'aaa'
and attr4 != 'bbb'
and attr5 != 'ccc'
and attr6 != 'ddd'

我已经能够修改它以返回以在子查询中使用...

SELECT input1,count(*) FROM `table`
where attr3 != 'aaa'
and attr4 != 'bbb'
and attr5 != 'ccc'
and attr6 != 'ddd'
GROUP BY input1
ORDER BY count(*) DESC
LIMIT 2

从这里开始，我尝试了 LEFT JOIN 语法和 UNION，但实际上完全没有成功。

Answer 1

如果我没理解错的话，您可以使用子查询来找到最常出现的 input1 和 input2 的适当值。然后，您可以加入这些信息并重新聚合:

select coalesce(i1.input1, 'other') as input1,
       coalesce(i2.input2, 'other') as input2,
       count(*)
from t left join
     (select input1, count(*) as cnt
      from t
      group by input1
      order by cnt desc
      limit 2
     ) i1
     on t.input1 = i1.input1 left join
     (select input2, count(*) as cnt
      from t
      group by input2
      order by cnt desc
      limit 2
     ) i2 
     on t.input2 = i2.input2
group by i1.input1, i2.input2;