Галерея
7757 8119 8300 8698 8817 9504 9722 9937
Интересные записи
Топовые новости
Новое

Предложение GROUP BY

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

Предложение GROUP BY применяется для вывода агрегатного значения по одной или нескольким строкам, которые возвращает инструкция SELECT, на основе одного или нескольких неагрегатных столбцов, которые называются столбцами группировки (grouping columns). Например, ниже приводится запрос, в котором определяется, сколько людей мы нанимали каждый год в период с 1999 по 2004 год.

SELECT
hire_year,
COUNT(emp_id) AS ‘nbr_emps’
FROM employee
WHERE status=’ACTIVE’ AND hire_year
BETWEEN 1999 AND 2004
GROUP BY hire.year;

Результаты будут следующие:

hire_year nbr_emps

1999 27

2000 17

2001 13

2002 19

2003 20

2004 32

Запросы, в которых используются агрегатные функции, могут предоставить много видов сводной информации. К самым распространенным агрегатным функциям относятся следующие.

AVG

Возвращается среднее всех непустых значений в указанном столбце (столбцах).

AVG DISTINCT

Возвращается среднее всех уникальных непустых значений в указанном столбце (столбцах).

COUNT

Возвращается количество всех непустых значений в указанном столбце (столбцах).

COUNT DISTINCT

Возвращается количество всех уникальных непустых значений в указанном столбце (столбцах).

COUNT (*)

Вычисляется общее количество записей в таблице.

МАХ

Возвращается наибольшее из непустых значений в указанном столбце (столбцах).

MIN

Возвращается наименьшее из непустых значений в указанном столбце (столбцах).

SUM

Сумма всех непустых значений в указанном столбце (столбцах).

SUM DISTINCT

Сумма всех уникальных непустых значений в указанном столбце (столбцах).

Некоторые запросы, в которых используются агрегаты, возвращают одиночное значение. Агрегаты, возвращающие одиночное значение, называются скалярными агрегатами. Для скалярных агрегатов не требуется предложение GROUP BY.

Например:

—Запрос

SELECT AVG(price)
FROM titles
// Результат: 14. 77

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

  • Помещайте предложение GROUP BY правильно относительно других предложений — после предложения WHERE и перед предложением ORDER BY.
  • В предложении GROUP BY все столбцы должны быть неагрегатными.
  • Не используйте в предложении GROUP BY псевдонимы столбцов, хотя использование псевдонимов таблиц является допустимым.

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

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

Кроме того, предложение GROUP В Y поддерживает несколько очень важных дополнительных предложений.

GROUP BY[{ROLLUP CUBE}] ([столбец_для_группировки [, …]]) [, список_наборов_для_группировки]

Агрегатные значения результирующего набора группируются в соответствии с одним или несколькими столбцами для группировки. (Предложение GROUP BY (столбец_для_группировки [, …]) без предложений ROLLUP и CUBE является наиболее простой и часто употребляемой формой предложения GROUP BY.

ROLLUP

Для каждого набора столбцов группировки создаются промежуточные итоговые значения в форме иерархического результирующего набора. При этом в результирующие наборы в иерархическом порядке добавляются промежуточные и общие итоги. Предложение ROLLUP возвращает одну строку для каждого столбца группировки, где в столбцах группировки стоят пустые значения и показаны промежуточные и общие итоговые агрегатные значения (которые мы скоро проиллюстрируем).

CUBE

Для всех столбцов группировки создаются промежуточные и перекрестные итоговые значения. По сути, предложение CUBE позволяет быстро получать многомерные результирующие наборы из стандартных реляционных таблиц без особых усилий по программированию. Предложение CUBE особенно полезно при работе с большими объемами данных. Как и предложение ROLLUP, предложение CUBE создает промежуточные итоги по столбцам группировки, но сюда также входят строки промежуточных итогов для всех возможных комбинаций столбцов группировки, указанных в запросе.

GROUP BY GROUPING SETS [(ROLLUP CUBE}} ([столбец_для_группировки [, …}]) [, список_наборов_для группировки}

Позволяет использовать агрегатные группы по нескольким разным наборам столбцов группировки в одном запросе. Это свойство особенно полезно, если вы хотите получить только часть агрегатного результирующего набора. Предложение GROUPING SETS также позволяет выбрать сравниваемые столбцы группировки, в то время как предложение CUBE возвращает все столбцы группировки, a ROLLUP -иерархическую часть столбцов группировки. Как показывает синтаксис, стандарт ANSI также позволяет объединять предложение GROUPING SETS с предложениями ROLLUP и CUBE.

Каждый тип предложения GROUP BY возвращает свой набор агрегатных значений, а также, в случае ROLLUP и CUBE, итоги и промежуточные итоги.

Принцип предложений ROLLUP CUBE и GROUPING SETS будет гораздо понятнее, если объяснить его на примере. В следующем примере мы запрашиваем данные, представляющие собой количество заказов (sa!es_orders) по годам (order_date) и кварталам (orderquarter).

Добавление столбцов группировки в запрос дает более подробный результирующий набор (с большим количеством промежуточных итогов). Мы изменим приведенный выше пример, добавив в запрос регион (region) (однако, поскольку количество столбцов возрастает, мы рассмотрим только первый и второй квартал).

SELECT
order_year AS year,
order_quarter AS quarter,
region,
COUNT (*) AS orders
FROM order_details
WHERE order_year IN (2003, 2004) AND order_quarter IN (1.2) AND region IN (‘USA’, ‘CANADA’)
GROUP BY ROLLUP (order_year, order_quarter)
ORDER BY order_year,
order_quarter;

Результат будет следующий.

year quarter region orders

Предложение GROUP BY CUBE полезно при выполнении многомерного анализа агрегатных данных. Как и в случае предложения GROUP BY ROLLUP, возвращаются промежуточные итоги. Однако в отличие от GROUP BY ROLLUP здесь промежуточные итоги возвращаются для всех комбинаций столбцов группировки запроса. (Как вы увидите, это предложение потенциально увеличивает количество строк в результирующем наборе данных.)

В следующем примере мы запрашиваем суммарные данные по количеству заказов (salesorders) по годам (orderdate) и кварталам (orderquarter).

SELECT
order_year AS year,
order_quarter AS quarter,
COUNT () AS скаегЕ
FROM order.details
WHERE order.year IN (2003, 2004)
GROUP BY id (order_year, order_quarter)
ORDER BY order_year, order_quarter

Предложение GROUP BY GROUPING SETS позволяет проводить агрегацию по нескольким группам в одном запросе. Для каждого набора групп запрос возвращает промежуточные итоги, где столбец группировки обозначается как NULL. Если предложения CUBE и ROLLUP помещают в результирующий набор заранее заданные промежуточные итоги, предложение GROUPING SETS позволяет вам управлять тем, какие промежуточные итоги будут включаться в запрос. Предложение GROUPING SETS не возвращает общего итога.

Используя пример, сходный с приведенными выше примерами для предложений ROLLUP и CUBE, мы выберем на этот раз промежуточный итог по годам и кварталам, а также отдельно — по годам.

Еще предложение GROUPING SETS можно представить как предложение UNION ALL, примененное к нескольким запросам GROUP BY, ссылающимся на разные части одних и тех же данных. Вы можете заставить систему добавить промежуточные итоги в GROUPING SET, просто добавив предложение ROLLUP или CUBE, в соответствии с которым вы хотите вычислить промежуточные итоги.

Предложения GROUPING SETS можно объединять, чтобы в краткой инструкции с генерировать большое число группировок. Объединенные предложения GROUPING SETS дают продукт перекрестной группировки всех группировок всех наборов, указанных в списке GROUPING SET. Объединенные предложения GROUPING SETS совместимы с предложениями ROLLUP и CUBE. Объединенные предложения GROUPING SETS, являясь продуктом перекрестной группировки, создадут очень большое количество конечных группировок даже из небольшого числа объединенных. Например, если мы расширим 3.3, включив в нее ссылки на объединенные наборы группировок, мы получим 3.4.

В примере, приведенном в 3.4, объединенные предложения GROUPING SETS создают большое количество окончательных группировок. Можно представить, каким большим будет результирующий набор, если объединенные предложения GROUPING SETS содержат большое количество группировок! Тем не менее полученная информация может быть очень ценной и ее сложно получить как-то еще.

Источник