SQL: запити

Навіщо це

Аналітик просить два звіти по «Крамниці»: скільки грошей принесли замовлення, за які платили, і які категорії товарів порожні. Запити виконуються без помилок і повертають правдоподібні результати.

Перший зʼєднує orders з payments і рахує sum(total_amount): виходить 91 714 019.40 грн, а сума по самій orders — 90 805 918.06. Другий шукає категорії за умовою category_id NOT IN (SELECT category_id FROM products) і повертає порожній результат, тож звіт стверджує, що порожніх категорій немає. Насправді їх 38.

Синтаксис тут ні до чого. У першому запиті замовлення з двома спробами оплати потрапили в суму двічі, а 277 замовлень без платежів випали зовсім. Дві помилки в різні боки дали число, схоже на правду. У другому NULL у products.category_id змінив відповідь підзапиту. Обидва випадки розібрано нижче, але спершу треба побачити, як база виконує запит.

Передумови. Тризначна логіка NULL і реляційна алгебра — з модуля 2, тут вони вважаються відомими. Схема датасету «Крамниця» описана на сторінці «Датасет», усі приклади працюють на ньому. Числа в модулі виміряно на розмірі small, див. Датасет.

Логічний порядок виконання

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

Запит читається за номерами. Псевдоніми з SELECT з'являються лише на кроці 5.

Кожен крок працює з результатом попереднього. WHERE відкидає рядки після FROM, HAVING відкидає групи після GROUP BY, а SELECT лише тепер обчислює вирази й дає їм імена. DISTINCT діє одразу за ним. Звідси найчастіша помилка: псевдонім, оголошений у SELECT, у WHERE не існує.

SELECT order_id, line_no,
       round(quantity * unit_price * (1 - discount_pct / 100), 2) AS line_total
FROM order_items
WHERE line_total > 20000;

ERROR:  column "line_total" does not exist
LINE 4: WHERE line_total > 20000;
              ^

База каже правду: на кроці 2 є лише колонки таблиць. Вираз можна повторити у WHERE, але чистіше обчислити його в підзапиті й фільтрувати за псевдонімом. Змініть поріг або додайте умову на line_no:

СпробуйPostgreSQLCtrl+Enter — виконати

 order_id | line_no | line_total
----------+---------+------------
    13888 |       3 |  667590.00
    18292 |       1 |  339245.00
     2925 |       3 |  284935.50

В ORDER BY псевдонім доступний, бо сортування стоїть після SELECT. PostgreSQL дозволяє його й у GROUP BY, хоча стандарт цього не вимагає. У HAVING псевдонім не працює.

Той самий порядок розводить WHERE і HAVING. Умова на рядок іде у WHERE і спрацьовує до групування. Умова на агрегат, як-от count(*) > 1, можлива лише в HAVING, бо до кроку 3 груп ще немає. Віконні функції рахуються на кроці 5, тож у WHERE їх теж не поставити: щоб відфільтрувати за місцем у рейтингу, запит обгортають.

Порядок логічний, а не фізичний. Планувальник запитів виконує кроки як завгодно, доки результат той самий (модуль 9).

Агрегати, NULL і групи

Агрегатні функції згортають набір рядків до одного значення й майже всі ігнорують NULL. Виняток — count(*): він рахує рядки, а не значення. Це видно на продавцях, у десятьох з яких ще немає оцінки:

SELECT count(*) AS sellers,
       count(rating_cached) AS rated,
       round(avg(rating_cached), 2) AS avg_rated,
       round(sum(rating_cached) / count(*), 2) AS sum_over_all
FROM sellers;

 sellers | rated | avg_rated | sum_over_all
---------+-------+-----------+--------------
     120 |   110 |      3.81 |         3.49

avg ділить на 110 продавців, у яких оцінка є. Ділення суми на всіх 120 дало б 3.49, але це вже припущення, що відсутня оцінка дорівнює нулю. Що означає «немає значення»: нуль, «невідомо» чи «не стосується», — запит за вас не вирішить. Якщо WHERE не лишив жодного рядка, агрегат без GROUP BY усе одно поверне один рядок: count дасть 0, а sum і avg — NULL.

GROUP BY складає всі NULL в одну групу, хоча NULL = NULL невідомо. Групування і DISTINCT вважають NULL однаковими, порівняння ні.

SELECT promo_code, count(*) AS uses
FROM orders
GROUP BY promo_code
HAVING count(*) >= 300
ORDER BY uses DESC;

 promo_code | uses
------------+-------
            | 18335
 WELCOME10  |  1169
 NY20       |   502
 BLACK25    |   381
 VIP5       |   371
 FRIEND10   |   340
 APP15      |   324

Перший рядок — замовлення без промокоду (psql показує NULL порожнім полем). Якщо це не промокод, такі рядки відкидають у WHERE promo_code IS NOT NULL до групування. HAVING тут не замінити на WHERE, бо count(*) існує лише після групування.

JOIN: пари рядків із двох таблиць

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

Два товари з категорією 4, товар 12 без категорії і порожня категорія 5. Виділено рядки без пари.

INNER JOIN лишає тільки пари. LEFT JOIN додає рядки лівої таблиці без пари, а колонки правої заповнює NULL. RIGHT JOIN дзеркальний, його замінюють переставленням таблиць. FULL JOIN зберігає рядки без пари з обох боків. CROSS JOIN не має умови й повертає всі пари: 3 × 2 = 6 рядків на рисунку, а для orders і customers було б понад сто мільйонів.

Товар 12 має category_id = NULL. Порівняння NULL = 4 дає «невідомо», тож пари він не знайде ніде, навіть серед інших NULL: NULL = NULL теж невідомо. Зберігають його лише LEFT і FULL JOIN. Запис USING (customer_id) скорочує ON a.customer_id = b.customer_id.

Зв’язок «один до багатьох» має наслідок, який зіпсував перший звіт. Лівий рядок повторюється стільки разів, скільки правих йому відповідає. Це називають розмноженням рядків (fan-out). Замовлення 198 має дві спроби оплати:

SELECT o.order_id, o.total_amount, p.payment_id, p.status
FROM orders AS o
JOIN payments AS p ON p.order_id = o.order_id
WHERE o.order_id = 198;

 order_id | total_amount | payment_id |  status
----------+--------------+------------+----------
      198 |       269.00 |        196 | failed
      198 |       269.00 |        197 | refunded

Сума 269.00 стоїть у двох рядках, і sum додасть її двічі. Таких замовлень 405, вони дають зайві 1 943 205.84. Водночас JOIN відкинув 277 замовлень без платежів на 1 035 104.50. Різниця, 908 101.34, і є перевищенням у першому звіті.

Виправляють це, зводячи payments до одного рядка на замовлення ще до JOIN. Якщо платежі потрібні лише для перевірки «чи є», пишуть EXISTS (нижче). DISTINCT не рятує: sum(DISTINCT total_amount) прибере й різні замовлення з однаковою сумою.

Друга пастка — умови в LEFT JOIN. Скільки покупців зі Львова й скасованих замовлень потрапить у результат?

СпробуйPostgreSQLCtrl+Enter — виконати

 customers | cancelled_orders
-----------+------------------
        31 |               48

У Львові 125 покупців, а в результаті 31. Перенесіть умову o.status = 'cancelled' із WHERE в ON: покупців стане 125, скасованих замовлень лишиться 48.

ON вирішує, які праві рядки підходять лівому, а WHERE відкидає вже готові рядки. Для покупця без скасованих замовлень LEFT JOIN додав рядок з NULL у o.status, а NULL = 'cancelled' невідомо, тож WHERE його викинув. Запит виглядає як LEFT JOIN, а діє як INNER JOIN. Умови на правий бік, які не мають вбивати лівий рядок, ставлять в ON.

Підзапити

Підзапит (subquery) стоїть усередині іншого запиту. Скалярний повертає одне значення, підзапит-список стоїть праворуч від IN, підзапит-таблиця стоїть у FROM і має псевдонім. Корельований підзапит посилається на колонки зовнішнього запиту, тож логічно виконується для кожного його рядка окремо. Замовлення, що перевищують середню суму замовлень того самого покупця, найбільші три:

SELECT o.order_id, o.customer_id, o.total_amount
FROM orders AS o
WHERE o.total_amount > (
  SELECT avg(x.total_amount) FROM orders AS x WHERE x.customer_id = o.customer_id
)
ORDER BY o.total_amount DESC, o.order_id
LIMIT 3;

 order_id | customer_id | total_amount
----------+-------------+--------------
    13888 |        3750 |    669329.00
    18292 |         244 |    339245.00
     2925 |        3331 |    287048.70

Корельований підзапит з агрегатом PostgreSQL 18 на JOIN не переписує: у плані він лишається як SubPlan і виконується по разу на кожен рядок зовнішньої таблиці. Корельовані EXISTS і IN планувальник зазвичай перетворює на JOIN (модуль 9).

EXISTS, IN і пастка NOT IN

EXISTS (підзапит) повертає true, щойно підзапит знайшов бодай один рядок, і ніколи не повертає NULL. Тож EXISTS і NOT EXISTS чесно ділять рядки на два набори. Для JOIN це semi-join і anti-join. На відміну від JOIN, зовнішній рядок не розмножується, скільки б пар не знайшлося.

IN і NOT IN влаштовані інакше. x NOT IN (2, NULL) розгортається в x <> 2 AND x <> NULL: друга умова невідома, тож весь вираз не стає true ніколи, а WHERE пропускає лише true. Отже, одне NULL у списку підзапиту робить NOT IN порожнім для кожного рядка. У датасеті таке NULL є: у 44 товарів немає категорії.

СпробуйPostgreSQLCtrl+Enter — виконати

 not_in | not_exists
--------+------------
      0 |         38

Виправити можна трьома способами: NOT EXISTS, LEFT JOIN ... WHERE p.product_id IS NULL або NOT IN з WHERE category_id IS NOT NULL у підзапиті. По колонці з NOT NULL NOT IN безпечний, але обмеження можуть зникнути після міграції, і запит мовчки змінить відповідь. Тому для «немає жодного» віддають перевагу NOT EXISTS.

CTE і рекурсія

CTE (common table expression, WITH name AS (...)) дає підзапиту ім’я й виносить його перед основним запитом, тож запит читається згори вниз. До PostgreSQL 12 CTE завжди обчислювався окремо. Відтоді CTE, на який посилаються один раз, вбудовується як звичайний підзапит, а MATERIALIZED і NOT MATERIALIZED задають поведінку вручну.

Рекурсії без WITH немає. Категорії утворюють дерево: parent_id указує на батька, у коренів він NULL, глибина до чотирьох рівнів. «Усі нащадки вузла» звичайним запитом не отримати: довелося б зʼєднувати categories із собою стільки разів, яка глибина дерева.

Рекурсивний CTE складається з двох частин через UNION ALL: початкової, що дає стартові рядки, і рекурсивної, яка бере рядки попереднього кроку й знаходить наступні. Крок повторюється, доки не дасть жодного рядка. Для піддерева «Смартфони і аксесуари» (id 2) це сам вузол, потім його діти 3, 7 і 8, потім діти дітей 4, 5 і 6. Четвертий крок порожній, і рекурсія зупиняється.

СпробуйPostgreSQLCtrl+Enter — виконати

              category              | depth | products
------------------------------------+-------+----------
 Смартфони і аксесуари              |     1 |        0
     Смартфони                      |     2 |        0
         Бюджетні смартфони         |     3 |        3
         Смартфони середнього класу |     3 |        6
         Флагманські смартфони      |     3 |        1
     Чохли і захисне скло           |     2 |       28
     Зарядні пристрої й павербанки  |     2 |       20

Товари лежать у листках дерева, внутрішні вузли порожні, тож підрахунок по кореню робиться по всьому піддереву. SEARCH DEPTH FIRST (PostgreSQL 14 і новіші) додає службову колонку для обходу вглиб. Без неї порядок рядків довільний.

Якщо в даних є цикл, UNION ALL ходитиме по колу, доки не вичерпає ресурси. Від цього захищає CYCLE (теж із 14-ї версії), лічильник глибини в умові або UNION, що відкидає вже бачений рядок, якщо рядки збігаються цілком.

Віконні функції

Агрегат із GROUP BY згортає групу в один рядок. Віконна функція рахує те саме, але рядки лишає: кожен отримує значення, обчислене по своєму «вікну» пов’язаних рядків. Вікно задає OVER (PARTITION BY ... ORDER BY ...): PARTITION BY ділить рядки на групи, ORDER BY впорядковує їх усередині групи.

Ранжують три функції. row_number() нумерує без повторів, rank() віддає рівним рядкам одне місце й лишає пропуск після них, dense_rank() дає одне місце без пропуску. Два найбільші міста кожної з трьох областей:

СпробуйPostgreSQLCtrl+Enter — виконати

       region       | settlement | population | place
--------------------+------------+------------+-------
 Львівська область  | Львів      |     717273 |     1
 Львівська область  | Дрогобич   |      73682 |     2
 Одеська область    | Одеса      |    1010537 |     1
 Одеська область    | Ізмаїл     |      69932 |     2
 Харківська область | Харків     |    1421125 |     1
 Харківська область | Лозова     |      54026 |     2

Запит обгорнуто підзапитом, бо place <= 2 не можна поставити у WHERE того самого рівня: вікна рахуються на кроці 5. Якби два міста ділили друге місце, rank() показав би обидва, а row_number() розвів би їх довільно й показав одне.

Наростаючий підсумок і порівняння з попереднім рядком дають sum() OVER і lag(). Замовлення наприкінці листопада 2025, день рахується за київським часом:

SELECT day, orders,
       sum(orders) OVER (ORDER BY day) AS running,
       orders - lag(orders) OVER (ORDER BY day) AS vs_prev
FROM (
  SELECT (placed_at AT TIME ZONE 'Europe/Kyiv')::date AS day, count(*) AS orders
  FROM orders
  WHERE (placed_at AT TIME ZONE 'Europe/Kyiv')::date BETWEEN DATE '2025-11-26' AND DATE '2025-11-30'
  GROUP BY 1
) AS daily
ORDER BY day;

    day     | orders | running | vs_prev
------------+--------+---------+---------
 2025-11-26 |     79 |      79 |
 2025-11-27 |    103 |     182 |      24
 2025-11-28 |    244 |     426 |     141
 2025-11-29 |    160 |     586 |     -84
 2025-11-30 |    171 |     757 |      11

lag(orders) бере значення з попереднього рядка вікна, для першого воно NULL. Рамка вікна за замовчуванням тягнеться до поточного рядка разом з усіма, що мають той самий ключ ORDER BY, тому такі рядки отримають однаковий підсумок. Строго по рядках рахує ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

LATERAL: топ-N на групу

Підзапит у FROM зазвичай не бачить таблиць, що стоять лівіше. LATERAL знімає це обмеження: підзапит виконується для кожного лівого рядка й може мати власний LIMIT. Так розв’язується «топ-N на групу»: три найдорожчі товари кожного з двох перших продавців.

SELECT s.seller_id, top.title, top.price
FROM sellers AS s
CROSS JOIN LATERAL (
  SELECT p.title, p.price
  FROM products AS p
  WHERE p.seller_id = s.seller_id
  ORDER BY p.price DESC
  LIMIT 3
) AS top
WHERE s.seller_id <= 2
ORDER BY s.seller_id, top.price DESC;

 seller_id |                title                 |  price
-----------+--------------------------------------+---------
         1 | Шампунь Bilyi Lotos                  |  565.00
         1 | Шампунь                              |  389.00
         1 | Магній B6 Kryshtal                   |  379.00
         2 | Сукня Kalyna Wear Home, жовта        | 4379.00
         2 | Кросівки Urban Hutir Home 24, рожеві | 4249.00
         2 | Сукня Family, чорна                  | 3519.00

CROSS JOIN LATERAL відкидає продавця без товарів. Щоб лишити його, пишуть LEFT JOIN LATERAL (...) AS top ON true. З індексом по (seller_id, price) три рядки на продавця читаються без ранжування всіх товарів (модуль 8).

Як це насправді

Запити вище виконуються й у справжньому PostgreSQL 18. Профіль postgres піднімає його з тим самим датасетом:

docker compose --profile postgres up -d --wait
docker compose exec postgres psql -U shop -d shop

psql має власні команди зі зворотною скісною рискою. \d categories показує колонки, типи, обмеження й ключі таблиці (вивід скорочено):

               Table "public.categories"
   Column    |  Type   | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
 category_id | integer |           | not null |
 parent_id   | integer |           |          |
 name        | text    |           | not null |
 slug        | text    |           | not null |
 is_active   | boolean |           | not null | true
Indexes:
    "categories_pkey" PRIMARY KEY, btree (category_id)
...

Колонка Nullable показує, де може з’явитися NULL. На неї варто дивитися перед NOT IN чи LEFT JOIN. Ще корисні \dt і \x.

Перед запитом можна дописати EXPLAIN: база його не виконає, а покаже план. Плани читає модуль 9, тут лише анонс:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on orders  (cost=0.00..547.38 rows=6 width=99)
   Filter: (customer_id = 42)

Seq Scan означає, що прочитано всю таблицю: індексу по customer_id у датасеті навмисно немає. cost виміряно в умовних одиницях планувальника, а rows — це його оцінка, а не результат.

Плани двох запитів із розділу про NOT IN уже відрізняються. NOT EXISTS стає anti-join (Hash Right Anti Join), а NOT IN лишається фільтром із підзапитом (hashed SubPlan), бо NULL не дає перетворити його на JOIN. Пам’ятайте, що EXPLAIN ANALYZE запит виконує, і для UPDATE чи DELETE це означає зміну даних.

Типові помилки розуміння

«Запит виконується в тому порядку, в якому написаний». Першим виконується FROM, а SELECT лише п’ятим, тому псевдонім недоступний у WHERE.

«NOT IN і NOT EXISTS — те саме». Вони збігаються, поки в підзапиті немає NULL. Одне NULL робить результат NOT IN порожнім без жодної помилки.

«DISTINCT лікує дублікати після JOIN». Він лише ховає розмноження рядків, а sum(DISTINCT x) ще й прибирає справжні однакові суми. Причину усувають до JOIN.

«Віконна функція працює як GROUP BY». GROUP BY повертає один рядок на групу, віконна функція лишає всі рядки й додає до кожного значення по вікну. Тому rank() не відфільтрувати у WHERE: значення з’являється вже після нього.

Перевір себе

1. Що поверне запит SELECT count(*) - count(birth_date) FROM customers у датасеті small?0, бо count(*) і count(birth_date) рахують те саме1200: кількість покупців без дати народження3800: кількість покупців із датою народження5000: кількість усіх покупців

2. Яке продовження запиту SELECT category_id знаходить 38 категорій без жодного товару, хоча в products.category_id є NULL?FROM categories WHERE category_id NOT IN (SELECT category_id FROM products)FROM categories LEFT JOIN products p USING (category_id) WHERE p.category_id = NULLFROM categories c WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category_id = c.category_id)FROM categories JOIN products p USING (category_id) WHERE p.product_id IS NULL

3. Виручку порахували як sum(o.total_amount) по orders o JOIN payments p. Число близьке до sum(total_amount) по orders, але не рівне йому. Що сталося?sum пропустив рядки з NULL у paymentsJOIN змінив тип total_amount на число з плаваючою комоюДо суми потрапили самі платежі payments.amount замість сум замовленьЗамовлення з кількома платежами враховано кілька разів, а без платежів — жодного

4. Три товари продано по 25 штук, перед ними товари з 93 і 33 штуками, а далі товар із 20. Які місця для нього дадуть rank() і dense_rank() за спаданням кількості?rank() = 6, dense_rank() = 4rank() = 4, dense_rank() = 4rank() = 6, dense_rank() = 6rank() = 4, dense_rank() = 6

5. Запит: SELECT c.customer_id, count(o.order_id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'cancelled' GROUP BY c.customer_id. Кого не буде в результаті?Лише покупців, які не зробили жодного замовленняУсіх покупців без скасованих замовлень, зокрема й тих, хто не замовляв нічогоНікого: LEFT JOIN зберігає всіх покупцівПокупців, у яких є скасовані замовлення

6. Рекурсивний CTE з UNION ALL обходить categories, а в даних з’явився цикл: А — батько Б, Б — батько А. Що буде?PostgreSQL розпізнає цикл і завершить запитЗапит поверне порожній результатЗапит працюватиме, доки не вичерпає ресурсиЗапит завершиться помилкою синтаксису

Лабораторна

L1. Запити до датасету: 29 бізнесових питань від фільтра до LATERAL з автоперевіркою. Частина з них побудована на пастках цього модуля.

Джерела

PostgreSQL Documentation, розділ 7 «Queries»: JOIN, LATERAL, WITH, SEARCH і CYCLE.
PostgreSQL Documentation, вирази-підзапити.
PostgreSQL Documentation, віконні функції і їхні виклики.
PostgreSQL 12 Release Notes: вбудовування CTE.
Itzik Ben-Gan, T-SQL Fundamentals: логічна обробка запиту.