что такое агрегатные функции в 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 Aggregate Functions – With Example Data Queries for Beginners».
Если вы когда-либо работали с таблицами Excel или Google Sheets, агрегатные функции SQL покажутся вам знакомыми. Например, вам случалось использовать SUM при работе с таблицами? Функция SUM есть и в SQL. Она как раз относится к агрегатным функциям.
Агрегатные функции выполняют конкретные действия со строками таблиц.
Допустим, вы каждый год запускаете сбор денег на какие-то нужды. У вас есть база жертвователей, где хранятся их имена, адреса электронной почты и перечисленные суммы (по годам).
При помощи функции COUNT вы можете определить, сколько всего пожертвований было сделано. А при помощи SUM можно вычислить общую сумму денег, которую удалось собрать в этом году.
В этой статье мы рассмотрим следующие агрегатные функции: COUNT, SUM, MIN/MAX и AVG. А для иллюстрации будем использовать небольшой набор данных:
| NAME | DONATION_2020 | DONATION_2021 | |
|---|---|---|---|
| Andrew Jones | ajones@someemail.com | 400 | 500 |
| Maria Rodriguez | maria77@someemail.com | 1000 | 350 |
| Gerry Ford | NULL | 25 | 25 |
| Isabella Munn | isamun91@someemail.com | 250 | NULL |
| Jennifer Ward | jjw1972@someemail.com | 2000 | 2300 |
| Rowan Parker | NULL | 5000 | 4000 |
Таблица donors
Функция COUNT
Функция COUNT возвращает количество строк. В самой простой форме COUNT подсчитывает общее количество строк в вашей таблице.
Вернется общее число жертвователей, в нашем случае это 6. Мы, конечно, и так видим, что их 6, но представьте, что таблица у нас очень большая.
Возможно, вам нужно сосчитать только какие-то определенные строки. Например, вывести число жертвователей, у которых указан адрес электронной почты.
Функция SUM
SUM — очень полезная агрегатная функция, с помощью которой можно складывать числовые значения в различных строках.
Поэтому в нашем примере при помощи функции SUM можно сложить все донаты всех жертвователей за определенный год, но не суммы, перечисленные каждым отдельным человеком за два года.
Функции MIN и MAX
Как вы наверняка догадываетесь, MIN и MAX используются для поиска минимального и максимального значений в определенном столбце базы данных.
Вернемся к нашему примеру. Допустим, нам нужно найти, каким было самое маленькое и самое большое пожертвование в 2021 году. Для этого мы можем запустить следующий запрос:
Обратите внимание, что здесь для возвращаемых столбцов мы добавили псевдонимы, при этом взяли их в кавычки. Если в вашем псевдониме нет пробелов, кавычки не обязательны, но у нас пробелы есть.
Любопытный факт: функции MIN и MAX можно использовать и для нечисловых значений.
MIN ищет самое маленькое число, букву, которая в алфавите стоит ближе всего к A, или самую раннюю дату. Функция MAX ищет самое большое число, букву, стоящую ближе всего к Z, или самую последнюю дату. Это очень полезная особенность!
Функция AVG
Чтобы получить средний размер пожертвований в 2020 году, можно запустить следующий запрос:
Итоги
Агрегатные функции SQL
Здравствуйте! Сегодня мы познакомимся с агрегатными функциями в SQL, подробно разберем их работу с данными из таблиц, которые создавали в прошлых уроках.
Общее понятие
В прошлом уроке по оператору SELECT мы познакомились с тем, как строятся запросы к данным. Агрегатные функции же существуют для того, чтобы была возможность каким либо образом обобщить полученные данные, то есть манипулировать ими так, как нам это захочется.
Эти функции выполняются с помощью ключевых слов, которые включаются в запрос SELECT, и о том, как они прописываются будет рассказано далее. Чтобы было понятно, вот некоторые возможности агрегатных функций в SQL:
Примеры агрегатных функций SQL
Мы разберем самые часто используемые функции и приведем несколько примеров.
Функция SUM
Эта функция позволяет просуммировать значения какого либо поля при запросе SELECT. Достаточно полезная функция, синтаксис которой довольно прост, как и всех других агрегатных функций в SQL. Для понимания сразу начнем с примера:
Получить сумму всех заказов из таблицы Orders, которые были совершены в 2016 году.
Можно было бы просто вывести сумму заказов, но мне кажется, что это совсем просто. Напомним структуру нашей таблицы:
| onum | amt | odate | cnum | snum |
|---|---|---|---|---|
| 1001 | 128 | 2016-01-01 | 9 | 4 |
| 1002 | 1800 | 2016-04-10 | 10 | 7 |
| 1003 | 348 | 2017-04-08 | 2 | 1 |
| 1004 | 500 | 2016-06-07 | 3 | 3 |
| 1005 | 499 | 2017-12-04 | 5 | 4 |
| 1006 | 320 | 2016-03-03 | 5 | 4 |
| 1007 | 80 | 2017-09-02 | 7 | 1 |
| 1008 | 780 | 2016-03-07 | 1 | 3 |
| 1009 | 560 | 2017-10-07 | 3 | 7 |
| 1010 | 900 | 2016-01-08 | 6 | 8 |
Следующий код осуществит нужную выборку:
В результате получим:
В данном запросе мы использовали функцию SUM, после которой в скобках нужно указать поле для суммирования. Затем мы указали условие в WHERE, которое отобрало строчки только с 2016 годом. На самом деле это условие можно записать по другому, но сейчас важнее агрегатная функция суммирования в SQL.
Функция AVG
Следующая функция осуществляет подсчет среднего арифметического поля данных, которое мы укажем в качестве параметра. Синтаксис такой функции идентичен функции суммирования. Поэтому сразу перейдем к простейшей задаче:
Вывести среднюю стоимость заказа из таблицы Orders.
В результате получим:
В целом, все похоже на предыдущую функцию. И синтаксис достаточно прост. В этом и состоит особенность языка SQL — быть понятным для человека.
Функции MIN и MAX
Еще 2 функции, которые близки по своему действию. Они находят минимальное или максимальное значение соответственно того параметра, который будет передан в скобках. Синтаксис повторяется и поэтому следующий пример:
Вывести максимальное и минимальное значения цены заказа, для тех заказов в которых цена менее 1000.
Получается такой запрос,
| MAX(amt) | MIN(amt) |
|---|---|
| 900 | 80 |
Также стоит сказать, что в отличие от предыдущих функций, эти 2 могут работать с символьными параметрами, то есть можно написать запрос типа MIN(odate) (в данном случае дата у нас символьная), и тогда нам вернется 2016-01-01.
Дело в том, что в этих функциях есть механизм преобразования символов в ASCII код, который потом они и сравнивают.
Еще одним важным моментом является то, что мы можем производить некоторые простые математические операции в запросе SELECT, например, такой запрос:
Вернет такой ответ:
Функция COUNT
Эта функция необходима для того, чтобы подсчитать количество выбранных значений или строк. Существует два основных варианта ее использования:
Теперь разберем пример использования COUNT в SQL:
Подсчитать количество сделанных заказов и количество продавцов в таблице Orders.
| COUNT(*) | COUNT(snum) |
|---|---|
| 10 | 5 |
Очевидно, что количество заказов — 10, но если вдруг у вас имеется большая таблица, то такая функция будет очень удобной. Что касается уникальных продавцов, то здесь необходимо использовать DISTINCT, потому что один продавец может обслужить несколько заказов.
Оператор GROUP BY
Теперь рассмотрим 2 важных оператора, которые помогают расширить функционал наших запросов в SQL. Первым из них является оператор GROUP BY, который осуществляет группировку по какому либо полю, что иногда является необходимым. И уже для этой группы производит заданное действие. Например:
Вывести сумму всех заказов для каждого продавца по отдельности.
То есть теперь нам нужно для каждого продавца в таблице Orders выделить поля с ценой заказа и просуммировать. Все это сделает оператор GROUP BY в SQL достаточно легко:
| snum | Сумма всех заказов |
|---|---|
| 1 | 428 |
| 3 | 1280 |
| 4 | 947 |
| 7 | 2360 |
| 8 | 900 |
Как видно, SQL выделил группу для каждого продавца и посчитал сумму всех их заказов.
Оператор HAVING
Этот оператор используется как дополнение к предыдущему. Он необходим для того, чтобы ставить условия для выборки данных при группировке. Если условие выполняется то выделяется группа, если нет — то ничего не произойдет. Рассмотрим следующий код:
Который создаст группу для продавца и посчитает сумму заказов этой группы, только в том случае, если максимальная сумма заказа больше 1000. Очевидно, что такой продавец только один, для него выделится группа и посчитается сумма всех заказов:
| snum | Сумма всех заказов |
|---|---|
| 7 | 2360 |
Казалось бы, почему тут не использовать условие WHERE, но SQL так построен, что в таком случае выдаст ошибку, и именно поэтому в SQL есть оператор HAVING.
Примеры на агрегатные функции в SQL
1. Напишите запрос, который сосчитал бы все суммы заказов, выполненных 1 января 2016 года.
2. Напишите запрос, который сосчитал бы число различных, отличных от NULL значений поля city в таблице заказчиков.
3. Напишите запрос, который выбрал бы наименьшую сумму для каждого заказчика.
4. Напишите запрос, который бы выбирал заказчиков чьи имена начинаются с буквы Г.
5. Напишите запрос, который выбрал бы высший рейтинг в каждом городе.
Заключение
На этом мы будем заканчивать. В этой статье мы познакомились с агрегатными функциями в SQL. Разобрали основные понятия и базовые примеры, которые могут пригодиться далее.
Если у вас остались вопросы, то задавайте их в комментариях.
Группировка
Агрегатные функции
Агрегатные функции выполняют вычисления над значениями в наборе строк. В T-SQL имеются следующие агрегатные функции:
AVG : находит среднее значение
SUM : находит сумму значений
MIN : находит наименьшее значение
MAX : находит наибольшее значение
COUNT : находит количество строк в запросе
В качестве аргумента все агрегатные функции принимают выражение, которое представляет критерий дя определения значений. Зачастую, в качестве выражения выступает название столбца, над значениями которого надо проводить вычисления.
Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL.
Функция Avg возвращает среднее значение на диапазоне значений столбца таблицы.
Пусть в базе данных у нас есть таблица товаров Products, которая описывается следующими выражениями:
Найдем среднюю цену товаров из базы данных:
Для поиска среднего значения в качестве выражения в функцию передается столбец Price. Для получаемого значения устанавливается псевдоним Average_Price, хотя можно его и не устанавливать.
Также мы можем применить фильтрацию. Например, найти среднюю цену для товаров какого-то определенного производителя:
И, кроме того, мы можем находить среднее значение для более сложных выражений. Например, найдем среднюю сумму всех товаров, учитывая их количество:
Count
Функция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:
Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:
Min и Max
Функции Min и Max возвращают соответственно минимальное и максимальное значение по столбцу. Например, найдем минимальную цену среди товаров:
Поиск максимальной цены:
Данные функции также игнорируют значения NULL и не учитывают их при подсчете.
Функция Sum вычисляет сумму значений столбца. Например, подсчитаем общее количество товаров:
Также вместо имени столбца может передаваться вычисляемое выражение. Например, найдем общую стоимость всех имеющихся товаров:
All и Distinct
Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.
Что такое агрегатные функции в sql
Агрегатные функции получают единственный результат из набора входных значений. Встроенные агрегатные функции общего назначения перечислены в Таблице 9.55, а статистические агрегатные функции — в Таблице 9.56. Встроенные внутригрупповые сортирующие агрегатные функции перечислены в Таблице 9.57, встроенные внутригрупповые гипотезирующие — в Таблице 9.58. Группирующие операторы, тесно связанные с агрегатными функциями, перечислены в Таблице 9.59. Особенности синтаксиса агрегатных функцией разъясняются в Подразделе 4.2.7. За дополнительной вводной информацией обратитесь к Разделу 2.7.
Таблица 9.55. Агрегатные функции общего назначения
Агрегатные функции, поддерживающие частичный режим, являются кандидатами на участие в различных оптимизациях, например, в параллельном агрегировании.
Примечание
Здесь ANY можно рассматривать и как объявление подзапроса, и как агрегатную функцию, если этот подзапрос возвращает одну строку с булевым значением. Таким образом, этим агрегатным функциям нельзя было дать стандартные имена.
Примечание
потребует затрат в количестве, пропорциональном размеру таблицы: PostgreSQL придётся полностью просканировать либо всю таблицу, либо один из индексов, включающий все её строки.
Но учтите, что этот подход может не работать, если на внешнем уровне запроса выполняется дополнительная обработка, например, соединение, так как при этом результат подзапроса может быть переупорядочен перед вычислением агрегатной функции.
В Таблице 9.56 перечислены агрегатные функции, обычно применяемые в статистическом анализе. (Они выделены просто для того, чтобы не загромождать список наиболее популярных агрегатных функций.) В их описании под N подразумевается число входных строк, для которых входные выражения не равны NULL. Все эти функции возвращают NULL во всех случаях, когда вычисление бессмысленно, например, когда N равно 0.
Таблица 9.56. Агрегатные функции для статистических вычислений
Таблица 9.57. Сортирующие агрегатные функции
| Функция | Тип непосредственного аргумента | Тип агрегированного аргумента | Тип результата | Частичный режим | Описание |
|---|---|---|---|---|---|
| mode() WITHIN GROUP (ORDER BY выражение_сортировки ) | любой сортируемый тип | тот же, что у выражения сортировки | Нет | возвращает значение, наиболее часто встречающееся во входных данных (если одинаково часто встречаются несколько значений, произвольно выбирается первое из них) | |
| percentile_cont( дробь ) WITHIN GROUP (ORDER BY выражение_сортировки ) | double precision | double precision или interval | тот же, что у выражения сортировки | Нет | непрерывный процентиль: возвращает значение, соответствующее заданной дроби по порядку, интерполируя соседние входные значения, если необходимо |
| percentile_cont( дроби ) WITHIN GROUP (ORDER BY выражение_сортировки ) | double precision[] | double precision или interval | массив типа выражения сортировки | Нет | множественный непрерывный процентиль: возвращает массив результатов, соответствующих форме параметра дроби (для каждого элемента не NULL подставляется значение, соответствующее данному процентилю) |
| percentile_disc( дробь ) WITHIN GROUP (ORDER BY выражение_сортировки ) | double precision | любой сортируемый тип | тот же, что у выражения сортировки | Нет | дискретный процентиль: возвращает первое значение из входных данных, позиция которого по порядку равна или превосходит указанную дробь |
| percentile_disc( дроби ) WITHIN GROUP (ORDER BY выражение_сортировки ) | double precision[] | любой сортируемый тип | массив типа выражения сортировки | Нет | множественный дискретный процентиль: возвращает массив результатов, соответствующих форме параметра дроби (для каждого элемента не NULL подставляется входное значение, соответствующее данному процентилю) |
Таблица 9.58. Гипотезирующие агрегатные функции
| Функция | Тип непосредственного аргумента | Тип агрегированного аргумента | Тип результата | Частичный режим | Описание |
|---|---|---|---|---|---|
| rank( аргументы ) WITHIN GROUP (ORDER BY сортированные_аргументы ) | VARIADIC «any» | VARIADIC «any» | bigint | Нет | ранг гипотетической строки, с пропусками повторяющихся строк |
| dense_rank( аргументы ) WITHIN GROUP (ORDER BY сортированные_аргументы ) | VARIADIC «any» | VARIADIC «any» | bigint | Нет | ранг гипотетической строки, без пропусков |
| percent_rank( аргументы ) WITHIN GROUP (ORDER BY сортированные_аргументы ) | VARIADIC «any» | VARIADIC «any» | double precision | Нет | относительный ранг гипотетической строки, от 0 до 1 |
| cume_dist( аргументы ) WITHIN GROUP (ORDER BY сортированные_аргументы ) | VARIADIC «any» | VARIADIC «any» | double precision | Нет | относительный ранг гипотетической строки, от 1/ N до 1 |
Таблица 9.59. Операции группировки
| Функция | Тип результата | Описание |
|---|---|---|
| GROUPING( аргументы. ) | integer | Целочисленная битовая маска, показывающая, какие аргументы не вошли в текущий набор группирования |
Операции группировки применяются в сочетании с наборами группирования (см. Подраздел 7.2.4) для различения результирующих строк. Аргументы операции GROUPING на самом деле не вычисляются, но они должны в точности соответствовать выражениям, заданным в предложении GROUP BY на их уровне запроса. Биты назначаются справа налево (правый аргумент отражается в младшем бите); бит равен 0, если соответствующее выражение вошло в критерий группировки набора группирования, для которого сформирована строка результата, или 1 в противном случае. Например: