MySQL 查询：计算一个非常大的表中的重复值

Question

我有这个 MySQL 表：

CREATE TABLE `triple` (
  `id_one` int(11) NOT NULL,
  `id_two` int(11) NOT NULL,
  `id_three` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

它包含近 1000 万行。中间列（“id_two”）中的 id 可以在不同的行中出现不同的时间。

简短示例：

id_one    id_two    id_three
1         2         3
2         2         3
3         2         1
68        98        1
1         4         3
2         4         4
4         5         33
6         5         3
90        5         3
34        5         83
9         3         98

现在我想计算不同的 id（“id_two”），即本例所示：

id_two     count
2     ->  3
98    ->  1
4     ->  2
5     ->  4
3     ->  1

如何有效地实现这一点？ （这是一次性工作......）这是第一件事。我需要的第二件事是：像上面那样计数，然后仅选择计数超过 100 的 id。

非常感谢您的帮助！

亲切的问候 奥夫风

Answer 1

完成此操作的基本命令是这样的：

SELECT id_two, count(*) FROM triple GROUP BY id_two;

如果您愿意，可以将其存储在临时表中

CREATE TEMPORARY TABLE xxx SELECT id_two, count(*) AS c FROM …
SELECT * FROM xxx WHERE c > 100;

……或者在外部查询中使用结果……

SELECT * FROM (SELECT id_two, count(*) AS c FROM triple GROUP BY id_two) t WHERE c > 100;

或者使用 HAVING 子句（如 Marc 在评论中建议的那样）：

SELECT id_two, count(*) AS c FROM triple GROUP BY id_two HAVING c > 100;

Answer 2

SELECT id_two, COUNT(*)
    FROM triple
    GROUP BY id_two
    HAVING COUNT(*) > 100

Answer 3

对于问题 1：

SELECT id_two, COUNT(1)
  FROM triple
GROUP BY id_two

对于问题 2：

SELECT id_two, COUNT(1)
  FROM triple
GROUP BY id_two
HAVING COUNT(1) > 100

Answer 4

对于“第二件事”，使用 HAVING

SELECT id_two, count(*) nb FROM triple GROUP BY id_two HAVING nb >= 100;

索引字段 id_two 应该会提高性能。

Answer 5

对于 1000 万行表上的一次性作业，我会完全跳过 SQL。尝试使用 GROUP BY 可能会锁定表太长时间。

SELECT id_two FROM TRIPLE INTO OUTFILE('/tmp/idtwo.txt')

在类 Unix 系统上，这将生成一个包含两列的列表：出现次数、ID

# sort -n /tmp/idtwo.txt | uniq -c

..如果必须使用 GROUP BY，请将 ORDER BY NULL 添加到 select 语句的末尾以节省一些时间。否则MySQL将尝试按默认顺序< /a> （id_two 的值）。

Answer 6

试试这个：

select id_two,Frequency=count(*)
from triple
group by id_two
having count(*) > 1 -- replace 1 with desired threshold