MySQL / 14. Обработка повторяющихся записей

Устранение дубликатов из результата самообъединения

Задача
Самообъединения часто выводят строки, близкие к дубликатам, то есть строки, которые содержат одинаковые значения, но в другом порядке. В этом случае запрос SELECT DISTINCT не удалит дубликаты.

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

Обсуждение
Самообъединения могут формировать строки, которые являются повторяющимися (в том смысле, что содержат одни и те же значения), хотя и не идентичными. Рассмотрим запрос, который применяет самообъединение для поиска всех пар штатов, вступивших в Союз в один и тот же год:

mysql> SELECT YEAR(s2.statehood) AS year, s1.name, s2.name
-> FROM states AS s1, states AS s2
-> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
-> AND s1.name != s2.name
-> ORDER BY year, s1.name, s2.name;

+-----+----------------+----------------+
| year | name             | name             |
+-----+----------------+----------------+
| 1787 | Delaware       | New Jersey    |
| 1787 | Delaware       | Pennsylvania |
| 1787 | New Jersey    | Delaware       |
| 1787 | New Jersey    | Pennsylvania |
| 1787 | Pennsylvania | Delaware       |
| 1787 | Pennsylvania | New Jersey    |
...
| 1912 | Arizona         | New Mexico  |
| 1912 | New Mexico   | Arizona        |
| 1959 | Alaska           | Hawaii          |
| 1959 | Hawaii           | Alaska          |
+-----+----------------+---------------+

Условие инструкции WHERE, требующее, чтобы названия штатов в паре не были идентичными, устраняет тривиальные совпадения, показывающие, что каждый штат вступил в Союз в том же году, что и он сам. Но каждая из оставшихся пар штатов опять-таки выводится дважды. Например, одна строка содержит Delaware и New Jersey, а другая – New Jersey и Delaware. Каждая такая пара строк может считаться дубликатами, так как они содержат одинаковые значения. Однако из-за того, что значения приведены в строкахв разном порядке, строки не идентичны, и от таких повторений невозможно избавиться при помощи добавления в запрос ключевого слова DISTINCT.

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

IF(val1<val2,val1,val2) AS lesser_value,
IF(val1<val2,val2,val1) AS greater_value

Применение этого приема к запросу о парах штатов выводит следующий результат (выражения обеспечивают отображение названий штатов в лексическом порядке внутри каждой строки):

mysql> SELECT YEAR(s2.statehood) AS year,
-> IF(s1.name<s2.name,s1.name,s2.name) AS state1,
-> IF(s1.name<s2.name,s2.name,s1.name) AS state2
-> FROM states AS s1, states AS s2
-> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
-> AND s1.name != s2.name
-> ORDER BY year, state1, state2;

+-----+-------------+----------------+
| year | state1         | state2             |
+-----+-------------+----------------+
| 1787 | Delaware    | New Jersey    |
| 1787 | Delaware    | New Jersey    |
| 1787 | Delaware    | Pennsylvania |
| 1787 | Delaware    | Pennsylvania |
| 1787 | New Jersey | Pennsylvania |
| 1787 | New Jersey | Pennsylvania |
...
| 1912 | Arizona      | New Mexico   |
| 1912 | Arizona      | New Mexico   |
| 1959 | Alaska        | Hawaii           |
| 1959 | Alaska        | Hawaii           |
+-----+-------------+----------------+

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

mysql> SELECT DISTINCT YEAR(s2.statehood) AS year,
-> IF(s1.name<s2.name,s1.name,s2.name) AS state1,
-> IF(s1.name<s2.name,s2.name,s1.name) AS state2
-> FROM states AS s1, states AS s2
-> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
-> AND s1.name != s2.name
-> ORDER BY year, state1, state2;

+-----+--------------+----------------+
| year | state1          | state2            |
+-----+--------------+----------------+
| 1787 | Delaware    | New Jersey    |
| 1787 | Delaware    | Pennsylvania |
| 1787 | New Jersey | Pennsylvania |
...
| 1912 | Arizona      | New Mexico   |
| 1959 | Alaska        | Hawaii           |
+-----+-------------+----------------+

Альтернативный подход к удалению неидентичных дубликатов заключается не в их выявлении и удалении, а в выборе строк таким образом, чтобы в результате запроса появлялась только одна строка из каждой пары. Это избавляет от необходимости переупорядочивания значений в строках и использования ключевого слова DISTINCT. Для запроса о парах штатов, выбор только тех строк, где название первого штата лексически меньше второго, автоматически удаляет строки, в которых имена присутствуют в обратном порядке:

mysql> SELECT YEAR(s2.statehood) AS year, s1.name, s2.name
-> FROM states AS s1, states AS s2
-> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
-> AND s1.name < s2.name
-> ORDER BY year, s1.name, s2.name;

+-----+--------------+----------------+
| year | name          | name             |
+-----+--------------+----------------+
| 1787 | Delaware    | New Jersey    |
| 1787 | Delaware    | Pennsylvania |
| 1787 | New Jersey | Pennsylvania |
...
| 1912 | Arizona      | New Mexico   |
| 1959 | Alaska       | Hawaii            |
+-----+-------------+----------------+

Статьи по MySQL на эту тему:

Удаление дубликатов из таблицы