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

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

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

SELECT DISTINCT vcName
FROM tbPeoples

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

SELECT count(*)
FROM tbPeoples
WHERE vcName='Андрей'

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

SELECT vcName, count(*)
FROM tbPeoples
GROUP BY vcName

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

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

АНДРЕЙ	5
БОЛИК	1
ВЛАД	1
ИВАН	3
ЛЕЛИК	1
СЕРГЕЙ	2
СЛАВИК	1
...

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

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

SELECT vcFamil, vcName, count(*)
FROM tbPeoples
GROUP BY vcFamil, vcName

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

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

SELECT vcName, count(*)
FROM tbPeoples
GROUP BY vcName
ORDER BY count(*) DESC

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

SELECT vcName, count(*) AS ct
FROM tbPeoples
GROUP BY vcName
ORDER BY ct DESC

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

SELECT vcSurName, count(*)
FROM tbPeoples
GROUP BY vcSurName

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

SELECT vcFamil, vcSurName, count(*)
FROM tbPeoples
GROUP BY vcSurName 

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

ИВАНОВ		ИВАНЫЧ
ПЕТРОВ		ПАЛЫЧ
СИДОРОВ	ПАЛЫЧ

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

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

SELECT pl.idPeoples, vcFamil, vcSurName, COUNT(vcPhoneNumber)
FROM tbPeoples pl, tbPhoneNumbers pn
WHERE pl.idPeoples *= pn.idPeoples
GROUP BY pl.idPeoples, vcFamil, vcSurName
ORDER BY COUNT(vcPhoneNumber) DESC

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

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

SELECT [Название товара], SUM(Количество)
FROM Товары
GROUP BY [Название товара]

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

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

SELECT [Название товара], SUM(Количество)
FROM Товары
GROUP BY [Название товара]
HAVING SUM(Количество)>1

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

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

SELECT vcFamil, count(*)
FROM tbPeoples
GROUP BY vcFamil
HAVING count(*)>1

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