mySQL:在两列之间查找最常见的值

Question

我正在使用两个表:目标表有mid，gid和miRNA表有mid，miRNA_name。(mid是miRNA的ID，gid是基因的ID )。

我想列出最常见基因调控最多的10种miRNAs。每一行都应该有miRNA id #1，miRNA名称#1，miRNA id #2，miRNA名称#2，以及共同的基因数目。

目标表ex：

mid    gid
1      1
1      297
1      305
2      198
2      209
3      209
3      297

miRNA表ex：

mid      name
1       hsa-miR-4711
2       hsa-miR-36
3       hsa-miR-54

我想我需要把这两张桌子按中间分一组，然后再按gid分类。这样，我应该找到哪些gid具有公共的gid，并按照select子句中指定的方式列出它们以及公共基因的#(计数gid)。

到目前为止，我有以下内容，但我不确定这是否是正确的方法。任何帮助都将不胜感激。(预先多谢！)

SELECT m.mid, m.name, n.mid, n.name, COUNT (*)
FROM (SELECT mid, gid FROM miRNA JOIN targets USING (mid)) as m
AND (SELECT mid, gid FROM miRNA JOIN targets USING (mid)) as n
WHERE COUNT(*)>1
GROUP BY gid
LIMIT 10;

输出应该如下所示：

mid1    name1          mid2     name2         count
1       hsa-miR-4711   3        hsa-miR-54    297
2       hsa-miR-36     3        hsa-miR-54    209

Answer 1

只是为了确认..。您只关心最常见的两个miRNA条目。因此，本质上，将miRNA #1与miRNA #2中的所有gIDs、miRNA #3中的gIDs进行比较，等等。

如果m#7有7个与m#58相同的基因组if，m#23与m#49等有6个共同的基因组if。顺流而下，但m#7也可能与m#38有5个相同之处。如果是这样的话，这个示例的列表将显示如下

miNRA7  (17's name)   miNRA 58  (58's name)   7
miNRA23 (23's name)   miNRA 49  (49's name)   6
miNRA7  (17's name)   miNRA 38  (38's name)   5

如果是这样的话，我会做一个连接的自连接，以获得那些合格的，然后得到名称。我将确保您的目标表作为一个索引( gid，mid )作为一个索引，而不是两个单独的索引。

select
      TopPairs.mid,
      m1.name as NRAName1,
      TopPairs.AltMID,
      m2.name as NRAName2,
      TopPairs.PairCount
   from
      ( select
              t1.mid,
              t2.mid as AltMID,
              count(*) as PairCount
           from
              Targets t1
                 JOIN Targets t2
                        t1.gid = t2.gid
                    AND t1.mid < t2.mid
           group by
              t1.mid, 
              t2.mid
           having
              count(*) > 1
           order by
              count(*) DESC
           limit 10 ) TopPairs
      JOIN miRNA m1
         on TopPairs.mid = m1.mid
      JOIN miRNA m2
         on TopPairs.AltMID = m2.mid

连接到"t2“别名(第二个实例)首先是在匹配的gIDs上显式的，然后才是那些t2.mid大于t1.mid的gIDs。这样就可以将#1与#2、#3、#4等进行比较，但不必将#2、#3或#4与#1进行比较，因为它们已经完成了。

只有在基因组比较完成并且有了有限的列表之后，这些列表才会被加入到命名的miNRA表中，以获取输出的显式名称。如果你扔掉300 K的基因组比较并保留10，就不需要得到这些细节。拿到10，然后加入。