Какие агрегатные функции можно использовать в операторе select
Перейти к содержимому

Какие агрегатные функции можно использовать в операторе select

  • автор:

Агрегатные функции (Transact-SQL)

Агрегатная функция выполняет вычисление на наборе значений и возвращает одиночное значение. Агрегатные функции, за исключением COUNT(*) , не учитывают значения NULL. Агрегатные функции часто используются в выражении GROUP BY инструкции SELECT.

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

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

  • Список выбора инструкции SELECT (вложенный или внешний запрос).
  • Предложение HAVING.

Transact-SQL предоставляет следующие агрегатные функции:

Geek Notes

заметки/статьи/переводы на темы программирования, алгоритмов и etc.

Выборка данных SQL. Операторы SELECT, in, BETWEEN, LIKE, GROUP by и HAVING

Mar 15 th , 2015 9:23 pm

В данном конспекте рассмотрим как производится выборка данных из базы с помощью оператора SELECT. Использование DISTINCT. Операторы сравнения и логические операторы применяемые в предложении WHERE. А также операторы IN, BETWEEN и LIKE. Агрегатные функции типа COUNT(), AVG() и т.д. И рассмотрим применение операторов GROUP BY и HAVING.

И так простейшая выборка данных осуществляется оператором SELECT:

Так как предложение WHERE фильтрует строки до того как они попадут в группы, т.е. до применения GROUP BY, то его использовать не возможно, если нам надо задать условие для всей группы. Для этого есть оператор HAVING, который похож на WHERE только задает фильтр сразу к группе.

Допустим, есть следующая таблица:

При выполнении следующего запроса, сначала все строки группируются по одинаковым сочетаиням значений в полях Snum и Odate, а затем, к каждой группе применяет опертор MAX():

1 2 3 4 
SELECT Snum, Odate, MAX( Amt ) FROM Orders GROUP BY Snum, Odate HAVING MAX( Amt ) > 3000.00 

Posted by fizikovnet Mar 15 th , 2015 9:23 pm sql

DeepEdit!

  • Увеличить размер шрифта
  • Размер шрифта по умолчанию
  • Уменьшить размер шрифта

— подсчет числа ненулевых значений в столбце stock таблицы stock.

2.2 Функции SUM(), AVG(), MIN(), MAX()

Синтаксис этих функций одинаков: имя_функции ( [DISTINCT] имя_столбца )

Эти функции находят сумму, среднее арифметическое, наибольшее или наименьшее

значение. Очевидно, что тип значений обрабатываемого столбца должен

соответствовать операции, т.е. быть числовым при суммировании или нахождении

среднего арифметического.

SELECT AVG( cost ) FROM stock; — определение средней цены книги

на складе ( stock ).

SELECT MAX( retail ), MIN( retail ) FROM stock; — определение диапазона розничных цен книг на складе.

Агрегатные функции не могут быть использованы во фразе условия WHERE, но с их

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

формулировке условия в запросе. Для этого используется техника т.н.

подзапросов.

Так, будет ошибкой пытаться вывести строку таблицы tab1, в которой значение в

столбце col1 принимает наибольшее значение с помощью запроса:

SELECT * FROM tab1 WHERE col1 = MAX( col1 );

Правильным решением будет использование результата подзапроса

Агрегатные функции в SQL: суть понятия и примеры

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

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

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

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

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

Свойства агрегатных функций:

  • Игнорируют NULL-значения. Исключение — функция COUNT(*). Дальше детально рассмотрим, что это значит.
  • Все агрегатные функции детерминированы. Это значит, что для одинакового набора данных функции возвращают одинаковое значение.

Агрегатные функции часто используются с операторами GROUP BY и HAVING. Оператор GROUP BY группирует строки с одинаковыми значениями в одну строку. Оператор HAVING используется в качестве фильтра для запросов, в которых есть оператор GROUP BY.

С агрегатными функциями можно использовать ключевые слова DISTINCT и ALL, которые мы рассмотрим ниже. Синтаксис выглядит так:

aggregation_function(DISTINCT | ALL expression)

В примерах будем использовать таблицу Employees («Сотрудники»).

name office_id salary role
Ivan 1 500 SWE
Misha 2 750 Manager
Olya 2 600 QA
Kolya 1 900 SWE
Max 2 NULL Manager

Создать ее можно с помощью команды:

CREATE TABLE IF NOT EXISTS employees ( name VARCHAR ( 50 ) NOT NULL, office_id INT, salary INT, role VARCHAR ( 50 ) NOT NULL ); INSERT INTO employees VALUES ('Ivan', 1, 500, 'SWE'), ('Misha', 2, 750, 'Manager'), ('Olya', 2, 600, 'QA'), ('Kolya', 1, 900, 'SWE'), ('Max', 2, NULL, 'Manager');

Протестировать команды ниже поможет сервис DB Fiddle. Используемый код совместим с программой PostgreSQL v14 — системой управления базами данных.

Функция COUNT

COUNT считает количество строк в таблице. Она может принимать в качестве параметров как числовые, так и нечисловые типы данных.

Об этом рассказывают в модуле по SQL на курсе «Анализ данных». Вы сможете создавать, изменять и удалять данные в таблицах. Научитесь использовать разные комбинации, чтобы решать реальные задачи. Кроме теории вас ждет практическая работа и защита диплома. После обучения сможете работать аналитиком данных, продуктовым или финансовым аналитиком.

COUNT(*) — специальная форма функции COUNT, которая возвращает количество всех строк в указанной таблице. Обратите внимание: COUNT(*) считает дубликат и NULL.

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

SELECT role, count(*) number_of_empoyee FROM employees GROUP BY role;

Что такое join в SQL и как с ним работать

Посчитаем количество сотрудников уникальных офисов:

role number_of _ employee
QA 1
Manager 2
SWE 2
SELECT COUNT(DiSTINCT office_id) AS unique_offices FROM employees;
unique_offices
2

SUM

Функция SUM вычисляет суммы всех выбранных столбцов. Обратите внимание: она работает только с числовыми полями.

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

SELECT SUM(salary) AS total_salary FROM employees;
total_salary
2750

MAX и MIN

Эти функции нужны для нахождения максимального и минимального значения для определенного столбца.

Например, минимальная зарплата среди всех сотрудников:

SELECT MIN(salary) AS min_salary FROM employees;
min_salary
500

Напишем запрос сложнее, который использует оператор HAVING, — максимальная зарплата для каждого офиса, где больше двух сотрудников:

SELECT office_id, MAX(salary) AS max_salary FROM employees GROUP BY office_id HAVING COUNT(*) > 2;
office_id max_salary
2 750

AVG

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

SELECT AVG(salary) AS avg_salary FROM employees;
avg_salary
687.500000000000

Важно: в столбце salary у нас есть значение NULL, но функция AVG проигнорировала его и посчитала среднее значение как (500 + 750 + 600 + 900) / 4 = 687,5

Где применяют агрегирующие функции

Их активно используют в различных проектах и продуктах. Например, с помощью функций:

  • строят модели в аналитике;
  • строят графики в реальном времени с метриками сервисов;
  • пишут отчеты.

Например, с их помощью бизнес-аналитик составляет стратегии развития компании и различные отчеты. Как это делать — узнаете на курсе «Бизнес-аналитик». После обучения сможете прогнозировать показатели бизнеса, отслеживать тенденции его развития и формулировать гипотезы роста.

Для некоторых продуктов критически важна производительность агрегирующих функций (например, для построения аналитики в реальном времени). Поэтому есть базы данных, которые оптимизированы для вычислений агрегатных функций: ClickHouse и Apache Druid. Еще есть хранилище данных, которое оптимизировано под выполнение запросов с агрегатными функциями, — firebolt.

Краткие итоги

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

Разобрали четыре функции:

  • COUNT — считает количество строк в таблице;
  • SUM — вычисляет суммы выбранных столбцов;
  • MAX и MIN — находят максимальное и минимальное значение определенного столбца;
  • AVG — вычисляет среднее значение столбца.

Агрегатные функции используют с операторами GROUP BY и HAVING:

  • GROUP BY — группирует строки с одинаковыми значениями в одну строку;
  • HAVING — используется как фильтр для запросов, в которых есть оператор GROUP BY.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *