какие колонки можно использовать в секции select если была реализована группировка через group by

Оператор SQL GROUP BY для группировки в запросах

Оператор GROUP BY имеет следующий синтаксис:

Группировка по одному столбцу без агрегатных функций

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

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

В примерах работаем с базой данных библиотеки и ее таблицей «Книга в пользовании» (Bookinuse). Отметим, что оператор GROUP BY ведёт себя несколько по-разному в MySQL и в MS SQL Server. Эти различия будут показаны на примерах.

Пример 1. Вывести авторов выданных книг, сгруппировав их. Пишем следующий запрос:

Этот запрос вернёт следующий результат:

Как видим, в таблице стало меньше строк, так как фамилии авторов остались каждая по одной.

В следующем примере увидим, что оператор GROUP BY не следует путать с оператором ORDER BY и поймём, чем эти операторы отличаются друг от друга.

Пример 2. Вывести авторов и названия выданных книг, сгруппировав по авторам. Пишем следующий запрос, который допустим в MySQL:

Этот запрос вернёт следующий результат:

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

Если бы нам требовалось вывести все книги, причём авторы должны были бы следовать не «вразброс», а по порядку: сначала Гоголь и все его книги, затем другие авторы и все их книги, то мы применили бы не оператор GROUP BY, а оператор ORDER BY.

Группировка по нескольким столбцам без агрегатных функций

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

Пример 3. Вывести авторов, названия выданных книг, ID пользователя и инвентарный номер выданной книги. Сгруппировать по авторам, ID пользователя и инвентарному номеру. На MySQL запрос будет следующим:

Этот запрос вернёт следующий результат:

По-другому ведёт себя оператор GROUP BY в MS SQL Server и в случае этого запроса.

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

Пример 4. Вывести количество выданных книг каждого автора. Запрос будет следующим:

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

Пример 5. Вывести количество книг, выданных каждому пользователю. Запрос будет следующим:

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

Примеры запросов к базе данных «Библиотека» есть также в уроках по оператору IN, предикату EXISTS и функциям CONCAT, COALESCE.

Источник

2.12. Группировка – Group By

Очень интересных эффектов можно добиться, если использовать математику вместе с GROP_BY. Что если нужно посчитать, количество различных имен в таблице tbPeoples. Как же это можно сделать?

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

После этого нужно определить количество каждого имени в таблице. Например, количество Андреев можно узнать следующим образом:

Но это сложно и требует ручного вмешательства. Конечно же, можно было бы использовать подзапросы для определения количества без вмешательства, но это будет сложно, и подзапросы мы еще не рассматривали. Самое простое решение кроется как раз в операторе GROUP BY. Рассмотрим эту возможность на примере:

Оператор GROUP BY группирует записи по указанным после оператора через запятую именам колонок. После оператора SELECT нужно перечислить те же имена колонок и математическую функцию, которую вы хотите использовать. В данном случае используется функция COUNT для подсчета количества строк в группе. Итак, в нашей таблице несколько Андреев, в запросе они объединяются в группу и в результате выводиться на экран количество строк в этой группе.

Результат выполнения запроса:

В первой колонке показано имя работника, а во второй колонке количество записей с таким именем.

Посмотрим еще пример, давайте посчитаем, сколько раз встречаются в таблице одинаковые записи в поля имени и фамилии

Результат – количество повторений из сочетания полей фамилия и имя. В моей тестовой таблице содержимое этих двух полей образуют уникальное значение, поэтому в колонке количества будет всегда единица.

Прежде чем рассматривать еще примеры, давайте узнаем, как можно сортировать строки по колонке количества записей:

В операторе ORDER BY без проблем можно писать функции. Чтобы сценарий был более красивым, лучше будет задать псевдоним для поля количества записей:

Чтобы лучше понять работу этого оператора, необходимо рассмотреть еще несколько примеров. Я сам не сразу же понял, как ей пользоваться, поэтому постараюсь вам показать максимум разных запросов, чтобы вы на практике увидели смысл их работы. Следующий запрос определяет количество повторений фамилий:

Обратите внимание, что поля, которые указываются в группировке, обязательно присутствуют в операторе SELECT. Другие поля там не могут присутствовать. Например, следующий запрос будет неверен:

Чтобы лучше понять почему, давайте разберем его работу. Допустим, что у нас есть таблица из двух колонок – фамилии и отчества:

Во время группировки по отчеству, вторая и третья строка должны восприниматься как одно целое, но какую из двух фамилий вывести в результате: Петров или Сидоров? Вот из-за этого в разделе SELECT должны быть только те поля, по которым происходит группировка.

Давайте посмотрим пример связанных таблиц. Допустим, что нам нужно определить, количество номеров телефонов для каждого пользователя. В этом случае, должно быть подсчитано, сколько записей в таблице tbPhoneNumbers соответствует каждой записи в таблице tbPeoples. Лучше будет сгруппировать по первичному ключу таблицы tbPeoples, потому что он обеспечивает уникальность строк, для которых нужно определить количество записей в другой таблице. Так как у нас группировка происходит по одной таблице, а количество считается по другой таблице, в группировку можно добавлять любые поля помимо ключевого. Например:

Рассмотрим этот запрос. Я решил вывести на экран помимо первичного ключа еще и фамилию и имя. Все эти поля перечислены в разделе SELECT и GROUP BY. Можно взять и другие поля из таблицы tbPeoples, но только из этой таблицы. В разделе WHERE наводиться связь между таблицами, а в разделе ORDER BY мы сортируем количество найденных телефонов.

С помощью GROUP BY можно не только определять количество записей с помощью оператора COUNT, но и суммы. Вспомним, что в нашей базе данных есть еще таблица товаров из следующих полей: Дата покупки, Название товара, Цена, Количество. Давайте сгруппируем таблицу по названию и определим количество каждого товара:

В этом примере с помощью группировки мы определили сумму по колонке с помощью оператора SUM.

С помощью секции HAVING очень удобно ограничивать вывод. Например, вам нужно вывести сумму количества товаров, но при этом должны отражаться только те записи, в которых количество более 1. Просто GROUP BY тут уже не поможет. Нужно добавить секцию HAVING, с нужным условием:

В секции HAVING мы написали, что сумма товара (SUM(Количество)) должна быть более 1.

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

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

Источник

SELECT — GROUP BY (Transact-SQL)

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

Синтаксис

Синтаксические обозначения в Transact-SQL (Transact-SQL)

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

column-expression

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

Допустимые выражения см. в разделе expression.

Столбец должен быть указан в предложении FROM инструкции SELECT, но не обязательно должен присутствовать в списке SELECT. Каждый столбец таблицы или представления в любом нестатистическом выражении в списке должен быть включен в список GROUP BY.

Следующие инструкции являются допустимыми.

Следующие инструкции не являются допустимыми.

Выражение столбца не может содержать:

Группирует результаты инструкции SELECT в соответствии со значениями в списке одного или нескольких выражений столбцов.

Например, этот запрос создает таблицу Sales со столбцами Country, Region и Sales. Он вставляет четыре строки, и две строки имеют совпадающие значения для столбцов Country и Region.

В таблице Sales содержатся указанные далее строки.

Этот запрос группирует значения столбцов Country и Region и возвращает общую сумму по каждому сочетанию значений.

Результат запроса содержит 3 строки, так как существует 3 сочетания значений для Country и Region. Значение TotalSales для Canada и British Columbia является суммой двух строк.

GROUP BY ROLLUP

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

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

Например, GROUP BY ROLLUP (col1, col2, col3, col4) создает группы для каждой комбинации выражений столбцов в следующих списках.

Принимая во внимание таблицу из предыдущего примера, этот код выполняет операцию GROUP BY ROLLUP вместо простого предложения GROUP BY.

Результатом запроса являются те же статистические вычисления, что и в простом предложении GROUP BY без ROLLUP. Кроме того, здесь создаются промежуточные итоги для каждого значения в столбце Country. Наконец, выводится общий итог для всех строк. Результат имеет следующий вид:

GROUP BY CUBE ( )

GROUP BY CUBE создает группы для всех возможных сочетаний столбцов. Для GROUP BY CUBE (a, b) результатами являются группы для уникальных значений (a, b) (NULL, b), (a, NULL) и (NULL, NULL).

Принимая во внимание таблицу из предыдущего примера, этот код выполняет операцию GROUP BY CUBE по столбцам Country и Region.

Результатом запроса являются группы для уникальных значений (Country, Region), (NULL, Region), (Country, NULL) и (NULL, NULL). Результат выглядит следующим образом:

GROUP BY GROUPING SETS ( )

Параметр GROUPING SETS позволяет объединять несколько предложений GROUP BY в одно предложение GROUP BY. Результаты эквивалентны тем, что формируются с применением конструкции UNION ALL к указанным группам.

Например, GROUP BY ROLLUP (Country, Region) и GROUP BY GROUPING SETS ( ROLLUP (Country, Region) ) возвращают одинаковые результаты.

Если параметр GROUPING SETS имеет два или более элементов, результатом будет объединение элементов. Этот пример возвращает объединение результатов ROLLUP и CUBE для Country и Region.

Результаты будут такими же, так как этот запрос возвращает объединение двух инструкций GROUP BY.

SQL не консолидирует повторяющиеся группы, созданные для списка GROUPING SETS. Например, в GROUP BY ( (), CUBE (Country, Region) ) оба элемента возвращают строку для общего итога, и в списке результатов будут указаны обе строки.

GROUP BY ()

Указывает пустую группу, что приводит к созданию общего итога. Он полезен в качестве одного из элементов GROUPING SET. Например, эта инструкция выводит общий объем продаж для каждой страны, а затем — общий итог по всем странам.

Область применения: SQL Server и база данных SQL Azure

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

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

Область применения: SQL Server и база данных SQL Azure

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

WITH (DISTRIBUTED_AGG)

Область применения: Azure Synapse Analytics и Система платформы аналитики (PDW)

Указание запроса DISTRIBUTED_AGG заставляет систему MPP перераспределять таблицу по определенному столбцу до выполнения статистического вычисления. Только один столбец в предложении GROUP BY может иметь указание запроса DISTRIBUTED_AGG. После завершения запроса перераспределенная таблица удаляется. Исходная таблица не изменяется.

Примечание. Указание запроса DISTRIBUTED_AGG предоставляется для обеспечения обратной совместимости с более ранними версиями Система платформы аналитики (PDW) и не улучшает производительность большинства запросов. По умолчанию MPP уже перераспределяет данные для улучшения производительности для статистических вычислений.

Общие замечания

Взаимодействие GROUP BY с инструкцией SELECT

Предложение ORDER BY:

Ограничения

Область применения: SQL Server (начиная с версии 2008) и Azure Synapse Analytics

Максимальная емкость

Для предложения GROUP BY, использующего ROLLUP, CUBE или GROUPING SETS, используется максимум 32 выражения. Максимальное количество групп — 4096 (2 12 ). Следующие примеры завершаются ошибкой, поскольку предложение GROUP BY имеет больше 4096 групп.

В следующем примере формируется 4097 (2 12 + 1) группирующих наборов, поэтому пример завершится ошибкой.

В этом примере используется синтаксис обратной совместимости. В примере создается 8192 (2 13 ) группирующих наборов, поэтому он завершится ошибкой.

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

Поддержка функций предложения GROUP BY, совместимых с ISO и ANSI SQL-2006

Предложение GROUP BY поддерживает все возможности предложения GROUP BY, включенные в стандарт SQL-2006, со следующими синтаксическими исключениями.

GROUP BY [ALL/DISTINCT] используется только в простом предложении GROUP BY, которое содержит выражения столбцов. Это предложение не может использоваться с конструкциями GROUPING SETS, ROLLUP, CUBE, WITH CUBE или WITH ROLLUP. Ключевое слово ALL применяется по умолчанию и задано неявно. Оно допускается только в синтаксисе обратной совместимости.

Сравнение поддерживаемых функций предложения GROUP BY

В следующей таблице описаны поддерживаемые возможности предложения GROUP BY с учетом версии SQL и уровня совместимости базы данных.

Например: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);

Выдается следующее сообщение об ошибке: «Применение неверного синтаксиса недалеко от ключевого слова ‘cube’|’rollup'».

Например: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);GROUPING SETSНе поддерживаетсяПоддерживаетсяПоддерживаетсяCUBEНе поддерживаетсяПоддерживаетсяНе поддерживаетсяROLLUPНе поддерживаетсяПоддерживаетсяНе поддерживаетсяОбщий итог, такой как GROUP BY ()Не поддерживаетсяПоддерживаетсяПоддерживаетсяФункция GROUPING_IDНе поддерживаетсяПоддерживаетсяПоддерживаетсяФункция GROUPINGПоддерживаетсяПоддерживаетсяПоддерживаетсяWITH CUBEПоддерживаетсяПоддерживаетсяПоддерживаетсяWITH ROLLUPПоддерживаетсяПоддерживаетсяПоддерживаетсяУдаление «повторяющегося» группирования с помощью конструкции WITH CUBE или WITH ROLLUPПоддерживаетсяПоддерживаетсяПоддерживается

Примеры

A. Использование простого предложения GROUP BY

Б. Использование предложения GROUP BY с несколькими таблицами

В. Использование предложения GROUP BY в выражениях

Г. Использование предложения GROUP BY с предложением HAVING

Примеры: Azure Synapse Analytics и Parallel Data Warehouse

Д. Базовое использование предложения GROUP BY

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

Е. Базовое использование указания DISTRIBUTED_AGG

В этом примере показано указание запроса DISTRIBUTED_AGG для принудительного перемещения в таблице по столбцу CustomerKey перед выполнением статистического вычисления.

Ж. Варианты синтаксиса для GROUP BY

Если в списке Select статистические вычисления, каждый столбец в списке Select должен быть включен в список GROUP BY. Вычисляемые столбцы в списке Select можно указать в списке GROUP BY (делать это необязательно). Ниже приведены примеры синтаксически правильных инструкций SELECT.

З. Использование GROUP BY с несколькими выражениями GROUP BY

И. Использование предложения GROUP BY с предложением HAVING

Источник