列出常用MySQL中值最多的前10项对

Question

我有一张miRNAs和DNA (基因)表。每个miRNA调控许多不同的基因。我们的目标是列出10个共同调控基因最多的前10位miRNAS。

我使用的表有89,043行，如下所示：

mid   gid
-----------
1     1       
1     297     
1     305     
1     394     
1     450     
2     1       
2     2       
2     61      
2     72      
3     1       
3     12      
3     61      
3     450     

mid代表miRNA；gid是吉恩。正如你所看到的那样，每个miRNA调控多个基因。有1498个miRNAs调控~30-40个基因。目标是创建一组由每个miRNA调控的基因，然后以某种方式查看每个集合与其他集合共享的值，计数共享值，按计数排序共享数据，并选择前10位。我不知道如何在MySQL...not中进行集合和比较，甚至可以确定是否可以；看起来更像是Python的工作.

任何帮助都将不胜感激！

编辑:我需要找到前10对miRNA，它们有最多的共同调控基因。例如，miRNA #17和#42分别调控着30个相同的基因，而不是一般的30个基因。我真不知道怎么比较.

Answer 1

查找前十位miRNA项是一个简单的聚合查询。

SELECT COUNT(*) genes_regulated,
       mid
  FROM the_table
 GROUP BY mid
 ORDER BY COUNT(*) DESC
 LIMIT 10

编辑，但是你正在寻找那些共同调控基因的miRNAs对。

所以，让我们解决这个问题。JOIN的组合能力在这里有很大的帮助。此查询将给出具有相同mid的一对gid项的列表。(http://sqlfiddle.com/#!2/2279bb/3/0)

      SELECT a.mid amid, b.mid bmid, a.gid
        FROM t a
        JOIN t b ON a.gid = b.gid AND a.mid < b.mid

此查询为引用相同gid的每一对gid提供一行。请注意，ON子句都匹配表之间的gids，并且还防止自匹配(其中a.mid = b.mid)和重复对，即(q，r)和(r，q)。

接下来，我们需要总结一下虚拟表--子查询--对对。这是一个简单的COUNT() ... GROUP BY聚合查询。(http://sqlfiddle.com/#!2/2279bb/4/0)

SELECT COUNT(*) mid_pair_count, gid
  FROM (
          SELECT a.mid amid, b.mid bmid, a.gid
            FROM t a
            JOIN t b ON a.gid = b.gid AND a.mid < b.mid
        ) pairs
 GROUP BY gid
 ORDER BY COUNT(*) DESC
 LIMIT 10

编辑--如果你想让miRNA对的身份按基因的数量排列，而不是由调控它们的miRNA对来排列，这是查询中的一个微不足道的改变。(http://sqlfiddle.com/#!2/2279bb/5/0)

SELECT COUNT(*) gene_count, amid, bmid
  FROM (
          SELECT a.mid amid, b.mid bmid, a.gid
            FROM t a
            JOIN t b ON a.gid = b.gid AND a.mid < b.mid
        ) pairs
 GROUP BY amid, bmid
 ORDER BY COUNT(*) DESC
 LIMIT 10

最后(这里是结构化查询语言的结构开始真正开始的地方)，您可以使用GROUP_CONCAT()获得一个清单，列出在您的前十名中，哪些基因是由每对基因调控的。(http://sqlfiddle.com/#!2/2279bb/7/0)

SELECT COUNT(*) gene_count, 
       GROUP_CONCAT(gid ORDER BY gid) gene_list,
       amid, bmid
  FROM (
          SELECT a.mid amid, b.mid bmid, a.gid
            FROM t a
            JOIN t b ON a.gid = b.gid AND a.mid < b.mid
        ) pairs
 GROUP BY amid, bmid
 ORDER BY COUNT(*) DESC
 LIMIT 10

关键是: SQL是一个集处理工具。您可以使用SQL JOIN执行组合爆炸。您可以通过明智地在JOIN ... ON上使用来限制组合爆炸。最后，您可以通过GROUP BY对这些组合进行总结。连接表本身是探索巧合和组合的有力方法。

Answer 2

尝尝这个

 select mid,count(gid) from TABLE group by mid ORDER BY count(gid)