Об’єднуємо, фільтруємо, групуємо: SQL-скрипти для отримання вибірок



Мова 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.