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

Условие поиска раздела HAVING строится по тем же синтаксическим правилам, что и условие поиска раздела WHERE, и может включать те же самые предикаты.

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

Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.

Пусть запрос вида (в качестве базовой таблицы см. рис. 4.22):

SELECT Ид_сотр, Дата, MAX ((Сумма))

FROM Ведомость1

GROUP BY Ид_сотр, Дата;

необходимо уточнить тем, чтобы были показаны только выплаты, превышающие 1000.

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

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

SELECT Ид_сотр, Дата, MAX (Сумма)

FROM Ведомость1

WHERE MAX ((Сумма)) > 1000 GROUP BY Ид_сотр, Дата;

Правильным предложением будет:

SELECT Ид_сотр, Дата, MAX ((Сумма))

Транслирует запрос SELECT во внутренний план исполнения («query plan»), который может различаться даже для синтаксически одинаковых запросов и от конкретной СУБД.

Оператор SELECT состоит из нескольких предложений (разделов):

• SELECT определяет список возвращаемых столбцов (как существующих, так и вычисляемых), их имена, ограничения на уникальность строк в возвращаемом наборе, ограничения на количество строк в возвращаемом наборе;
• FROM задаёт табличное выражение, которое определяет базовый набор данных для применения операций, определяемых в других предложениях оператора;
• WHERE задает ограничение на строки табличного выражения из предложения FROM;
• GROUP BY объединяет ряды, имеющие одинаковое свойство с применением агрегатных функций
• HAVING выбирает среди групп, определенных параметром GROUP BY
• ORDER BY задает критерии сортировки строк; отсортированные строки передаются в точку вызова.

Структура оператора [ | ]

Оператор SELECT имеет следующую структуру:

SELECT [ DISTINCT | DISTINCTROW | ALL ] select_expression ,... FROM table_references [ WHERE where_definition ] [ GROUP BY { unsigned_integer | col_name | formula } ] [ HAVING where_definition ] [ ORDER BY { unsigned_integer | col_name | formula } [ ASC | DESC ], ...]

Параметры оператора [ | ]

Используется для определения, какие строки должны быть выбраны или включены в GROUP BY.

GROUP BY [ | ]

GROUP BY - необязательный параметр операторa SELECT , для группировки строк по результатам агрегатных функций (MAX, SUM, AVG, …).

HAVING [ | ]

HAVING - необязательный (опциональный) параметр оператора SELECT для указания условия на результат агрегатных функций (MAX, SUM, AVG, …).

HAVING <условия> аналогичен WHERE <условия> за исключением того, что строки отбираются не по значениям столбцов, а строятся из значений столбцов, указанных в GROUP BY , и значений агрегатных функций, вычисленных для каждой группы, образованной GROUP BY.

Необходимо, чтобы в SELECT были заданы только требуемые в выходном потоке столбцы, перечисленные в GROUP BY и/или агрегированные значения. Распространённая ошибка - указание в SELECT столбца, пропущенного в GROUP BY.

Если параметр GROUP BY в SELECT не задан, HAVING применяется к «группе» всех строк таблицы, полностью дублируя (допускается не во всех реализациях стандарта SQL).

ORDER BY [ | ]

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

Использование предложения ORDER BY является единственным способом отсортировать результирующий набор строк. Без этого предложения СУБД может вернуть строки в любом порядке. Если упорядочение необходимо, ORDER BY должен присутствовать в SELECT , UNION .

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

• Параметр ASC (по умолчанию) устанавливает порядок сортировки по возрастанию, от меньших значений к большим.
• Параметр DESC устанавливает порядок сортировки по убыванию, от больших значений к меньшим.

Примеры [ | ]

SELECT * FROM T ;

SELECT C1 FROM T ;

Для таблицы T запрос

SELECT * FROM T ;

вернёт все столбцы всех строк данной таблицы. Для той же таблицы запрос

SELECT C1 FROM T ;

вернёт значения столбца C1 всех строк таблицы- в терминах реляционной алгебры проекция . Для той же таблицы запрос

вернёт значения всех столбцов всех строк таблицы, у которых значение поля C1 равно "1"- в терминах реляционной алгебры можно сказать, что была выполнена выборка , так как присутствует ключевое слово WHERE. Последний запрос

SELECT * FROM T ORDER BY C1 DESC ;

вернёт те же строки, что и первый, однако результат будет отсортирован в обратном порядке (Z-A) из-за использования ключевого слова ORDER BY с полем C1 в качестве поля сортировки. Этот запрос не содержит ключевого слова WHERE, поэтому он вернёт всё, что есть в таблице. Несколько элементов ORDER BY могут быть указаны разделённые запятыми [напр. ORDER BY C1 ASC, C2 DESC] для более точной сортировки.

Отбирает все строки, где поле column_name равно одному из перечисленных значений value1,value2,…

Возвращает список идентификаторов отделов, продажи которых превысили 1000 долларов за 1 января 2000 года, вместе с суммами продаж за этот день:

Ограничение возвращаемых строк [ | ]

Согласно ISO SQL:2003 возвращаемый набор данных может быть ограничен с помощью:

Оконная функция ROW_NUMBER() [ | ]

Существуют различные оконные функции . ROW_NUMBER() OVER может быть использована для простого ограничения числа возвращаемых строк. Например, для возврата не более десяти строк:

ROW_NUMBER может быть недетерминированным: если key не уникален, каждый раз при выполнении запроса возможно присвоение разных номеров строкам, у которых key совпадает. Когда key уникален, каждая строка будет всегда получать уникальный номер строки.

Оконная функция RANK() [ | ]

Функция RANK() OVER работает почти так же, как ROW_NUMBER, но может вернуть более чем n строк при определённых условиях. Например, для получения top-10 самых молодых людей:

Данный код может вернуть более чем 10 строк. Например, если есть два человека с одинаковым возрастом, он вернёт 11 строк.

Нестандартный синтаксис [ | ]

Не все СУБД поддерживают вышеуказанные оконные функции. При этом многие имеют нестандартный синтаксис для решения тех же задач. Ниже представлены варианты простого ограничения выборки для различных СУБД:

Предложение 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

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

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

AVG DISTINCT

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

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

COUNT DISTINCT

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

COUNT (*)

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

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

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

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

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 возвращает одну строку для каждого столбца группировки, где в столбцах группировки стоят пустые значения и показаны промежуточные и общие итоговые агрегатные значения (которые мы скоро проиллюстрируем).

Для всех столбцов группировки создаются промежуточные и перекрестные итоговые значения. По сути, предложение 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 содержат большое количество группировок! Тем не менее полученная информация может быть очень ценной и ее сложно получить как-то еще.

Одной из важных команд в SQL является GROUP BY . Данная конструкция создана для выборки отдельных групп строк из таблицы, к каждой из которых применяются функции, указанные в SELECT (например, COUNT() , MIN() и так далее). Давайте разберём на конкретных примерах.

Допустим, у нас есть таблица супермаркетов:

• id - уникальный идентификатор.
• shop_id - уникальный идентификатор супермаркета.
• price - цена на молоко.

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

Исходная таблица выглядит следующим образом:

Таким образом, мы узнали среднюю цену в конкретной сети супермаркетов (или в одиночном магазине).

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

Допустим, у нас есть таблица с пользователями:

• id - уникальный идентификатор.
• email - e-mail пользователя.
• hash - уникальный хэш пользователя.

И перед нами встала задача выбрать уникальных пользователей , причём именно уникальных людей, а не уникальных учётных записей. Ведь у одного человека может быть и 100 аккаунтов с разными e-mail и, разумеется, id . А hash - это некая строка, характеризующая его как уникального человека.

Таким образом, нам надо выбрать все записи с уникальным hash . Для этого опять же используется GROUP BY :

SELECT * FROM `table` GROUP BY `hash`

В результате, будут извлечены только уникальные hash , то есть 2 одинаковых hash в результирующей выборке Вы не увидите.

Вот таких два практических примера использования GROUP BY в SQL мы разобрали.

Мы с Вами рассмотрели много материала по SQL , в частности Transact-SQL, но мы не затрагивали такую, на самом деле простую тему как группировка данных GROUP BY . Поэтому сегодня мы научимся использовать оператор group by для группировки данных.

Многие начинающие программисты, когда сталкиваются с SQL, не знают о такой возможности как группировка данных с помощью оператора GROUP BY, хотя эта возможность требуется достаточно часто на практике, в связи с этим наш сегодняшний урок, как обычно с примерами, посвящен именно тому, чтобы Вам было проще и легче научиться использовать данный оператор, так как Вы с этим обязательно столкнетесь. Если Вам интересна тема SQL, то мы, как я уже сказал ранее, не раз затрагивали ее, например, в статьях Язык SQL – объединение JOIN или Объединение Union и union all , поэтому можете ознакомиться и с этим материалом.

И для вступления небольшая теория.

Что такое оператор GROUP BY

GROUP BY – это оператор (или конструкция, кому как удобней ) SQL для группировки данных по полю, при использовании в запросе агрегатных функций, таких как sum, max, min, count и других.

Как Вы знаете, агрегатные функции работают с набором значений, например sum суммирует все значения. А вот допустим, Вам необходимо просуммировать по какому-то условию или сразу по нескольким условиям, именно для этого нам нужен оператор group by, чтобы сгруппировать все данные по полям с выводом результатов агрегатных функций.

Как мне кажется, наглядней будет это все разобрать на примерах, поэтому давайте перейдем к примерам.

Примечание! Все примеры будем писать в Management Studio SQL сервера 2008.

Примеры использования оператора GROUP BY

И для начала давайте создадим и заполним тестовую таблицу с данными, которой мы будет посылать наши запросы select с использованием группировки group by. Таблица и данные конечно выдуманные, чисто для примера.

Создаем таблицу

Я ее заполнил следующими данными:

• Id –идентификатор записи;
• Name – фамилия сотрудника;
• Summa- денежные средства;
• Priz – признак денежных средств (допустим 1- Оклад; 2-Премия).

Группируем данные с помощью запроса group by

И в самом начале давайте разберем синтаксис group by , т.е. где писать данную конструкцию:

Синтаксис :

Select агрегатные функции

From источник

Where Условия отбора

Group by поля группировки

Having Условия по агрегатным функциям

Order by поля сортировки

Теперь если нам необходимо просуммировать все денежные средства того или иного сотрудника без использования группировки мы пошлем вот такой запрос:

SELECT SUM(summa)as summa FROM test_table WHERE name="Иванов"

А если нужно просуммировать другого сотрудника, то мы просто меняем условие. Согласитесь, если таких сотрудников много, зачем суммировать каждого, да и это как-то не наглядно, поэтому нам на помощь приходит оператор group by. Пишем запрос:

SELECT SUM(summa)as summa, name FROM test_table GROUP BY name

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

Примечание! Сразу отмечу то, что, сколько полей мы пишем в запросе (т.е. поля группировки), помимо агрегатных функций, столько же полей мы пишем в конструкции group by. В нашем примере мы выводим одно поле, поэтому в group by мы указали только одно поле (name), если бы мы выводили несколько полей, то их все пришлось бы указывать в конструкции group by (в последующих примерах Вы это увидите).

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

SELECT SUM(summa)as [Всего денежных средств], COUNT(*) as [Количество поступлений], Name [Сотрудник] FROM test_table GROUP BY name

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

SELECT SUM(summa)as [Всего денежных средств], COUNT(*) as [Количество поступлений], Name [Сотрудник] , Priz [Источник] FROM test_table GROUP BY name, priz ORDER BY name

Теперь у нас все отображается, т.е. сколько денег поступило сотруднику, сколько раз, а также из какого источника.

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

SELECT SUM(summa) AS [Всего денежных средств], COUNT(*) AS [Количество поступлений], Name [Сотрудник], CASE WHEN priz = 1 then "Оклад" WHEN priz = 2 then "Премия" ELSE "Без источника" END AS [Источник] FROM test_table GROUP BY name, priz ORDER BY name

Вот теперь все достаточно наглядно и не так уж сложно, даже для начинающих.

Также давайте затронем условия по итоговым результатам агрегатных функций (having ). Другими словами, мы добавляем условие не по отбору самих строк, а уже на итоговое значение функций, в нашем случае это sum или count. Например, нам нужно вывести все то же самое, но только тех, у которых «всего денежных средств» больше 200. Для этого добавим условие having:

SELECT SUM(summa)as [Всего денежных средств], COUNT(*) as [Количество поступлений], Name [Сотрудник], CASE WHEN priz = 1 then "Оклад" WHEN priz = 2 then "Премия" ELSE "Без источника" END AS [Источник] FROM test_table GROUP BY name, priz --группируем HAVING SUM(summa) > 200 --отбираем ORDER BY name -- сортируем

Теперь у нас вывелись все значения sum(summa), которые больше 200, все просто.

Надеюсь, после сегодняшнего урока Вам стало понятно, как и зачем использовать конструкцию group by . Удачи! А SQL мы продолжим изучать в следующих статьях.