mysql - 按 id 对行进行分组，然后将不同的组相互比较

Question

我有一张表，里面有很多seq_id的数据。每个 seq_id 在不同的行上有很多命中 (hit_name_id)。我想做的是将 seqs 分组，如果它们的命中率相似(即共享约 70-80% 的命中率)，例如下表中的序列 1,2 和 4 实际上非常相似，因此它们很可能是一样。我希望能够为所有相似的命中分配一个组 ID，以便我以后可以只提取唯一的序列。

我创建这个查询是为了证明每个 seq_id 可以有很多可能共享也可能不共享的命中:

mysql> SELECT seq_id,GROUP_CONCAT(hit_name_id ORDER BY hit_name_id), count(hit_name_id) FROM polished_data
    -> GROUP BY seq_id;
+--------+------------------------------------------------+--------------------+
| seq_id | GROUP_CONCAT(hit_name_id ORDER BY hit_name_id) | count(hit_name_id) |
+--------+------------------------------------------------+--------------------+
|      1 | 4,5,6,9,10,14,19,20,21                         |                  9 |
|      2 | 4,6,9,10,14,18,19,20,21                        |                  9 |
|      3 | 6,12,13,14,18,20                               |                  6 |
|      4 | 4,7,8,11,14,18,19,20,21                        |                  9 |
|      5 | 1,2,3,15,16,17,32                              |                  7 |
+--------+------------------------------------------------+--------------------+

我不确定我是否可以在 MySQL 中完成此操作，或者我是否需要在我的链接程序中编写此步骤。

Answer 1

这将计算相同的命中数。

SELECT seq_id, COUNT(*) AS same
FROM polished_data
WHERE 
    hit_name_id IN (SELECT hit_name_id FROM polished WHERE seq_id = ###) 
    AND and seq_id != ### 
GROUP BY seq_id

然后您可以扩展它并计算有多少不同(它出现在其中一个而不是两个)，然后将它们连接在一起。

SELECT *, (same/(same+diff)) AS similarity   
FROM
(
    SELECT 
        s.seq_id, 
        s.same,
        ((t.total-s.same)+(ct.total-s.same)) AS diff 

    FROM 

        (SELECT seq_id, COUNT(*) as total FROM polished_data
         GROUP BY seq_id) AS t  

    LEFT JOIN

        (SELECT seq_id, COUNT(*) AS same
         FROM polished_data
         WHERE 
             hit_name_id IN 
                 (SELECT hit_name_id FROM polished_data 
                  WHERE seq_id = ###) 
         GROUP BY seq_id) AS s

    ON t.seq_id = s.seq_id

    JOIN

        (SELECT COUNT(*) as total FROM polished_data
         WHERE seq_id = ###) AS ct  

) as result   

使用随机数据你会得到这样的结果(使用 ### 替换为 1 进行测试)。

+--------+------+------+------------+
| seq_id | same | diff | similarity |
+--------+------+------+------------+
|      1 |   22 |    0 |     1.0000 |
|      2 |    4 |   45 |     0.0816 |
|      3 |    5 |   57 |     0.0806 |
|      4 |    8 |   34 |     0.1905 |
|      5 |    9 |   47 |     0.1607 |
|      6 |    3 |   36 |     0.0769 |
|      7 |    7 |   45 |     0.1346 |
|      8 |    3 |   48 |     0.0588 |
|      9 |    9 |   46 |     0.1636 |
|     10 |    4 |   48 |     0.0769 |
+--------+------+------+------------+

将上述 SQL 中的 ### 更改为您要比较的 seq_id。