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

Задача
Самообъединения часто выводят строки, близкие к дубликатам, то есть строки, которые содержат одинаковые значения, но в другом порядке. В этом случае запрос 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 IF(val1
Применение этого приема к запросу о парах штатов выводит следующий результат (выражения обеспечивают отображение названий штатов в лексическом порядке внутри каждой строки):

mysql> SELECT YEAR(s2.statehood) AS year,
-> IF(s1.name -> IF(s1.name -> 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 -> IF(s1.name -> 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 |
+-----+-------------+----------------+.



Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Обработка дубликатов на этапе создания записи
Подсчет и выявление дубликатов
Предотвращение появления дубликатов в таблице
Удаление дубликатов из таблицы
Устранение дубликатов из результата запроса