SQL: мені тільки спитати. Як писати скрипти для отримання виборок

Всім привіт! Мене звуть Андрій, і трохи більше трьох років я займаюсь аналізом даних та використовую SQL для роботи з різними базами даних. Зараз працюю аналітиком даних у компанії Genesis. У статті розповім про те, як писати SQL-скрипти для отримання вибірок (з нуля в один), на прикладах.

Матеріал розрахований на читача з початковим рівнем володіння SQL для максимально швидкого занурення в технологію.

Передісторія

Одного разу компанія, в якій я обіймав посаду Data Analyst, вирішила навчити персонал основ роботи з базою даних і для цього найняла викладача. Мотивація полягала в тому, щоб колеги, маючи read-only доступ, отримували актуальну інформацію та просту статистику, не звертаючись до технічних фахівців. Після трьох занять учні не написали жодного запиту і були незадоволені матеріалом.

Програма курсу потрапила до мене на «переосмислення», як наслідок з неї було виключено історію, теорію, все, що стосується проєктування та створення БД, а залишилися способи отримання вибірок даних.

Це все нагадало мені, як колись я сам був змушений швидко опанувати SQL. Шпаргалки містили всілякі типи з’єднань таблиць та мінімалістичні приклади, а онлайн-курси розповідали про нормальні форми, процедури та типи даних, що мало підходило для моїх цілей.

Так і виникла ідея написати статтю, яка зможе послідовно і без води розповісти, як використовувати SQL для отримання вибірок з декількох джерел з потрібними обмеженнями, у потрібному вигляді та потрібних розрізах. Особливості використовуваної БД, оптимізація та інша теорія залишаються для самостійного вивчення.

Отже, почнемо.

Перший SELECT, сортування, LIMIT

У базі даних інформація зберігається в таблицях.

Припустимо, у нас є таблиця users, що містить дані про реєстрації користувачів, яка лежить у схемі (скажімо, папці) product. У таблиці є кілька десятків колонок (полів). Виберемо ті, що нас цікавлять:

SELECT reg_dt, id, gender, age, country_code, app — список полів, що цікавлять.
FROM product.users — джерело, схема.назва_таблиці.
LIMIT 5 — кількість рядків, які потрібно вивести.

Примітка. Великими літерами позначимо зарезервовані слова (команди).

Працюватиме і з малими літерами, але так прийнято для наочності.

Як любить казати мій колега: «SQL-запит — це ж просто англійський текст. Візьми це звідси, відфільтруй, відсортуй і так далі...»

Отже, ми отримали таблицю:

Те саме, відсортоване за віком та кодом країни:

SELECT reg_dt, id, gender, age, country_code, app
FROM product.users
ORDER BY age DESC, country_code

LIMIT 5 — цей рядок можна видалити, тоді у нас буде список з усіх користувачів (але їх багато).

За замовчуванням команда ORDER BY відсортує поле за зростанням (текстове за алфавітом, наприклад), але, вказавши DESC (descending) після назви поля, ми задамо сортування за спаданням.

(Щоб вибрати всі поля, вкажіть зірочку * замість назв полів SELECT * FROM product.users.)

Структура запиту, умови WHERE, логічні оператори

Модифікуємо запит, щоб знайти користувачів-жінок старше 45 років:

SELECT reg_dt, id, gender, age, country_code, app
FROM product.users
WHERE gender =  'f' AND age > 45 – вказуємо умови для полів, що виводяться.
ORDER BY age DESC, country_code

'f' значення взято в одинарні лапки, щоб вказати, що це текст, а не назва поля.

Порядок команд у запиті фіксований:

SELECT 'стовпці або * для вибору всіх стовпців; обов\'язково'
FROM 'таблиця'*
WHERE 'умова/фільтрація; необов\'язково'
GROUP BY 'стовпці, за якими хочемо згрупувати дані; необов\'язково'
HAVING 'умова/фільтрація на рівні згрупованих даних; необов\'язково'
ORDER BY 'стовпці, за якими хочемо відсортувати виведення; необов\'язково'

*Якщо вдаватись в деталі, можна написати запит без вказівки таблиці, приклад: SELECT 1 column1, 'text' column2. У цьому блоці можна записати кілька джерел, але про це поговоримо далі.

В умові WHERE можна використовувати велику кількість критеріїв, пов’язаних логічними операторами AND та OR.

age > 45 (ще варіанти: =, >, <, <=, >= та != не дорівнює).

Варто додати, що 45 < age дасть еквівалентний результат, крім того, можна порівняти поля між собою age > steps_count.

country_code IN ('US', 'PL', 'CL') Якщо потрібно порівняти наші значення зі значеннями у списку, використовуємо IN, вказавши в дужках можливі варіанти через кому.

Умови також можна задавати з приставкою «не» country_code NOT IN ('RU', 'VE').

Це виключить з вибірки країни, перераховані у списку.

BETWEEN '2020-02-02' AND '2020-07-04' — значення між вказаними датами, включно.

У SQL дата записується в наступному форматі: '2020-12-29' (рік-місяць-день).

Дата і час: '2020-04-15 06:25:49.000009'. Якщо точність не потрібна, то запис можна скоротити '2020-04-15 06:25:49' (відкинута справа частина часу буде замінена нулями '2020-04-15 06:25:49.000000'). Відповідно, коли ми фільтруємо поле, яке містить дату і час, вказуючи лише дату BETWEEN '2020-02-02' AND '2020-07-04', це рівносильно '2020-02-02 00:00:00.000000' AND '2020-07-04 00:00:00.000000'.

Для наступних прикладів розглянемо таблицю:

Порівнювати рядки можна конструкцією site LIKE '%.com'.

Це формулювання допоможе знайти користувачів, зареєстрованих на ресурсах, які містять будь-який набір символів (задано зарезервованим символом %), потім .com, таким чином ми отримаємо користувачів, зареєстрованих на ресурсі з доменом .com.

SELECT site FROM product.users WHERE site LIKE '%.com';

SELECT site FROM product.users WHERE site LIKE 'abcdefghij.%';

Символ % може міститися в будь-якій частині рядка або бути вказаний кілька разів. Означає «будь-який набір символів або відсутність символу».

Написавши 'a%.com', ми знайдемо користувачів у домені .com, де назва сайту починається з літери a.

Є ще один зарезервований символ _ (нижнє підкреслення), що позначає один символ. Таким чином знайдемо користувачів ресурсу, який містить другим символом літеру m і закінчується доменом .us, можна так: site LIKE '_m%.us'.

Як і в курсі шкільної математики (множення виконується раніше, ніж віднімання), оператори мають порядок виконання.

При використанні логічного оператора OR («або») варто бути обережним і ставити дужки, чітко позначаючи альтернативи.

Якщо ми захочемо знайти користувачів, що зареєструвались в конкретному часовому діапазоні з доменом '%.com' або '%.us', напишемо запит:

SELECT reg_dt, gender, age, site
FROM product.users
WHERE reg_dt BETWEEN '2012-01-01' AND '2015-01-01' 
      AND site LIKE '%.us' OR site LIKE '%.com'

У SQL можна використовувати переноси рядків та відступи для форматування, вони не впливають на виконання запиту.

Примітка. В умові хочеться написати site LIKE '%.com' OR '%.us', але так не працює, після команди LIKE очікується один шуканий патерн, потрібно повторити конструкцію для другого патерну.

Виконавши запит, отримаємо список користувачів. Деякі з них зареєстровані за межами виділеного нами діапазону часу 2012–2014 рр.

А все тому, що вказана в кінці умова OR скасовує всі попередні умови, пов’язані оператором AND.

Цю умову варто інтерпретувати як «дай вказані поля для користувачів, які зареєстровані в дату таку-то і мають домен .us або просто мають домен .com (без умови по даті)».

Відбувається це якраз з тієї причини, що AND виконується раніше, ніж OR.

Якщо ми хочемо, щоб умова дати зберігалась для обох доменів, варто явно виділити альтернативи для оператора OR за допомогою круглих дужок:

WHERE reg_dt BETWEEN '2012-01-01' AND '2015-01-01'
         AND (site LIKE '%.us' OR site LIKE '%.com')

Умова може бути сформульована і як результат перетворень:

SELECT age, site, LENGTH(site) 
FROM product.users
WHERE age%2 != 0 OR LENGTH(site) > 17

Вік — непарне число (остача від ділення на 2 не дорівнює 0) або довжина ресурсу — більше 17 символів. Причому поле, для якого створена умова, необов’язково виводити в SELECT.

JOIN, що видно і чого не видно. UNION

В основному доводиться використовувати більше одного джерела.

У нашій схемі, крім таблиці з реєстраціями:

Є таблиця замовлень:

І таблиця-довідник для розшифровки типів сервісів:

Щоб витягти дані з додаткових таблиць, використовуємо LEFT JOIN, що означає, що наша вихідна таблиця users ніби знаходиться зліва і ми приставляємо до неї відповідні рядки з таблиці orders_paid.

SELECT u.reg_dt, u.gender AS sex, u.age, u.id, op.user_id, order_id,
op.dt order_dt, op.service_id, pay_services.*
FROM product.users AS u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
LEFT JOIN dictionary.pay_services ON pay_services.id = op.service_id
WHERE u.reg_dt > '2013-02-02' – умови, застосовні до поля з будь-якої таблиці.
ORDER BY u.reg_dt DESC

Для поля gender ми використовували так званий аліас (іншими словами, перейменували для нашої результуючої таблиці в sex) u.gender AS sex. Для цього можна використовувати слово AS або написати аліас через пробіл, працює ідентично dt order_dt.

Аліаси застосовні і до назв таблиць з тим самим синтаксисом (з AS або без).

Найчастіше аліас для таблиці — це абревіатура її назви orders_paid op.

Це зручніше, ніж назва t1, table_1, і дозволяє прискорити запис op.user_id замість orders_paid.user_id.

Незважаючи на зручність скорочення, іноді краще не використовувати аліаси, оскільки це може ускладнити розуміння скрипту.

Як видно у прикладі order_id, можна використовувати тільки назву поля, без вказівки таблиці, якщо ця назва унікальна. Але все ж більш наочний і універсальний спосіб писати таблиця.поле.

pay_services.* дозволяє вивести всі поля з таблиці pay_services.

В результаті виконання запиту отримали наступну таблицю:

Для користувачів, які здійснили кілька покупок, рядки з user задублювалися.

Для користувачів, у яких немає оплат, ми отримали значення NULL. Його потрібно розуміти як «немає записів у таблиці, що приєднується».

Таблиці users та orders_paid пов’язані через ID покупця, його називають зв’язком (link). До одного рядка в користувачах може знаходитися кілька (в тому числі 0) рядків замовлень, таким чином можна сказати, що цей зв’язок має тип (1:М) один до багатьох.

Для того щоб коректно зіставити рядки таблиць, після ON вказуються умови, які повинні виконуватися, щоб рядок було додано у вибірку. У нашому випадку ми позначаємо, що рядки з таблиці замовлень мають бути виведені в тих випадках, коли ідентифікатор користувача в таблиці реєстрацій дорівнює ідентифікатору користувача в таблиці оплат u.id = op.user_id.

Аналогічно пов’язані ps.id = op.service_id за тим винятком, що довідник має лише по одному запису для кожного ідентифікатора і не збільшує кількість рядків.

Подальша взаємодія з отриманим набором даних не відрізняється від однієї таблиці — вибираємо поля, що нас цікавлять, фільтруємо та сортуємо.

Таким чином, якщо ми після об’єднання таблиць напишемо умову, яка залишить тільки ті рядки, де ідентифікатор замовлення невідомий, отримаємо список користувачів, для яких немає жодного замовлення.

SELECT u.reg_dt, u.gender, u.age, u.id, op.order_id
FROM product.users AS u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE op.order_id IS NULL

Причому нам необов’язково виводити поле, яке перевіряємо у WHERE.

Порівняння з NULL проводиться через IS, адже NULL — невизначене значення і не дорівнює саме собі. Оскільки якщо ми використовуємо рівність, то результат порівняння order_id = NULL — брехня, навіть якщо order_id набуде значення NULL.

Щоб знайти рядки для користувачів, які мали замовлення, модифікуємо умову WHERE op.order_id IS NOT NULL або використовуємо інший вид з’єднання таблиць.

LEFT JOIN залишає всі значення таблиці «зліва» і додає значення із зазначеної таблиці, якщо виконується умова в ON.

INNER JOIN, або JOIN, впливає на обидві таблиці та повністю приховує рядок, у якому не виконується умова ON.

Для знаходження рядків, які мають відповідність в обох таблицях, можемо використовувати JOIN product.orders_paid op ON u.id = op.user_id. Потрібно враховувати особливість INNER JOIN приховувати значення.

Якщо JOIN’и використовуються для горизонтального об’єднання таблиць, існує спосіб вертикально об’єднати вибірки за допомогою UNION:

SELECT id, country_code
FROM product.users
WHERE id IN (4446556074, 4436447691, 4446022755)
UNION ALL
SELECT id, country_code
FROM product.users
WHERE id IN (4446022755, 4436702697, 4481548107)

UNION при цьому відкине дублікати (за рахунок пошуку дублів виконується довше).

Обидва запити повинні мати однакову кількість стовпців та відповідні типи даних для колонок (якщо у першому запиті цифри у вигляді int, то вони не зможуть об’єднатися з VARCHAR у другому).

Таким чином можна об’єднати велику кількість вибірок з відповідним набором полів, отримавши на виході одну таблицю.

Типи зв’язків, відмінність умов у WHERE та LEFT JOIN

Якщо перерахувати таблиці через кому у FROM, отримаємо результат як у CROSS JOIN, який зіставляє рядки «кожен з кожним» (кількість рядків у таблиці дорівнюватиме добутку кількості рядків вихідних таблиць). CROSS JOIN при цьому більш поширений та явний спосіб зробити декартів добуток множин.

SELECT u.id, op.user_id, op.order_id
FROM product.users u, product.orders_paid op

Що еквівалентно:

SELECT u.id, op.user_id, op.order_id
FROM product.users u
CROSS JOIN product.orders_paid op

У нашому випадку для кожного користувача будуть записані замовлення всіх користувачів.

Вказавши в умові WHERE, що ID користувача повинен бути спільним для двох таблиць:

SELECT u.id, op.user_id, op.order_id
FROM product.users u, product.orders_paid op
WHERE u.id = op.user_id

Отримаємо результат, аналогічний наступному:

SELECT u.id, op.user_id
FROM product.users u
INNER JOIN product.orders_paid op ON u.id = op.user_id

До кожного користувача віднесені рядки його замовлень з таблиці orders_paid (виключаючи користувачів без замовлень).

Відмінність умов у WHERE та LEFT JOIN

Умова, написана в LEFT JOIN, впливає тільки на поля з таблиці, що приєднується:

SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id AND op.service_id = 14

Спочатку було проведено фільтрацію таблиці orders_paid, і залишилися лише всі замовлення сервісу № 14, потім — об’єднання. Таким чином отримали з users всіх зареєстрованих користувачів. Для користувачів, у яких є замовлення сервісу № 14, будуть виведені рядки з таблиці замовлень, для інших — NULL.

Коли умова написана у WHERE, вона фільтрує вибірку вже після об’єднання таблиць.

До фільтрації:

SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id

Після фільтрації:

SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE op.service_id = 14

У підсумку, незважаючи на використання LEFT JOIN, ми отримали аналогічну поведінку, як у INNER JOIN (у здатності приховувати рядки, що не підходять під умову однієї з таблиць).

Умови у блоці WHERE впливають на всю отриману після об’єднання вибірку, це потрібно враховувати. Як видно у прикладі вище ON u.id = op.user_id AND op.service_id = 14, блок ON не обмежується рівністю ідентифікаторів. Це просто блок з умовами (за тими самими правилами, що й у WHERE), які можуть бути різними залежно від ситуації.

Одна і та ж таблиця може бути приєднана кілька разів або приєднана сама до себе. У нашій таблиці з ордерами є поле parent_order_id. Воно вказує на замовлення, яке ініціювало підписку у користувача. Таким чином перші платежі та продовження зберігаються в одній таблиці, і ми пов’язуємо таблицю orders_paid саму з собою за ідентифікатором батьківського платежу.

Так само прив’яжемо довідник з назвами сервісів. Використовуємо INNER JOIN для зв’язку з orders_paid, щоб залишились тільки користувачі із замовленнями.

SELECT u.id, opp.order_id parent_order, opp.dt AS parent_dt, op.order_id, op.dt AS order_dt, ps.service_name
FROM product.users u
INNER JOIN product.orders_paid op ON u.id = op.user_id
LEFT JOIN product.orders_paid opp ON op.parent_order_id = opp.order_id
LEFT JOIN product.pay_services ps ON ps.id = op.service_id
ORDER BY op.order_id

Бачимо, що для ініціюючого платежу parent_order = order_id. А сервіс vip не має parent_order, оскільки це інший тип сервісу. Таким чином parent_dt — це дата початку підписки, а order_dt — дата конкретного платежу.

Агрегації (distinct та розрізи)

У SQL є агрегатні функції, які можуть перетворити кілька рядків на один:

SUM, COUNT, AVG, MIN, MAX і так далі.

Повертає один рядок:

SELECT COUNT(u.id)
FROM product.users u

8073565 — кількість рядків у таблиці users.

Функція COUNT дозволяє вписати *, щоб підрахувати кількість рядків у вибірці:

SELECT COUNT(*)
FROM product.users u

У більшості випадків нам потрібно рахувати величини в будь-яких розрізах, залишаючи незгруповані поля.

Ми можемо знайти дату першої реєстрації та кількість зареєстрованих користувачів у кожній країні окремо для чоловіків та жінок (впорядковано від найбільшої групи):

SELECT country_code, gender, MIN(reg_dt), COUNT(id) id_count
FROM product.users
GROUP BY country_code, gender
ORDER BY COUNT(id) DESC

Загальне правило звучатиме так: для агрегації даних потрібно внести всі поля розрізів (поля, що не повинні бути агреговані) в конструкцію GROUP BY, а для полів, що мають бути агреговані, — вибрати агрегатні функції.
Також, зрозуміло, доступні математичні перетворення +, -, *, /.

Якщо у SELECT вказані тільки агреговані вирази, GROUP BY відсутній.
Якщо у SELECT всі поля без агрегацій і вони ж у GROUP BY — запит поверне тільки унікальні значення.
Важливо тримати в голові рівень деталізації даних, який виходить на кожному етапі запиту.
Інакше кажучи, чому відповідає один рядок даних в результаті виконання запиту.

SELECT u.country_code,
      COUNT(DISTINCT u.id)                                    registered,
      COUNT(DISTINCT op.user_id)                              payers,
      COUNT(DISTINCT op.user_id) / COUNT(DISTINCT u.id) * 100 conv
FROM product.users u
    
    LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE reg_dt BETWEEN '2020-02-02' AND '2020-02-03'
GROUP BY u.country_code
HAVING COUNT(DISTINCT op.user_id) > 100

Цим запитом ми хочемо порахувати відсоток користувачів, які здійснили хоча б одну оплату.
Для цього потрібно кількість унікальних користувачів COUNT(DISTINCT u.id), що зареєструвалися, поділити на кількість унікальних користувачів з таблиці оплат COUNT(DISTINCT op.user_id).
Слово DISTINCT видалить дублікати.

Підрахунок ми проведемо окремо для кожної локації GROUP BY u.country_code.
З отриманої вибірки HAVING COUNT(DISTINCT op.user_id) > 100 залишимо тільки рядки, що відповідають умові, у нашому випадку це локації, в яких більше 100 платників.

У блоці WHERE вказуються умови, що належать до даних у вихідних таблицях, а в HAVING — умови, які належать до результатів агрегації.
Запит обробляється в такому порядку:

1. FROM (і JOIN’и)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Таким чином спочатку відбудеться об’єднання джерел даних в одну таблицю, аналогічне виконанню такого запиту:

SELECT u.country_code, u.id id_from_u, op.user_id id_from_op, op.order_id*
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE reg_dt BETWEEN '2020-02-02' AND '2020-02-03'

*op.order_id виведено для демонстрації.

Бачимо, що для користувачів, у яких більше одного замовлення, з’явилось по кілька рядків.
І якби ми використовували COUNT без DISTINCT, то підрахували б кількість користувачів, помножену на кількість їхніх замовлень.

Потім будуть проведені агрегації та видалення зі списку локацій з малою кількістю користувачів HAVING COUNT(DISTINCT op.user_id) > 100.
Агрегатні функції будуть підраховані без урахування значень NULL.

Підсумок

Тут ми проаналізували, як витягувати дані з таблиць, коректно їх об’єднувати, фільтрувати та групувати.
Цього достатньо для того, щоб отримувати нескладні вибірки та рахувати статистику.

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті