Итоговые данные

10. Итоговые данные

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

10.1.  Создание групп - GROUP BY

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

Например, подсчитаем число продуктов, предлагаемых поставщиком DLL01.

SELECT COUNT (*) AS num_prods

FROM Products

WHERE vend_id=’DLL01’

--------------------------

Рекомендуемые материалы

num_prodst

4

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

SELECT vend_id, COUNT (*) AS num_prods

FROM Products

GROUP BY vend_id

--------------------------

num_prodst        num_prods

BRS01                3

DLL01                4

FNG01               2

Предложение GROUP BY указывает СУБД группировать данные и затем выполнять вычисление по каждой группе, а не по всему набору результатов. В примере поставщики сгруппированы по столбцу vend_id и по каждой группе подсчитано число продуктов.

При использовании предложения  GROUP BY необходимо руководствоваться следующими правилами:

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

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

· Каждый столбец, указанный в предложении GROUP BY, должен быть столбцом выборки или выражением, но не функцией группирования. Если в операторе SELECT используется какое-то выражение, то же самое выражение должно быть указано в предложении GROUP BY, псевдонимы применять нельзя.

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

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

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

· Предложение GROUP BY должно следовать после предложения WHERE и до какого-либо предложения GROUP BY.

10.2.  Фильтрующие группы HAVING

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

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

Рассмотрим пример.

SELECT cust_id, COUNT (*) AS orders

FROM Orders

GROUP BY cust_id

--------------------------

cust_id                orders

1000000001        2

1000000003        1

1000000004        1

1000000005        1

Получаем 4 группы. Добавим предложение HAVING COUNT(*) >= 2, которое должно фильтровать эти группы. Предложение WHERE здесь не подойдет, поскольку фильтрация основана на итоговом значении групп, а не на значениях указанных строк.

SELECT cust_id, COUNT (*) AS orders

FROM Orders

GROUP BY cust_id

HAVING COUNT(*) >= 2;

--------------------------

cust_id                orders

1000000001        2

Основное отличие: WHERE фильтрует до того, как данные будут сгруппированы, а HAVING фильтрует после того, как данные были сгруппированы.

Допускается использовать как предложение WHERE, так и предложения HAVING в одном операторе SELECT.

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

SELECT vend_id, COUNT (*) AS num_prods                                   выбор полей

FROM Products                                                                        из таблицы

WHERE prod_price > 4                                                            фильтр строк

GROUP BY vend_id                                                                группировка

HAVING COUNT(*) >= 2;                                                     фильтр групп

--------------------------

vend_id              num_prods

BRS01                3

FNG01               2

Если убрать предложение WHERE, то будет получена еще один поставщик, предлагающий 4 продукта.

SELECT vend_id, COUNT (*) AS num_prods                                   выбор полей

FROM Products                                                                        из таблицы

GROUP BY vend_id                                                                группировка

HAVING COUNT(*) >= 2;                                                     фильтр групп

--------------------------

vend_id              num_prods

BRS01                3

DLL01                4

FNG01               2

10.3.  Группировка и сортировка

Предложения GROUP BY и ORDER BY отличаются в следующем:

Когда используется предложение             GROUP BY, приходится указывать и предложение             ORDER BY, это – единственный способ, гарантирующий, что данные будут отсортированы правильно, т.к. не следует надеяться на то, что данные отсортирует предложение GROUP BY.

В примере требуется определить в таблице OrderItems номера заказов, у которых количество заказанных предметов больше или равно 3,                   

SELECT order_num, COUNT(*) AS items

FROM OrderItems

GROUP BY order_num

HAVING COUNT(*) >= 3;

--------------------------

order_num          items

20006              3

20007              5

20008              5

20009              3

Чтобы отсортировать результат по количеству заказанных предметов, необходимо добавить предложение  ORDER BY.

SELECT order_num, COUNT(*) AS items

FROM OrderItems

GROUP BY order_num

HAVING COUNT(*) >= 3

ORDER BY items, order_num;

--------------------------

order_num          items

20006              3

20009              3

20007              5

20008              5

СУБД Access не позволяет осуществлять сортировку по псевдонимам, и этот пример работать не будет. Необходимо в предложении ORDER BY значение столбца items заменить вычисляемым выражением  

ORDER BY COUNT(*), order_num

или номером поля

ORDER BY 1, order_num

В этом примере предложение GROUP BY группирует данные по номеру заказа (столбец order_num), функция COUNT(*) возвращает количество предметов в каждом заказе. Предложение HAVING фильтрует данные таким образом, что возвращаются только заказы с тремя и более предметами. Результат сортируется за счет использования предложения ORDER BY.

10.4.  Упорядочение предложения SELECT

Предложения оператора SELECT указываются в следующей последовательности: