L11. Аналітичний запит у трьох системах

середнійспирається на модуль 19

Мета

Побачити на одних і тих самих даних, чим відрізняється рядкова база від двох колонкових і що в ClickHouse вирішує ключ ORDER BY. Після роботи ви переносите аналітичний запит між трьома діалектами, знаєте, де вони розходяться (часові пояси, NULL у JOIN, ділення DECIMAL), і вмієте за EXPLAIN та лічильниками прочитаного пояснити, чому одна система обробила звіт за 60 мс, а інша за пів секунди.

Завдання

Результат: каталог labs/l11-analytics/solution/ з дванадцятьма запитами (чотири питання у трьох системах) і схемою ClickHouse, плюс файл notes.md з поясненням. Заготовки лежать у starter/:

cp -r labs/l11-analytics/starter labs/l11-analytics/solution

Готово, коли ./labs/l11-analytics/check.sh виводить «усе гаразд», а notes.md відповідає на запитання з етапу 5. Чекер працює на розмірі medium (Датасет) і порівнює результати з еталонними.

Спільні правила для всіх питань:

Покупка — замовлення зі статусом, відмінним від cancelled і returned.
Час у таблицях зберігається в UTC, а день, місяць і межі періодів рахуються за київським часом (Europe/Kyiv). Кінець періоду виключний.
Виручка рядка замовлення — round(quantity * unit_price * (1 - discount_pct / 100), 2).
Відсотки — round(…, 2).

Результати зіставляються за ключем (перша колонка, а для четвертого питання дві перші), порядок рядків не важливий. Цілі числа мають збігатися точно. Числа з десятковою крапкою можуть відрізнятися до 0.01: системи по-різному округлюють половини, і вимагати збігу до останнього знака було б нечесно. Тому гроші й відсотки віддавайте з двома знаками, а не з одним.

Питання 1. Когорти повторних покупок

Покупців групують за місяцем першої покупки (когорта). Скільки з них купили ще раз у перший, другий і третій місяці після неї?

Колонка	Значення
`cohort_month`	перше число місяця першої покупки
`customers`	покупців у когорті
`m1`, `m2`, `m3`	різних покупців когорти, які мали покупку в місяці +1, +2, +3
`m1_pct`	`round(100 * m1 / customers, 2)`

Когорти останніх місяців мають неповні m1–m3, бо дані закінчуються 31 грудня 2025. Це нормально: рахуйте як є. На medium в результаті 24 рядки.

Питання 2. Воронка «перегляд → кошик → покупка» за категоріями

Подій у view_events і замовлень не зв’язує спільний сеанс, тож воронку рахуємо для пари «покупець — категорія» за 2025 рік (за київським часом) і лише для подій із відомим customer_id. Категорія — це products.category_id товару (листок дерева, не корінь).

Колонка	Значення
`category`	назва категорії, а для товарів без категорії рядок `(без категорії)`
`viewed`	пар із подією `view`
`carted`	пар із `view`, які мають і подію `add_to_cart`
`bought`	пар із `carted`, де покупець купив у 2025 році товар цієї категорії
`cart_pct`	`round(100 * carted / viewed, 2)`
`buy_pct`	`round(100 * bought / carted, 2)`, а коли `carted = 0` — `0`

На medium 68 рядків, зокрема рядок (без категорії).

Питання 3. Виручка з ковзним середнім

Виручка за днями жовтня–грудня 2025 (від 2025-10-01 до 2025-12-31 включно, 92 дні) і її середнє за сім днів.

Колонка	Значення
`day`	день за київським часом
`revenue`	сума виручки рядків покупок цього дня, `round(…, 2)`
`orders`	різних замовлень-покупок цього дня
`avg7`	середнє `revenue` за цей день і шість попередніх, `round(…, 2)`

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

Питання 4. Події за тиждень

Скільки переглядів і додавань у кошик було кожного дня тижня 24–30 листопада 2025 на кожному пристрої? Колонки: day, device, views (події view), carts (події add_to_cart; wishlist не рахується). У результаті 21 рядок.

ClickHouse: свої таблиці

У ClickHouse (база shop, користувач shop) ви самі створюєте й завантажуєте п’ять таблиць: categories, products, orders, order_items, view_events. Імена колонок як у CSV (потрібні лише ті, що використовують запити), рушій родини MergeTree, а ORDER BY кожної таблиці — ваше рішення, яке ви пояснюєте в notes.md. Схема лежить у solution/clickhouse/schema.sql. Для view_events чекер перевіряє наслідки вашого вибору, про це в розділі «Перевірка».

Чого робити не треба: змінювати check.sh, lib.sh, lib/ і expected/; перейменовувати таблиці й колонки; обмежувати дані (LIMIT, відбір рядків) під час завантаження.

Перед початком

Прочитайте модуль 19, особливо розділи про колонкове зберігання, ClickHouse і DuckDB. Потрібні Docker і bash (Git Bash на Windows, WSL, Linux, macOS); Node.js і Python на хості не потрібні. Датасет medium треба згенерувати й завантажити в PostgreSQL (setup/README.md, розділ 5):

./setup/generate-dataset.sh medium
./setup/load-dataset.sh medium --reset
docker compose --profile postgres --profile clickhouse up -d --wait

Чекер очікує саме той medium, який дає генератор із --seed 42 на Node.js 24; скрипт generate-dataset.sh подбає про це сам і за потреби візьме Node 24 з контейнера. Якщо проєкт compose має власну назву чи порти, передайте їх: COMPOSE_PROJECT_NAME=моя-назва ./labs/l11-analytics/check.sh. docker context ls має показувати локальний Docker.

DuckDB не потребує окремого профілю: образ duckdb/duckdb із фіксованим тегом читає CSV з dataset/medium напряму, таблиці orders, order_items та інші вже є в ньому як представлення:

./labs/l11-analytics/duckdb.sh -c "SELECT count(*) FROM orders"
./labs/l11-analytics/duckdb.sh labs/l11-analytics/solution/duckdb/01.sql

Етапи

PostgreSQL. Напишіть solution/pg/01.sql … 04.sql: по одному запиту SELECT (чи WITH … SELECT) у файлі. Запускайте їх у psql (docker compose exec postgres psql -U shop -d shop) і звіряйте з описом колонок. Для запиту 2 зніміть EXPLAIN (ANALYZE, BUFFERS) і запишіть час та кількість буферів: це 8 КіБ кожен.
DuckDB. Перенесіть ті самі чотири запити в solution/duckdb/. Діалект близький до PostgreSQL, але не той самий. Перевірте, що отримали ті самі числа, і запишіть, що довелося змінити. Зніміть EXPLAIN ANALYZE запиту 2.
ClickHouse: схема й завантаження. Створіть таблиці й завантажте дані зі stdin, як це робить модуль 19:
Terminal window
```
docker compose exec -T clickhouse clickhouse-client --user shop --password shop \
  --query "INSERT INTO shop.view_events SETTINGS date_time_input_format = 'best_effort', input_format_skip_unknown_fields = 1 FORMAT CSVWithNames" \
  < dataset/medium/view_events.csv
```
Прапорець -T потрібен, щоб compose не чекав термінала, а input_format_skip_unknown_fields дозволяє не створювати колонки, які вам не потрібні. Для view_events обміркуйте ORDER BY до створення таблиці: з нього залежить, чи відсікатиме запит за період гранули. Перевірте: SELECT count() FROM shop.view_events дорівнює кількості в PostgreSQL.
ClickHouse: запити. Напишіть solution/clickhouse/01.sql … 04.sql (звертайтеся до таблиць як shop.orders тощо). Для запиту 4 зніміть EXPLAIN indexes = 1 і подивіться рядок Granules. Порівняйте з таблицею, створеною з ORDER BY tuple(): чим вона відрізняється? Для запитів 1–3 запишіть rows_read і bytes_read із FORMAT JSON.
Нотатки. У solution/notes.md відповідайте коротко: (а) скільки часу й даних прочитала кожна система на запиті 2 і чому так; (б) який ORDER BY ви обрали для view_events й orders і які запити від цього виграють; (в) що довелося змінити, переносячи запити між діалектами (щонайменше три місця).
Здача. ./labs/l11-analytics/check.sh. Кожне «НІ» називає запит і розбіжність, а підказка вказує на типову пастку.

Перевірка

./labs/l11-analytics/check.sh              # розв'язок із labs/l11-analytics/solution/
./labs/l11-analytics/check.sh інший/каталог

Чекер нічого не змінює ні в PostgreSQL, ні в ClickHouse: запити студента виконуються в режимі лише читання (default_transaction_read_only, readonly = 2). Він перевіряє:

Результати. Усі дванадцять запитів дають результат, який збігається з еталонним (правила збігу вище).
Повноту завантаження. Кожна з п’яти таблиць shop.* у ClickHouse має стільки рядків, скільки в PostgreSQL.
Відсікання гранул. Чекер виконує пробний запит count() по view_events за тиждень 24–30 листопада і дивиться, скільки рядків прочитано. Це лічильник rows_read самого запиту: він не залежить від того, чим відсічено дані, ключем сортування чи партиціями. Таблиця має прочитати не більш як 20 % рядків. Еталонна схема читає 16 384 з 610 011 (2 %), таблиця з ORDER BY tuple() читає всі.
Читання колонок. Запит по одній колонці (event_type) має прочитати не більш як 10 % нестисненого розміру view_events. Еталон читає 2.3 %. Це перевіряє, що таблиця справді колонкова, а не зберігає рядок CSV одним полем, з якого запити розбирають значення.

Чому саме rows_read, а не EXPLAIN indexes = 1: вивід EXPLAIN залежить від версії й від того, чим відсікають, а лічильник показує те, що справді прочитано. EXPLAIN корисний вам, щоб побачити механізм; чекеру потрібен результат. Кожен запит чекера обмежений за часом, збій контейнера, psql чи DuckDB дає «НІ» з текстом помилки, а не мовчання.

Часті помилки

Місяць за UTC. Замовлення о 22:30 UTC 31 січня в Києві вже лютневе. Когорту, пораховану за UTC, чекер відхилить: значення в рядках будуть іншими. Так само для меж 2025 року й тижня в питаннях 2 і 4.

NULL у JOIN. Товари без категорії мають category_id IS NULL, а NULL = NULL невідомо. У PostgreSQL і DuckDB допоможе IS NOT DISTINCT FROM. У ClickHouse NULL у ключі JOIN теж не збігається, а LEFT JOIN без join_use_nulls = 1 для незнайдених рядків повертає значення за замовчуванням (0), а не NULL: умова IS NOT NULL стає завжди істинною.

Ділення DECIMAL у DuckDB. discount_pct / 100 для DECIMAL дає DOUBLE, і сума рядків відхиляється від PostgreSQL у копійках. Множте на 0.01 або порівнюйте тип через typeof().

Ділення на нуль. У PostgreSQL x / 0 — помилка, у DuckDB NULL, у ClickHouse inf або nan. Для buy_pct умову carted = 0 обробляйте явно.

Псевдонім збігається з колонкою у ClickHouse. countIf(viewed) AS viewed призводить до помилки «aggregate function found inside another aggregate function»: псевдонім підставляється всюди, зокрема в сам агрегат. Називайте результат інакше.

Екранування у виводі. Клієнт ClickHouse у форматі TSV пише М\'які іграшки. Чекер читає результат у форматі TSVRaw; цей формат підійде й вам, коли порівнюєте вивід систем.

ORDER BY лише за ідентифікатором. Для view_events він дає порядок вставки й жодного відсікання за часом. Ключ відсікає, коли умова запиту стоїть у ньому першою.

Docker дивиться на віддалену машину. docker context use desktop-linux (Docker Desktop) або default (Linux).

Далі, якщо цікаво

Запустіть ті самі запити на full (./setup/generate-dataset.sh full) і порівняйте: у модулі 19 той самий звіт на 8 мільйонах подій (full) у PostgreSQL іде секундами, а в ClickHouse долями секунди. Збережіть view_events у Parquet (COPY … TO 'файл.parquet' у DuckDB) і запитайте його без завантаження. Додайте матеріалізоване представлення ClickHouse з подіями за днями й подивіться, скільки байтів тепер читає питання 4.