3.2.3.1. Агрегатные функции без использования фразы group by.

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

Пример 12.

Выдать данные о блюде (Блюдо=10), указать количество ингредиентов и их суммарный вес:

SELECT COUNT(Продукт) as Количество, SUM(Вес) as Вес

Результат
Количество	Вес
5	360

FROM Состав

WHERE Блюдо = 10;

При выполнении запроса

SELECT Блюдо, COUNT(Продукт), SUM(Вес)

FROM Состав WHERE Блюдо = 10;

будет получено сообщение об ошибке:

Column 'Состав.Блюдо' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Это связано с тем, что агрегатная функция создает единственное значение из множества значений столбца-аргумента, а для "свободного" столбца должно быть выдано все множество его значений. Без специального указания (оно задается фразой GROUP BY) SQL не будет выяснять, одинаковы значения этого множества (как в данном примере, где Блюдо = 10) или различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос отвергается системой.

Правда, никто не запрещает дать запрос

SELECT 'Блюдо 10', COUNT(Продукт), SUM(Вес)

FROM Состав WHERE Блюдо = 10;

Отметим также, что в столбце-аргументе перед применением любой функции, кроме COUNT(*), исключаются все неопределенные значения. Если оказывается, что аргумент - пустое множество, функция COUNT принимает значение 0, а остальные - NULL.

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

SELECT ’01.02.2011’ as Дата,

COUNT( DISTINCT ID_заказ) as Кол_заказов,

COUNT( DISTINCT блюдо) as Кол_блюд,

SUM(Количество_порций) as Кол_порций

FROM Заказы

Результат
Дата	Кол_заказов	Кол_блюд	Кол_порций
01.02.2011	2	5	24

WHERE Дата=’10.02.2011’

и получить

Если в запросе указать дату, когда ресторан не работал

SELECT ’01.01.2011’ as Дата,

COUNT( DISTINCT ID_заказ) as Кол_заказов,

COUNT( DISTINCT блюдо) as Кол_блюд,

SUM(Количество_порций) as Кол_порций

FROM Заказы

WHERE Дата=’01.01.2011’

Результат
Дата	Кол_заказов	Кол_блюд	Кол_порций
01.01.2011	0	0	NULL

будет получен ответ:

3.2.3.2. Фраза group by.

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

Пример 13.

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

SELECT Дата,

COUNT( DISTINCT ID_заказ) as Кол_заказов,

COUNT( DISTINCT блюдо) as Кол_блюд,

SUM(Количество_порций) as Кол_порций

FROM Заказы

GROUP BY Дата;

Результат
Дата	Кол_заказов	Кол_блюд	Кол_порций
2011-01-02	2	5	24
2011-01-03	3	5	10
…

Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY. В рассматриваемом примере строки таблицы Заказы группируются так, что в одной группе содержатся все строки для заказов с Дата=’ 2011-01-02’, в другой – для заказов с Дата=’ 2011-01-03’ и т.д. Далее к каждой группе применяется фраза SELECT. Каждое выражение в этой фразе должно принимать единственное значение для группы, т.е. оно может быть либо значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой, либо одной из агрегатных функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме).

Если в запросе используются фразы WHERE и GROUP BY, то строки, не удовлетворяющие фразе WHERE, исключаются до выполнения группирования.

SELECT Дата,

COUNT( DISTINCT ID_заказ) as Кол_заказов,

COUNT( DISTINCT Блюдо) as Кол_блюд,

SUM(Количество_порций) as Кол_порций

FROM Заказы

WHERE Дата BETWEEN ’01.01.2011’ and ’01.31.2011’

GROUP BY Дата;

Отметим, что фраза GROUP BY не предполагает ORDER BY. Чтобы гарантировать упорядочение по дате результата рассматриваемого примера, следует дать запрос

SELECT Дата,

COUNT( DISTINCT ID_заказ) as Кол_заказов,

COUNT( DISTINCT Блюдо) as Кол_блюд,

SUM(Количество_порций) as Кол_порций

FROM Заказы

WHERE Дата BETWEEN ’01.01.2011’ and ’31.01.2011’

GROUP BY Дата

ORDER BY Дата;

Наконец, отметим, что строки таблицы можно группировать по любой комбинации ее столбцов.

Так, по запросу

SELECT Дата, Блюдо,

SUM(Количество_порций) as Кол_порций

FROM Заказы

WHERE Дата BETWEEN ’01.01.2011’ and ’01.31.2011’

GROUP BY Дата, Блюдо

ORDER BY Дата;

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

Результат
Дата	Блюдо	Кол_порций
2011-01-02	1	10
2011-01-02	14	4
2011-01-02	15	6
2011-01-02	27	2
2011-01-02	33	2
2011-01-03	2	3
2011-01-03	4	3
2011-01-03	15	2
2011-01-03	16	1
2011-01-03	25	1

Синтаксис фразы GROUP BY

GROUP BY [ALL] [ CUBE | ROLLUP] {[таблица.]столбец [, [таблица.]столбец] …}

позволяет указать следующие параметры

ALL – означает включение в результат выборки всех групп, независимо от того, соответствуют ли связанные с ним данные существующим в разделе WHERE условиям выборки. В строках не соответствующих условию выборки, во всех столбцах, кроме столбцов, по которым осуществляется группировка, будут выведены значения NULL.

Результат
Основа	Количество
Кофе	0
Крупа	0
Молоко	0
Мясо	0
Овощи	4
Рыба	0
Фрукты	0
Яйца	0

Пример 14.

SELECT Основа, COUNT(*) as Количество

FROM Блюда

WHERE Основа = 'Овощи'

GROUP BY ALL Основа

ROLLUP ( )

Формирует статистические строки простого предложения GROUP BY и строки подытогов или строки со статистическими вычислениями высокого уровня, а также строки общего итога.

Количество возвращаемых группирований равно количеству выражений в списке элементов GROPU BY плюс один.

Например, рассмотрим следующую инструкцию.

SELECT a, b, c, SUM( <expression> )

FROM T

GROUP BY ROLLUP(a, b, c)

Для каждого уникального сочетания значений (a, b, c), (a, b) и (a) формируется одна строка с подытогом. Вычисляется также строка общего итога.

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

Результат
Дата	Блюдо	Кол_порций
2011-01-02	1	10
2011-01-02	14	4
2011-01-02	15	6
2011-01-02	27	2
2011-01-02	33	2
2011-01-02	NULL	24
2011-01-03	2	3
2011-01-03	4	3
2011-01-03	15	2
2011-01-03	16	1
2011-01-03	25	1
2011-01-03	NULL	10
NULL	NULL	34

SELECT Дата, Блюдо,

SUM(Количество_порций) as Кол_порций

FROM Заказы

WHERE Дата BETWEEN

’01.01.2011’ and ’01.31.2011’

GROUP BY ROLLUP(Дата, Блюдо);

CUBE ( )

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

Выходные данные CUBE являются группированием для всех перестановок выражений в списке элементов GROPU BY.

Количество формируемых группирований равно (2ⁿ), где n — количество выражений в списке элементов GROPU BY . Например, рассмотрим следующую инструкцию.

SELECT a, b, c, SUM(<expression>)

FROM T

GROUP BY CUBE(a, b, c)

Формируется одна строка для каждого уникального сочетания значений (a, b, c), (a, b), (a, c), (b, c), (a), (b) и (c) с подытогом для каждой строки и строкой общего итога.

Выходные данные CUBE не зависят от порядка столбцов.

Результат
Дата	Блюдо	Кол_порций
2011-01-02	15	6
2011-01-03	15	2
NULL	15	8
2011-01-03	16	1
NULL	16	1
2011-01-03	25	1
NULL	25	1
2011-01-02	27	2
NULL	27	2
2011-01-02	33	2
NULL	33	2
NULL	NULL	14
2011-01-02	NULL	10
2011-01-03	NULL	4

Рассмотрим пример для нашей БД.

SELECT Дата, Блюдо,

SUM(Количество_порций) as Кол_порций

FROM Заказы

WHERE Блюдо >= 15

GROUP BY CUBE(Дата, Блюдо);