Группировка и групповые вычисления

Group by (список полей)

Поля могут задаваться именем или порядковым номером поля в списке выборке.

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

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

Функции, определённые на группах (не записях), называют агрегатными. При использовании опции group by список выборки обязан состоять только из обращений к таким функциям.

К групповым функциям относятся (по определению):

· поля, входящие в список группировки, а также следующие функции:

· count (выражение над полями записи) – количество значений данного выражения в группе;

· count (*) – число записей в группе;

· min (выражение), max (выражение) – соответственно минимальное и максимальное значение заданного выражения для каждой группы; определены для значений произвольного скалярного типа;

· sum (выражение числового типа) – сумма значений по группе

· avg (выражение числового типа) – среднее арифметическое значение по группе.

Примечание. Обращение к агрегатным функциям возможно и без явной группировки, то есть использования опции group by; в этом случае группой считается вся таблица.

Пример. Для каждого покупателя подсчитать количество заказов.

select Cust_Ref , count (Id)

from Orders

group by Cust_Ref;

Не выдаст записи со значением null, то есть покупателей без заказов. В реальности синтаксис практически свободный.

Опция having употребляется только вместе с group by. Это предикат, ссылающийся на значения групповых функций.

Синтаксис: having (условие на группу)

Как правило, сравнение значений агрегатной функции с константой.

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

Пример. Вычислить среднее число покупок казанских покупателей – для достаточно крупных покупателей (то есть тех, у кого больше ста покупок).

select count(order.Id)

from Custoner, Orders

where (customer.Id=orders.Cust_Ref) and (customer.city=’Казань’)

group by Cust_Ref

having count(orders.Id)>100;