Розширені запити в 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