GROUP BY і HAVING: бази даних для PHP-розробника

01 · ЯК ПРАЦЮЄ ГРУПУВАННЯ

З рядків у групи

GROUP BY company_id розкладає вакансії на купки з однаковим company_id, і кожна купка стає одним рядком результату. COUNT, SUM, AVG, MIN і MAX стискають купку в одне число. Вакансії без компанії теж утворюють групу, з company_id = NULL: для групування всі NULL однакові, хоча NULL = NULL у WHERE і не дає TRUE.

СЛОВНИКАгрегатна функція. Функція над множиною рядків, що повертає одне значення: COUNT, SUM, AVG, MIN, MAX, а ще string_agg у PostgreSQL і GROUP_CONCAT у MySQL та SQLite. Усі, крім COUNT(*), пропускають NULL.

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

СЛОВНИКHAVING. Умова на групу. Виконується після GROUP BY, тому бачить агрегати. Без GROUP BY весь результат вважається однією групою.

Поруч з агрегатом у SELECT можна ставити колонки з GROUP BY і те, що від них . Якщо групуємо за первинним ключем компанії, її назва однозначна. Назва вакансії при групуванні за компанією однозначною не буде, і PostgreSQL та MySQL з режимом такий запит не виконають. SQLite виконає і візьме назву з якогось рядка групи.

СЛОВНИКФункціональна залежність. Колонка B залежить від A, якщо за значенням A значення B визначене однозначно. Первинний ключ визначає всі колонки свого рядка, тож GROUP BY c.id дозволяє вибрати c.name.

СЛОВНИКONLY_FULL_GROUP_BY. Режим sql_mode у MySQL, увімкнений за замовчуванням з 5.7.5. Забороняє в SELECT колонки, яких немає в GROUP BY і які від нього функціонально не залежать.

02 · WHERE ЧИ HAVING

Куди писати умову

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

SELECT ... FROM vacancies ... GROUP BY company_id

WHERE status = 'active' GROUP BY company_id до групування Закрита вакансія Team Lead не потрапляє в жодну групу. У Kvitka Soft лишається 2 вакансії замість 3.

GROUP BY company_id HAVING COUNT(*) >= 2 після групування Лишаються компанії, у яких щонайменше дві вакансії. Агрегат у HAVING це основна причина, навіщо HAVING існує.

WHERE COUNT(*) >= 2 помилка Груп ще немає. MySQL: Invalid use of group function. PostgreSQL: aggregate functions are not allowed in WHERE. SQLite: misuse of aggregate.

GROUP BY status HAVING status = 'active' працює, але Результат правильний, а план різний: PostgreSQL сам переносить таку умову в WHERE, MySQL 8.0 спершу групує все. Умову без агрегатів пишіть у WHERE. Різницю видно в EXPLAIN нижче.

SELECT status, COUNT(*) AS n ... HAVING n > 1 не всюди MySQL і SQLite дозволяють аліас з SELECT у HAVING, PostgreSQL ні: column "n" does not exist. Переносимо HAVING COUNT(*) > 1.

SELECT c.id, c.name, COUNT(v.id) ... GROUP BY c.id залежність від ключа c.id первинний ключ, тож c.name однозначна. PostgreSQL, MySQL і SQLite виконують запит без c.name у GROUP BY.

SELECT company_id, title, COUNT(*) ... GROUP BY company_id неоднозначно У Kvitka Soft три назви, і яку показати, незрозуміло. PostgreSQL і MySQL дають помилку, SQLite мовчки повертає Senior PHP.

Кожен рядок виконано на MySQL 8.0 з sql_mode за замовчуванням, PostgreSQL 16 і SQLite 3.45.

03 · ONLY_FULL_GROUP_BY

Колонка, якої немає в GROUP BY

Той самий запит у трьох базах: назва вакансії поруч з кількістю вакансій компанії. PostgreSQL відмовляє завжди. MySQL відмовляє, поки в sql_mode є ONLY_FULL_GROUP_BY, а Laravel з 'strict' => true у config/database.php ставить цей режим сам. Вимкнути strict щоб «запрацювало» означає отримати випадкове значення замість помилки.

SQLite не має такого режиму взагалі. Тому запит, який проходить тести на SQLite, може впасти на PostgreSQL у продакшні.

MySQL 8.0 PostgreSQL 16 SQLite 3.45

1SELECT company_id, title, COUNT(*)

2FROM vacancies

3GROUP BY company_id;

5-- якщо справді байдуже, яку назву взяти:

6SELECT company_id, ANY_VALUE(title) AS title,

7 COUNT(*) AS n

8FROM vacancies

9GROUP BY company_id;

ERROR 1055 (42000): Expression #2 of SELECT list is not
in GROUP BY clause and contains nonaggregated column
'lab.vacancies.title' which is not functionally dependent
on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by

+------------+-------------+---+
| company_id | title       | n |
+------------+-------------+---+
|       NULL | Symfony Dev | 1 |
|          1 | Senior PHP  | 3 |
|          2 | Middle PHP  | 1 |
+------------+-------------+---+

ANY_VALUE() прямо каже: «будь-яке значення з групи підходить». Це чесніше за вимкнений режим, бо видно, де саме значення випадкове.

1SELECT company_id, title, COUNT(*)

2FROM vacancies

3GROUP BY company_id;

5-- а так можна: c.id первинний ключ

6SELECT c.id, c.name, COUNT(v.id) AS n

7FROM companies c

8LEFT JOIN vacancies v ON v.company_id = c.id

9GROUP BY c.id;

ERROR:  column "vacancies.title" must appear in the GROUP BY
clause or be used in an aggregate function

 id |    name     | n
----+-------------+---
  1 | Kvitka Soft | 3
  2 | Dnipro Labs | 1
  3 | Lviv Pay    | 0
(3 rows)

Функціональну залежність PostgreSQL визнає лише від первинного ключа таблиці, згрупованої за всіма його колонками. Від унікального індексу вона не рахується, і GROUP BY c.slug з c.name поруч дасть ту саму помилку. MySQL тут поблажливіший: унікальний ключ на колонці NOT NULL для нього теж визначає рядок.

1SELECT company_id, title, COUNT(*)

2FROM vacancies

3GROUP BY company_id;

company_id  title        COUNT(*)
----------  -----------  --------
NULL        Symfony Dev  1
1           Senior PHP   3
2           Middle PHP   1

Помилки немає, назва взята з якогось рядка групи. Виняток SQLite робить для MIN() і MAX(): з ними «голі» колонки беруться з того рядка, де знайдено мінімум чи максимум.

04 · УМОВНА АГРЕГАЦІЯ

Кілька лічильників за один прохід

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

Переносний запис COUNT(CASE WHEN ... THEN 1 END) працює всюди: CASE без ELSE дає NULL, а COUNT NULL не рахує. PostgreSQL і SQLite з 3.30 мають стандартний FILTER (WHERE ...), який читається легше. MySQL FILTER не знає, зате там порівняння повертає 1 або 0, і SUM(status = 'active') теж працює.

PostgreSQL 16 MySQL 8.0 SQLite 3.45

1SELECT company_id,

2 COUNT(*) AS total,

3 COUNT(*) FILTER (WHERE status = 'active')

4 AS active

5FROM vacancies

6GROUP BY company_id

7ORDER BY company_id;

 company_id | total | active
------------+-------+--------
          1 |     3 |      2
          2 |     1 |      1
       NULL |     1 |      1
(3 rows)

SUM(status = 'active') тут не пройде: ERROR: function sum(boolean) does not exist. Булеве значення в PostgreSQL не число.

1SELECT company_id,

2 COUNT(*) AS total,

3 SUM(CASE WHEN status = 'active'

4 THEN 1 ELSE 0 END) AS active,

5 COUNT(CASE WHEN status = 'closed'

6 THEN 1 END) AS closed

7FROM vacancies

8GROUP BY company_id

9ORDER BY company_id;

+------------+-------+--------+--------+
| company_id | total | active | closed |
+------------+-------+--------+--------+
|       NULL |     1 |      1 |      0 |
|          1 |     3 |      2 |      1 |
|          2 |     1 |      1 |      0 |
+------------+-------+--------+--------+

Коротше SUM(status = 'active'): порівняння в MySQL дає 1 або 0. Але такий запит не перенесеться на PostgreSQL, а CASE перенесеться.

1SELECT company_id,

2 COUNT(*) AS total,

3 COUNT(*) FILTER (WHERE status = 'active')

4 AS active

5FROM vacancies

6GROUP BY company_id

7ORDER BY company_id;

company_id  total  active
----------  -----  ------
NULL        1      1
1           3      2
2           1      1

SQLite розуміє і FILTER, і SUM(status = 'active'), бо порівняння в нього теж дає 1 або 0. Запит, який ганяють у тестах на SQLite, а в продакшні виконують на PostgreSQL, може взяти FILTER, але не SUM від порівняння. COUNT(CASE ...) пройде всюди.

05 · HAVING БЕЗ АГРЕГАТА

Та сама умова в WHERE і в HAVING

Кількість вакансій однієї компанії, 42-ї, можна відфільтрувати в WHERE до групування або в HAVING після. Результат однаковий. PostgreSQL помічає, що в HAVING немає агрегата, і сам переносить умову в WHERE: обидва плани однакові. MySQL 8.0 виконує HAVING буквально: групує всі 200 000 вакансій у 8 821 групу, а потім лишає одну.

WHERESELECT company_id, COUNT(*) FROM vacancies WHERE company_id = 42 GROUP BY company_id;

-> Group aggregate: count(0)  (actual time=0.0398..0.0399 rows=1 loops=1)
    -> Covering index lookup on vacancies using vacancies_company_id_index (company_id=42)
         (actual time=0.0252..0.0327 rows=22 loops=1)

HAVINGSELECT company_id, COUNT(*) FROM vacancies GROUP BY company_id HAVING company_id = 42;

-> Filter: (vacancies.company_id = 42)  (actual time=3.76..82.2 rows=1 loops=1)
    -> Group aggregate: count(0)  (actual time=3.56..81.5 rows=8821 loops=1)
        -> Covering index scan on vacancies using vacancies_company_id_index
             (actual time=2.84..62.7 rows=200000 loops=1)

MySQL 8.0.46, InnoDB, 200 000 вакансій з індексом на company_id. Вивід знято зі справжньої бази, оцінки cost прибрано.

WHERESELECT company_id, COUNT(*) FROM vacancies WHERE company_id = 42 GROUP BY company_id;

GroupAggregate  (cost=0.29..4.74 rows=1 width=12) (actual time=0.143..0.144 rows=1 loops=1)
  ->  Index Only Scan using vacancies_company_id_index on vacancies  (actual time=0.130..0.134 rows=22 loops=1)
        Index Cond: (company_id = 42)

HAVINGSELECT company_id, COUNT(*) FROM vacancies GROUP BY company_id HAVING company_id = 42;

GroupAggregate  (cost=0.29..4.74 rows=1 width=12) (actual time=0.017..0.017 rows=1 loops=1)
  ->  Index Only Scan using vacancies_company_id_index on vacancies  (actual time=0.010..0.013 rows=22 loops=1)
        Index Cond: (company_id = 42)

HAVING з агрегатомSELECT company_id, COUNT(*) FROM vacancies GROUP BY company_id HAVING COUNT(*) > 30;

HashAggregate  (cost=4665.00..4775.18 rows=2938 width=12) (actual time=78.252..79.580 rows=1 loops=1)
  Group Key: company_id
  Filter: (count(*) > 30)
  Rows Removed by Filter: 8820
  ->  Seq Scan on vacancies  (actual time=0.008..21.465 rows=200000 loops=1)
Execution Time: 82.789 ms

PostgreSQL 16, ті самі 200 000 вакансій. Вивід знято зі справжньої бази, рядки Planning Time і частину оцінок прибрано.

Group aggregate: Групування по відсортованому потоку: рядки йдуть з індексу вже впорядкованими за company_id, тож окремого сортування чи тимчасової таблиці немає.
Covering index lookup: У першому плані пошук по індексу за company_id = 42, 22 рядки. У другому Covering index scan, тобто прохід усім індексом, 200 000 рядків.
Filter: Умова з HAVING стоїть над групуванням: вона перевіряє 8 821 готову групу. 0.04 мс проти 82 мс.

GroupAggregate: Групування відсортованого потоку, тут з індексу. HashAggregate будує хеш-таблицю груп у памʼяті й не потребує сортування.
Index Cond: В обох перших планах умова company_id = 42 стала умовою пошуку в індексі, хоча в другому запиті вона написана в HAVING.
Filter: HAVING з агрегатом перенести нікуди: COUNT(*) відомий лише після групування. Тому третій план читає всю таблицю, і це нормально, бо питання стосується всіх компаній.
Rows Removed by Filter: 8 820 груп відкинуто, одна лишилася. Для такого запиту індекс не допоможе, допоможе лише лічильник, який зберігають окремо.

06 · ЯК У PHP UKRAINE

Джерела трафіку одним запитом

Звіт аналітики в адмінці показує, звідки приходять відвідувачі: переходи й унікальних відвідувачів по кожному джерелу. Це один запит з GROUP BY source і двома агрегатами: count(*) рахує всі перегляди групи, count(distinct visitor) лише різних відвідувачів. orderByDesc('visits') сортує за аліасом з SELECT, і це законно: ORDER BY виконується після SELECT, на відміну від WHERE і HAVING. Будівник запиту бере toBase(), бо моделі для агрегатів не потрібні.

php EloquentTrafficReport.php фрагмент, скорочено

1$rows = $this->visits($from, $to)

2 ->selectRaw('source, count(*) as visits, count(distinct visitor) as visitors')← два агрегати на групу

3 ->groupBy('source')← рядок на кожне джерело

4 ->orderByDesc('visits')← аліас з SELECT

5 ->get();

src/Hiring/Infrastructure/Catalog/EloquentJobCatalog.phpgroupCounts() рахує фасети каталогу (фреймворк, рівень, формат) через whereNotNull($column)->groupBy($column). whereNotNull тут стоїть до групування, у WHERE: інакше вакансії без рівня зібралися б в окрему групу з NULL.

src/Content/Infrastructure/Persistence/EloquentInterviewCatalog.phpЛічильники питань співбесід за темою і за рівнем: selectRaw('topic, count(*) as n')->groupBy('topic')->pluck('n', 'topic'). pluck() одразу перетворює групи на масив «тема → число».

src/Hiring/Domain/Job/SalaryDistribution.phpМедіану й перцентилі зарплат сайт рахує в PHP лінійною інтерполяцією, як percentile_cont у PostgreSQL. У SQLite, на якому йдуть тести, такої агрегатної функції немає, як і в MySQL.

07 · ДЕ ГРУПУВАННЯ БРЕШЕ

Число є, але не те

✕ 1

Вимкнений ONLY_FULL_GROUP_BY

Запит з назвою вакансії поруч з GROUP BY company_id падає на MySQL, і його «лагодять» через 'strict' => false. Помилка зникає, а назва береться з випадкового рядка групи і може змінитися після будь-якого оновлення таблиці.

Натомість: Вирішіть, яке значення потрібне: MIN(), MAX(), ANY_VALUE(), або згрупуйте за первинним ключем. Режим лишайте увімкненим.

✕ 2

COUNT(колонки) замість COUNT(*)

COUNT(salary_max) пропускає вакансії без зарплати: 4 з 5. Для «скільки вакансій» це помилка, для «скільки із зарплатою» саме те.

Натомість: COUNT(*) рахує рядки, COUNT(колонки) рахує не-NULL значення. Пишіть те, що означає питання.

✕ 3

COUNT після LEFT JOIN

COUNT(*) по companies LEFT JOIN vacancies дає Lviv Pay одну вакансію: рядок з NULL праворуч теж рядок.

Натомість: Рахуйте колонку правої таблиці: COUNT(v.id) дасть 0. Або withCount() в Eloquent, який рахує підзапитом.

✕ 4

SUM після JOIN з іншою таблицею

JOIN з відгуками повторює рядок вакансії на кожен відгук, і SUM(v.salary_max) рахує одну зарплату кілька разів. COUNT(DISTINCT) лікує кількість, але не суму.

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

✕ 5

Умова без агрегата в HAVING

HAVING company_id = 42 дає правильний результат, але MySQL спершу групує всю таблицю. На 200 000 рядків це 82 мс проти 0.04 мс.

Натомість: HAVING лише для умов на агрегати. Решту пишіть у WHERE.

✕ 6

Кілька запитів заради кількох лічильників

Активні, закриті й усі вакансії трьома count() з різним where() це три проходи по таблиці.

Натомість: Один запит з умовною агрегацією: COUNT(CASE WHEN ...) або FILTER (WHERE ...), у Laravel через selectRaw().

08 · СЛОВНИК

Агрегатна функція: Функція над множиною рядків, що повертає одне значення: COUNT, SUM, AVG, MIN, MAX, а ще string_agg у PostgreSQL і GROUP_CONCAT у MySQL та SQLite. Усі, крім COUNT(*), пропускають NULL.
HAVING: Умова на групу. Виконується після GROUP BY, тому бачить агрегати. Без GROUP BY весь результат вважається однією групою.
Функціональна залежність: Колонка B залежить від A, якщо за значенням A значення B визначене однозначно. Первинний ключ визначає всі колонки свого рядка, тож GROUP BY c.id дозволяє вибрати c.name.
ONLY_FULL_GROUP_BY: Режим sql_mode у MySQL, увімкнений за замовчуванням з 5.7.5. Забороняє в SELECT колонки, яких немає в GROUP BY і які від нього функціонально не залежать.

09 · ПЕРЕВІР СЕБЕ

Питання зі співбесід

Усі питання з SQL →

WHERE фільтрує рядки до групування і не бачить агрегатів. HAVING фільтрує групи після GROUP BY і може використовувати COUNT, SUM та інші. Умови без агрегатів пишуть у WHERE: так у групування йде менше рядків.

COUNT(*) = 5, бо рахує рядки. COUNT(salary_max) = 4, бо NULL пропускається. AVG(salary_max) = 4750: сума 19 000 ділиться на 4, а не на 5. Якщо вакансію без зарплати треба рахувати як нуль, пишуть AVG(COALESCE(salary_max, 0)), і вийде 3800.

PostgreSQL визнає функціональну залежність лише від первинного ключа: якщо згруповано за всіма колонками PK, інші колонки цієї таблиці однозначні. Унікальний індекс для цього правила не рахується, тож для c.slug треба додати c.name у GROUP BY або загорнути в агрегат. MySQL такий запит виконає, якщо slug оголошено NOT NULL і UNIQUE: він визнає залежність і від унікального ключа.

Найімовірніше, у SELECT є колонка поза GROUP BY без функціональної залежності: SQLite мовчки бере значення з якогось рядка, PostgreSQL дає помилку. Друга часта причина: аліас у HAVING чи SUM(булевого виразу), які SQLite приймає, а PostgreSQL ні. Запобігти можна, якщо ганяти хоча б частину тестів на тій самій СУБД, що й продакшн, і тримати агрегатні запити в переносному SQL: COUNT(CASE ...), агрегат у HAVING замість аліаса.

← ПОПЕРЕДНЯJOIN НАСТУПНА →Підзапити й CTE

ТЕМАПорядок виконання SELECT ТЕМАJOIN ТЕМАNULL і його пастки ТЕМАВіконні функції ПИТАННЯ · SQLЧим HAVING відрізняється від WHERE і як працює GROUP BY? ПИТАННЯ · SQLЯк читати EXPLAIN і що в ньому найважливіше? КОНЦЕПЦІЯ · АРХІТЕКТУРАCQRS

GROUP BY і HAVINGGrouping and aggregates

З рядків у групи

Куди писати умову

Колонка, якої немає в GROUP BY

Кілька лічильників за один прохід

Та сама умова в WHERE і в HAVING

Джерела трафіку одним запитом

Число є, але не те

Вимкнений ONLY_FULL_GROUP_BY

COUNT(колонки) замість COUNT(*)

COUNT після LEFT JOIN

SUM після JOIN з іншою таблицею

Умова без агрегата в HAVING

Кілька запитів заради кількох лічильників

Питання зі співбесід