Перейти до змісту

Матеріали курсу

Ця сторінка містить допоміжні матеріали курсу SQL для продакт-менеджера.

Розділ 1. Вступ

1.1 Хто я, для кого та про що цей курс.

Про що цей курс.

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

Для кого розрахований курс.

Основна аудиторія продакт-менеджери, курс також може бути корисним для людей, які хочуть опанувати SQL для аналізу даних.

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

  1. Як влаштована сучасна аналітика в IT компаніях.
  2. Що таке SQL, чим він може бути корисний продакт-менеджеру.
  3. Як підключатись до баз даних.
  4. Як зберігається інформація у базах даних та які основні типи даних існують.
  5. Як обробляти та отримувати дані з бази даних за допомогою SQL. (На прикладі бази даних Postgres)

Про автора курсу.

Мене звати Антон Король. Мій шлях роботи з даними почався у 2018 році, у якості аналітика даних, за 4 роки мені вдалося попрацювати майже з двома десятками продакт-менеджерів і даними таких проєктів як prom.ua, bigl.ua, vchasno.ua та інші.

З 2022 року я займаюсь інженерією даних на проєкті prom.ua, проєктую та створюю аналітичні сховища даних, розбудовую інфраструктуру для їх обчислення.

1.2 Матеріали курсу та як з ними працювати.

Матеріали цієї сторінки будуть містити різну інформацію, яка може бути корисною для проходження курсу.

Кожен розділ містить інформацію відповідно до відео, яке має таку ж саму назву. Зазвичай тут можна буде знайти:

  1. Посилання на матеріал, який я використовую у відеолекціях;
  2. Посилання на додаткові тексти, статті, відео, які при бажанні допоможуть глибше ознайомитись з темою;
  3. Код, який я демонструю у відеолекціях;
  4. Домашні завдання, підказки та варіанти його вирішення;
  5. Дуже коротку вижимку відеолекцій.

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

1.3 Сучасна аналітика у сучасних компаніях.

Зазвичай на проєктах аналітичними даними займаються люди, які займають наступні ролі:

  • Data Engineer - створює та підтримує інфраструктуру для роботи з даними.
  • Data Analyst - шукає інсайти, генерує та валідує гіпотези, розробляє і підтримує звітність.
  • Data Scientist - використовує машинне навчання для аналізу даних та покращення функціонала проєкту.

Miro схема з ролями.

Рух аналітичних даних в ідеалі виглядає наступним чином:

graph LR
  A[API] --> B(Data Lake);
  C[Бази даних] --> B(Data Lake);
  D[Файли] --> B(Data Lake);
  E[Зовнішні сервіси] --> B(Data Lake);
  F[...] --> B(Data Lake);
  B[Data Lake] --> G(Data Warehouse);
  B[Data Lake] --> H(Data Warehouse);
  B[Data Lake] --> I(...);
  G(Data Warehouse) --> K(BI Tool);
  H(Data Warehouse) --> K(BI Tool);
  I(...) --> K(BI Tool);
  1. Сирі дані з різних джерел систематизовано завантажуються у Data Lake (Озеро даних);
  2. Нормалізовані та очищені дані завантажуються до Data Warehouse (Аналітичне сховище даних);
  3. На основі даних з Data Warehouse будуються звіти, візуалізації.

Miro схема з рухом даних.

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

1.4 Продакт-менеджер у рамках сучасної аналітики та навіщо йому потрібен SQL.

В рамках роботи менеджер зазвичай отримує аналітичну інформацію по проєкту з:

  • Дашбордів, звітів, візуалізацій.
  • Взаємодії з аналітиками, саентістами та іншими менеджерами.
  • З зовнішніх джерел (аутсорсингові аналітичні агентства тощо).

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

  • Більш швидкому отриманню інформації. Менеджер самостійно може робити, переглядати прості вибірки та агрегації даних у сховищі, вивільняючи від них беклог аналітиків, щоб вони могли сконцентруватися на складних задачах.
  • Кращому розумінню сутностей проєкту та взаємозв'язків між ними. Безпосередня робота з даними дозволяє швидше, якісніше і детальніше зрозуміти з чого складається проєкт, як він функціонує, що в свою чергу покращує якість управлінських рішень.

1.5 Які існують бази даних та як з ними взаємодіяти.

З технічної точки зори існують різні класифікації баз даних, якщо дивитись простіше, то їх умовно можна поділити на два типи:

  • Загального призначення - зазвичай до них відносяться реляційні бази даних PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server, завдяки широкому функціоналу вони є найпоширенішими у світі, використовуються як основні та допоміжні бази проєктів.
  • Спеціалізованого призначення - спеціалізуються на ефективному виконанні певних можливостей/функціоналу, наприклад, для швидких агрегацій у реальному часі Apache Druid, ClickHouse, у якості аналітичних сховищ для великих даних (big data) BigQuery, Redshift, Snowflake, зазвичай такі бази є допоміжними, вони використовуються для закриття окремих, специфічних потреб проєкту.

Для взаємодії з базою даних використовується SQL (Structured Query Language) мова структурованих запитів. Наразі SQL скоріше представляє собою стандарт, останньою ітерацією якого є ISO/IEC 9075:2016. Керуючись стандартом різні вендори баз даних роблять свої версії SQL, які називаються діалектами. Це означає що один і той самий запит може бути написаний по різному у PostgreSQL та MySQL, оскільки в них різні діалекти, але гарна новина полягає в тому, що більшість діалектів на 80%-85% майже ідентичні.

1.6 Встановлення pgAdmin4 для роботи з базою даних PostgreSQL.

Курс передбачає роботу з SQL та базою даних PostgreSQL, для цього рекомендується встановити графічний інтерфейс pgAdmin4.

Завантажити pgAdmin4 для Windows. Завантажити pgAdmin4 для MacOS. Завантажити pgAdmin4 для Ubuntu.

pgAdmin4 був створений для роботи тільки з базами даних PostgreSQL, його буде достатньо для проходження цього курсу, але якщо в майбутньому у вас буде потреба одночасної роботи з різними базами, як альтернативу можна розглянути dbeaver.io або DataGrip.

1.7 Підключення та огляд бази даних, де знайти дані.

Для підключення до навчальної бази даних PostgreSQL, використовуйте наступні значення: 

Database:
  - host: 209.38.222.64
  - port: 5433
  - database: sql-course-prjctr
  - user: student
  - password: student-password

Дані в базі даних зберігаються у вигляді таблиць table, які входять у схеми schema, тобто до схем можна відноситись як до папок, які містять в собі таблиці.

Навчальна база даних містить згенеровані дані умовного інтернет-магазину в Україні, за 2023 рік, таблиці з навчальними даними містяться у схемі data, перелік та опис таблиць представлений нижче.

Перелік та опис таблиць (натисніть для перегляду)

data.order - таблиця с замовленнями інтернет-магазину.

Колонка Опис
order_id ID замовлення.
date_created Дата та час створення замовлення.
price Загальна сума замовлення.
status Поточний статус замовлення.
device_type Тип дейвайсу, на якому було створено замовлення.
delivery_type Тип доставки замовлення.
delivery_location_id ID локації доставки замовлення.
payment_type Тип оплати замовлення.

data.order_item - таблиця з товарами в замовленнях інтернет-магазину.

Колонка Опис
order_id ID замовлення.
order_date Дата та час створення замовлення.
product_id ID товару в замовленні.
category_id ID категорії товару в замовленні.
quantity Кількість одиниць товару в замовленні.
unit_price Ціна за одиницю товару в замовленні.
is_gift Статус, який відображає чи йде товар у якості подарунка.

data.opinion - таблиця з відгуками покупців по замовленнях інтернет-магазину.

Колонка Опис
opinion_id ID відгуку.
date_created Дата та час створення відгуку.
order_id ID замовлення, до якого належить відгук.
rating Оцінка, яку містить відгук.
contain_image Статус, який відображає чи містить відгук зображення.

data.product - таблиця з товарами інтернет-магазину та їх поточним станом.

Колонка Опис
product_id ID товару.
category_id Категорія товару.
status Поточний статус товару.
type Тип товару.

data.category - таблиця з категоріями товарів, які продає інтернет-магазин та їх поточним станом.

Колонка Опис
category_id ID категорії.
name Назва категорії.
status Поточний статус категорії.
type Тип категорії.

data.location - таблиця з містами, куди доставляє інтернет-магазин.

Колонка Опис
location_id ID міста.
location Назва міста.
region Регіон міста.
country Країна міста.
latitude Широта (координати місцезнаходження міста).
longitude Довгота (координати місцезнаходження міста).
population Кількість населення міста.
Залежність таблиць між собою (натисніть для перегляду)

Залежність таблиць

Розділ 2. Основи SQL

2.1 Що представляє собою SQL.

SQL (Structured Query Language) мова структурованих запитів для взаємодії з базами даних, складається з 5 компонентів:

graph TD
    SQL --> DQL[DQL <br> Data Query Language <br><hr><br><ul><li align='left'>SELECT</li><br>]
    SQL --> DDL[DDL <br> Data Definition Language <br><hr><br><ul><li align='left'>CREATE</li><br><li align='left'>DROP</li><br><li align='left'>ALTER</li><br><li align='left'>TRUNCATE</li></ul><br>]
    SQL --> DCL[DCL <br> Data Control Language <br><hr><br><ul><li align='left'>GRANT</li><br><li align='left'>REVOKE</li></ul><br>]
    SQL --> DML[DML <br> Data Manipulation Language <br><hr><br><ul><li align='left'>INSERT</li><br><li align='left'>UPDATE</li><br><li align='left'>DELETE</li></ul><br>]
    SQL --> TCL[TCL <br> Transaction Control Language <br><hr><br><ul><li align='left'>COMMIT</li><br><li align='left'>ROLLBACK</li><br><li align='left'>SAVEPOINT</li></ul><br>]

  • DQL (Data query language) - виконання запитів до даних у базі даних.

  • DDL (Data Definition Language) - створення або зміна об’єктів бази даних, таких як таблиці, індекси та користувачі.

  • DCL (Data Control Language) - контроль доступу до даних, що зберігаються в базі даних.

  • DML (Data Manipulation Language) - додавання, оновлення та видалення даних у базі даних.

  • TCL (Transaction Control Language) - управління та контроль транзакцій у базі даних.

В окремих теоретичних матеріалах DQL є частиною DML, оскільки вони можуть використовувати окремі елементи один одного, але цей нюанс не є принциповим.

Курс передбачає розгляд DQL та частину DML, яка відповідає за обробку даних. Цих знань буде достатньо для обробки та отримання необхідних даних з бази, для їх подальшого аналітичного аналізу.

2.2 Огляд основних типів даних.

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

Категорія Приклади типів даних Опис / Приклади значень
Текст text, varchar, character 'Kyiv', 'Ukraine', 'Eurovision 2023'
Число integer, smallint, bigint 91, -8, 312315
Число з плаваючою точкою numeric, decimal, double precision 1.0, 2.3151, -3.0123
Дата та час date, timestamp '2022-12-31', '2023-05-14 18:49:09.403363'
Логічний boolean true, false
Масив integer[], text[], numeric[] Набір значень в рамках визначеного типу.
[1, 5, -100], ['Venus', 'Earth', 'Mars'], [-0.13, 87.31, 562.013]
Словник json Пара або список пар у форматі "ключ-значення".
{'age':48}, {'type':'car', 'price':35000, 'currency':'USD'}
Інші специфічні типи bytea, inet, xml, path, interval ...

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

2.3 Як отримувати дані з таблиці (SELECT).

Приклад запиту до схеми data, таблиці order, який поверне усі записи цієї таблиці:

1
2
3
4
select
    *
from
    data.order

Такий самий запит, але з визначеним переліком колонок order_id, date_created, price, дані будуть повернуті тільки по цим колонкам:

1
2
3
4
5
6
select
    order_id,
    date_created,
    price
from
    data.order

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

1
2
3
4
5
-- Це коментар
select
    * -- Ще один коментар
from
    data.order -- Ще один коментар

select distinct повертає тільки унікальні рядки, тобто якщо існують рядки з однаковою комбінацією значень, запит поверне тільки один такий рядок. Приклад запиту, який поверне унікальні значення колонки region з таблиці location у схемі data: 

1
2
3
4
select distinct
    region
from
    data.location

За допомогою оператору as, можна присвоювати псевдоніми alias колонкам та таблицям, простіше кажучи змінювати їх назву.
Приклад запиту, який поверне колонку date_created з таблиці order у схемі data, змінивши її назву з date_created на created_at: 

1
2
3
4
select
    date_created as created_at
from
    data.order

limit дозволяє обмежити кількість рядків, які будуть повернуті у результаті запиту, він пишеться в самому кінці запиту.
Приклад запиту, який поверне 200 записів order_id з таблиці order у схемі data:

1
2
3
4
5
select
    order_id
from
    data.order
limit 200
Домашнє завдання (натисніть для перегляду)
  1. Напишіть запит, який поверне 50 записів усіх колонок з таблиці category у схемі data.
  2. Напишіть запит, який поверне усі записи колонок location, region, population з таблиці location у схемі data, змінивиши назву колонки location на city.
Приклад вирішення домашнього завдання (натисніть для перегляду)

  1. Напишіть запит, який поверне 50 записів усіх колонок з таблиці category у схемі data. 

    1
2
3
4
5
    select
    *
from
    data.category
limit 50

  2. Напишіть запит, який поверне усі записи колонок location, region, population з таблиці location у схемі data, змінивиши назву колонки location на city. 

    1
2
3
4
5
6
    select
    location as city,
    region,
    population
from
    data.location

2.4 Конвертація даних (CAST).

Конвертація дозволяє змінити тип даних, при умові що початкове значення сумісне з типом, в який воно конвертується.

Конвертація відбувається за допомогою функції cast(), у форматі cast(дані as тип), наприклад: 

1
2
3
select
    cast(1 as text),        -- конвертує число 1 у текст
    cast('232' as integer)  -- конвертує текст 232 у число


1
2
select                       -- поверне помилку при спробі ковертації тексту f/96 у число, 
    cast('f/96' as integer)  -- оскільки воно містить символи, які не відносяться до чисел

Приклад запиту, який поверне записи колонки date_created з таблиці order у схемі data, конвертувавши їх у тип date:

1
2
3
4
select
    cast(date_created as date)
from
    data.order

Вендори баз даних іноді реалізовують додаткові/альтернативні способи конвертації даних, наприклад у базах даних PostgreSQL можна конвертувати дані за допомогою ::, не використовуючи функцію cast(), це сприяє швидшому написанню і кращій читабельності коду: 

1
2
3
4
select
    date_created::date -- поверне той самий результат що і cast(date_created as date)
from
    data.order

Домашнє завдання (натисніть для перегляду)
  1. Використовуючи функцію cast(), напишіть запит, який конвертує і поверне значення колонки order_date у типі date з таблиці order_item у схемі data, присвойте цій колонці псевдонім day_created.
  2. Використовуючи альтернативний спосіб конвертації даних PostgreSQL ::, напишіть запит, який конвертує і поверне значення колонки unit_price у типі integer з таблиці order_item у схемі data, присвойте цій колонці псевдонім price_per_unit.
Приклад вирішення домашнього завдання (натисніть для перегляду)

  1. Використовуючи функцію cast(), напишіть запит, який конвертує і поверне значення колонки order_date у типі date з таблиці order_item у схемі data, присвойте цій колонці псевдонім day_created. 

    1
2
3
4
    select
    cast(date_created as date) as day_created
from
    data.order_item

  2. Використовуючи альтернативний спосіб конвертації даних PostgreSQL ::, напишіть запит, який конвертує і поверне значення колонки unit_price у типі integer з таблиці order_item у схемі data, присвойте цій колонці псевдонім price_per_unit. 

    1
2
3
4
    select
    unit_price::integer as price_per_unit
from
    data.order_item

2.5 Сортування даних (ORDER BY).

Сортування даних допомагає простіше сприймати дані та іноді використовується в певних функціях. Є два способи сортування:

  1. Від малого до великого, тобто від 1-9 або A-Z, помічається словом asc, скорочення від слова ascending.
  2. Від великого до малого, тобто від 9-1 або Z-A, помічається словом desc, скорочення від слова descending.

У SQL сортування відбувається завдяки ключовому слову order by, у форматі order by колонка спосіб_сортування, якщо спосіб сортування не вказати, за замовчуванням він буде asc.

Приклад запиту, який поверне перелік населених пунктів України, відсортованих за кількістю населення від найбільших до найменших:

1
2
3
4
5
6
7
8
select
    location,
    region,
    country,
    population
from
    data.location
order by population desc

Через кому можна одночасно сортувати по декількох колонках.
Приклад того самого запиту, але відсортованого спочатку по регіонах, а потім за кількістю населення:

1
2
3
4
5
6
7
8
select
    location,
    region,
    country,
    population
from
    data.location
order by region asc, population desc

У базах даних PostgreSQL можна сортувати колонки вказуючи не тільки їх назви, а і позицію у запиті:

1
2
3
4
5
6
7
8
select
    location,
    region,
    country,
    population
from
    data.location
order by 2 asc, 4 desc -- поверне той самий результат що і order by region asc, population desc

Усі типи даних підтримують сортування, наприклад наступний запит поверне 200 найсвіжіших замовлень, які є в таблиці:

1
2
3
4
5
6
select
    *
from
    data.order
order by date_created desc
limit 200
Домашнє завдання (натисніть для перегляду)
  1. Напишіть запит, який поверне усі записи колонок location, region, population з таблиці location у схемі data, одночасно відсортувавиши їх по region desc, та по location asc.
Приклад вирішення домашнього завдання (натисніть для перегляду)
  1. Напишіть запит, який поверне усі записи колонок location, region, population з таблиці location у схемі data, одночасно відсортувавиши їх по region desc, та по location asc. 
    1
2
3
4
5
6
7
    select
    location,
    region,
    population
from
    data.location
order by region desc, location asc

2.6 Фільтрація даних, оператори порівняння (WHERE).

Фільтрація є одним із ключових аспектів роботи з даними, вона дозволяє отримати дані, які відповідають конкретним критеріям.

Дані фільтруються за допомогою операторів порівняння та логічних операторів.

Оператори порівняння (натисніть для перегляду)
Оператор Опис
X < Y X менше Y.
X > Y X більше Y.
X <= Y X менше або дорівнює Y.
X >= Y X більше або дорівнює Y.
X = Y X дорівнює Y.
A between X and Y A у межах X та Y.
A is null Де A є NULL.
A in(X, Y, ...) Де A дорівнює X або Y або ....
like Виконує фільтрацію колонки згідно з вказаним патерном.
not Робить дію інших операторів протилежною.
Логічні оператори (натисніть для перегляду)
Оператор Опис
and Логічний оператор для об'єднання умов фільтрації.
or Логічний оператор для розділення умов фільтрації.

Оператори порівняння дозволяють створити умови фільтрації, умови фільтрації вказуються після оператора where.
Приклад запиту, який поверне усі записи замовлень у статусі completed:

1
2
3
4
5
6
select
    *
from
    data.order
where
    status = 'completed'

Логічні оператори дають можливість логічно об'єднати або розділити декілька умов в рамках одного запиту.
Якщо у запиті необхідне одночасне виконання декількох умов, ці умови об'єднуються між собою логічним оператором and.

Приклад запиту, який поверне усі записи замовлень у статусі completed, від 10000 грн (включно):

1
2
3
4
5
6
7
select
    *
from
    data.order
where
    status = 'completed' and
    price >= 10000

Приклад запиту, який поверне записи замовлень з доставкою від "Нова пошта" та "Укрпошта", за серпень 2023 року:

1
2
3
4
5
6
7
8
select
    *
from
    data.order
where
    delivery_type in('novaposhta', 'ukrposhta') and
    cast(date_created as date) >= '2023-08-01' and -- конвертую колонку date_created з timestamp у date
    cast(date_created as date) <= '2023-08-31'     -- конвертую колонку date_created з timestamp у date

Логічний оператор or дозволяє розділити (ізолювати) декілька умов. Приклад запиту, який поверне записи замовлень у статусі completed з оплатою card_online або у статусі canceled з оплатою cash:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
select
    *
from
    data.order
where
    status = 'completed' and
    payment_type = 'card_online'
    or                           -- or розділяє/ізолює умови перед та після нього
    status = 'canceled' and
    payment_type = 'cash'
Домашнє завдання (натисніть для перегляду)
  1. Напишіть запит, який поверне усі записи з таблиці location у схемі data, region яких належить до Львівська область, Харківська область, Миколаївська область, відсортуйте результат по колонці region.
  2. Напишіть запит, який поверне усі записи колонок location, region, population з таблиці location у схемі data, region яких належить до Київська область і population від 20000, відсортуйте результат по колонці population від найбільшого до найменшого.
Приклад вирішення домашнього завдання (натисніть для перегляду)

  1. Напишіть запит, який поверне усі записи з таблиці location у схемі data, region яких належить до Львівська область, Харківська область, Миколаївська область, відсортуйте результат по колонці region. 

    1
2
3
4
5
6
7
    select
    *
from
    data.location
where
    region in ('Львівська область', 'Харківська область', 'Миколаївська область')
order by region
    Альтернативний спосіб. 
    1
2
3
4
5
6
7
8
9
    select
    *
from
    data.location
where
    region = 'Львівська область' or
    region = 'Харківська область' or
    region = 'Миколаївська область'
order by region

  2. Напишіть запит, який поверне усі записи колонок location, region, population з таблиці location у схемі data, region яких належить до Київська область і population від 20000, відсортуйте результат по колонці population від найбільшого до найменшого. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    select
    location,
    region,
    population
from
    data.location
where
    region = 'Київська область' and
    population > 20000
order by population desc

2.7 Фільтрація по тексту (LIKE).

Для фільтрації по тексту використовуються оператори порівняння =, in та like.

= використовується коли необхідно знайти текст по повному збігу, наприклад наступний запит поверне записи де region має значення Київська область: 

1
2
3
4
5
6
7
8
select
    location,
    region,
    population
from
    data.location
where
    region = 'Київська область'

in теж шукає повний збіг, але дає можливість одночасно вказувати пошук по декількох збігах, наприклад наступний запит поверне записи де region має значення Київська область та Вінницька область: 

1
2
3
4
5
6
7
8
select
    location,
    region,
    population
from
    data.location
where
    region in('Київська область', 'Вінницька область')

like є складнішим оператором, котрий дозволяє шукати дані по вказаному патерну, який формується на основі двох знаків:

  1. Знак відсотка % - означає відповідність будь-якій послідовності з нуля чи більше символів.
  2. Знак підкреслення _ - означає відповідність будь-якому окремому символу.

Приклади патернів:

  1. location like 'Ки%' - шукатиме значення у location, які починаються на Ки.
  2. location like '%ий' - шукатиме значення у location, які закінчуються на ий.
  3. location like '%риж%' - шукатиме значення у location, які містять риж.
  4. location like 'Ки_в' - шукатиме значення у location, які починаються на Ки, закінчуються на в, між собою мають 1 символ.

Приклад запиту, який поверне усі записи локації, які починаються на Ки:

1
2
3
4
5
6
7
8
select
    location,
    region,
    population
from
    data.location
where
    location like 'Ки%'
Домашнє завдання (натисніть для перегляду)
  1. Напишіть запит, який поверне усі записи з таблиці location у схемі data, де location містять ив.
  2. Напишіть запит, який поверне усі записи з таблиці location у схемі data, де location починаються на Ки.
  3. Напишіть запит, який поверне усі записи з таблиці location у схемі data, які починаються на Ві, закінчуються на ниця, між собою мають 1 символ.
Приклад вирішення домашнього завдання (натисніть для перегляду)

  1. Напишіть запит, який поверне усі записи з таблиці location у схемі data, де location містить ив. 

    1
2
3
4
5
6
    select
    *
from
    data.location
where
    location like '%ив%'

  2. Напишіть запит, який поверне усі записи з таблиці location у схемі data, де location починаються на Ра. 

    1
2
3
4
5
6
    select
    *
from
    data.location
where
    location like 'Ра%'

  3. Напишіть запит, який поверне усі записи з таблиці location у схемі data, які починаються на Ві, закінчуються на ниця, між собою мають 1 символ. 

    1
2
3
4
5
6
    select
    *
from
    data.location
where
    location like 'Ві_ниця'

2.8 Особливості складної фільтрації.

Під складною фільтрацією мається на увазі фільтрація, яка використовує логічні оператори and та or, нижче наведені приклади некоректного написання складних запитів та варіанти їх виправлення.

  • Запит, який повинен повернути перелік локацій з таблиці location у схемі data, region яких відноситься до Чернігівська область, Одеська область та Сумська область:

Так неправильно

1
2
3
4
5
6
7
8
select
    *
from
    data.location
where
    region = 'Чернігівська область' and
    region = 'Одеська область' and
    region = 'Сумська область'

Так правильно

1
2
3
4
5
6
select
    *
from
    data.location
where
    region in('Чернігівська область', 'Одеська область', 'Сумська область')
  • Запит, який повинен повернути перелік виконаних замовлень (status дорівнює completed) з таблиці order у схемі data, з сумою замовлень 500 та менше грн, де payment_type дорівнює cash та delivery_type дорівнює pickup або delivery_type містить novaposhta чи ukrposhta:

Так неправильно

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
select
    *
from
    data.order
where
    status = 'completed' and
    price <= 500 and
    payment_type = 'cash' and
    delivery_type = 'pickup' or
    delivery_type in('novaposhta', 'ukrposhta')

Так правильно, але це неоптимально

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
select
    *
from
    data.order
where
    status = 'completed' and
    price <= 500 and
    payment_type = 'cash' and
    delivery_type = 'pickup' or
    status = 'completed' and
    price <= 500 and
    delivery_type in('novaposhta', 'ukrposhta')

Так правильно

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
select
    *
from
    data.order
where
    status = 'completed' and
    price <= 500 and
    (payment_type = 'cash' and
    delivery_type = 'pickup' or
    delivery_type in('novaposhta', 'ukrposhta'))

2.9 Сегментація даних (CASE).

Сегментація даних допомагає системніше аналізувати дані та будувати розрахункові моделі, вона полягає в тому щоб розділити дані на групи, які визначаються на основі умов. SQL дає можливість сегментувати дані виразом case, він має наступну структуру: 

1
2
3
4
5
6
case 
    when умова_1 then результат_1
    when умова_2 then результат_2
    ...
    else результат_для_всього_іншого
    end as назва_колонки

Як приклад, наступний запит сегментує усі замовлення з таблиці order у схемі data на 3 групи:

  1. Замовлення до 500 грн, назва сегмента low_price;
  2. Замовлення від 500 до 2000 грн, назва сегмента mid_price;
  3. Замовлення від 2000 грн, назва сегмента high_price.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select
    order_id,
    price,
    case
        when price > 0 and price <= 500 then 'low_price'
        when price > 500 and price <= 2000 then 'mid_price'
        when price > 2000 then 'high_price'
        else 'other_price'
        end as price_segment
from
    data.order

Оскільки перевірка сегментів у виразі case відбувається з гори до низу, запит можна спростити наступним чином:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select
    order_id,
    price,
    case
        when price > 2000 then 'high_price' -- першим сегмент проставиться усім, де сума вищее 2000 грн
        when price > 500 then 'mid_price'   -- другим сегмент проставиться усім, де сума вищее 500 грн
        when price > 0 then 'low_price'     -- третім сегмент проставиться усім, де сума вищее 0 грн
        else 'other_price'                  -- четвертим усім, що залишились
        end as price_segment
from
    data.order

Вираз case не обмежується присвоюванням текстового значення, він також може здійснювати розрахунки та виконувати функції.

Як приклад, наступний запит розрахує відсоток комісії з оплати карткою, для виконаних замовлень, у таблиці order, в схемі data для:

  • Оплати карткою онлайн payment_type = card_online, комісія 3%;
  • Оплати карткою через термінал при отриманні payment_type = card_on_arrival, комісія 2%;
  • Усіх інших способів оплати, комісія 0%;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select
    order_id,
    price,
    status,
    payment_type,
    case
        when status = 'completed' and payment_type = 'card_online' then 0.03
        when status = 'completed' and payment_type = 'card_on_arrival' then 0.02
        else 0 end as payment_commission_rate
from
    data.order

Для розрахунку суми комісії, можна одразу виконати арифметичну дію множення:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select
    order_id,
    price,
    status,
    payment_type,
    case
        when status = 'completed' and payment_type = 'card_online' then price * 0.03
        when status = 'completed' and payment_type = 'card_on_arrival' then price * 0.02
        else 0 end as payment_commission
from
    data.order
Домашнє завдання (натисніть для перегляду)
  1. Напишіть запит, який розрахує вартість доставки для замовлень у статусі status виконане completed, в залежності від логіста delivery_type, в таблиці location, у схемі data, тариф логіста ukrposhta становить 50 грн, а novaposhta 70 грн, назвіть цю колонку delivery_cost.
Приклад вирішення домашнього завдання (натисніть для перегляду)
  1. Напишіть запит, який розрахує вартість доставки для замовлень у статусі status виконане completed, в залежності від логіста delivery_type, в таблиці location, у схемі data, тариф логіста ukrposhta становить 50 грн, а novaposhta 70 грн, назвіть цю колонку delivery_cost. 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    select
    order_id,
    price,
    status,
    delivery_type,
    case
        when status = 'completed' and delivery_type = 'ukrposhta' then 50
        when status = 'completed' and delivery_type = 'novaposhta' then 70
        else 0
        end as delivery_cost
from
    data.order

Розділ 3. Агрегації даних

3.1 Агрегатні функції та групування даних (GROUP BY).

Агрегатні функції виконують обчислювальні операції на наборі записів. Основними агрегатними функціями є:

  • avg() – повертає середнє значення.
  • sum() – повертає суму значень.
  • count() – повертає кількість значень.
  • max() – повертає максимальне значення.
  • min() – повертає мінімальне значення.

Приклад запиту, який розрахує агреговану інформацію по замовленнях, з таблиці order, у схемі data: 

1
2
3
4
5
6
7
8
select
    count(order_id) as total_order_count, -- загальна кількість замовлень
    sum(price) as total_order_amount,     -- загальна сума замовлень
    avg(price) as average_order_amount,   -- середня сума замовлень
    min(price) as min_order_amount,       -- мінімальна сума замовлень
    max(price) as max_order_amount        -- максимальна сума замовлень
from
    data.order

Функція count() додатково вміє розраховувати кількість унікальних записів за допомогою ключового слова distinct.
Приклад запиту, який розрахує кількість унікальних, замовлених товарів, з таблиці order_item, у схемі data: 

1
2
3
4
select
    count(distinct product_id) as unique_product_count
from
    data.order_item

group by дозволяє агрегувати дані в рамках визначених груп, у форматі group by колонка.
Приклад запиту, який розрахує кількість та суму замовлень по способах доставки, з таблиці order_item, у схемі data: 

1
2
3
4
5
6
7
select
    delivery_type,
    count(order_id) as order_count,
    sum(price) as order_amount
from
    data.order
group by delivery_type

У базах даних PostgreSQL можна групувати дані вказуючи не тільки назви колонок, а і їх позицію у запиті:

1
2
3
4
5
6
7
select
    delivery_type,
    count(order_id) as order_count,
    sum(price) as order_amount
from
    data.order
group by 1 -- поверне той самий результат що і group by delivery_type

Через кому можна одночасно групувати по декількох групах. Той самий запит, але згрупований по способу доставки та даті замовлення:

1
2
3
4
5
6
7
8
9
select
    cast(date_created as date) as day,
    delivery_type,
    count(order_id) as order_count,
    sum(price) as order_amount
from
    data.order
group by day, delivery_type
order by day, delivery_type -- сортування результату по групах
Домашнє завдання (натисніть для перегляду)
  1. Напишіть запит, який розрахує кількість відгуків opinion_id та їх середню оцінку rating, з таблиці opinion, у схемі data.
  2. Напишіть запит, який розрахує кількість відгуків opinion_id по кожній оцінці rating, з таблиці opinion, у схемі data, відсортуйте результат за кількістю відгуків.
Приклад вирішення домашнього завдання (натисніть для перегляду)
  1. Напишіть запит, який розрахує кількість відгуків opinion_id та їх середню оцінку rating, з таблиці opinion, у схемі data. 
    1
2
3
4
5
    select
    count(opinion_id) as opinion_count,
    avg(rating) as average_rating
from
    data.opinion
  2. Напишіть запит, який розрахує кількість відгуків opinion_id по кожній оцінці rating, з таблиці opinion, у схемі data, відсортуйте результат за кількістю відгуків. 
    1
2
3
4
5
6
7
    select
    rating,
    count(opinion_id) as opinion_count
from
    data.opinion
group by rating
order by opinion_count desc

3.2 Додаткові можливості фільтрації при агрегаціях (FILTER, HAVING).

У запиті з агрегаціями можна використовувати звичайну фільтрацію даних через where, вона застосовується до всіх даних у запиті.
Приклад розрахунку кількості та суми замовлень по днях, за травень 2023 року, з таблиці order, у схемі data: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select
    cast(date_created as date) as day,
    count(order_id) as order_count,
    sum(price) as order_amount
from
    data.order
where
    cast(date_created as date) >= '2023-05-01' and
    cast(date_created as date) <= '2023-05-31'
group by day
order by day desc

Іноді загальної фільтрації через where може бути недостатньо, в такому випадку до конкретної агрегації можна застосувати додатковий фільтр filter, його застосування виглядає наступним чином агрегатна_функція() filter(where умова).

Наступний запит розрахує загальну, виконану, відмінену кількість замовлень по днях, за травень 2023 року: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
select
    cast(date_created as date) as day,
    count(order_id) as order_count,
    count(order_id) filter(where status = 'completed') as completed_order_count,
    count(order_id) filter(where status = 'canceled') as canceled_order_count
from
    data.order
where
    cast(date_created as date) >= '2023-05-01' and
    cast(date_created as date) <= '2023-05-31'
group by day
order by day desc

За допомогою having можна відфільтрувати результат агрегації, у форматі having агрегатна_функція() оператор_порівняння значення.
Приклад того самого запиту, але тільки по днях, де сума замовлень більше або дорівнює 50: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
select
    cast(date_created as date) as day,
    count(order_id) as order_count,
    count(order_id) filter(where status = 'completed') as completed_order_count,
    count(order_id) filter(where status = 'canceled') as canceled_order_count
from
    data.order
where
    cast(date_created as date) >= '2023-05-01' and
    cast(date_created as date) <= '2023-05-31'
group by day
having count(order_id) >= 50
order by day desc

Домашнє завдання (натисніть для перегляду)
  1. Напишіть запит, який розрахує суму замовлень price по способах доставки delivery_type в окремих колонках, використовуючи фільтрацію при агрегації filter, з таблиці order, у схемі data, тільки по днях, де загальна сума замовлень за день більше 100000.
Приклад вирішення домашнього завдання (натисніть для перегляду)
  1. Напишіть запит, який розрахує суму замовлень price по способах доставки delivery_type в окремих колонках, використовуючи фільтрацію при агрегації filter, з таблиці order, у схемі data, тільки по днях, де загальна сума замовлень за день більше 100000. 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    select
    cast(date_created as date) as day,
    sum(price) as order_amount,
    sum(price) filter(where delivery_type = 'pickup') as pickup_order_amount,
    sum(price) filter(where delivery_type = 'ukrposhta') as ukrposhta_order_amount,
    sum(price) filter(where delivery_type = 'novaposhta') as novaposhta_order_amount
from
    data.order
group by day
having sum(price) > 100000
order by day desc

Розділ 4. Об'єднання даних

4.1 Реляційність у базах даних та навіщо об'єднувати таблиці.

Реляційна модель базується на зберіганні даних по об'єктах в окремих таблицях, тобто кожна таблиця представляє окремий об'єкт, а колонки в ній це набір атрибутів об'єкта. Завдяки спільним атрибутам об'єктів, вони можуть бути пов'язані між собою, в цьому і полягає відношення (relation) їх один до одного.

Навчальна база даних цього курсу є типовим представником реляційної моделі зберігання і використання даних.

Відношення таблиць між собою (натисніть для перегляду)

Залежність таблиць

SQL надає можливості об'єднувати дані з різних таблиць, це дозволяє збагачувати їх додатковими атрибутами.

4.2 Які існують можливості об'єднання даних і на чому варто зосередитись.

Операції по об'єднанню даних поділяються на горизонтальні та вертикальні.

Горизонтальні операції об'єднання зазвичай відбуваються на основі спільного атрибута(ів) таблиць. Наприклад, таблиці data.order, data.order_item, data.opinion у якості спільного атрибута мають колонку order_id і тому можуть бути між собою горизонтально об'єднані на основі цього атрибута.

Image title

Основні операції горизонтального об'єднання: left join, right join, inner join, full outer join, cross join.

Далі, в рамках курсу описується принцип роботи кожного з них, але слід зазначити, що більше за всіх використовуються саме left join та inner join, тому їм краще приділити особливу увагу.

Вертикальні операції об'єднання, об'єднують результати запитів, можуть бути здійснені тільки в тому випадку, коли результати запитів мають однакову кількість колонок та послідовність їх типів.

Такого роду об'єднання можуть бути корисні при об'єднанні однакових даних за різні періоди або по різних сегментах.

Image title

Основні операції вертикального об'єднання: union, union all, intersect, except.

Далі, в рамках курсу описується принцип роботи кожного з них, але слід зазначити, що більше за всіх використовуються саме union та union all, тому їм краще приділити особливу увагу.

4.3 LEFT JOIN, RIGHT JOIN.

left join приєднує до лівої таблиці дані правої таблиці, на основі вказаної умови (зазвичай спільного атрибута(ів)). Таке об'єднання залишає усі записи лівої таблиці, при цьому приєднує тільки ті записи правої таблиці, по яких виконується умова об'єднання (наприклад збіг атрибутів), якщо таких записів не знайдено, то буде проставлено значення null.

Image title

Щоб виконати об'єднання left join, потрібно виконати 4 кроки:

  1. У select вказати колонки таблиць, які необхідно вивести в результаті запиту.
  2. У from вказати ліву таблицю.
  3. У left join вказати праву таблицю.
  4. Після ключового слова on вказати умову об'єднання таблиць.

Приклад запиту, який виконає об'єднання left join таблиці data.order з data.opinion, на основі спільного атрибута order_id: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select
    ord.date_created,
    ord.order_id,
    ord.status,
    ord.price,
    opn.rating
from
    data.order as ord            -- це ліва таблиця
left join
    data.opinion as opn on       -- це права таблиця
    ord.order_id = opn.order_id  -- це умова об'єднання

Результат запиту поверне перелік усіх замовлень та рейтинг відгуку по ним, якщо відгуку по замовленню не існує, буде значення null.

У прикладі запиту також можна побачити, що таблицям присвоюються псевдоніми через оператор as, так таблиці data.order присвоюється псевдонім ord, а data.opinion псевдонім opn, це робиться, щоб зручніше посилатись на колонки цих таблиць у запиті.

Об'єднання right join працює аналогічно left join, єдина їх відмінність в тому, що right join приєднує дані лівої таблиці до правої.

Домашнє завдання (натисніть для перегляду)
  1. Використовуючи left join напишіть запит, який приєднає до таблиці data.product таблицю data.category на основі атрибута category_id, в цьому запиті виведіть колонки product_id, category_id з таблиці data.product, колонку name з таблиці data.category, колонці name присвойте ім'я category_name.
  2. Використовуючи left join напишіть запит, який приєднає до таблиці data.order таблицю data.location на основі атрибута delivery_location_id = location_id, в цьому запиті виведіть колонки order_id, date_created, price, delivery_type з таблиці data.order, колонки location, region з таблиці data.location, колонці location присвойте ім'я delivery_location, а колонці region delivery_region.
Приклад вирішення домашнього завдання (натисніть для перегляду)

  1. Використовуючи left join напишіть запит, який приєднає до таблиці data.product таблицю data.category на основі атрибута category_id, в цьому запиті виведіть колонки product_id, category_id з таблиці data.product та колонку name з таблиці data.category, колонці name присвойте ім'я category_name. 

    1
2
3
4
5
6
7
8
9
    select
    prd.product_id,
    prd.category_id,
    cat.name as category_name
from
    data.product as prd
left join
    data.category as cat on
    prd.category_id = cat.category_id

  2. Використовуючи left join напишіть запит, який приєднає до таблиці data.order таблицю data.location на основі атрибута delivery_location_id = location_id, в цьому запиті виведіть колонки order_id, date_created, price, delivery_type з таблиці data.order, колонки location, region з таблиці data.location, колонці location присвойте ім'я delivery_location, а колонці region delivery_region. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    select
    ord.order_id,
    ord.date_created,
    ord.price,
    ord.delivery_type,
    loc.location as delivery_location,
    loc.region as delivery_region
from
    data.order as ord
left join
    data.location as loc on
    ord.delivery_location_id = loc.location_id

4.4 INNER JOIN.

inner join об'єднує дані двох таблиць, на основі вказаної умови (зазвичай спільного атрибута(ів)). Таке об'єднання залишає тільки ті записи обох таблиць, де були виконані умови об'єднання, усі інші записи повернуті не будуть.

Image title

Щоб виконати об'єднання inner join, потрібно виконати 4 кроки:

  1. У select вказати колонки таблиць, які необхідно вивести в результаті запиту.
  2. У from вказати ліву таблицю.
  3. У inner join вказати праву таблицю.
  4. Після ключового слова on вказати умову об'єднання таблиць.

Приклад запиту, який виконає об'єднання inner join таблиці data.order з data.opinion, на основі спільного атрибута order_id: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select
    ord.order_id,
    ord.date_created,
    ord.price,
    opn.opinion_id,
    opn.rating
from
    data.order as ord           -- це ліва таблиця
inner join
    data.opinion as opn on      -- це права таблиця
    ord.order_id = opn.order_id -- це умова об'єднання

Результат запиту поверне тільки ті замовлення, по яких є відгуки, та тільки ті відгуки, по яких є замовлення, об'єднавши їх між собою.

Домашнє завдання (натисніть для перегляду)
  1. Використовуючи inner join напишіть запит, який об'єднає таблиці data.order_item та data.opinion на основі атрибута order_id, в запиті виведіть колонки order_id, product_id, quantity, unit_price з таблиці data.order_item, колонку rating з таблиці data.opinion.
Приклад вирішення домашнього завдання (натисніть для перегляду)
  1. Використовуючи inner join напишіть запит, який об'єднає таблиці data.order_item та data.opinion на основі атрибута order_id, в запиті виведіть колонки order_id, product_id, quantity, unit_price з таблиці data.order_item, колонку rating з таблиці data.opinion. 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    select
    itm.order_id,
    itm.product_id,
    itm.quantity,
    itm.unit_price,
    opn.rating
from
    data.order_item as itm
inner join
    data.opinion as opn on
    itm.order_id = opn.order_id

4.5 FULL OUTER JOIN.

full outer join об'єднує дані двох таблиць, на основі вказаної умови (зазвичай спільного атрибута(ів)). Таке об'єднання залишає усі записи обох таблиць, об'єднуючи тільки ті з них, які виконують умови об'єднання, іншим присвоює значення null у відсутніх атрибутах.

Image title

Щоб виконати об'єднання full outer join, потрібно виконати 4 кроки:

  1. У select вказати колонки таблиць, які необхідно вивести в результаті запиту.
  2. У from вказати ліву таблицю.
  3. У full outer join вказати праву таблицю.
  4. Після ключового слова on вказати умову об'єднання таблиць.

Приклад запиту, який виконає об'єднання full outer join таблиці data.order з data.opinion, на основі спільного атрибута order_id: 

1
2
3
4
5
6
7
8
select
    prd.product_id,
    cat.category_id
from
    data.product as prd               -- це ліва таблиця
full outer join 
    data.category as cat on           -- це права таблиця
    prd.category_id = cat.category_id -- це умова об'єднання

Результат запиту поверне перелік усіх записів з обох таблиць, об'єднавши ті з них, які виконують умови об'єднання.

4.6 CROSS JOIN.

cross join об'єднує дані двох таблиць у вигляді декартового добутку. Таке об'єднання залишає усі записи обох таблиць, формуючи усі можливі комбінації з них.

Image title

Щоб виконати об'єднання cross join, потрібно виконати 3 кроки:

  1. У select вказати колонки таблиць, які необхідно вивести в результаті запиту.
  2. У from вказати ліву таблицю.
  3. У cross join вказати праву таблицю.

Приклад запиту, який виконає об'єднання cross join таблиці data.product з data.category: 

1
2
3
4
5
6
7
8
select
    prd.product_id,
    cat.category_id,
    cat.name as category_name
from
    data.product as prd   -- це ліва таблиця
cross join
    data.category as cat  -- це права таблиця

Результат запиту поверне перелік усіх товарів з усіма можливими комбінаціями категорій.

4.7 UNION, UNION ALL.

union об'єднує результати двох або більше запитів в один набір результатів, повертає унікальні записи.

Image title

union all об'єднує результати двох або більше запитів в один набір результатів, повертає усі записи.

Image title

Щоб виконати об'єднання union або union all, запити повинні виконувати 2 вимоги:

  1. Кількість і порядок стовпців у запитах мають бути однаковими.
  2. Типи даних у запитах мають бути сумісними.

Приклад об'єднання запитів, використовуючи union, який поверне кількість замовлень по днях за січень та грудень 2023 року: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
    cast(date_created as date) as day,
    count(order_id) as order_count
from
    data.order
where
    cast(date_created as date) >= '2023-01-01' and
    cast(date_created as date) <= '2023-01-31'
group by day

union

select
    cast(date_created as date) as day,
    count(order_id) as order_count
from
    data.order
where
    cast(date_created as date) >= '2023-12-01' and
    cast(date_created as date) <= '2023-12-31'
group by day
order by day desc

Домашнє завдання (натисніть для перегляду)
  1. Використовуючи union напишіть запит, який поверне унікальні товари product_id з таблиці data.order_item, які були замовлені за червень та серпень 2023 року order_date.
  2. Використовуючи union all об'єднайте 2 запити. Перший запит повертає колонки location, population з таблиці data.location де кількість населення population більше 1000000. Другий запит повертає ті самі колонки з тієї самої таблиці місту location Київ.
Приклад вирішення домашнього завдання (натисніть для перегляду)

  1. Використовуючи union напишіть запит, який поверне унікальні товари product_id з таблиці data.order_item, які були замовлені за червень та серпень 2023 року order_date. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    select
    product_id
from
    data.order_item
where
    cast(order_date as date) >= '2023-06-01' and
    cast(order_date as date) >= '2023-06-30'

union

select
    product_id
from
    data.order_item
where
    cast(order_date as date) >= '2023-08-01' and
    cast(order_date as date) >= '2023-08-31'

  2. Використовуючи union all об'єднайте 2 запити. Перший запит повертає колонки location, population з таблиці data.location де кількість населення population більше 1000000. Другий запит повертає ті самі колонки з тієї самої таблиці місту location Київ. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    select
    location,
    population
from
    data.location
where
    population > 1000000

union all

select
    location,
    population
from
    data.location
where
    location = 'Київ'

4.8 INTERSECT, EXCEPT.

intersect об'єднує результати двох або більше запитів в один набір результатів, повертає записи які зустрічаються в усіх запитах.

Image title

except повертає записи першого запиту, які не зустрічаються в другому запиті.

Image title

Щоб виконати об'єднання intersect або except, запити повинні виконувати 2 вимоги:

  1. Кількість і порядок стовпців у запитах мають бути однаковими.
  2. Типи даних у запитах мають бути сумісними.

Приклад запиту використовуючи intersect, який поверне товари замовлені, як в січні, так і в грудні 2023 року: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
select distinct
    product_id
from
    data.order_item
where
    cast(order_date as date) >= '2023-01-01' and
    cast(order_date as date) <= '2023-01-31'

intersect

select distinct
    product_id
from
    data.order_item
where
    cast(order_date as date) >= '2023-12-01' and
    cast(order_date as date) <= '2023-12-31'

4.9 Складні об'єднання даних.

Використовуючи логічні оператори and та or можна писати більш комплексні умови об'єднання.
Наступний запит, завдяки двом умовам об'єднання, поверне замовлення та відгуки, які були зроблені день в день:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select
    ord.date_created as order_date,
    ord.order_id,
    opn.opinion_id,
    opn.date_created as opinion_date
from
    data.order as ord
inner join
    data.opinion as opn on
    ord.order_id = opn.order_id and                                 -- перша умова об'єднання
    cast(ord.date_created as date) = cast(opn.date_created as date) -- друга умова об'єднання

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
select
    ord.date_created,
    ord.order_id,
    ord.status as order_status,
    itm.product_id,
    itm.category_id,
    cat.name as category_name,
    ord.delivery_type,
    loc.location as delivery_location,
    loc.region as delivery_region
from
    data.order as ord                          -- таблиця з замовленнями
left join
    data.order_item as itm on                  -- таблиця з товарами замовлень
    ord.order_id = itm.order_id
left join
    data.category as cat on                    -- таблиця з категоріями
    itm.category_id = cat.category_id
left join
    data.location as loc on                    -- таблиця з локаціями
    ord.delivery_location_id = loc.location_id

Використовуючи оператор where, можна фільтрувати результати запитів з об'єднаннями, так само як і звичайні запити.
Наступний запит, поверне перелік замовлень з доставкою по Київській області:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
select
    ord.date_created,
    ord.order_id,
    ord.delivery_type,
    loc.location as delivery_location,
    loc.region as delivery_region
from
    data.order as ord
left join
    data.location as loc on
    ord.delivery_location_id = loc.location_id
where
    loc.region = 'Київська область'
Домашнє завдання (натисніть для перегляду)
  1. Використовуючи left join напишіть запит, який до таблиці з товарами замовлень data.order_item приєднає таблицю з категоріями data.category, на основі атрибута category_id, виведіть колонки order_date, order_id, product_id, category_id з таблиці data.order_item, колонку name з таблиці data.category, відфільтруйте результат запиту по колонці name, щоб отримати записи тільки по категорії 'велосипеди'.
  2. Використовуючи left join напишіть запит, який до таблиці з відгуками data.opinion приєднає таблицю з замовленнями data.order і таблицю з локаціями data.location, на основі атрибутів order_id і delivery_location_id = location_id відповідно, виведіть колонки date_created, opinion_id з таблиці data.opinion, колонку delviery_location_id з таблиці data.order, колонки location, region з таблиці data.location.
Приклад вирішення домашнього завдання (натисніть для перегляду)

  1. Використовуючи left join напишіть запит, який до таблиці з товарами замовлень data.order_item приєднає таблицю з категоріями data.category, на основі атрибута category_id, виведіть колонки order_date, order_id, product_id, category_id з таблиці data.order_item, колонку name з таблиці data.category, відфільтруйте результат запиту по колонці name, щоб отримати записи тільки по категорії 'велосипеди'. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    select
    itm.order_date,
    itm.order_id,
    itm.product_id,
    itm.category_id,
    cat.name
from
    data.order_item as itm
left join
    data.category as cat on
    itm.category_id = cat.category_id
where
    cat.name = 'велосипеди'

  2. Використовуючи left join напишіть запит, який до таблиці з відгуками data.opinion приєднає таблицю з замовленнями data.order і таблицю з локаціями data.location, на основі атрибутів order_id і delivery_location_id = location_id відповідно, виведіть колонки date_created, opinion_id з таблиці data.opinion, колонку delviery_location_id з таблиці data.order, колонки location, region з таблиці data.location. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
    select
    opn.date_created,
    opn.opinion_id,
    ord.delivery_location_id,
    loc.location,
    loc.region
from
    data.opinion as opn
left join
    data.order as ord on
    opn.order_id = ord.order_id
left join
    data.location as loc on
    ord.delivery_location_id = loc.location_id

Розділ 5. Загальні табличні вирази

5.1 Що таке загальні табличні вирази (WITH).

CTE (Common Table Expression) загальний табличний вираз — це тимчасовий набір результатів, на який можна посилатись як на таблицю, їх тимчасовість полягає в тому, що вони існують лише під час виконання запиту.

Загальний табличний вираз має наступну структуру:

1
2
3
4
5
6
7
8
9
with
назва_виразу as(
select ...
)

select
    ...
from
    назва_виразу
  1. Ключовим словом with ініціюється створення табличного виразу.
  2. Табличному виразу присвоюється назва.
  3. У блоці as() вказується запит, результат якого буде перетворений на табличний вираз.
  4. Пишеться запит, який посилається на табличний вираз.

Приклад запиту з використанням табличного виразу: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
with
big_cities as(
select
    location as city,
    region,
    population
from
    data.location
where
    population > 500000
)

select
    *
from
    big_cities

Можна створювати декілька табличних виразів, розділяючи їх комою: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
with
big_cities as(
select
    location as city,
    region,
    population
from
    data.location
where
    population > 500000
),

small_cities as(
select
    location as city,
    region,
    population
from
    data.location
where
    population < 5000
)

select
    *
from
    small_cities

Одні табличні вирази можуть посилитися на інші табличні вирази: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
with
big_cities as(
select
    location as city,
    region,
    population
from
    data.location
where
    population > 500000
),

very_big_cities as(
select
    *
from
    big_cities
where
    population > 1000000
)

select
    *
from
    very_big_cities

Табличні вирази можна поєднувати між собою та з таблицями: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
with
completed_orders as(
select
    order_id
from
    data.order
where
    status = 'completed'
),

order_items as(
select
    order_id,
    product_id,
    category_id
from
    data.order_item
),

sold_items as(
select
    ord.order_id,
    itm.product_id,
    itm.category_id,
    cat.name as category_name
from
    completed_orders as ord
left join
    order_items as itm on
    ord.order_id = itm.order_id
left join
    data.category as cat on
    itm.category_id = cat.category_id
)

select
    category_name,
    count(product_id) as sold_product_count
from
    sold_items
group by category_name
order by sold_product_count desc

Домашнє завдання (натисніть для перегляду)

  1. Напишіть запит, який посилається на регулярний табличний вираз під назвою cities, складений з колонок location, region, таблиці data.location.

  2. Напишіть запит, який складається з двох регулярних табличних виразів:

    • cities, складений з колонок location, region, таблиці data.location.
    • regions, який розраховує кількість міст location по регіонах region з виразу cities.

    Виведіть результат виразу regions, відсортувавши регіони за кількістю міст від більшого до меншого.

Приклад вирішення домашнього завдання (натисніть для перегляду)

  1. Напишіть запит, який посилається на регулярний табличний вираз під назвою cities, складений з колонок location, region, таблиці data.location. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    with
cities as(
select
    location,
    region
from
    data.location
)

select
    *
from
    cities

  2. Напишіть запит, який складається з двох регулярних табличних виразів:

    • cities, складений з колонок location, region, таблиці data.location.
    • regions, який розраховує кількість міст location по регіонах region з виразу cities.

    Виведіть результат виразу regions, відсортувавши регіони за кількістю міст від більшого до меншого.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
with
cities as(
select
    location,
    region
from
    data.location
),

regions as(
select
    region,
    count(location) as city_count
from
    cities
group by region
)

select
    *
from
    regions
order by city_count desc

5.2 Підзапити як часткова альтернатива.

Підзапит (subquery) – це запит, вкладений в інший запит, вони можуть використовуватись в рамках операторів select, from, where.

Приклад використання запиту в рамках оператору where наведений нижче.
Результат запиту поверне перелік замовлень, сума яких вище загальної середньої суми замовлень: 

1
2
3
4
5
6
select
    *
from
    data.order
where
    price > (select avg(price) from data.order)

Приклад використання запиту в рамках оператору from наведений нижче.
Результат запиту поверне перелік виконаних замовлень, сума яких вище 1000. 

1
2
3
4
select
    *
from
    (select order_id, price from data.order where status = 'completed' and price > 1000) as order_subquery

Приклад використання запиту в рамках оператору select наведений нижче.
Результат запиту сегментує замовлення, в залежності від відношення їх суми замовлення до середньої суми замовлень. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select
    order_id,
    price,
    case
        when price > (select avg(price) from data.order) then 'price_higher_than_average'
        when price < (select avg(price) from data.order) then 'price_lower_than_average'
        when price = (select avg(price) from data.order) then 'price_equals_to_average'
        else 'other'
        end as price_segment
from
    data.order

Домашнє завдання (натисніть для перегляду)
  1. Використовуючи підзапит в рамках оператору where, напишіть запит який поверне усі записи відгуків з таблиці data.opinion, які мають оцінку нижче середньої.
  2. Використовуючи підзапит в рамках оператору from, напишіть запит який поверне кількість міст location по регіонах region з таблиці data.location (виконайте агрегацію у підзапиті).
Приклад вирішення домашнього завдання (натисніть для перегляду)

  1. Використовуючи підзапит в рамках оператору where, напишіть запит який поверне усі записи відгуків з таблиці data.opinion, які мають оцінку нижче середньої. 

    1
2
3
4
5
6
    select
    *
from
    data.opinion
where
    rating < (select avg(rating) from data.opinion)

  2. Використовуючи підзапит в рамках оператору from, напишіть запит який поверне кількість міст location по регіонах region з таблиці data.location (виконайте агрегацію у підзапиті). 

    1
2
3
4
    select
    *
from
    (select region, count(location) from data.location group by region) as subquery

5.3 Робимо складні запити простіше.

Завдяки загальним табличним виразам складні по логіці запити можна робити значно простіше, розділяючи їх на блоки.

Розрахунок кількості та середньої оцінки відгуків по категоріях:

Не використовуючи CTE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
select
    cat.name as category_name,
    count(opn.opinion_id) as opinion_count,
    avg(opn.rating) as average_opinion_rating
from
    data.order_item as itm
left join
    data.category as cat on
    itm.category_id = cat.category_id
inner join
    data.opinion as opn on
    itm.order_id = opn.order_id
group by category_name
order by opinion_count desc

Використовуючи CTE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
with
items_category as(                    -- спочатку ідентифікуємо назви категорій
select
    itm.order_id,
    itm.product_id,
    itm.category_id,
    cat.name as category_name
from
    data.order_item as itm
left join
    data.category as cat on
    itm.category_id = cat.category_id
),

items_opinion as(                     -- додаємо відгуки
select
    icat.order_id,
    icat.product_id,
    icat.category_name,
    opn.opinion_id,
    opn.rating as opinion_rating
from
    items_category as icat
inner join
    data.opinion as opn on
    icat.order_id = opn.order_id
)

select                                -- агрегуємо
    category_name,
    count(opinion_id) as opinion_count,
    avg(opinion_rating) as average_opinion_rating
from
    items_opinion
group by category_name
order by opinion_count desc

Розрахунок кількості замовлень по категоріях:

Не використовуючи CTE (з підзапитом)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
select
    subquery.category_id,
    cat.name as category_name,
    subquery.order_count
from(
    select
        category_id,
        count(distinct order_id) as order_count
    from
        data.order_item
    group by category_id
) as subquery
left join
    data.category as cat on
    subquery.category_id = cat.category_id
order by order_count desc

Використовуючи CTE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
with
category_orders as(      -- агрегуємо
select
    category_id,
    count(distinct order_id) as order_count
from
    data.order_item
group by category_id
)

select                   -- ідентифікуємо назви категорій
    cor.category_id,
    cat.name as category_name,
    cor.order_count
from
    category_orders as cor
left join
    data.category as cat on
    cor.category_id = cat.category_id
order by order_count desc

Розділ 6. Базові функції PostgreSQL

6.1 Базові функції у SQL.

Існує два типи функцій SQL: агрегатні функції та скалярні (не агрегатні) функції. Агрегатні функції працюють із багатьма записами та повертають підсумок, тоді як скалярні функції працюють із кожним записом окремо.

Агрегатні функції розглядались у Розділ 3. Агрегації даних .

Варто зазначити, що функції не є стандартизованими для усіх видів баз даних, тобто для виконання одних і тих самих операції над даними, різні вендори баз даних мають свої власні назви функцій, в першу чергу це стосується саме скалярних (не агрегатних) функцій. Це означає, що, наприклад, код запиту, який використовує певну функцію в PostgreSQL може не працювати в MySQL або ClickHouse.

Більшість вендорів баз даних підтримує стандартний перелік функцій для роботи з:

  • Числами (виконання різноманітних математичних операцій).
  • Датою та часом (визначення місяця (тижня, року), встановлення та маніпулювання часовими зонами, генерація періодів тощо).
  • Текстом (виокремлення частини символів з тексту, визначення довжини тексту, переведення символів в певний регістр тощо).

Нижче наведені посилання з переліком вбудованих функцій для різних баз даних:

6.2 Базові функції для дати та часу.

Базові функції для роботи з часом у PostgreSQL:

  • current_date - повертає поточну дату (локальну дату сервера, на якому знаходиться база даних).
  • date_trunc() - повертає округлений вираз дати на основі вказаної частини.
  • date_part() - повертає виділену частину дати, яка була вказана.
  • generate_series() - повертає згенерований період, на основі вказаного старту, кінця та гранулярності.

Офіційна документація | Функції для дати та часу PostgreSQL.

Приклади використання date_trunc(): 

1
2
3
4
5
6
7
8
9
select
    date_created,                               -- як виглядає дата в таблиці

    date_trunc('day', date_created) as day,     -- округлена дата до дня
    date_trunc('week', date_created) as week,   -- округлена дата до початку тижня
    date_trunc('month', date_created) as month, -- округлена дата до початку місяця
    date_trunc('year', date_created) as year    -- округлена дата до початку року
from
    data.order

Приклади використання date_part(): 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
select
    date_created,                                        -- як виглядає дата в таблиці

    date_part('hour', date_created) as hour,             -- година
    date_part('day', date_created) as number_of_day,     -- день
    date_part('week', date_created) as number_of_week,   -- тиждень
    date_part('month', date_created) as number_of_month, -- місяць
    date_part('year', date_created) as number_of_year    -- рік
from
    data.order

Приклад використання generate_series(): 

1
2
3
4
5
6
select
    generate_series(
        date('2023-01-01'), -- початок періоду
        date('2023-01-31'), -- кінець періоду
        interval'1 day'     -- гранулярність
    ) as days

Дані, які належать до типу дати та часу можна модифікувати додаючи або віднімаючи інтервали:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select
    current_date,                                                  -- сьогоднішня дата (локальна дата сервера)

    cast(current_date + interval '1 day' as date) as tomorrow,     -- завтра
    cast(current_date - interval '1 day' as date) as yesterday,    -- вчора

    cast(current_date + interval '1 month' as date) as next_month, -- через місяць
    cast(current_date - interval '1 month' as date) as prev_month, -- місяць тому

    cast(current_date + interval '1 year' as date) as next_year,   -- через рік
    cast(current_date - interval '1 year' as date) as prev_year    -- рік тому

6.3 Базові функції для чисел та арифметика.

Базові функції для роботи з числами у PostgreSQL:

  • abs() - повертає модуль числа.
  • round() - повертає округлене число на основі вказаної точності.
  • trunc() - повертає обрізане число на основі заданої точності.
  • sqrt() - повертає квадратний корінь числа.
  • cbrt() - повертає кубічний корінь числа.

Офіційна документація | Функції для чисел PostgreSQL.

Приклади використання abs(): 

1
2
3
4
5
6
select
    abs(-2),    -- модуль числа -2
    abs(4),     -- модуль числа 4

    abs(-7.61), -- модуль числа -7.61
    abs(4.252)  -- модуль числа 4.252

Приклади використання round() та trunc(): 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
select
    round(4.3515, 0), -- округлене до цілого
    round(4.3515, 1), -- округлене до десятого
    round(4.3515, 2), -- округлене до сотого
    round(4.3515, 3), -- округлене до тисячного

    trunc(4.3515, 0), -- обрізане до цілого
    trunc(4.3515, 1), -- обрізане до десятого
    trunc(4.3515, 2), -- обрізане до сотого
    trunc(4.3515, 3)  -- обрізане до тисячного

Приклади використання sqrt() та cbrt(): 

1
2
3
4
5
6
select
    sqrt(4),   -- квадратний корінь з 4
    sqrt(16),  -- квадратний корінь 16

    cbrt(8),   -- кубічний корінь з 8
    cbrt(64)   -- кубічний корінь з 64

Арифметичні дія з числами:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
select
    2 + 2 as addition,       -- додавання
    3 - 2 as subtraction,    -- віднімання

    5 * 5 as multiplication, -- множення
    100.0 / 8.0 as division, -- ділення

    9 % 2 as remainder,      -- розрахунок залишку

    2 ^ 3 as exponentiation  -- зведення в степінь

6.4 Базові функції для тексту.

Базові функції для роботи з текстом у PostgreSQL:

  • lower() - повертає текст у нижньому регістрі.
  • upper() - повертає текст у верхньому регістрі.
  • length() - повертає кількість символів в тексті.
  • left() - повертає зазначену кількість символів з лівої сторони.
  • right() - повертає зазначену кількість символів з правої сторони.

Офіційна документація | Функції для тексту PostgreSQL.

Приклади використання lower() та upper(): 

1
2
3
4
5
select
    'Kyiv is the capital city.' as text,

    lower('Kyiv is the capital city.'), -- поверне весь текст у нижньому регістрі
    upper('Kyiv is the capital city.')  -- поверне весь текст у верхньому регістрі

Приклади використання length: 

1
2
3
4
select
    length('Kyiv is the capital city.'), -- 25 символів
    length('Kyiv'),                      -- 4 символи
    length('is the')                     -- 6 символів, пробіл це теж символ

Приклади використання left() та right(): 

1
2
3
4
5
6
7
8
select
    'Kyiv is the capital city.' as text,

    left('Kyiv is the capital city.', 4),   -- 4 символи з лівої сторони
    left('Kyiv is the capital city.', -6),  -- усі символи з лівої сторони, окрім 6 символів з правої сторони

    right('Kyiv is the capital city.', 5),  -- 4 символи з правої сторони
    right('Kyiv is the capital city.', -5)  -- усі символи з правої сторони, окрім 5 символів з лівої сторони

Розділ 7. Застосовуємо все на практиці

7.1 Верхньорівневий звіт інтернет-магазину.

Код звіту (натисніть для перегляду) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
with
period as(
select
    generate_series(
        date('2023-01-01'),
        date('2023-12-31'),
        interval'1 day'
    )::date as day
),

orders as(
select
    date_created::date as day,
    count(order_id) as order_count,
    count(order_id) filter(where status = 'completed') as order_completed_count,
    count(order_id) filter(where status = 'canceled') as order_canceled_count,
    (count(order_id) filter(where status = 'canceled') / count(order_id)::numeric)::numeric(5,4) as order_cancelation_rate,
    sum(price) filter(where status = 'completed')::numeric(16,2) as order_completed_amount,
    avg(price) filter(where status = 'completed')::numeric(16,2) as order_completed_average_amount
from
    data.order
where
    date_created::date >= (select min(day) from period) and
    date_created::date <= (select max(day) from period)
group by 1
order by 1 desc
),

opinions as(
select
    date_created::date as day,
    count(opinion_id) as opinion_count,
    avg(rating)::numeric(5,4) as opinion_average_rating,
    (count(opinion_id) filter(where rating in (4,5)) / count(opinion_id)::numeric)::numeric(16,2) opinion_promoter_share,
    (count(opinion_id) filter(where rating in (1,2)) / count(opinion_id)::numeric)::numeric(16,2) as opinion_detractor_share,
    (count(opinion_id) filter(where rating = 3) / count(opinion_id)::numeric)::numeric(16,2) as opinion_passive_share
from
    data.opinion
where
    date_created::date >= (select min(day) from period) and
    date_created::date <= (select max(day) from period)
group by 1
order by 1 desc
)

select
    p.day,

    coalesce(ordr.order_count, 0) as order_count,
    coalesce(ordr.order_completed_count, 0) as order_completed_count,
    coalesce(ordr.order_canceled_count, 0) as order_canceled_count,
    coalesce(ordr.order_cancelation_rate, 0) as order_cancelation_rate,
    coalesce(ordr.order_completed_amount, 0) as order_completed_amount,
    coalesce(ordr.order_completed_average_amount, 0) as order_completed_average_amount,

    coalesce(opn.opinion_count, 0) as opinion_count,
    coalesce(opn.opinion_average_rating, 0) as opinion_average_rating,
    coalesce(opn.opinion_promoter_share, 0) as opinion_promoter_share,
    coalesce(opn.opinion_detractor_share, 0) as opinion_detractor_share,
    coalesce(opn.opinion_passive_share, 0) as opinion_passive_share
from
    period as p
left join
    orders as ordr on
    p.day = ordr.day
left join
    opinions as opn on
    p.day = opn.day
order by 1 desc

7.2 Попит на категорії товарів інтернет-магазину.

Код звіту (натисніть для перегляду) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
with
items as(
select
    itm.order_date,
    itm.order_id,
    itm.product_id,
    itm.category_id,
    cat.name as category_name
from
    data.order_item as itm
left join
    data.category as cat on
    itm.category_id = cat.category_id
where
    itm.order_date::date >= '2023-01-01' and
    itm.order_date::date <= '2023-12-31'
),

monthly_stats as(
select
    date_trunc('month', order_date)::date as month,
    category_name,
    count(distinct order_id) as order_count
from
    items
group by 1,2
order by 3 desc
)

select
    category_name,
    coalesce((sum(order_count) filter(where month = '2023-01-01') / avg(order_count)::numeric)::numeric(5,4), 0) as january,
    coalesce((sum(order_count) filter(where month = '2023-02-01') / avg(order_count)::numeric)::numeric(5,4), 0) as february,
    coalesce((sum(order_count) filter(where month = '2023-03-01') / avg(order_count)::numeric)::numeric(5,4), 0) as march,
    coalesce((sum(order_count) filter(where month = '2023-04-01') / avg(order_count)::numeric)::numeric(5,4), 0) as april,
    coalesce((sum(order_count) filter(where month = '2023-05-01') / avg(order_count)::numeric)::numeric(5,4), 0) as may,
    coalesce((sum(order_count) filter(where month = '2023-06-01') / avg(order_count)::numeric)::numeric(5,4), 0) as june,
    coalesce((sum(order_count) filter(where month = '2023-07-01') / avg(order_count)::numeric)::numeric(5,4), 0) as july,
    coalesce((sum(order_count) filter(where month = '2023-08-01') / avg(order_count)::numeric)::numeric(5,4), 0) as august,
    coalesce((sum(order_count) filter(where month = '2023-09-01') / avg(order_count)::numeric)::numeric(5,4), 0) as september,
    coalesce((sum(order_count) filter(where month = '2023-10-01') / avg(order_count)::numeric)::numeric(5,4), 0) as october,
    coalesce((sum(order_count) filter(where month = '2023-11-01') / avg(order_count)::numeric)::numeric(5,4), 0) as november,
    coalesce((sum(order_count) filter(where month = '2023-12-01') / avg(order_count)::numeric)::numeric(5,4), 0) as december
from
    monthly_stats
group by 1

Розділ 8. Зрештою

8.1 Як писати чистий та зрозумілий SQL код.

1. Для структуризації запиту використовуйте відступи та зноси після select, from, where ,left join ...

Уникайте

1
2
3
select order_id, date_created, price, status, device_type,
delivery_type, delivery_location_id, payment_type
from data.order where status = 'completed' and delivery_type = 'pickup'

Віддавайте перевагу

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
select 
    order_id,
    date_created,
    price,
    status,
    device_type,
    delivery_type,
    delivery_location_id,
    payment_type
from 
    data.order
where
    status = 'completed' and 
    delivery_type = 'pickup'

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

Уникайте

1
2
3
where
    status='completed' and
    price>=2*1000

Віддавайте перевагу

1
2
3
where
    status = 'completed' and
    price >= 2 * 1000

3. Використовуйте псевдоніми, які покращують читабельність та зрозумілість коду.

Уникайте

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
with info as(
select
    cast(date_created as date) as day,
    count(order_id) as order_1,
    count(order_id) filter(where status = 'completed') as order_2,
    count(order_id) filter(where status = 'canceled') as order_3
from
    data.order
group by day
)

select
    *
from
    info

Віддавайте перевагу

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
with orders as(
select
    cast(date_created as date) as day,
    count(order_id) as order_count,
    count(order_id) filter(where status = 'completed') as completed_order_count,
    count(order_id) filter(where status = 'canceled') as canceled_order_count
from
    data.order
group by day
)

select
    *
from
    orders

4. Зносьте рядок, коли він занадто великий.

Уникайте

1
2
3
4
5
6
7
8
select
    cast(date_created as date) as day,
    count(order_id) filter(where status = 'completed' and delivery_type = 'novaposhta') / count(order_id) filter(where status = 'completed')::numeric as novaposhta_delivery_share,
    count(order_id) filter(where status = 'completed' and delivery_type = 'ukrposhta') / count(order_id) filter(where status = 'completed')::numeric as ukrposhta_delivery_share
from
    data.order
group by day
order by day desc

Віддавайте перевагу

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
select
    cast(date_created as date) as day,

    count(order_id) filter(where status = 'completed' and delivery_type = 'novaposhta') 
        / count(order_id) filter(where status = 'completed')::numeric as novaposhta_delivery_share,

    count(order_id) filter(where status = 'completed' and delivery_type = 'ukrposhta') 
        / count(order_id) filter(where status = 'completed')::numeric as ukrposhta_delivery_share
from
    data.order
group by day
order by day desc

5. Розділяйте смислові блоки великих запитів коментарями та зносами.

Уникайте

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
select
    cast(date_created as date) as day,

    count(order_id) as order_count,
    count(order_id) filter(where status = 'completed') / count(order_id)::numeric as completed_order_share,
    count(order_id) filter(where status = 'completed') / count(order_id)::numeric as canceled_order_share,

    count(order_id) filter(where status = 'completed' and delivery_type = 'novaposhta')
        / count(order_id) filter(where status = 'completed')::numeric as novaposhta_delivery_share,

    count(order_id) filter(where status = 'completed' and delivery_type = 'ukrposhta')
        / count(order_id) filter(where status = 'completed')::numeric as ukrposhta_delivery_share,

    count(order_id) filter(where status = 'completed' and delivery_type = 'pickup')
        / count(order_id) filter(where status = 'completed')::numeric as pickup_delivery_share,

    count(order_id) filter(where status = 'completed' and payment_type = 'cash')
        / count(order_id) filter(where status = 'completed')::numeric as cash_payment_share,

    count(order_id) filter(where status = 'completed' and payment_type = 'card_online')
        / count(order_id) filter(where status = 'completed')::numeric as card_online_payment_share,

    count(order_id) filter(where status = 'completed' and payment_type = 'card_on_arrival')
        / count(order_id) filter(where status = 'completed')::numeric as card_on_arrival_payment_share
from
    data.order
group by day
order by day desc

Віддавайте перевагу

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
select
    cast(date_created as date) as day,


    -- ORDERS
    count(order_id) as order_count,
    count(order_id) filter(where status = 'completed') / count(order_id)::numeric as completed_order_share,
    count(order_id) filter(where status = 'completed') / count(order_id)::numeric as canceled_order_share,


    -- DELIVERIES
    count(order_id) filter(where status = 'completed' and delivery_type = 'novaposhta')
        / count(order_id) filter(where status = 'completed')::numeric as novaposhta_delivery_share,

    count(order_id) filter(where status = 'completed' and delivery_type = 'ukrposhta')
        / count(order_id) filter(where status = 'completed')::numeric as ukrposhta_delivery_share,

    count(order_id) filter(where status = 'completed' and delivery_type = 'pickup')
        / count(order_id) filter(where status = 'completed')::numeric as pickup_delivery_share,


    -- PAYMENTS
    count(order_id) filter(where status = 'completed' and payment_type = 'cash')
        / count(order_id) filter(where status = 'completed')::numeric as cash_payment_share,

    count(order_id) filter(where status = 'completed' and payment_type = 'card_online')
        / count(order_id) filter(where status = 'completed')::numeric as card_online_payment_share,

    count(order_id) filter(where status = 'completed' and payment_type = 'card_on_arrival')
        / count(order_id) filter(where status = 'completed')::numeric as card_on_arrival_payment_share
from
    data.order
group by day
order by day desc

8.2 Як перевіряти свій код на наявність логічних помилок.

Зазвичай логічні помилки виникають при неправильному використанні логічних операторів and і or при фільтрації або об'єднані даних, в результаті таких помилок трапляється втрата або дублікація записів, що призводить до некоректного результату запиту.

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

Якщо причина помилки не є очевидною, можна спробувати наступне:

  • Розкласти запит на декілька частин, після чого поступово поєднуючи їх перевіряти на якому з етапів з'являється помилка.
  • Перевірити коректність обраних типів об'єднань даних та їх умови, якщо вони є в запиті, можливо inner join потрібно замінити на left join або union на union all.
  • Перевірити фільтри вказані після оператору where, можливо там вказаний некоректний оператор порівняння, логічний оператор.
  • Так само перевірити фільтри агрегатних функцій, якщо вони є.

8.3 Що робити, якщо все забулося, де знайти інформацію, щоб все згадати.

Ресурси, які можуть бути корисними при згадуванні та практиці: