Предложение GROUPING SETS

Предложение GROUPING SETS – это дальнейшее расширение предложения GROUP BY, которое можно использовать, чтобы задать несколько группирований данных.

Такой подход облегчает эффективное агрегирование и, следовательно, упрощает анализ данных по нескольким измерениям.

• Синтаксис предложения GROUPING SETS используется для определения нескольких группирований в одном и том же запросе.

• Все группирования, заданные в предложении GROUPING SETS, являются вычисляемыми, и результаты отдельных группирований объединяются операцией UNION ALL.

• Достоинства предложения GROUPING SETS:

  • Требуется только один проход по базовой таблице.

  • Не нужно создавать сложные инструкции UNION.

  • Чем больше элементов содержит предложение GROUPING SETS, тем больше выигрыш в производительности.

Вместо нескольких инструкций SELECT, объединенных операторами UNION ALL, использование предложения GROUPING SETS позволяет написать одну инструкцию SELECT, определяющую несколько группирований (в которые также могут входить операторы ROLLUP или CUBE). Например:

SELECT department_id, job_id, manager_id, AVG(salary) FROM employees GROUP BY GROUPING SETS ((department_id, job_id, manager_id), (department_id, manager_id),(job_id, manager_id));

Эта инструкция вычисляет агрегаты по трем группированиям:

(department_id, job_id, manager_id), (department_id, manager_id)and (job_id, manager_id)

Без данной функции для получения результата, аналогичного приведенной инструкции SELECT, потребовалось бы несколько запросов, объединенных с помощью UNION ALL. Использование нескольких запросов неэффективно, так как требует нескольких сканирований одних и тех же данных.

Сравните предыдущий пример со следующей альтернативой:

SELECT department_id, job_id, manager_id, AVG(salary) FROM employees GROUP BY CUBE(department_id, job_id, manager_id);

Эта инструкция вычисляет все 8 (2 *2 *2) группирований, но нас интересуют только группы(department_id, job_id, manager_id), (department_id, manager_id) и (job_id, manager_id).

Еще одним вариантом является следующая инструкция:

SELECT department_id, job_id, manager_id, AVG(salary) FROM employees GROUP BY department_id, job_id, manager_id UNION ALL SELECT department_id, NULL, manager_id, AVG(salary) FROM employees GROUP BY department_id, manager_id UNION ALL SELECT NULL, job_id, manager_id, AVG(salary) FROM employees GROUP BY job_id, manager_id;

Для этой инструкции требуются три сканирования базовой таблицы, что снижает производительность.

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

Далее: Обзор сортировки данных