Предположим, у меня есть две таблицы 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 ответ1

0

Я думаю, что я понял это. Огромное спасибо Алексу Рису за то, что он был правлением, и за этот ответ за обеспечение основы для получения стабильных результатов. Я поставил свой окончательный ответ и мой процесс получения его ниже.


Окончательный ответ

> 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) не гарантирует, что идентификатор и метка, которые он захватывает, будут из той же строки, что и 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;

Это делает работу. Вы можете видеть, что он правильно пересчитывает правильную метку, независимо от того, что возвращает внутренний запрос, присоединяясь к числу, равному максимальному количеству.

К сожалению, я не собирался выполнять это в среде, где я могу просто создавать дополнительные таблицы. И если я вместо этого использовал CREATE TEMPORARY TABLE , я не могу ссылаться на него дважды, как мне нужно в этом запросе. Таким образом, последним шагом было создание таблицы LabelCounts. Вы можете увидеть полученный запрос в верхней части этого ответа.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .