HAVING: фильтр по группам

Бран · продюсер «Хроник Пограничья»

Запускаем турнир гильдий. Мелкие гильдии на нём только мешают: три человека против сотни. Пришли список гильдий, где больше 20 персонажей, приглашения уйдут сегодня.

В прошлом уроке мы научились получать строку на каждую группу. Сгруппировать персонажей по гильдиям и посчитать их мы уже умеем. Осталось выкинуть маленькие гильдии. Первое, что приходит в голову, WHERE count(*) > 20, и это ошибка.

Охранник на входе и судья на финише

Представь забег. На входе стоит охранник и проверяет каждого бегуна по отдельности: есть ли номер, нет ли травмы. Те, кого он не пустил, в забеге не участвуют. На финише сидит судья и смотрит на команды целиком: команда, где добежало меньше пяти человек, не получает приз.

Охранник не может судить по командному результату: забег ещё не начался. Судья не проверяет каждого бегуна: он видит только итоги.

В SQL охранник это WHERE, а судья это HAVING. WHERE проверяет отдельные строки до группировки. HAVING проверяет группы после неё, и в нём можно использовать агрегаты.

Почему WHERE не подходит

Попробуем по-простому:

SELECT guild_id, count(*) AS members
FROM characters
WHERE count(*) > 20
GROUP BY guild_id;

ERROR:  aggregate functions are not allowed in WHERE

База отвечает прямо: в WHERE агрегатам не место. Когда работает WHERE, стопок ещё нет, и считать count(*) не по чему.

Переносим условие в HAVING:

SELECT guild_id, count(*) AS members
FROM characters
GROUP BY guild_id
HAVING count(*) > 20
ORDER BY members DESC;

 guild_id | members
----------+---------
          |    4358
        1 |     255
        2 |     132
        3 |      98
 ...

Почти готово, но первая строка странная: гильдия без номера на 4358 персонажей. Это все персонажи без гильдии, guild_id IS NULL. Мы видели это в прошлом уроке: NULL тоже образует группу, и здесь она самая большая. На турнир её не пригласишь.

WHERE и HAVING вместе

Персонажей без гильдии надо убрать ещё до группировки. Это условие на отдельную строку, значит, работа для WHERE:

SELECT guild_id, count(*) AS members
FROM characters
WHERE guild_id IS NOT NULL
GROUP BY guild_id
HAVING count(*) > 20
ORDER BY members DESC, guild_id;

 guild_id | members
----------+---------
        1 |     255
        2 |     132
        3 |      98
        4 |      65
        5 |      65
        7 |      41
        8 |      41
        6 |      40
       10 |      35
        9 |      29
       13 |      27
       11 |      26
       16 |      26
       15 |      25
       12 |      24
       14 |      23

Шестнадцать гильдий. Остальные 24 гильдии слишком маленькие, самая скромная, гильдия 40, держится на шести персонажах.