mysql查询

Question

我有一个这样的表:

col1   col2

2001   3001
2002   3001
2003   3001
2004   3002
2002   3003
2001   3003

我想创建一个查询，找出 col1 中的项目与 col1 中的某个项目的所有组合(如果它们出现在 col2 中的同一项目中)以及出现的频率。例如，如果我们想探索 col1 中的项目编号“2001”，则查询应返回:

col3   col4   col5
2001   2002   0.667
2001   2003   0.333

有人可以给我提示吗？先谢谢了。

Answer 1

这样想，您正在尝试通过 col2 将表与其自身关联起来，因此我们将从连接到自身开始:

select left.col1, right.col1/*, ...*/ from table left inner join table right on left.col2 = right.col2 where left.col1 = '2001' /* ... */

现在是您的汇总。您想要聚合 all right.col1 以获取该列的计数:

select left.col1, right.col1, COUNT(*) from table left inner join table right on left.col2 = right.col2 where left.col1 = '2001' group by right.col2

我相信这应该与原始计数一起出现。我相信您必须旋转查询并获得总计才能获得频率。

请注意，如果 col1 不是常量，情况会复杂得多。

编辑添加:如果您正在查找一个查询，而不是使用您查询的任何语言对其进行迭代，则需要执行此分组两次:

SELECT abs_freq.col1, abs_freq.col2, abs_freq.freq / totals.total
FROM (SELECT
       left.col1 AS col1,
       COUNT(*)  AS total
      FROM TABLE LEFT
      INNER JOIN TABLE RIGHT
        ON left.col2 = right.col2
      WHERE left.col1 = '2001'
      GROUP BY left.col1
      ) totals
INNER JOIN (SELECT
              left.col1  AS col1,
              right.col1 AS col2,
              COUNT(*)   AS freq
            FROM TABLE LEFT
            INNER JOIN TABLE RIGHT
              ON left.col2 = right.col2
            WHERE left.col1 = '2001'
            GROUP BY right.col2
            ) abs_freq
   ON abs_freq.col1 = totals.col1