Продвинутые запросы в SQL, которые сэкономят твое время

За годы работы с телекоммуникационными данным моя папка с фрагментами кода собрала много таких примеров, которые можно использовать многократно. И речь идет не о “SELECT * FROM Table1”, я говорю о поиске и обработке или удаление дубликатов, выборе топ N значений из каждой группы данных в пределах одной таблицы, перемешивания записей в группах, но сохранение сортировки групп, расширение чисел на N цифр и так далее.

Сегодня я хотел бы поделиться этими фрагментами и объяснить, как это все работает. Я работаю с Microsoft SQL Server и использую T-SQL в моих запросах, но я уверен, что можно найти альтернативы и для других СУБД. Если нет, напишите мне и мы попробуем найти решение вместе.

Для этой статьи я подготовил таблицу поддельных звонков с фальшивыми номерами и реальными странами, поэтому, если вы попробуете повторить ту же транзакцию – не сработает 😀 (ок, кроме Великобритании, они действительно имеют код страны +44, но я не уверен, что номер который я выбрал, существует)

Работа с дубликатами

Самым быстрым и простым способом выбора уникальных значений из таблицы является использование ключевого слова DISTINCT.

SELECT DISTINCT *
FROM [test].[dbo].[12162018_FakeCalls]

Отметив столбцы, которые мы хотим получить, мы можем выбрать уникальные значения основываясь только на номерах и странах.

SELECT DISTINCT NumberFrom, CountryFrom, NumberTo, CountryTo
FROM [test].[dbo].[12162018_FakeCalls]

Но мы теряем информацию из других столбцов.

Также мы можем найти дубликаты с GROUP BY, HAVING и COUNT (*).

SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, count(*) as dups
FROM [test].[dbo].[12162018_FakeCalls]
GROUP BY NumberFrom, CountryFrom, NumberTo, CountryTo
HAVING COUNT(*) > 1
ORDER BY dups desc

Я знаю, что в базе данных SQL дубликаты не должны появляться вообще, мы должны использовать уникальные идентификаторы, ограничения (constrains) и индексы. Но иногда ты просто выполняешь некоторые тесты или вычисления впервые, экспериментируешь – и появляются дубликаты. Или ваши записи имеют различные идентификаторы, хотя вы понимаете, что один из них должен быть удален. Что бы вы делали в этих случаях? Давненько уже я нашел это решение на stackoverflow, сохранил его, и с тех пор использую его как минимум несколько раз в день (дубликаты достаточно распространены, специфика работы). WITH statement и ROW_NUMBER () являются моим спасителями.

WITH CTE AS(
SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, Duration, id,
RN = ROW_NUMBER()OVER(PARTITION BY NumberFrom, CountryFrom, NumberTo, CountryTo ORDER BY CountryFrom)
FROM #t
)
SELECT * FROM CTE WHERE RN > 1

Я скопировал исходные данные во временную таблицу #t, поскольку после удаления «Отмена» не работает, heh 🙂, и мы видим тот же результат, что и с GROUP BY, но уже со всеми столбцами. Теперь мы можем выполнить DELETE и удалить все ненужные значения на основе нашего определения дублирования.

WITH CTE AS(
SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, Duration, id,
RN = ROW_NUMBER()OVER(PARTITION BY NumberFrom, CountryFrom, NumberTo, CountryTo ORDER BY CountryFrom)
FROM #t
)
DELETE FROM CTE WHERE RN > 1;

SELECT *
FROM #t

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

Выбор top N записей внутри группы

Я уверен, что вы уже знаете, как это сделать: D. Так, с ROW_NUMBER () снова. Эта функция будет разбивать наши данные и назначать номер каждой записи в группе, поэтому нам просто необходимо указать, какой диапазон верхнего N мы хотим выбрать: топ-10? – “RN <= 10", с топ 3 до топ 7? - легко, "RN between 3 and 7" или "RN> = 3 and RN <= 7".

Давайте выберем только топ 2-ю запись в группах стран, из которых были осуществлены звонки ( ‘CountryFrom”), имена которых начинаются с той же буквы и сортированные по продолжительности звонка в каждой группе. (Я вставил те же данные во временную таблицу 3 раза, чтобы лучше видеть различия).

WITH CTE AS(
SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, Duration, id,
RN = ROW_NUMBER()OVER(PARTITION BY LEFT(CountryFrom,1) ORDER BY Duration ASC)
FROM #t
)
SELECT *
FROM CTE;

Посмотрите, как Германия и Греция попадают в одну группу, а Ирландия с Италией в другую. А теперь остается выбрать только вторую запись в каждой группе:

WITH CTE AS(
SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, Duration, id,
RN = ROW_NUMBER()OVER(PARTITION BY LEFT(CountryFrom,1) ORDER BY Duration ASC)
FROM #t
)
SELECT *
FROM CTE
WHERE RN = 2;

Перемешивание записей внутри группы

Фактически, эта методика может быть использована для перемешивания записей в любом случае, не только внутри группы, но, как мы уже начали с группами, ROW_NUMBER и WITH statements, я решил немного усложнить задачу. Не знаю придется вам в какой-то период своей жизни перетасовать записи в группе, но если появится такой случай, вы будете знать, как это сделать. Опять же, это часть моей повседневной работы, и я понимаю, что это редкий случай :)) Итак, вернемся к запросам. Мы будем использовать те же условия группировки, а для перестановки используем комбинацию функций ABS() – функция, которая используется для получения абсолютной величины передаваемого числа как аргумента, CHECKSUM() – функция, которая возвращает вычисленное значение контрольной суммы над строкой таблицы или над списком выражений и NEWID() – что создает уникальное значение типа uniqueidentifier. Читая, можешь подумать – что это за хрень и как это оно вместе должно работать? Но я тебе говорю, делает работу на ура. Смотри:

WITH CTE AS(
SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, CallStarted, Duration, id,
RN = ROW_NUMBER()OVER(PARTITION BY LEFT(CountryFrom,1) ORDER BY ABS(CHECKSUM(NewId())) % 1000000 DESC)
FROM [test].[dbo].[12162018_FakeCalls]
)
SELECT * FROM CTE

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

Вы можете заметить, что сейчас Польша – первая строка, а Испания – вторая.

Вот и все на сегодня. Несколько советов по ROW_NUMBER(), которые мне очень помогают в работе, и я надеюсь, что помогут кому-то еще.


Несмотря на то, что эта статья не касается машинного обучения, искусственного интеллекта, распознавания изображений, настройки модели, глубоких нейронных сетей или каких-либо других “фэнси” терминов, используемых в области анализа, работа с данными с использованием SQL все еще остается частью профессии. И еще существует много профессионалов в области, работающих с этим языком и реляционными базами данных. Давайте не забывать наши корни, мои друзья.

Спасибо за внимание, будьте крутыми, профессионалами и фантастического вам дня.

Фото Kalen Emsley на Unsplash

Karma +1 when you share it:

Leave a Reply

Your email address will not be published. Required fields are marked *