В данном посте посмотрим как работают некоторые агрегатные функции. Воспользуемся клиентом Mysql.exe – эта программа входит в инсталляционный пакет MySQL.
Что такое агрегатные функции?
Если нам необходимо, чтобы СУБД посчитала некоторую статистику, например сумму по полям, среднее и так далее, и выдала результат, который мы в дальнейшем можем где-то использовать, то для этого агрегатные функции и созданы. На многих сайтах есть описание этих функций, следующий блок, я взял с сайта sql.ru
* COUNT – производит номера строк или не-NULL значения полей которые выбрал запрос.
* SUM – производит арифметическую сумму всех выбранных значений данного пол.
* AVG – производит усреднение всех выбранных значений данного пол.
* MAX – производит наибольшее из всех выбранных значений данного пол.
* MIN – производит наименьшее из всех выбранных значений данного пол.
Как пользоваться агрегатными функциями?
Рассмотрим пример в программе Mysql.exe, которая входит в поставку вместе с сервером MySQL. Итак, поехали. Допустим у нас есть некоторая база данных. Я воспользовался одной из тех, которую создал ранее в программе MySQL WorkBench.
Для начала посмотрим на единственную таблицу данной базы – sales через запрос select*from sales;
Теперь применим агрегатную функцию MAX через запрос select Max(SalesAmount) from sales
Как видно, программа выдала единственное значение из ряда записей SalesAmount, равное 60 000;
Можно сделать и по другому… select Salesman, Max(SalesAmount) from sales
Итак, мы будем знать не просто максимум, но и у какого продавца. Запрос Select sum(salesamount) from sales даст сумму всех продаж…
Функцию Count используем для подсчета числа записей в поле, либо числа строк в таблице.
При этом, если добавить select count(distinct salesamount) from sales, то у нас удалятся в результатах повторяющиеся строки.
Напротив, если сделать select count(all salesamount) from sales, то у нас посчитаются все строки, включая дублирующие (за исключением NULL строк)
Count(*) в запросе select count(*) from sales даст нам число строк в таблице…
Скалярные выражения из агрегатных функций
Если нам необходимо посчитать значение, используя несколько полей и агрегатные функции, то это можно сделать, например таким образом
select (sum(salesamount)+sum(salesamount)) from sales
Group by
Здесь остановлюсь подробнее! Так как долго сам не мог понять смысл этого запроса, но нашел классное объяснение здесь.
Смысл в том, что если в результатах запроса есть повторяющиеся значения записей, то их можно сгруппировать (пример взял отсюда).
Пример SQL GROUP BY
Есть следующая таблица “Orders”:
| O_Id | OrderDate | OrderPrice | Customer |
|---|---|---|---|
| 1 | 2008/11/12 | 1000 | Hansen |
| 2 | 2008/10/23 | 1600 | Nilsen |
| 3 | 2008/09/02 | 700 | Hansen |
| 4 | 2008/09/03 | 300 | Hansen |
| 5 | 2008/08/30 | 2000 | Jensen |
| 6 | 2008/10/04 | 100 | Nilsen |
Теперь мы хотим найти общую сумму заказа для каждого клиента.
Выполним запрос:
|
|
Результат запроса:
| Customer | SUM(OrderPrice) |
|---|---|
| Hansen | 2000 |
| Nilsen | 1700 |
| Jensen | 2000 |
Теперь давайте посмотрим, что произойдет, если мы не используем запрос GROUP BY:
|
|
Результат запроса:
| Customer | SUM(OrderPrice) |
|---|---|
| Hansen | 5700 |
| Nilsen | 5700 |
| Hansen | 5700 |
| Hansen | 5700 |
| Jensen | 5700 |
| Nilsen | 5700 |
Итак, попробуем воспроизвести в mysql.exe, но для этого, предварительно воссоздадим таблицу Orders в программе workbench;
А вот и результат моего эксперимента. Всё как по часам.
Теперь тоже самое, без группировки. У меня получилось так (см. рис. ниже)
В последнем запросе не было смысла, поэтому программа выдала непонятно что. Посчитала сумму по всем заказам и приписала её первому покупателю.
Но смысл запроса с group by вполне понятен! Иными словами мы можем производить действия над подмножествами. Когда мы написали group by customer, мы как бы сказали – если есть одинаковые покупатели (customer), то просуммируй их цены покупок (OrderPrice). В результатах запроса – группы покупателей с одинаковыми значениями в поле записи customer.
Может быть не совсем ясно объяснил, но посмотрите ещё раз пример выше и разберетесь!
Having
Запрос Having это тоже самое, что запрос Where, но только для подмножеств. На примере, это будет выглядеть так…
select customer, sum(OrderPrice) from orders group by customer having sum(OrderPrice)>1800;
На этом данный пост закончу. В следующем посте попробую проделать аналогичные операции с агрегатными функциями из клиента Delphi.