что такое агрегирующие функции в sql

Агрегатные функции SQL

Здравствуйте! Сегодня мы познакомимся с агрегатными функциями в SQL, подробно разберем их работу с данными из таблиц, которые создавали в прошлых уроках.

Общее понятие

В прошлом уроке по оператору SELECT мы познакомились с тем, как строятся запросы к данным. Агрегатные функции же существуют для того, чтобы была возможность каким либо образом обобщить полученные данные, то есть манипулировать ими так, как нам это захочется.

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

Примеры агрегатных функций SQL

Мы разберем самые часто используемые функции и приведем несколько примеров.

Функция SUM

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

Получить сумму всех заказов из таблицы Orders, которые были совершены в 2016 году.

Можно было бы просто вывести сумму заказов, но мне кажется, что это совсем просто. Напомним структуру нашей таблицы:

Следующий код осуществит нужную выборку:

В результате получим:

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

Функция AVG

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

Вывести среднюю стоимость заказа из таблицы Orders.

В результате получим:

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

Функции MIN и MAX

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

Вывести максимальное и минимальное значения цены заказа, для тех заказов в которых цена менее 1000.

Получается такой запрос,

Также стоит сказать, что в отличие от предыдущих функций, эти 2 могут работать с символьными параметрами, то есть можно написать запрос типа MIN(odate) (в данном случае дата у нас символьная), и тогда нам вернется 2016-01-01.

Дело в том, что в этих функциях есть механизм преобразования символов в ASCII код, который потом они и сравнивают.

Еще одним важным моментом является то, что мы можем производить некоторые простые математические операции в запросе SELECT, например, такой запрос:

Вернет такой ответ:

Функция COUNT

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

Теперь разберем пример использования COUNT в SQL:

Подсчитать количество сделанных заказов и количество продавцов в таблице Orders.

Очевидно, что количество заказов — 10, но если вдруг у вас имеется большая таблица, то такая функция будет очень удобной. Что касается уникальных продавцов, то здесь необходимо использовать DISTINCT, потому что один продавец может обслужить несколько заказов.

Оператор GROUP BY

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

Вывести сумму всех заказов для каждого продавца по отдельности.

То есть теперь нам нужно для каждого продавца в таблице Orders выделить поля с ценой заказа и просуммировать. Все это сделает оператор GROUP BY в SQL достаточно легко:

Как видно, SQL выделил группу для каждого продавца и посчитал сумму всех их заказов.

Оператор HAVING

Этот оператор используется как дополнение к предыдущему. Он необходим для того, чтобы ставить условия для выборки данных при группировке. Если условие выполняется то выделяется группа, если нет — то ничего не произойдет. Рассмотрим следующий код:

Который создаст группу для продавца и посчитает сумму заказов этой группы, только в том случае, если максимальная сумма заказа больше 1000. Очевидно, что такой продавец только один, для него выделится группа и посчитается сумма всех заказов:

Казалось бы, почему тут не использовать условие WHERE, но SQL так построен, что в таком случае выдаст ошибку, и именно поэтому в SQL есть оператор HAVING.

Примеры на агрегатные функции в SQL

1. Напишите запрос, который сосчитал бы все суммы заказов, выполненных 1 января 2016 года.

2. Напишите запрос, который сосчитал бы число различных, отличных от NULL значений поля city в таблице заказчиков.

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

4. Напишите запрос, который бы выбирал заказчиков чьи имена начинаются с буквы Г.

5. Напишите запрос, который выбрал бы высший рейтинг в каждом городе.

Заключение

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

Если у вас остались вопросы, то задавайте их в комментариях.

Источник

Агрегатные функции SQL: полное руководство

Агрегатные функции SQL извлекают информацию о данных в базе данных. Например, вы можете использовать COUNT, чтобы узнать, сколько записей находится в базе данных. Допустимые агрегатные функции: COUNT, MIN, MAX, AVG и SUM.

Бывают случаи, когда вы можете захотеть получить информацию о данных, но не о самих данных. Возможно, вы хотите узнать, сколько сотрудников работает в каждом офисе или кто проработал в вашем бизнесе дольше всех. Здесь на помощь приходят агрегатные функции SQL.

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

Примечание. Для целей этой статьи мы используем PostgreSQL.

Агрегатные функции SQL

Агрегатные функции SQL собирают информацию о том, что находится в базе данных. Например, вы можете использовать СУММ, чтобы найти сумму всех значений в столбце. Функции агрегирования экономят ваше время, когда вам нужно найти информацию, которая включает агрегирование записей.,

Вот список агрегатных функций в SQL, которые вы можете использовать:

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

Агрегатные функции используются в начале оператора SQL SELECT.

Если вы запрашиваете и столбец, и агрегатную функцию в одном операторе SELECT, вы должны использовать предложение SQL GROUP BY. Это предложение сообщает SQL, как представлять данные в вашем запросе.

SQL COUNT

Функция SQL COUNT возвращает общее количество строк, возвращённых запросом. Используя оператор WHERE, функция COUNT возвращает количество строк, соответствующих вашему условию.

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

SELECT COUNT(name) FROM employees WHERE branch = «Stamford»;

Наш запрос возвращает количество сотрудников, работающих в филиале Стэмфорд:

SQL MIN и MAX

Функция SQL MIN возвращает наименьшее значение в столбце. Оператор SQL MAX возвращает наибольшее значение в столбце. Оба этих оператора являются агрегатными функциями SQL.

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

SELECT MIN(employee_month_awards) FROM employees;

Наш запрос возвращает:

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

SELECT MAX(employee_month_awards) FROM employees;

Результат нашего запроса выглядит следующим образом:

Следует отметить, что вы можете использовать функции MIN и MAX как для числовой, так и для буквенной информации в вашей базе данных.

Если вы хотите получить имя, которое появилось первым в алфавите из вашей базы данных, вы можете использовать MIN (имя). Точно так же MAX (имя) можно использовать для получения имени, которое идёт последним в алфавите.

SQL AVG

Функция SQL AVG возвращает среднее значение определённого столбца.

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

SELECT AVG(employee_month_awards) FROM employees;

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

SQL SUM

Функция SQL SUM находит общую сумму определённого столбца.

Предположим, вы хотите узнать, сколько наград работника месяца когда-либо было выдано. Эта информация легко доступна, если вы используете SQL-оператор SUM:

SELECT SUM(employee_month_awards) FROM employees;

Наш запрос возвращает следующее:

Вывод

Агрегатные функции SQL возвращают информацию о данных в базе данных. AVG, например, возвращает среднее значение в столбце базы данных. Всего существует пять агрегатных функций: MIN, MAX, COUNT, SUM и AVG.

Источник

Что такое агрегирующие функции в sql

Для выполнения агрегирующих операций используются следующие функции:

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

Общая форма агрегирующей функции имеет следующий вид:

SQL:
SELECT AVG(price) FROM tbl_service

В результате выполнения запроса получим одно значение:

С помощью следующего оператора можно найти среднюю стоимость всех услуг в случае удвоения их цены:

SQL:
SELECT AVG(price*2) FROM tbl_service

Если в столбце, к которому применяется агрегирующая функция, имеются нулевые (NULL) значения, то они просто игнорируются.

Исключением является функция COUNT(*), которая всегда подсчитывает общее количество строк, независимо от наличия в них нулевых значений.

SQL:
SELECT COUNT(retire_date) FROM tbl_contract

Поскольку поле retire_date содержит дату расторжения договора и значение NULL в случае, если договор не был расторгнут, функция COUNT не учитывает строки, содержащие значения NULL в поле retire_date.

SQL:
SELECT COUNT(*) FROM tbl_contract

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

Если условиям запроса не удовлетворяет ни одна строка, агрегирующие функции возвращают значение NULL. Исключение составляет функции COUNT, которая возвращает нуль.

Подсчитать количество клиентов мужского пола:

SQL:
SELECT COUNT(*) FROM tbl_clients WHERE sex=’m’

Источник

Что такое агрегирующие функции в sql

Агрегирующая функция может применяться ко всем записям БД слоя, к выборке по заданным условиям и, кроме того, возможно группирование записей слоя в несколько групп, и применение агрегирующей функции к каждой группе («Группировка записей»).

Общая запись агрегирующих функций:

([ DISTINCT ] )

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

Остальная часть запроса задается стандартным образом.

В языке SQL используются следующие агрегирующие функции:

SUM ([ DISTINCT ] )

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

AVG ([ DISTINCT ] )

Среднее значение для выражения. Выражение должно возвращать числовое значение.

COUNT ([ DISTINCT ] |*)

При используемом формате функции COUNT (*) возвращает общее количество записей в БД слоя.

MAX ( )

Возвращает максимальное значение выражения для выборки.

MIN ( )

Возвращает минимальное значение выражения из выборки.

Применение агрегирующих функций

Простой пример

Выводит сумму периметров зданий.

Одновременное применение нескольких функций

Выводит среднюю площадь здания и общее количество зданий.

Применение функций совместно с условиями отбора

Возвращает сумму площадей зданий расположенных на улице Нахимова.

Применение выражений в качестве аргументов агрегирующих функций

Для каждого здания рассчитывается величина равная Площадь/Периметр*2 и суммируется.

Применение агрегирующих функций в составе выражений

Использование ключевого слова DISTINCT

Возвращает количество разных названий улиц в БД слоя.

Источник

Агрегатные функции

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

В большинстве задач можно обойтись следующим джентельменским набором агрегатных функций:

Чтобы вызвать агрегатную функцию необходимо указать ее в списке выборки (после SELECT ). Если в SELECT использована хотя бы одна агрегатная функция, то значениями других столбцов могут быть только вызовы агрегатных функций, либо константы. Попробуем воспользоваться агрегатными функциями на следующей таблице:

При попытке вызова агрегатной функции вместе с выводом столбца таблицы в списке выборки возникнет ошибка:

И это логично. Агрегатная функция должна вернуть одно значение на весь набор данных (в нашем запросе одну строку), а какое значение product_id взять из всех строк? Помимо агрегатных функций есть еще аналитические функции, которые не изменяют количество строк в результате выборки, но при этом позволяют вычислять значения по набору строк. С ними мы познакомимся позднее.

Источник