Наука о данных
2 лучших приема SQL для поиска повторяющихся значений в таблице
Избавьтесь от дубликатов записей, чтобы сэкономить время и деньги
Повторяющиеся записи повсюду!
И являются одной из самых распространенных проблем, с которыми сталкивается каждый пользователь базы данных.
Такие дублированные записи присутствуют в базе данных по разным причинам, таким как интеграция данных, системные сбои, человеческие ошибки при обновлении базы данных и отсутствие проверок достоверности данных.
Повторяющиеся записи делают данные несогласованными. Такая база данных стоит вам или вашей компании больше денег, времени и ресурсов для обслуживания и работы. Дублированные записи излишне занимают дополнительное пространство для хранения и замедляют выполнение запроса.
Поэтому, прежде чем приступить к анализу, вы должны исключить такие повторяющиеся записи хотя бы из запрашиваемой таблицы.
Вот почему во время собеседований при приеме на работу компании также часто задают вопросы об обработке дубликатов записей.
В этой статье вы познакомитесь с двумя лучшими и экономящими время способами SQL для выявления повторяющихся записей. По пути я привел интересные примеры, чтобы ясно объяснить концепции.
Также не забудьте проверить удивительные ресурсы SQL в конце этого чтения.
Давайте начнем!
Я использую MySQL Workbench и подмножество самостоятельно созданных Данных о продажах, созданных с помощью Faker. Вы можете получить его в моем репозитории Github бесплатно по Лицензии MIT!
Это небольшое подмножество 18 x 6 этого набора данных — таблица заказов — как вы можете видеть ниже. В нем есть несколько повторяющихся записей.
Прежде чем непосредственно переходить к запросам, необходимо определить критерии поиска повторяющихся записей в таблице. Могут быть сценарии, когда дублируются определенные значения в одном столбце или вся запись, т.е. значения во всех столбцах в определенной строке, дублируются в таблице.
В этом кратком чтении вы изучите как возможности, так и способы борьбы с такими дублированными записями.
Самый простой способ выявить повторяющиеся записи – просто подсчитать, сколько раз каждая запись появляется в таблице. И запись, которая появляется более одного раза, дублируется.
Найдите повторяющиеся записи, используя GROUP BY и HAVING
Функция GROUP BY широко используется в SQL для агрегирования данных. Это означает, что вы можете группировать записи на основе значений в одном или нескольких столбцах и получать агрегированные значения, такие как количество или сумма других столбцов.
Имея это в виду, давайте рассмотрим, как можно найти повторяющиеся значения в одном столбце.
Найти повторяющиеся значения в одном столбце
Могут быть ситуации, когда повторяющиеся значения присутствуют только в одном столбце. Причиной таких дублирующихся записей может быть простая человеческая ошибка при вводе данных или обновлении базы данных.
Возьмем пример из таблицы заказов и выясним, какие OrderID дублируются. Поскольку вам нужно подсчитать, сколько раз каждый OrderID появлялся в таблице, вы должны сгруппировать записи по OrderID, как показано ниже.
SELECT OrderID , COUNT(*) as occurrences FROM orders GROUP BY OrderID
Выделенные записи (OrderID) встречаются в наборе данных более одного раза, т. е. они дублируются.
Однако вам не нужно создавать отдельные столбцы, как показано на рисунке выше. Вы можете напрямую получить повторяющиеся идентификаторы OrderID, используя предложение HAVING после GROUP BY, как показано ниже.
SELECT OrderID FROM orders GROUP BY OrderID HAVING COUNT(*) > 1;
Таким образом, вы получаете только дублированные идентификаторы OrderID, которые совпадают с выделенными в таблице выше.
Точно так же могут быть ситуации, когда значения в нескольких столбцах для строки дублируются в таблице.
Найти повторяющиеся значения в нескольких столбцах
Хотя вся строка дублируется в таблице, логика остается прежней, меняются только столбцы, указанные в предложении GROUP BY.
Вместо группировки записей по одному столбцу здесь вам нужно сгруппировать записи по нескольким столбцам.
Позвольте мне показать вам, как это сделать.
Предположим, вы хотите просмотреть записи, в которых комбинация OrderID, Quantity и Product_Category появлялась в таблице несколько раз.
SELECT OrderID
, Quantity
, Product_Category
, COUNT(*) as occurrences
FROM orders
GROUP BY OrderID
, Quantity
, Product_Category
Таким образом, вы можете видеть, что выделенная комбинация значений в столбцах OrderID, Quantity и Product_Category встречается в таблице более одного раза.
Опять же, вам нужно просто добавить HAVING COUNT(*) > 1 в конце запроса, чтобы получить эти повторяющиеся записи.
Поскольку процесс поиска дубликатов зависит от количества появлений записи в таблице, вы также можете использовать оконную функцию ROW_NUMBER.
Найти повторяющиеся записи с помощью ROW_NUMBER()
Оконная функция ROW_NUMBER() присваивает уникальный порядковый номер каждой записи в окне, определенном с помощью предложения PARTITION_BY.
Таким образом, вы можете фактически определить окно, используя те же столбцы, где вы ожидаете иметь повторяющиеся значения. Таким образом, если запись появляется несколько раз, повторяющимся записям будет присвоен номер строки больше 1.
Продолжим на том же примере.
Чтобы получить записи, в которых комбинация OrderID, Quantity и Product_Category появлялась в таблице несколько раз, необходимо определить окно с использованием этих столбцов в предложении PARTITION_BY, как показано ниже.
SELECT OrderID
, Quantity
, Product_Category
, ROW_NUMBER() OVER (PARTITION BY OrderID, Quantity, Product_Category ORDER BY OrderID) AS row_num
FROM orders
Вот как вы получите все записи и соответствующие номера строк, разделенные заданным набором столбцов. Таким образом, все выделенные записи, в которых номер строки равен 2, являются повторяющимися записями.
Вы можете передать приведенный выше запрос целиком в качестве подзапроса во внешний оператор SELECT ниже, чтобы получить только дублированные записи.
SELECT OrderID
, Quantity
, Product_Category
FROM (
SELECT OrderID
, Quantity
, Product_Category
, ROW_NUMBER() OVER (PARTITION BY OrderID, Quantity, Product_Category ORDER BY OrderID) AS row_num
FROM orders
) AS subquery
WHERE row_num > 1;
В качестве альтернативы, если вы не хотите использовать подзапрос, вы можете создать CTE и получить данные из этого CTE, используя другой запрос, как показано ниже.
WITH temp_orders AS
(
SELECT OrderID
, Quantity
, Product_Category
, ROW_NUMBER() OVER (PARTITION BY OrderID, Quantity, Product_Category ORDER BY OrderID) AS row_num
FROM orders
)
SELECT OrderID
, Quantity
, Product_Category
FROM temp_orders
WHERE row_num > 1;
Этот запрос также вернет точно такой же результат. Так что выбор за вами.
Чтобы узнать больше о ROW_NUMBER(), CTE и GROUP BY, не забудьте ознакомиться с интересными ресурсами в конце этой статьи. читать!
Когда такие данные, содержащие повторяющиеся записи, используются для дальнейшего анализа, это может привести к путанице в отношении правильной записи. Таким образом, вы должны исправить такие повторяющиеся записи, прежде чем приступить к анализу.
Работа с дублированными записями на самом раннем этапе проекта, безусловно, сэкономит ваше время, усилия и вычислительные затраты. Таким образом, поиск дубликатов и принятие мер по их устранению помогут вам проверить данные и получить данные хорошего качества.
Тем не менее, это один из самых распространенных вопросов на собеседованиях для аналитиков данных или инженеров данных. Таким образом, овладение этими приемами и понимание того, как подойти к этому вопросу, несомненно, помогут вам успешно пройти собеседование.
Я расскажу больше о таких интересных темах и часто задаваемых вопросах SQL-интервью и о различных подходах к их решению — Оставайтесь с нами!
Хотите читать НЕОГРАНИЧЕННОЕ количество историй на Medium?
💡 Рассмотрите вариант Стать участником Medium, чтобы получить неограниченный доступ к историям на Medium и ежедневным интересным информационным бюллетеням Medium. Я получу небольшую часть вашего гонорара и никаких дополнительных затрат для вас.
💡 Обязательно нажмите Зарегистрируйтесь и присоединитесь к более чем 200 другим пользователям, чтобы не пропустить новые статьи с руководствами, рекомендациями и советами по обработке и анализу данных, а также передовыми методами работы с SQL и Python.
Прочтите эту статью, чтобы узнать подробнее о ROW_NUMBER() и CTE.
Чтобы освоить SQL GROUP BY — я настоятельно рекомендую прочитать статью ниже.
Спасибо за чтение!
