Группировка

Для группировки данных в PostgreSQL применяются операторы GROUP BY и HAVING, для использования которых применяется следующий формальный синтаксис:

SELECT столбцы
FROM таблица
[WHERE условие_фильтрации_строк]
[GROUP BY столбцы_для_группировки]
[HAVING условие_фильтрации_групп]
[ORDER BY столбцы_для_сортировки]

Для рассмотрения операторов возьмем следующую таблицу:

CREATE TABLE Products
(
    Id SERIAL PRIMARY KEY,
    ProductName VARCHAR(30) NOT NULL,
    Company VARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price NUMERIC NOT NULL,
	IsDiscounted BOOL
);

GROUP BY

Оператор GROUP BY определяет, как строки будут группироваться.

Например, сгруппируем товары по производителю

SELECT Company, COUNT(*) AS ModelsCount
FROM Products
GROUP BY Company;

Первый столбец в выражении SELECT - Company представляет название группы, а второй столбец - ModelsCount представляет результат функции Count, которая вычисляет количество строк в группе.

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

И если в выражении SELECT производится выборка по одному или нескольким столбцам и также используются агрегатные функции, то необходимо использовать выражение GROUP BY. Так, следующий пример работать не будет, так как он не содержит выражение группировки:

SELECT Company, COUNT(*) AS ModelsCount
FROM Products;

Другой пример, добавим группировку по количеству товаров:

SELECT Company, ProductCount, COUNT(*) AS ModelsCount
FROM Products
GROUP BY Company, ProductCount;

Оператор GROUP BY может выполнять группировку по множеству столбцов.

Если столбец, по которому производится группировка, содержит значение NULL, то строки со значением NULL составят отдельную группу.

Следует учитывать, что выражение GROUP BY должно идти после выражения WHERE, но до выражения ORDER BY:

SELECT Company, COUNT(*) AS ModelsCount
FROM Products
WHERE Price > 30000
GROUP BY Company
ORDER BY ModelsCount DESC;

Фильтрация групп. HAVING

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

Например, сгруппируем по производителям и найдем все группы, для которых определено более 1 модели:

SELECT Company, COUNT(*) AS ModelsCount
FROM Products
GROUP BY Company
HAVING COUNT(*) > 1;

При этом в одной команде мы можем использовать выражения WHERE и HAVING:

SELECT Company, COUNT(*) AS ModelsCount
FROM Products
WHERE Price * ProductCount > 80000
GROUP BY Company
HAVING COUNT(*) > 1;

То есть в данном случае сначала фильтруются строки: выбираются те товары, общая стоимость которых больше 80000. Затем выбранные товары группируются по производителям. И далее фильтруются сами группы - выбираются те группы, которые содержат больше 1 модели.

Если при этом необходимо провести сортировку, то выражение ORDER BY идет после выражения HAVING:

SELECT Company, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
WHERE Price * ProductCount > 80000
GROUP BY Company
HAVING SUM(ProductCount) > 2
ORDER BY Units DESC;

Здесь группировка идет по производителям, и также выбирается количество моделей для каждого производителя (Models) и общее количество всех товаров по всем этим моделям (Units). Затем группы сортируются по количеству товаров по убыванию.

GROUPING SETS, CUBE и ROLLUP

В дополнение к оператору GROUP BY PostgreSQL поддерживает еще три специальных расширения для группировки данных: GROUPING SETS, ROLLUP и CUBE.

GROUPING SETS

Оператор GROUPING SETS позволяет по отдельности сгруппировать получемые наборы, выбранные предложениями FROM и WHERE. Каждый поднабор,полученный с помощью GROUPING SETS, может содержать ноль или более столбцов или выражений и интерпретируется так же, как если бы он был непосредственно в предложении GROUP BY:

SELECT Company, COUNT(*) AS Models, ProductCount
FROM Products
GROUP BY GROUPING SETS(Company, ProductCount);

В выражении SELECT производится выборка компаний, количества моделей и количества товаров. То есть мы получаем три категории. Оператор GROUPING SETS производит группировку по двум столбцам - Company и ProductCount. В итоге будет создаваться две группы: 1) компании и количество моделей и 2)количество моделей и количество товаров.

ROLLUP

Оператор ROLLUP добавляет суммирующую сводку в результирующий набор без необходимости выпллнять сложные подзапросы. ROLLUP особенно полезен для генерации промежуточных и общих итогов в отчетах. Она имеет следующий синтаксис:

Syntax

SELECT
    column1,
    column2,
    column3,
    aggregate(column4)
FROM
    table_name
GROUP BY
    ROLLUP (column1, column2, column3);

Здесь применяются следующие элементы:

• column1, column2, column3: столбцы, которые используются для группировки

• aggregate(column4): агрегатная функция, которую мы хотим применить (SUM, COUNT, AVG и т.д.)

Пример применения:

SELECT Company, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
GROUP BY ROLLUP(Company);

Как видно из скриншота, в начало таблицы была добавлена дополнительная строка, которая суммирует значение столбцов.

При группировке по нескольким критериям ROLLUP будет создавать суммирующую строку для каждой из подгрупп:

SELECT Company, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
GROUP BY ROLLUP(Company, ProductCount)
ORDER BY Company;

При сортировке с помощью ORDER BY следует учитывать, что она применяется уже после добавления суммирующей строки.

CUBE

CUBE используется в запросах SQL для одновременного создания нескольких группирующих наборов. Это особенно полезно в сценариях, где необходимо анализировать данные по нескольким измерениям без запуска нескольких запросов. Синтаксис:

SELECT
    column1,
    column2,
    column3,
    aggregate (column4)
FROM
    table_name
GROUP BY
    CUBE (column1, column2, column3);

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

SELECT Company, ProductName, SUM(ProductCount * Price) AS Total
FROM Products
GROUP BY CUBE(Company, ProductName);

Здесь мы выбираем три компонента: название компании, название модели и совокупную стоимость всех товаров (по компании или по определенной модели), которая вычисляется путем умножения цены товара на количество товаров. В CUBE передается два столбца, соответственно группируем по этим столбцам - названию компании и названию модели. В итоге мы получим несколько групп.

Прежде всего мы получаем выборку по всем моделям компаний с совокупной ценой всех товаров по данной модели:

Потом у нас есть выборка по всем компаниям с совокупной ценой всех товаров по данной компании:

И также у нас есть выборка по всем моделям компаний с совокупной ценой всех товаров по данной модели (фактически повторяет первую выборку, только без указания компании):

И самая первая строка представляет общую сводку - какова общая цена по всем товарам всех компаний