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

Агрегатные функции в 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	EMAIL	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 подсчитывает общее количество строк в вашей таблице.

Чтобы получить это значение для нашей таблицы с пожертвованиями, нужно запустить запрос SELECT COUNT(*) FROM donors .

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

Возможно, вам нужно сосчитать только какие-то определенные строки. Например, вывести число жертвователей, у которых указан адрес электронной почты.

Запустив запрос SELECT COUNT(email) FROM donors , вы получите 4 — общее число жертвователей с ненулевым значением в столбце email .

Имейте в виду, что если вы не используете псевдонимы, возвращаемый столбец будет обозначен просто как count. Если вам нужно более описательное имя, запустите запрос с созданием псевдонима при помощи AS . Например, SELECT COUNT(email) FROM donors AS email_count .

Функция SUM

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

В нашей базе можно использовать SUM , чтобы высчитать сумму денег, полученных в 2021 году. Для этого нужно запустить запрос SELECT SUM(donation_2021) FROM donors . Обратите внимание, что функция SUM игнорирует значения NULL .

Помните, что SUM , как и другие агрегатные функции, работает по строкам, а не по столбцам.

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

Функции MIN и MAX

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

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

SELECT MIN(donation_2021) AS "Minimum donation 2021", MAX(donation_2021) AS "Maximum donation 2021" FROM donors

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

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

MIN ищет самое маленькое число, букву, которая в алфавите стоит ближе всего к A, или самую раннюю дату. Функция MAX ищет самое большое число, букву, стоящую ближе всего к Z, или самую последнюю дату. Это очень полезная особенность!

Функция AVG

Наконец, функция AVG вычисляет среднее значение в указанном столбце. Как и SUM , эта функция игнорирует NULL .

Чтобы получить средний размер пожертвований в 2020 году, можно запустить следующий запрос:

SELECT AVG(donation_2020) FROM donors

Итоги

Как видите, агрегатные функции — это простые и полезные инструменты для анализа данных в SQL. Умение пользоваться AVG , MIN / MAX , SUM и COUNT — важное дополнение к вашему набору SQL-навыков.

Оператор SELECT

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

20 дек. 2020 · 9 минуты на чтение

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

Синтаксис оператора SELECT использует следующие основные предложения:

SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ]

Кратко пояснить смысл предложений оператора SELECT можно следующим образом:

SELECT — выбрать данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями
FROM — из перечисленных таблиц, в которых расположены эти столбцы
WHERE — где строки из указанных таблиц должны удовлетворять указанному перечню условий отбора строк
GROUP BY — группируя по указанному перечню столбцов с тем, чтобы получить для каждой группы единственное значение
HAVING — имея в результате лишь те группы, которые удовлетворяют указанному перечню условий отбора групп
ORDER BY — сортируя по указанному перечню столбцов

Как видно из синтаксиса рассматриваемого оператора, обязательными являются только два первых предложения: SELECT и FROM .

Рассмотрим каждое предложение оператора SELECT .

Спонсор поста

База данных для примеров

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

Постановка задачи: пусть требуется разработать БД для предметной области «Поставка деталей»!

Требуется хранить следующую информацию:

О поставщиках (P) pnum, pname
О деталях (D) pnum, dname, dprice
О поставках (PD) volume

Значения таблицы P

pnum	pname
1	Иванов
2	Петров
3	Сидоров
4	Кузнецов

Значения таблицы D

pnum	dname	dprice
1	Болт	10
2	Гайка	20
3	Винт	30

Значения таблицы PD

pnum	dnum	volume
1	1	100
1	2	100
1	3	300
2	1	150
1	2	250
3	1	1000

Предложение SELECT

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

Столбцы в результирующей таблице размещаются в том порядке, в котором они были указаны в предложении SELECT . Имена столбцов указываются через запятую.

Если имя столбца содержит пробелы или разделители, то его необходимо заключить в квадратные скобки.

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

Предложение FROM

В предложении FROM перечисляются имена таблиц, которые содержат столбцы, указанные после слова SELECT .

Пример 1.
Вывести список наименований деталей из таблицы D (“Детали”).

SELECT dname FROM D

Пример 2.
Получить всю информацию из таблицы D (“Детали”).

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

SELECT dnum, dname, dprice FROM D

SELECT * FROM D

В результате и первого и второго запроса получаем новую таблицу, представляющую собой полную копию таблицы D (“Детали”).

Можно осуществить выбор отдельных столбцов и их перестановку.

Пример 3.
Получить информацию о наименовании и номере поставщика.

SELECT pname, pnum FROM P

Пример 4.
Определить номера поставщиков, которые поставляют детали в настоящее время (то есть номера тех поставщиков, которые присутствуют в таблице PD (“Поставки”)).

SELECT pnum FROM PD

pnum
1
1
1
2
2
3

Дополнительно о SELECT

Теперь, когда мы научились делать простые запросы с SELECT и FROM , можно ненадолго снова вернуться к SELECT .

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

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

Агрегатная функция записывается в следующем виде: ()

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

SUM ‑ вычисляет сумму множества значений указанного столбца;
COUNT ‑ вычисляет количество значений указанного столбца;
MIN / MAX ‑ определяет минимальное/максимальное значение в указанном столбце;
AVG ‑ вычисляет среднее арифметическое значение множества значений столбца;
FIRST / LAST ‑ определяет первое/последнее значение в указанном столбце.

Пример 5.
Определить общий объем поставляемых деталей.

SELECT SUM(volume) FROM PD

Expr1000
2000

Вычисляемые столбцы

Столбцы результирующей таблицы, которых не существовало в исходных таблицах, называются вычисляемыми. Таким столбцам СУБД присваивает системные имена, что не всегда является удобным.

При вычислении результатов любой агрегатной функции СУБД сначала исключает все NULL -значения, после чего требуемая операция применяется к оставшимся значениям.

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

Следует запомнить, что агрегатные функции нельзя вкладывать друг в друга. Такая конструкция работать не будет: MAX(SUM(VOLUME))

Переименование столбца

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

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

SELECT SUM(volume) AS SUM FROM PD

Пример 6.
Определить количество поставщиков, которые поставляют детали в настоящее время.

SELECT COUNT(pnum) AS COUNT FROM PD

Несмотря на то, что реальное число поставщиков деталей в таблице PD равно 3, СУБД возвращает число 6. Такой результат объясняется тем, что СУБД подсчитывает все строки в таблице PD, не обращая внимание на то, что в строках есть одинаковые значения.

Операция DISTINCT

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

SELECT COUNT(DISTINCT pnum) AS COUNT FROM PD

DISTINCT можно задать только один раз для одного предложения SELECT .

Противоположностью DISTINCT является операция ALL . Она имеет противоположное действие «показать все строки таблицы» и предполагается по умолчанию.

Операция TOP

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

Для этого используется операция TOP , которая записывается в предложении SELECT следующим образом: SELECT TOP N [PERCENT]

Пример 7.
Определить номера первых двух деталей таблицы D.

SELECT TOP 2 dnum FROM D

Стандарт SQL требует, чтобы при сортировке NULL -значения трактовались либо как превосходящие, либо как уступающие по сравнению со всеми остальными значениями. Так как конкретный вариант стандартом не оговаривается, то в зависимости от используемой СУБД при сортировке NULL -значения следуют до или после остальных значений. В MS SQL Server NULL -значения считаются уступающими по сравнению с остальными значениями.

Рандомный блок

Предложение WHERE

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

Типы условий выбора:

Сравнение значений атрибутов со скалярными выражениями, другими атрибутами или результатами вычисления выражений.
Проверка значения на принадлежность множеству.
Проверка значения на принадлежность диапазону.
Проверка строкового значения на соответствие шаблону.
Проверка на наличие null -значения.

Сравнение

В языке SQL используются традиционные операции сравнения = , <> , < , , >= .

В качестве условия в предложении WHERE можно использовать сложные логические выражения, использующие атрибуты таблиц, константы, скобки, операции AND , OR , отрицание NOT .

Пример 8.
Определить номера деталей, поставляемых поставщиком с номером 2.

SELECT dnum FROM PD WHERE pnum = 2

Пример 9.
Получить информацию о поставщиках Иванов и Петров.

SELECT * FROM P WHERE pname='Иванов' OR pname='Петров'

Строковые значения атрибутов заключаются в апострофы.

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

Операция IN проверяет, принадлежит ли значение атрибута заданному множеству.

Пример 10.
Получить информацию о поставщиках ‘Иванов’ и ‘Петров’.

SELECT * FROM P WHERE pname IN ('Иванов','Петров')

Пример 11.
Получить информацию о деталях с номерами 1 и 2.

SELECT * FROM D WHERE dnum IN (1, 2)

Проверка на принадлежность диапазону

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

Пример 12.
Определить номера деталей, с ценой от 10 до 20 рублей.

SELECT dnum FROM D WHERE dprice BETWEEN 10 AND 20

Пример 13.
Вывести наименования поставщиков, начинающихся с букв от ‘К’ по ‘П’.

SELECT pname FROM P WHERE pname BETWEEN 'К' AND 'Р'

Сравнение символов

Буква Р в условии запроса объясняется тем, что строки сравниваются посимвольно. Для каждого символа при этом определяется код. Для нашего случая справедливо условие: П < Петров < Р

Читать:

Как распаковать кортеж с одним элементом python

Проверка строкового значения на соответствие шаблону

Операция LIKE используется для поиска подстрок. Значения столбца, указываемого перед служебным словом LIKE сравниваются с задаваемым после него шаблоном. Форматы шаблонов различаются в конкретных СУБД.

Для СУБД MS SQL Server:

Символ % заменяет любое количество любых символов.
Символ _ заменяет один любой символ.
[] ‑ вместо символа строки может быть подставлен один любой символ из множества возможных, указанных в ограничителях.
[^] ‑ вместо символа строки может быть подставлен любой из символов кроме символов из множества, указанного в ограничителях.

Множество символов в квадратных скобках можно указывать через запятую, либо в виде диапазона.

Пример 14.
Вывести фамилии поставщиков, начинающихся с буквы И .

SELECT pname FROM P WHERE pname LIKE 'И%'

Пример 15.
Вывести фамилии поставщиков, начинающихся с букв от К по П .

SELECT pname FROM P WHERE dname LIKE '[К-П]%'

Проверка на наличие null -значения

Операции IS NULL и IS NOT NULL используются для сравнения значения атрибута со значением NULL .

Пример 16.
Определить наименования деталей, для которых не указана цена.

SELECT dname FROM D WHERE dprice IS NULL

Пример 17.
Определить номера поставщиков, для которых указано наименование.

SELECT pnum FROM P WHERE pname IS NOT NULL

Предложение GROUP BY

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

Обычно предложение GROUP BY применяют, если формулировка задачи содержит фразу «для каждого…», «каждому..» и т.п.

Пример 18.
Определить суммарный объем деталей, поставляемых каждым поставщиком.

SELECT pnum, SUM(VOLUME) AS SUM FROM PD GROUP BY pnum

pnum	sum
1	600
2	400
3	1000

Выполнение запроса можно описать следующим образом: СУБД разбивает таблицу PD на три группы, в каждую из групп помещаются строки с одинаковым значением номера поставщика. Затем к каждой из полученных групп применяется агрегатная функция SUM, что дает единственное итоговое значение для каждой группы.

Рассмотрим два похожих примера. В примере 19 определяется минимальный объем поставки каждого поставщика. В примере 20 определяется объем минимальной поставки среди всех поставщиков.

Пример 19:

SELECT pnum, MIN(VOLUME) AS MIN FROM PD GROUP BY pnum

Пример 20:

SELECT MIN(VOLUME) AS MIN FROM P

Результаты запросов представлены в следующей таблице:

pnum	min	max
1	100	100
2	150
3	1000

Следует обратить внимание, что в первом примере мы можем вывести номера поставщиков, соответствующие объемам поставок, а во втором примере – не можем.

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

Однако в предложении GROUP BY могут быть указаны имена столбцов, не перечисленные в списке вывода после ключевого слова SELECT .

Если предложение GROUP BY расположено после предложения WHERE , то группы создаются из строк, выбранных после применения WHERE .

Пример 21.
Для каждой из деталей с номерами 1 и 2 определить количество поставщиков, которые их поставляют, а также суммарный объем поставок деталей.

SELECT dnum, COUNT(pnum) AS COUNT, SUM(volume) AS SUM FROM PD WHERE dnum=1 OR dnum=2 GROUP BY dnum

dnum	COUNT	SUM
1	3	1250
2	2	450

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

Предложение HAVING

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

Выполнение предложения HAVING сходно с выполнением предложения WHERE . Но предложение WHERE исключает строки до того, как выполняется группировка, а предложение HAVING — после. Поэтому предложение HAVING может содержать агрегатные функции, а предложение WHERE — не может.

Пример 22.
Определить номера поставщиков, поставляющих в сумме более 500 деталей.

SELECT pnum, SUM(volume) AS SUM FROM PD GROUP BY pnum HAVING SUM(volume) > 500

pnum	SUM
1	600
3	1000

Пример 23.
Определить номера поставщиков, которые поставляют только одну деталь.

SELECT pnum, COUNT(dnum) AS COUNT FROM PD GROUP BY pnum HAVING COUNT(dnum) = 1

Предложение ORDER BY

При выполнении запроса СУБД возвращает строки в случайном порядке. Предложение ORDER BY позволяет упорядочить выходные данные запроса в соответствии со значениями одного или нескольких выбранных столбцов.

Можно задать возрастающий — ASC (от слова Ascend) или убывающий — DESC (от слова Descend) порядок сортировки. По умолчанию принят возрастающий порядок сортировки.

Пример 24.
Отсортировать таблицу PD в порядке возрастания номеров поставщиков, а строки с одинаковыми значениями pnum отсортировать в порядке убывания объема поставок.

SELECT pnum, volume, dnum FROM PD ORDER BY pnum ASC, volume DESC

pnum	volume	dnum
1	300	3
1	200	2
1	100	1
2	250	2
2	150	1
3	1000	1

Операцию TOP удобно применять после сортировки результирующего набора с помощью предложения ORDER BY .

Пример 25.
Определить номера первых двух деталей с наименьшей стоимостью.

SELECT TOP 2 dnum FROM D ORDER BY dprice ASC

Следует отметить, что если в таблице D будут две детали без указания цены, то именно их и отобразит предыдущий запрос. Поэтому при наличии NULL -значений их необходимо исключать с помощью предложения WHERE .

SELECT TOP 2 dnum FROM D WHERE dprice IS NOT NULL ORDER BY dprice ASC

Заключение

В статье было рассмотрен оператор выборки SELECT . Знание оператора SELECT является ключевым при написании любых SQL-запросов. Он позволяет производить выборку данных из таблиц и преобразовывать результаты в соответствии с нужными выражениями и функциями.

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

Синтаксис оператора SELECT содержит несколько предложений, из которых обязательными являются только SELECT и FROM . Остальные предложения, такие как WHERE , GROUP BY , HAVING и ORDER BY , могут использоваться по желанию для уточнения выборки данных.

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

bestprogrammer.ru

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

База данных

На чтение 4 мин Просмотров 2к. Опубликовано 16.03.2021

Агрегатные функции 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.