Агрегаты, GROUP BY и HAVING

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

Кассовый отчёт

В конце дня кассир не пересказывает каждый чек. Он пишет одну строку: «чеков 48, выручка 52 300». А если попросить отчёт по отделам, он разложит чеки на стопки по отделам и для каждой стопки напишет свою строку итогов.

Это и есть два шага, которые делает база. Функция, которая сворачивает много значений в одно, называется агрегатной. А раскладку строк на стопки делает GROUP BY.

Итог по всей таблице

Пять главных агрегатов на книгах магазина:

SELECT count(*)   AS books,
       min(price) AS cheapest,
       max(price) AS priciest,
       avg(price) AS avg_price,
       sum(pages) AS pages
FROM books;

 books | cheapest | priciest |      avg_price       | pages
-------+----------+----------+----------------------+-------
     9 |   250.00 |  2490.00 | 836.2500000000000000 |  2768
(1 row)

Девять строк превратились в одну. Длинный хвост нулей у среднего это особенность numeric: PostgreSQL не округляет сам. Округлить можно функцией round(avg(price), 2), получится 836.25.

Обрати внимание на среднее. Сумма восьми цен равна 6690, а 6690 / 9 это 743.33, не 836.25. Дело в «Носе» без цены. Агрегаты пропускают NULL: среднее посчитано по восьми книгам, у которых цена есть. Это видно на двух видах count:

SELECT count(*) AS all_rows, count(price) AS with_price FROM books;

 all_rows | with_price
----------+------------
        9 |          8
(1 row)

count(*) считает строки. count(price) считает только строки, где в price есть значение. Чем ещё отличаются варианты count, разобрано в уроке для собеседования.

GROUP BY: итог по стопкам

Сколько книг в каждом жанре?

SELECT genre, count(*) AS books, round(avg(price), 2) AS avg_price
FROM books
GROUP BY genre
ORDER BY genre;

   genre    | books | avg_price
------------+-------+-----------
 повесть    |     3 |    320.00
 роман      |     2 |    650.00
 учебник    |     2 |   1890.00
 фантастика |     2 |    485.00
(4 rows)

GROUP BY genre раскладывает книги на стопки с одинаковым жанром, и агрегаты считаются по каждой стопке отдельно. Повестей три, но средняя цена 320 посчитана по двум: у «Носа» цены нет.

Главное правило группировки: в SELECT может стоять либо колонка из GROUP BY, либо агрегат. Попробуй вывести ещё и название:

SELECT genre, title, count(*) FROM books GROUP BY genre;

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

PostgreSQL прав: в стопке «повесть» три книги, и какое из трёх названий показать в одной строке итогов, непонятно. Либо группируй и по названию, либо сверни названия агрегатом, например string_agg(title, ', ') склеит их через запятую.