Группировка записей

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

Чтобы эффективно пользоваться агрегированием, необходимо понимать, что все целевые поля, участвующие в агрегирующем запросе, но не указанные в секции GROUP BY, доступны лишь при выборке через агрегатную функцию. Агрегатная функция получает имя поля (или выражение, в котором участвует хотя бы одно имя поля), представляющее несколько значений (например, в нескольких сгруппированных записях), выполняет с ними некоторую операцию и возвращает одно значение.

Самые распространенные агрегатные функции:

• count () – возвращает количество записей в наборе;
• mах () – возвращает максимальное значение в наборе;
• min () – возвращает минимальное значение в наборе.

Агрегатные функции работают только с записями итогового набора, поэтому они выполняются после обработки всех условных объединений и секций WHERE.

Предположим, вы хотите вывести количество книг, хранящихся в базе данных booktown для каждого издательства. Название издательства связывается с названиями опубликованных книг простым объединением таблиц editions и publishers, однако подсчитывать записи вручную весьма утомительно, а при очень больших объемах итоговых наборов – вообще неприемлемо.

В листинге 27 выполняется стандартное объединение двух таблиц базы данных booktown, но в нем присутствуют два новых элемента: вызов функции count () и секция GROUP BY.

Листинг 27. Группировка записей.

booktown=# SELECT count(e.isbn) AS "number of books",
  p.name AS publisher
  FROM editions AS e INNER JOIN publishers AS p
  ON (e.publisher_id = p.id)
  GROUP BY p.name;

number of books | publisher
2 | Ace Books
1 | Books of Wonder
2 | Doubleday
1 | HarperCollins
1 | Henry Holt & Company. Inc.
1 | Kids Can Press
1 | Mojo Press
1 | O'Reilly & Associates
1 | Penguin
3 | Random House
2 | Roc
1 | Watson-Guptill Publications
(12 rows)

Секция GROUP BY в листинге 27 указывает PostgreSQL на то, что записи объединенного набора данных должны группироваться по имени р.name, которое в данном запросе является ссылкой на имя name таблицы publishers. Все записи с одинаковым названием издательства группируются, после чего функция count() подсчитывает в каждой группе количество значений isbn из таблицы editions и возвращает результат – количество записей, объединенных в каждую группу для одного издательства.

Учтите, что в листинге 27 аргумент (поле isbn таблицы editions) функции count () был выбран только для того, чтобы дать наглядное представление о смысле команды (подсчет количества книг для одного издателя). Его можно было заменить любым другим полем, поскольку функция count() всегда возвращает количество записей в текущей агрегатной группе.

При проектировании агрегатных запросов следует помнить, что секция WHERE не может содержать агрегатных функций, поэтому вместо нее следует использовать секцию HAVING. Секция HAVING работает аналогично секции WHERE, но ее условия должны быть основаны на агрегатных функциях, а не на условиях для отдельных записей. С точки зрения синтаксиса секция HAVING должна следовать за секцией GROUP BY (листинг 28).

Листинг 28. Использование секции HAVING.

booktown=# SELECT count(e.isbn) AS "number of books",
  p.name AS publisher
  FROM editions AS e INNER JOIN publishers AS p
  ON (e.publisher_id – p.id)
  GROUP BY publisher
  HAVING count(e.isbn) > 1;

number of books | publisher
2 | Ace Books
2 | Doubleday
3 | Random House
2 | Roc
(4 rows)

В листингах 27 и 28 набор данных создается внутренним объединением таблиц editions и publishers, однако листинг 28 ограничивает результат теми издательствами, которые представлены в базе данных booktown двумя и более книгами. Задача решается при помощи секции HAVING.

Примечание. Если поле итогового набора связывается ключевым словом AS с синонимом, совпадающим с именем существующего поля в одном из исходных наборов данных, то при использовании этого имени в секции GROUP BY PostgreSQL предполагает, что имя относится к исходному полю, а не к синониму.