Данная статья относится к циклу статей, посвященных PostgreSQL. В предыдущей статье мы говорили об операторе PostgreSQL EXCEPT. Теперь же мы углубимся в изучение условия GROUPING SETS, которое используется для генерации результирующего набора, эквивалентного тому, который генерирует UNION ALL из нескольких условий GROUP BY.
Настройка таблицы
Прежде чем мы начнём, для наглядности создадим таблицу sales.
CREATE TABLE sales (
brand VARCHAR NOT NULL,
segment VARCHAR NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (brand, segment)
);
INSERT INTO sales (brand, segment, quantity)
VALUES
('ABC', 'Premium', 100),
('ABC', 'Basic', 200),
('XYZ', 'Premium', 100),
('XYZ', 'Basic', 300);
В таблице sales хранится количество продуктов, проданных по брендам или сегментам.
Введение в условие PostgreSQL GROUPING SETS
Групповой набор — это набор столбцов, по которым осуществляется группировка. Как правило, один агрегатный запрос определяет один групповой набор.
Например, следующий запрос определяет групповой набор бренда и сегмента. Возвращает количество продуктов проданных по бренду и сегменту.
SELECT
brand,
segment,
SUM (quantity)
FROM
sales
GROUP BY
brand,
segment;
Следующий запрос определяет количество продуктов, проданных по брендам. Он определяет групповой набор бренда:
Следующий запрос определяет количество продуктов, проданных по сегментам. Он определяет групповой набор сегмента:
SELECT
segment,
SUM (quantity)
FROM
sales
GROUP BY
segment;
Следующий запрос определяет количество продуктов, проданных для всех брендов и сегментов. Он определяет пустой групповой набор:
SELECT
SUM (quantity)
FROM
sales;
Предположим, что вместо четырех наборов результатов вы хотели видеть унифицированный набор результатов с общими данными для всех групповых наборов. Чтобы объединить все запросы выше — воспользуйтесь оператором UNION ALL:
Поскольку оператор UNION ALL требует, чтобы все наборы результатов имели одинаковое количество столбцов с совместимыми типами данных, вам необходимо настроить запросы, добавив NULL в список выбора каждого из них, как показано ниже:
SELECT
brand,
segment,
SUM (quantity)
FROM
sales
GROUP BY
brand,
segment
UNION ALL
SELECT
brand,
NULL,
SUM (quantity)
FROM
sales
GROUP BY
brand
UNION ALL
SELECT
NULL,
segment,
SUM (quantity)
FROM
sales
GROUP BY
segment
UNION ALL
SELECT
NULL,
NULL,
SUM (quantity)
FROM
sales;
Данный запрос сгенерировал один общий набор результатов для всех групповых наборов.
Несмотря на то, что код работает так, как вы ожидали, у него есть две основные проблемы. Первая — это очень длинный код. Вторая же проблема связана с производительностью, так как PostgreSQL должен сканировать таблицу продаж отдельно для каждого запроса.
Чтобы сделать код более эффективным, PostgreSQL предоставляет условие GROUPING SETS, которое является подразделом условия GROUP BY.
Итак, GROUPING SETS позволяет определить несколько групповых наборов в одном запросе. Синтаксис вызова выглядит следующим образом:
SELECT
c1,
c2,
aggregate_function(c3)
FROM
table_name
GROUP BY
GROUPING SETS (
(c1, c2),
(c1),
(c2),
()
);
Здесь у нас есть четыре групповых набора (c1,c2), (c1), (c2), and ().
Чтобы применить этот запрос к примеру выше — используйте GROUPING SETS вместо оператора UNION ALL, как показано ниже:
SELECT
brand,
segment,
SUM (quantity)
FROM
sales
GROUP BY
GROUPING SETS (
(brand, segment),
(brand),
(segment),
()
);
Функции GROUPING
Функция GROUPING принимает имя столбца и возвращает 0 бит, если столбец является членом текущего группового набора, и 1 в противном случае. Обратите внимание на следующий пример:
SELECT
GROUPING(brand) grouping_brand,
GROUPING(segment) grouping_segement,
brand,
segment,
SUM (quantity)
FROM
sales
GROUP BY
GROUPING SETS (
(brand, segment),
(brand),
(segment),
()
)
ORDER BY
brand,
segment;
На этом данный урок подходит к концу, а мы переходим к изучению PostgreSQL CUBE.
Источник: PostgreSQL Tutorial from Scratch