Мова SQL — невіддільний інструмент при роботі з даними. Аналітики, тестувальники, продакт-менеджери послуговуються нею для зберігання, зміни та обробки великих масивів інформації. У статті для DOU дата-аналітик компанії Holy Water з екосистеми Genesis Андрій Ніколаєнко розповів про базові навички написання SQL-скриптів для отримання вибірок. Матеріал буде корисний фахівцям із початковим рівнем володіння SQL. Публікуємо короткий переказ для читачів блогу.
Відбір, сортування та ліміти
Як отримувати вибірки з декількох джерел даних, застосовуючи необхідні в конкретній ситуації обмеження? Розглянемо процес на прикладі таблиці users. Вона знаходиться у схемі (папці) product та містить дані про реєстрацію користувачів.
В результаті маємо таку таблицю:
reg_dt | id | gender | age | country_code | app |
2014-01-02 17:30:21 | 4446022755 | f | 37 | US | desktop |
2014-01-02 21:07:08 | 4446556074 | f | 40 | CH | android |
2014-01-14 21:07:15 | 4481548107 | m | 40 | GE | mobile |
2013-12-30 8:33:09 | 4436447691 | m | 49 | US | mobile |
2013-12-30 11:04:15 | 4436702697 | m | 61 | CH | desktop |
Ті ж дані, що відсортовані за віком та кодом країни:
SELECT reg_dt, id, gender, age, country_code, app
FROM product.users
ORDER BY age DESC, country_code
Команда ORDER BY відсортує поле за зростанням, а команда DESC — за зниженням. Аби вибрати всі поля, треба вказати * замість назв колонок 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
column1 | column2 |
1 | text |
В умові WHERE можна використовувати велику кількість критеріїв, які пов'язані логічними операторами AND та OR. При використанні OR («або») будьте обережні й ставте дужки, щоб альтернативи були чітко окреслені. В SQL можна використовувати перенесення рядків та відступи для форматування, вони не впливають на виконання запиту.
Коли ми виконали запит, отримаємо список користувачів. Деякі з них зареєстровані поза межами часового діапазону, який ми виділили — 2012–2014 рр.
reg_dt | gender | age | site |
2012-08-01 0:43:09 | m | 34 | |
2014-02-02 0:49:19 | f | 28 | |
2018-04-02 4:07:42 | f | 55 | |
2021-05-08 12:00:02 | m | 48 |
Так виходить, тому що умова 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')
reg_dt | gender | age | site |
2012-08-01 0:43:09 | m | 34 | |
2014-02-02 0:49:19 | f | 28 |
Умову можна сформулювати і як результат перетворень:
SELECT age, site, LENGTH(site)
FROM product.users
WHERE age%2 != 0 OR LENGTH(site) > 17
Об’єднуємо вибірки
Зазвичай для вибірок використовують більше одного джерела. У запропонованій схемі є таблиця з реєстраціями (1), таблиця із замовленнями (2), і таблиця для розшифрування типів сервісів (3).
(1)
reg_dt | id | gender | age | country_code | ... |
2014-01-02 17:30:21 | 4446022755 | f | 37 | US | ... |
2014-01-02 21:07:08 | 4446556074 | f | 40 | CH | ... |
2014-01-14 21:07:15 | 4481548107 | m | 40 | GE | ... |
2013-12-30 8:33:09 | 4436447691 | m | 49 | US | ... |
... | ... | ... | ... | ... | ... |
| | | | | |
(2)
user_id | dt | order_id | service_id | ... |
4446022755 | 2014-01-02 18:30:01 | 2435206 | 14 | ... |
4446022755 | 2014-02-02 18:25:17 | 2437018 | 14 | ... |
4481548107 | 2014-01-14 21:08:45 | 2455378 | 18 | ... |
4481548107 | 2014-04-11 15:11:18 | 2460491 | 14 | ... |
4481548107 | 2014-04-13 12:10:09 | 2460602 | 5 | ... |
... | ... | ... | ... | ... |
(3)
id | service_name |
5 | test_srv |
9 | vip |
14 | month |
18 | 90 day |
... | ... |
Аби отримати дані із додаткових таблиць, використовуємо LEFT JOIN. Наша вихідна таблиця знаходиться зліва, і ми ніби додаємо до неї відповідні рядки з таблиці замовлень.
Для поля gender ми використали аліас u.gender AS sex. Для цього можна використовувати слово AS або написати аліас через пробіл.
Аліаси можна застосовувати й до назв таблиць із тим самим синтаксисом (з AS або без нього).
Виконавши запит, ми отримали таку таблицю:
reg_dt | sex | age | id | user_id | order_id | order_dt | service_id | id | service_name |
2014-01-14 21:07:15 | m | 37 | 4481548107 | 4481548107 | 2460602 | 2014-04-13 12:10:09 | 9 | 9 | vip |
2014-01-14 21:07:15 | m | 37 | 4481548107 | 4481548107 | 2460491 | 2014-04-11 15:11:18 | 14 | 14 | month |
2014-01-14 21:07:15 | m | 37 | 4481548107 | 4481548107 | 2455378 | 2014-01-14 21:08:45 | 18 | 18 | 90 day |
2014-01-02 21:07:08 | f | 37 | 4446556074 | NULL | NULL | NULL | NULL | NULL | NULL |
2014-01-02 17:30:21 | f | 37 | 4446022755 | 4446022755 | 2435206 | 2014-01-02 18:30:01 | 14 | 14 | month |
2014-01-02 17:30:21 | f | 37 | 4446022755 | 4446022755 | 2437018 | 2014-02-02 18:25:17 | 14 | 14 | month |
2013-12-30 11:04:15 | m | 37 | 4436702697 | NULL | NULL | NULL | NULL | NULL | NULL |
2013-12-30 8:33:09 | m | 37 | 4436447691 | NULL | NULL | NULL | NULL | NULL | NULL |
Користувачі, які здійснили декілька покупок, продублювалися в таблиці. А для користувачів, у яких немає оплат, застосували значення NULL.
Далі ми працюємо з отриманим набором даних за тим же принципом, як раніше працювали з однією таблицею — обираємо поля, які нас цікавлять, фільтруємо і сортуємо.
Чим відрізняються умови в 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
id | service_id | user_id |
4446022755 | 14 | 4446022755 |
4446022755 | 14 | 4446022755 |
4481548107 | 14 | 4481548107 |
4436702697 | NULL | NULL |
4436447691 | NULL | NULL |
4446556074 | NULL | NULL |
Спочатку ми відфільтрували таблицю 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
id | service_id | user_id |
4446022755 | 14 | 4446022755 |
4446022755 | 14 | 4446022755 |
4436702697 | NULL | NULL |
4446556074 | NULL | NULL |
4436447691 | NULL | NULL |
4481548107 | 14 | 4481548107 |
4481548107 | 9 | 4481548107 |
4481548107 | 18 | 4481548107 |
Після фільтрації:
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
id | service_id | user_id |
4446022755 | 14 | 4446022755 |
4446022755 | 14 | 4446022755 |
4481548107 | 14 | 4481548107 |
Одну і ту ж таблицю ми можемо доєднати декілька разів, або доєднати саму до себе. У нашій таблиці із замовленнями є поле parent_order_id. Воно позначає замовлення, яке ініціювало підписку користувача. Таким чином перші платежі й подовження підписок зберігаються в одній таблиці, і ми пов’язуємо таблицю orders_paid саму із собою за ідентифікатором parent-платежу.
Також можемо доєднати таблицю-довідник із типами сервісів. Використовуємо 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
id | parent_order | parent_dt | order_id | order_dt | service_name |
4446022755 | 2435206 | 2014-01-02 18:30:01 | 2435206 | 2014-01-02 18:30:01 | month |
4446022755 | 2435206 | 2014-01-02 18:30:01 | 2437018 | 2014-02-02 18:25:17 | month |
4481548107 | 2455378 | 2014-01-14 21:08:45 | 2455378 | 2014-01-14 21:08:45 | 90 day |
4481548107 | 2460491 | 2014-04-11 15:11:18 | 2460491 | 2014-04-11 15:11:18 | month |
4481548107 | NULL | NULL | 2460602 | 2014-04-13 12:10:09 | vip |
Тут бачимо, що для платежу, що ініціює підписку, parent_order = order_id. А сервіс vip не має parent_order, оскільки це інший тип сервісу. Таким чином parent_dt — це дата початку підписки, а order_dt — дата конкретного платежу.
Детальніше про типи зв'язків та агрегатні функції в SQL ви можете дізнатися в оригінальному матеріалі на DOU.