vyvchy
    Теми розділу

    05 · Бази даних і SQL для QA

    Агрегація: GROUP BY і HAVING

    Зміст

    Майже кожна звірка QA рано чи пізно впирається в питання «а скільки їх?». Скільки замовлень у користувача, скільки рядків із таким статусом, чи збігається кількість у списку на екрані з кількістю в базі, чи немає в таблиці двох акаунтів на один email. На всі ці питання відповідає агрегація — механізм, який згортає багато рядків в один підсумковий.

    Тема має високу вагу на співбесідах з двох причин. По-перше, вона всюди в реальній роботі: звіти, дашборди, метрики, пошук дублікатів — усе це агрегати. По-друге, тут легко «попливти»: різниця між COUNT(*) і COUNT(col), правило GROUP BY, вибір між WHERE і HAVING — це саме ті деталі, на яких видно, чи людина розуміє, як база обчислює запит, чи вгадує синтаксис. Спершу розберемо самі агрегатні функції, потім групування, потім логічний порядок виконання запиту — ключ, який пояснює половину «дивних» помилок, — і завершимо двома прикладними сюжетами: пошуком дублікатів і агрегацією поверх JOIN.

    Агрегатні функції: COUNT, SUM, AVG, MIN, MAX

    Агрегатна функція (aggregate function) бере набір рядків і повертає одне значення. П'ять базових:

    ФункціяЩо повертає
    COUNTкількість
    SUMсуму числових значень
    AVGсереднє арифметичне
    MINнайменше значення
    MAXнайбільше значення

    MIN і MAX працюють не лише з числами: для рядків це алфавітний порядок, для дат — хронологічний, тож MAX(created_at) — це «остання за часом». Без GROUP BY агрегат згортає всю таблицю в один рядок:

    SELECT COUNT(*), SUM(amount), AVG(amount), MIN(amount), MAX(amount)
FROM orders;

    Головна деталь, яку треба засвоїти раз і назавжди: агрегати ігнорують NULL. SUM, AVG, MIN, MAX і COUNT(col) пропускають рядки, де значення — NULL, ніби їх немає. Це логічне продовження того, що NULL — це «невідомо» (докладно — у главі про NULL): невідоме не можна ні додати, ні усереднити.

    Найпідступніший наслідок — AVG. Він ділить суму на кількість не-NULL значень, а не на кількість рядків:

    -- Колонка discount: значення 10, 20 і NULL (три рядки)
SELECT AVG(discount) FROM orders;   -- 15, а не 10

    Інтуїція «третій рядок без знижки, отже знижка нуль, середнє = 30/3 = 10» — хибна. База бачить два відомих значення й одне невідоме, тож рахує 30 / 2 = 15. Якщо за бізнес-логікою відсутня знижка — це саме нуль, NULL треба замінити явно: AVG(COALESCE(discount, 0)). Ця різниця — типове джерело розбіжності «середнє в SQL не збігається із середнім в UI».

    Друга пастка тієї ж родини — агрегат поверх порожнього набору. COUNT над нулем рядків поверне 0, а от SUM, AVG, MIN, MAX повернуть NULL, а не нуль:

    SELECT SUM(amount) FROM orders WHERE customer_id = 999;  -- немає таких → NULL, не 0

    Тому у звітах суму часто загортають у COALESCE(SUM(amount), 0), щоб порожній результат показувався як нуль, а не як пусто.

    COUNT(*) vs COUNT(col) vs COUNT(DISTINCT)

    Три форми COUNT рахують різні речі, і плутанина між ними — класика і в багах, і на співбесідах.

    ФормаЩо рахує
    COUNT(*)усі рядки, разом із тими, де скрізь NULL
    COUNT(col)рядки, де col не NULL
    COUNT(DISTINCT col)різні не-NULL значення col

    Розглянемо таблицю users із 10 рядків, де в колонці phone заповнено 7, а 3 — NULL, і серед заповнених два номери однакові:

    SELECT
  COUNT(*)               AS rows_total,      -- 10: усі рядки
  COUNT(phone)           AS phones_filled,   -- 7: не-NULL значення
  COUNT(DISTINCT phone)  AS phones_unique;   -- 6: унікальні номери

    COUNT(*) — це «скільки рядків узагалі»; NULL його не обходить, бо він рахує рядки, а не значення. COUNT(phone) — «у скількох є телефон». COUNT(DISTINCT phone) — «скільки різних телефонів». Практична цінність для QA: коли кількість у SQL не збігається з UI, перше питання — яку саме кількість рахує кожна сторона. UI показує «7 користувачів з телефоном», а ти рахуєш COUNT(*) і отримуєш 10 — розбіжність не в даних, а в тому, що ти рахуєш не те.

    COUNT(1) — те саме, що COUNT(*): рахує рядки. Розмови «COUNT(1) швидший за COUNT(*)» — міф; сучасні оптимізатори обробляють їх однаково.

    GROUP BY: одне значення на групу

    Агрегат над усією таблицею — це один підсумок. Але зазвичай потрібен підсумок у розрізі: скільки замовлень у кожного користувача, сума по кожній валюті. Для цього є GROUP BY: він розбиває рядки на групи за однаковим значенням ключа, і агрегат рахується окремо в кожній групі. На виході — по одному рядку на групу.

    SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status;

    orders: багато рядків

    GROUP BY status

    група 'new'

    група 'paid'

    група 'cancelled'

    COUNT(*) → 1 рядок

    COUNT(*) → 1 рядок

    COUNT(*) → 1 рядок

    orders: багато рядків

    GROUP BY status

    група 'new'

    група 'paid'

    група 'cancelled'

    COUNT(*) → 1 рядок

    COUNT(*) → 1 рядок

    COUNT(*) → 1 рядок

    Групувати можна за кількома колонками одразу — тоді група утворюється на кожну унікальну комбінацію значень:

    SELECT status, currency, COUNT(*), SUM(amount)
FROM orders
GROUP BY status, currency;

    Тепер головне правило, яке ламає більшість перших запитів із GROUP BY:

    Кожна колонка у SELECT має бути або в GROUP BY, або всередині агрегатної функції.

    Причина — механічна, а не формальна. Один рядок на виході відповідає цілій групі. Якщо ти просиш колонку, якої немає в ключі групування й немає під агрегатом, база не знає, яке з багатьох значень групи показати:

    -- Помилка: email не в GROUP BY і не під агрегатом
SELECT status, email, COUNT(*)
FROM users
GROUP BY status;
-- У групі 'active' сотні різних email — котрий вивести?

    PostgreSQL на такий запит чесно віддасть помилку column "users.email" must appear in the GROUP BY clause or be used in an aggregate function. І тут — важлива пастка діалектів: MySQL історично цього не забороняв і мовчки повертав довільне значення з групи. Починаючи з версії 5.7.5 MySQL за замовчуванням вмикає режим ONLY_FULL_GROUP_BY, який приводить поведінку до стандарту, але на старих базах або з вимкненим режимом ти можеш отримати запит, що «працює» й тихо бреше — показує email якогось випадкового рядка групи. Якщо бачиш такий SELECT — це не «коротший стиль», це потенційно неправильний звіт.

    Виняток, який варто знати: якщо в GROUP BY є первинний ключ таблиці, решту її колонок можна вибирати без агрегату — вони функціонально залежні від ключа, тож двозначності немає. І PostgreSQL, і MySQL з ONLY_FULL_GROUP_BY таке розпізнають: SELECT c.id, c.name, COUNT(*) … GROUP BY c.id — легальний запит. Утім, переносніше й читабельніше явно перелічити колонки в GROUP BY.

    Ще дрібниця, яка витікає з семантики NULL: у GROUP BY усі NULL потрапляють в одну групу. Попри те що NULL = NULL дає UNKNOWN, групування використовує не рівність, а «не-різність», тож рядки з NULL-ключем збираються разом в один рядок результату.

    HAVING проти WHERE

    Коли треба відфільтрувати не рядки, а групи — наприклад, «покажи лише статуси, яких понад 100», — постає питання, куди писати умову. Відповідь: WHERE фільтрує рядки до групування, HAVING фільтрує групи після агрегації.

    SELECT customer_id, COUNT(*) AS orders_cnt
FROM orders
WHERE status = 'paid'        -- відсіює рядки ДО групування
GROUP BY customer_id
HAVING COUNT(*) > 5;         -- відсіює групи ПІСЛЯ підрахунку

    Цей запит читається так: «серед оплачених замовлень знайди клієнтів, у яких таких понад п'ять». WHERE прибирає неоплачені рядки ще до того, як утворилися групи; HAVING працює вже над готовими групами й може посилатися на агрегат.

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

    -- Помилка: агрегат у WHERE
SELECT customer_id FROM orders WHERE COUNT(*) > 5 GROUP BY customer_id;
-- COUNT(*) на момент WHERE ще не існує — групи не створені

    На момент, коли працює WHERE, груп ще немає, отже й COUNT(*) рахувати нема над чим. Умову на агрегат приймає лише HAVING. Дзеркально: умову на окремий рядок (status = 'paid') технічно можна написати й у HAVING, але це помилка за змістом — тоді база спершу згрупує зайві рядки й лише потім їх відкине. Правило просте:

    WHEREHAVING
    Коли працюєдо GROUP BYпісля GROUP BY
    Над чимнад окремими рядкаминад групами
    Можна агрегатнітак
    Типова умоваstatus = 'paid'COUNT(*) > 5

    Практичний наслідок для швидкодії: умову, яку можна поставити у WHERE, треба ставити у WHERE — так база відкидає рядки раніше й групує менше. Фільтрувати у HAVING те, що стосується окремого рядка, — марна робота.

    (HAVING можна вжити й без GROUP BY: тоді вся таблиця — одна група, і SELECT COUNT(*) FROM orders HAVING COUNT(*) > 1000 поверне рядок лише якщо замовлень понад тисячу.)

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

    Ми пишемо запит у порядку SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY, але база обчислює його в іншому порядку. Розуміння цього логічного порядку (logical query processing order) — той самий ключ, що пояснює і чому агрегат заборонений у WHERE, і чому аліас із SELECT не завжди видно в інших частинах запиту.

    FROM / JOIN — зібрати рядки з таблиць

    WHERE — відсіяти окремі рядки

    GROUP BY — розбити на групи

    HAVING — відсіяти групи

    SELECT — обчислити вирази й аліаси

    DISTINCT — прибрати дублікати

    ORDER BY — відсортувати

    LIMIT / OFFSET — обрізати

    FROM / JOIN — зібрати рядки з таблиць

    WHERE — відсіяти окремі рядки

    GROUP BY — розбити на групи

    HAVING — відсіяти групи

    SELECT — обчислити вирази й аліаси

    DISTINCT — прибрати дублікати

    ORDER BY — відсортувати

    LIMIT / OFFSET — обрізати

    Це логічний порядок: він описує, який результат має вийти, а не те, як фізично працює двигун — оптимізатор вільний переставляти кроки, доки відповідь така сама. Але саме з цієї послідовності випливають правила, які інакше здаються сваволею:

    • Агрегат не можна в WHERE, бо WHERE (крок 2) відпрацьовує раніше за GROUP BY (крок 3) — груп, а отже й COUNT(*), ще не існує.
    • Аліас із SELECT не видно у WHERE, GROUP BY і HAVING, бо SELECT (крок 5) обчислюється пізніше. WHERE cnt > 5, де cnt — аліас COUNT(*), у стандарті не спрацює.
    • Аліас видно в ORDER BY, бо сортування (крок 7) іде після SELECT. ORDER BY cnt DESC — коректно.

    Останні два пункти мають нюанс діалектів: MySQL і PostgreSQL як розширення дозволяють аліас у GROUP BY (а MySQL — і в HAVING), але переносний, гарантований стандартом варіант — аліас лише в ORDER BY. Якщо запит із аліасом у GROUP BY «не завівся» на іншій СУБД — причина саме тут.

    Пошук дублікатів через HAVING COUNT(*) > 1

    Це, мабуть, найчастіший практичний прийом агрегації в роботі QA. Питання «чи немає в таблиці двох записів там, де має бути один» розв'язується одним патерном: згрупувати за колонкою, яка мусить бути унікальною, і лишити групи, де рядків більше одного.

    -- Чи є два акаунти на один email?
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

    Порожній результат — унікальність тримається; будь-які рядки — дублікати, з якими треба розбиратися. Це найшвидший спосіб перевірити те, що начебто гарантує обмеження UNIQUE, але могло зламатися при міграції даних, імпорті чи гонці на рівні застосунку (докладніше про перевірку цілісності — у главі про тестування БД).

    Дублікат «за комбінацією полів» шукається так само — просто ключем стає кілька колонок:

    -- Два бронювання на той самий номер і ту саму дату
SELECT room_id, booking_date, COUNT(*)
FROM bookings
GROUP BY room_id, booking_date
HAVING COUNT(*) > 1;

    Один нюанс, який відрізняє впевнену відповідь на співбесіді: цей запит показує, які значення задубльовані й скільки разів, але не самі рядки-дублікати (з їхніми id). Щоб дістати конкретні рядки — наприклад, аби вирішити, який лишити, — потрібен підзапит або віконна функція ROW_NUMBER; це вже територія глави про підзапити й CTE і глави про віконні функції.

    Агрегація поверх JOIN

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

    Уяви customers і orders у зв'язку 1:N. Якщо в клієнта 3 замовлення, після JOIN рядок цього клієнта повторюється тричі. Тепер небезпечні запити:

    -- Скільки клієнтів зробили замовлення?
SELECT COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.id;
-- НЕ кількість клієнтів, а кількість замовлень: рядки розмножені JOIN-ом

    COUNT(*) тут рахує рядки після з'єднання, тобто замовлення, а не клієнтів. Правильно — рахувати унікальних клієнтів:

    SELECT COUNT(DISTINCT c.id) FROM customers c JOIN orders o ON o.customer_id = c.id;

    Ось де COUNT(DISTINCT) з абстрактної форми стає щоденним інструментом: він знімає дублікати, які створив JOIN.

    Друга, ще підступніша ситуація — LEFT JOIN і підрахунок по кожному клієнту. Порахуймо замовлення на кожного клієнта, включно з тими, хто не зробив жодного:

    SELECT c.id, c.name, COUNT(o.id) AS orders_cnt
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

    Ключова деталь — COUNT(o.id), а не COUNT(*). При LEFT JOIN клієнт без замовлень усе одно дає один рядок, але колонки з orders у ньому — NULL. COUNT(o.id) пропускає цей NULL і чесно повертає 0. А COUNT(*) порахував би той рядок-заглушку й повернув би 1 — «клієнт без замовлень має одне замовлення». Ось де три форми COUNT із початку глави сходяться з поведінкою JOIN в один робочий приклад: правильний вибір COUNT(col) замість COUNT(*) — це різниця між правдивим і брехливим звітом.

    Абзац глибини — подвійний рахунок при двох JOIN-ах (fan-out, «пастка віяла»). Якщо приєднати до orders одночасно дві дочірні таблиці 1:N — скажімо, order_items і payments, — рядки перемножаться, і SUM(payments.amount) порахує кожен платіж стільки разів, скільки в замовлення позицій. Сума роздується, а помилку майже не видно оком. Захист — агрегувати кожну гілку окремо (підзапитом чи CTE) перед з'єднанням, або звіряти SUM із контрольним значенням. Якщо сума в SQL «трохи більша», ніж має бути, а в даних усе гаразд — перша підозра саме на подвійний JOIN.

    Типові помилки

    • Виглядає як «AVG порахує середнє по всіх рядках», а насправді він ділить на кількість не-NULL значень: рядки з NULL не входять у знаменник, і середнє виходить вищим, ніж «сума / кількість рядків».
    • Виглядає як «SUM над порожньою вибіркою дасть 0», а насправді він дасть NULL (нуль повертає лише COUNT); щоб у звіті був нуль, потрібен COALESCE(SUM(x), 0).
    • Виглядає як «COUNT(*) і COUNT(col) — те саме», а насправді COUNT(*) рахує всі рядки, а COUNT(col) — лише ті, де колонка не NULL; звідси й розбіжність із кількістю в UI.
    • Виглядає як «можна вибрати будь-яку колонку разом з GROUP BY», а насправді незгрупована колонка без агрегату — це або помилка (PostgreSQL), або довільне значення з групи (старий MySQL без ONLY_FULL_GROUP_BY).
    • Виглядає як «фільтр на COUNT(*) можна поставити у WHERE», а насправді агрегат у WHERE неможливий — груп ще немає; умова на агрегат живе тільки в HAVING.
    • Виглядає як «WHERE cnt > 5 спрацює, бо cnt — це мій COUNT(*)», а насправді аліас із SELECT у WHERE не видно: SELECT обчислюється пізніше за WHERE у логічному порядку.
    • Виглядає як «COUNT(*) після JOIN порахує клієнтів», а насправді він рахує роздуті 1:N-рядки, тобто замовлення; клієнтів дає COUNT(DISTINCT c.id).
    • Виглядає як «при LEFT JOIN клієнт без замовлень матиме нуль через COUNT(*)», а насправді COUNT(*) порахує рядок-заглушку як 1; нуль дає лише COUNT(o.id), який пропускає NULL.

    Підсумок

    1. Агрегати SUM, AVG, MIN, MAX, COUNT(col) ігнорують NULL; над порожнім набором вони повертають NULL, і тільки COUNT повертає нуль.
    2. COUNT(*) рахує всі рядки, COUNT(col) — не-NULL значення, COUNT(DISTINCT col) — різні не-NULL значення; плутанина між ними — головне джерело розбіжностей із UI.
    3. Правило GROUP BY: кожна колонка у SELECT має бути в GROUP BY або під агрегатом — інакше база не знає, яке зі значень групи показати.
    4. WHERE фільтрує рядки до групування й не приймає агрегатів; HAVING фільтрує групи після агрегації — це прямий наслідок логічного порядку виконання запиту (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT).
    5. Поверх JOIN 1:N рядки розмножуються: рятує COUNT(DISTINCT), а при LEFT JOIN — COUNT(col) замість COUNT(*), щоб рядки без пари не рахувалися як одиниця.

    Що питають на співбесіді

    • «Чим відрізняються COUNT(*), COUNT(col) і COUNT(DISTINCT col)?» — базовий фільтр. Сильна відповідь не переказує означення, а пояснює наслідок: COUNT(col) менший за COUNT(*) рівно на кількість NULL, а DISTINCT ще й прибирає повтори.
    • «Яка різниця між WHERE і HAVING?» — очікують не «HAVING для агрегатів», а механізм: WHERE працює до групування над рядками, HAVING — після, над групами; тому агрегат у WHERE неможливий.
    • «У якому порядку виконується SQL-запит?» — перевірка глибини. Треба назвати логічний порядок (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT) і пояснити хоч один наслідок: чому агрегат не можна у WHERE або чому аліас із SELECT там не видно.
    • «Як знайти дублікати за email у таблиці users?» — практичне завдання; чекають GROUP BY email HAVING COUNT(*) > 1. Плюс кандидату — згадка, що це показує задубльовані значення, а не самі рядки-дублікати.
    • «Порахуй кількість замовлень по кожному клієнту, включно з тими, у кого замовлень немає» — тест на розуміння LEFT JOIN + агрегація. Пастка — COUNT(*), що дасть таким клієнтам 1; правильно COUNT(o.id).
    • «Чому середнє в SQL не збігається із середнім у застосунку?» — перевірка на зрілість: чи згадаєш, що AVG ігнорує NULL, а застосунок міг рахувати NULL як нуль.

    Інтерв'юер на цій темі відрізняє кандидата, який пише агрегати «поки цифра не зійдеться», від того, хто передбачає результат до запуску: знає, що NULL випаде з AVG, що JOIN роздує COUNT, що фільтр на групу — це HAVING. Найсильніше враження справляє не знання синтаксису, а вміння пояснити, чому саме вийшла та цифра, яку повернула база.

    Джерела