☰

查找 MySQL 列值中最常见的大写字母

tag-icon • tag-icon mysql
查找 MySQL 列值中最常见的大写字母

假设我有两个 MySQL 表，分别表示某种项目和应用于这些项目的文本标签。这是项目到标签的多对多映射，用外键关系表示：

> SELECT * FROM Labels;
+----+--------+
| id | label  |
+----+--------+
| 1  | foobar |
| 2  | FooBar |
| 3  | fooBar |
| 4  | Foobar |
| 5  | BLAH   |
| 6  | blah   |
| 7  | Stuff  |
+----+--------+

> SELECT * FROM ItemsToLabels;
+----+----------+
| id | label_id |
+----+----------+
| 1  | 1        |
| 1  | 6        |
| 2  | 1        |
| 3  | 1        |
| 3  | 7        |
| 4  | 2        |
| 5  | 4        |
| 6  | 6        |
| 7  | 5        |
| 7  | 6        |
+----+----------+

我想构造一个查询，它将返回以下列表最常被使用标签大写。换句话说，我想要以下结果：

> SELECT some magic query;
+----+--------+-----+
| id | label  | cnt |
+----+--------+-----+
| 1  | foobar | 3   |
| 6  | blah   | 3   |
| 7  | Stuff  | 1   |
+----+--------+-----+

我知道我可以使用COLLATE UTF8_GENERAL_CI获取所有拼写相同但大小写不同的行，但这并没有让我找到解决方案。有什么想法吗？

答案1

我想我已经明白了。非常感谢 Alex Reece 的反馈，以及这个答案提供获得稳定结果的框架。我已将我的最终答案和得出该答案的过程放在下方。

最终答案

> SELECT lc1.id, lc1.label, mc.max_cnt
FROM (
  SELECT Labels.id, Labels.label, COUNT(*) AS cnt
  FROM Labels
  JOIN ItemsToLabels
  ON Labels.id = ItemsToLabels.label_id
  GROUP BY Labels.id
) lc1
INNER JOIN (
  SELECT id, label, max(cnt) as max_cnt
  FROM (
    SELECT Labels.id, Labels.label, COUNT(*) AS cnt
    FROM Labels
    JOIN ItemsToLabels
    ON Labels.id = ItemsToLabels.label_id
    GROUP BY Labels.id
  ) lc2
  GROUP BY label COLLATE UTF8_GENERAL_CI
) mc
ON lc1.cnt = mc.max_cnt
AND lc1.label LIKE mc.label COLLATE UTF8_GENERAL_CI;
+----+--------+-----+
| id | label  | cnt |
+----+--------+-----+
|  1 | foobar |   3 |
|  6 | blah   |   3 |
|  7 | Stuff  |   1 |
+----+--------+-----+

您可以在这里看到它的 SQLFiddle：http://www.sqlfiddle.com/#!9/19bb0/1

到达该目标的过程

计算很重要

第一步是简单地计算每个标签的使用次数。这很简单：

> SELECT Labels.id, Labels.label, COUNT(*) AS cnt
  FROM Labels
  JOIN ItemsToLabels
  ON Labels.id = ItemsToLabels.label_id
  GROUP BY Labels.id;
+----+--------+-----+
| id | label  | cnt |
+----+--------+-----+
|  1 | foobar |   3 |
|  2 | FooBar |   1 |
|  3 | fooBar |   0 |
|  4 | Foobar |   1 |
|  5 | BLAH   |   1 |
|  6 | blah   |   3 |
|  7 | Stuff  |   1 |
+-------------+-----+

获取最大计数

然后我需要按降序排列标签，并从每组拼写相同但大写不同的标签中取出最上面的标签：

> SELECT id, label, max(cnt)
  FROM (subquery ORDER BY cnt) AS s
  GROUP BY label COLLATE UTF8_GENERAL_CI;

这将产生如下查询：

> SELECT id, label, max(cnt)
  FROM (
      SELECT Labels.id, Labels.label, COUNT(*) AS cnt
      FROM Labels
      JOIN ItemsToLabels
      ON Labels.id = ItemsToLabels.label_id
      GROUP BY Labels.id
      ORDER BY cnt DESC;
  ) AS s
  GROUP BY label COLLATE UTF8_GENERAL_CI;
+----+--------+----------+
| id | label  | max(cnt) |
+----+--------+----------+
|  1 | foobar |        3 |
|  6 | blah   |        3 |
|  7 | Stuff  |        1 |
+----+--------+----------+

这看起来不错！而且……几乎就是。

为什么这不管用

事实证明，MySQL 并不保证 GROUP BY 执行稳定排序。只是通过更改/内部实现细节，在子查询中执行 ORDER BY，然后在外部查询中执行 GROUP BY，导致顶部排序的行仍然位于顶部。并且该SELECT id, label, max(cnt)语句不保证它抓取的 id 和标签将来自与 max(cnt) 相同的行。例如，只需更改子查询中的 ORDER BY 会导致返回相同的计数，但标签错误：

> SELECT id, label, max(cnt)
  FROM (
      SELECT Labels.id, Labels.label, COUNT(*) AS cnt
      FROM Labels
      JOIN ItemsToLabels
      ON Labels.id = ItemsToLabels.label_id
      GROUP BY Labels.id
      ORDER BY cnt ASC;
  ) AS s
  GROUP BY label COLLATE UTF8_GENERAL_CI;
+----+--------+----------+
| id | label  | max(cnt) |
+----+--------+----------+
|  1 | fooBar |        3 |
|  6 | BLAH   |        3 |
|  7 | Stuff  |        1 |
+----+--------+----------+

所以我们需要一种方法来避免这种情况。

更稳定的解决方案

幸运的是，我找到了另一个答案这解释了如何做与我想要的类似的事情。问题是，在那个问题中，他们想要的最大值（时间戳）已经在表中了。而在这个问题中，我必须计算计数。

因此，我的第一个尝试是创建一个包含我需要的所有信息的表，然后像在另一个答案中一样引用它：

CREATE TABLE LabelCounts (
    `id` INT, `label` VARCHAR(80), `cnt` INT
);

INSERT INTO LabelCounts (
  SELECT Labels.id, Labels.label, COUNT(*) AS cnt
  FROM Labels
  JOIN ItemsToLabels
  ON Labels.id = ItemsToLabels.label_id
  GROUP BY Labels.id
);

SELECT *
FROM LabelCounts lc1
INNER JOIN (
  SELECT id, label, max(cnt) as max_cnt
  FROM LabelCounts
  GROUP BY label COLLATE UTF8_GENERAL_CI
) lc2
ON lc1.cnt = lc2.max_cnt
AND lc1.label LIKE lc2.label COLLATE UTF8_GENERAL_CI;

这样就完成了任务。您可以看到，无论内部查询返回什么，它都通过 JOIN 来正确地重新计算正确的标签，使计数等于最大计数。

不幸的是，我不会在可以创建其他表的环境中执行此操作。如果我改用CREATE TEMPORARY TABLE，我无法像在此查询中那样引用它两次。因此，最后一步是内联创建 LabelCounts 表。您可以在此答案的顶部看到结果查询。

相关内容