Агрегатные функции SQL — что это
Агрегатные функции в SQL — это функции, которые обрабатывают группу строк в таблице и в результате выдают одно значение. Эти операции используются для вычисления набора чисел и получения сводной статистики, такой как sum, count, average, maximum и minimum. В SQL-запросах часто используются процедуры агрегирования для объединения данных из одной или нескольких таблиц. После группировки и агрегирования функции также могут использоваться с предложением HAVING или WHERE для дальнейшей фильтрации данных.
Синтаксис для работы с агрегатными функциями:
aggregate_function(MODIFIER | expression)Агрегатной функцией, например, может быть AVG, COUNT, MAX, MIN или SUM. Модификатором могут быть все значения или выборочные из них в определенном столбце.
Виды агрегатных функций SQL
В SQL агрегатные функции важны для вычисления набора значений и возврата одного значения. Они также полезны при работе с большими наборами данных. При использовании агрегатных функций в SQL важно учитывать ссылки на столбцы. Ссылка на столбец — это название, содержащее данные, которые нужно агрегировать. Чтобы использовать агрегатную функцию со ссылкой на столбец, нужно указать имя столбца в круглых скобках функции. Есть и другие особенности работы с каждым из видов агрегатных функций.
APPROX_COUNT_DISTINCT
SQL APPROX_COUNT_DISTINCT() возвращает приблизительное количество строк с различными значениями выражений. Эта функция предоставляет альтернативу COUNT (DISTINCT expression). Она использует меньше памяти, чем исполнительная операция COUNT-DISTINCT.
APPROX_COUNT_DISTINCT() в основном используется в сценариях с большими данными. Она полезна для возвращения уникальных ненулевых значений в группе. Функция предназначена для больших наборов данных, содержащих более миллиона строк, и агрегирования столбцов со многими различными значениями.
Рассмотрим пример использования. Можно получить приблизительное количество значений в столбце «Age» из таблицы customers с помощью APPROC_COUNT_DISTINCT(). Создать таблицу можно так:
CREATE TABLE customers(ID INT NOT NULL,
NAME VARCHAR(30) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(30),
SALARY DECIMAL(18, 2));В таблице хранятся идентификатор, ИМЯ, ВОЗРАСТ, АДРЕС и ЗАРПЛАТА. Теперь мы вставляем семь записей в таблицу клиентов, используя инструкцию INSERT.
INSERT INTO customers VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
INSERT INTO customers VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
INSERT INTO customers VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
INSERT INTO customers VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
INSERT INTO customers VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
INSERT INTO customers VALUES(6, 'Komal', 22, 'MP', 4500.00);
INSERT INTO customers VALUES(7, 'Aman', 23, 'Ranchi', null);
INSERT INTO customers VALUES(8,'Aman', 23, 'Delhi', 3000.00);
INSERT INTO customers VALUES(9, 'Khilan', 25, 'Delhi', 3000.00);На выходе будет такая таблица:
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+---------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Aman | 23 | Ranchi | 5000.00 |
| 8 | Aman | 22 | Delhi | 3000.00 |
| 9 | Khilan | 25 | Delhi | 3000.00 |
SQL−запрос отображает приблизительное количество клиентов разного возраста:
SELECT APPROX_COUNT_DISTINCT(AGE) AS Approx_Distinct_AGE FROM customersИтог:
+--------------------+
|Approx_Distinct_AGE |
+--------------------+
| 5 |
+--------------------+
AVG
Эта функция вернет среднее значение всех выражений в столбце. Ее можно использовать для определения средней цены товара.
Сначала нужно создать новую таблицу в базе данных, в которой будет выполняться функция MIN. Эта инструкция CREATE TABLE создает таблицу Bikes_Details с пятью полями:
CREATE TABLE Bikes_Details
(
Bike_Number INT PRIMARY KEY,
Bike_Model INT,
Bike_Name VARCHAR (50),
Number_of_Bikes INT NOT NULL
Bike_Price INT NOT NULL
) ; Запрос INSERT вставляет запись об автомобилях в таблицу Bikes_Details:
INSERT INTO Bikes_Details (Bike_Number, Bike_Model, Bike_Name, Number_of_Bikes, Bike_Price)
VALUES (2578, 2018, KTM, 3, 1500000),
(9258, 2019, Apache, 2, 3000000),
(8233, 2018, Pulsar, 6, 900000),
(8990, 2018, RX100, 7, 700000),
(9578, 2020, Splender+, 6, 8000000),
(1258, 2021, Bullet, 2, 1500000),
(2564, 2019, Passion, 4, 6000000),
(2155, 2020, Livo, 8, 1800000); | Bike_Number | Bike_Model | Bike_Name | Number_of_Bikes | Bike_Price |
| 2578 | 2018 | KTM | 3 | 900000 |
| 9258 | 2019 | Apache | 2 | 1100000 |
| 8233 | 2018 | Pulsar | 6 | 900000 |
| 8990 | 2018 | RX100 | 7 | 700000 |
| 9578 | 2020 | Splender+ | 6 | 8000000 |
| 1258 | 2021 | Buller | 2 | 1500000 |
| 2564 | 2019 | Passion | 4 | 6000000 |
| 2155 | 2020 | Livo | 8 | 1800000 |
Этот запрос найдет среднее значение столбца Bike_Price из приведенной выше таблицы Bikes_Details:
SELECT AVG(Bike_Price) As "Average of Bike's Price " FROM Bikes_Details;
Output:
2612500CHECKSUM_AGG
Возвращает значение контрольной суммы столбца, указанного в выражении. Она суммирует все значения и вычисляет контрольную сумму. Если строка или строки меняются, контрольная сумма тоже будет меняться. Функция CHECKSUM_AGG() игнорирует null.
Она позволяет использовать два необязательных модификатора ALL и DISTINCT. При использовании ALL функция вычисляет совокупность всех значений. DISTINCT возвращает количество уникальных и ненулевых значений.
CREATE TABLE customers(ID INT NOT NULL,
NAME VARCHAR(30) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(30),
SALARY DECIMAL(18, 2));Можно вставить семь записей в таблицу клиентов, используя инструкцию INSERT.
INSERT INTO customers VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
INSERT INTO customers VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
INSERT INTO customers VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
INSERT INTO customers VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
INSERT INTO customers VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
INSERT INTO customers VALUES(6, 'Komal', 22, 'MP', 4500.00);
INSERT INTO customers VALUES(7, 'Aman', 23, 'Ranchi', null);| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+---------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Aman | 23 | Ranchi | NULL |
SQL-запрос отображает исходное значение контрольной суммы и обновленное значение контрольной суммы после обновления столбца age:
-- get the checksum value before the column value changed.
SELECT CHECKSUM_AGG(AGE) AS original_checksum from customers;
UPDATE customers SET AGE = 30 WHERE NAME = 'Aman';
--get the checksum of the modified column.
SELECT CHECKSUM_AGG(AGE) AS original_checksum from customers;Выход:
+--------------------+
| original_checksum |
+--------------------+
| 32 |
+--------------------+
+--------------------+
| original_checksum |
+--------------------+
| 36 |
+--------------------+
COUNT
COUNT возвращает количество строк в столбце, не учитывая значение NULL. Подходит для подсчета определенного количества значений в столбце. Например, если нужно посчитать количество новых клиентов или продуктов.
В таблице products у каждого продукта есть соответствующий бренд. Можно посчитать количество уникальных продуктов и брендов.
SELECT
COUNT(DISTINCT product_id) AS unique_product_count,
COUNT(DISTINCT brand_id) AS unique_brand_count
FROM products| unique_product_count | unique_brand_count |
| 321 | 9 |
COUNT_BIG
SQL COUNT_BIG полезна для подсчета количества элементов или строк, выбранных оператором SELECT. Она работает так же, как и функция Count, но возвращает значение bigint.
Пример:
USE WideWorldImportersDW;
SELECT COUNT_BIG(*) AS 'Row Count'
FROM Fact.[Order];Результат:
+-------------+
| Row Count |
|-------------|
| 231412 |
+-------------+
В таблице Fact содержится 231412 строк.
GROUPING
GROUPING указывает, относится ли столбец в списке GROUP BY к агрегированным или нет. Эту функцию можно использовать только для одного столбца. Группировка возвращает значение 1, если данные агрегированы по указанному столбцу, или 0, если они не агрегированы в результирующем наборе.
Здесь функция применена к столбцам «страна» и «город».
Select country, city, sum(sales) as total_sales
GROUPING(country) as GP_country,
GROUPING(city) as GP_city
from sales_history
GROUP BY ROLLUP (country, city);+--------+----------+-------------+-------------+------------+
| country| city | total_sales | GP_country | GP_city |
+--------+----------+-------------+-------------+------------+
| US | San Jose | 1000 | 0 | 0 |
| US | Fremont | 2000 | 0 | 0 |
| US | NULL | 3000 | 0 | 1 |
| Japan | Hiroshima| 5000 | 0 | 0 |
| Japan | Tokyo | 3000 | 0 | 0 |
| Japan | NULL | 4000 | 0 | 0 |
| Japan | NULL | 12000 | 0 | 1 |
| NULL | NULL | 15000 | 1 | 1 |
+--------+----------+-------------+-------------+------------+
В приведенном выше примере значение «NULL» в третьей строке столбца «город» представляет общий объем продаж в обоих городах. Это итоговая строка, добавленная в результате операции свертки в столбце «город». Поскольку это агрегированное значение, результирующий набор для GROUPING (город) обозначается единицей в столбце GP_city column для соответствующей строки. Однако нулевое значение в 6-й строке для столбца city показывает продажи в неизвестном городе в Японии. Поскольку это фактическое нулевое значение в необработанных данных, и оно не указывает на агрегат, соответствующий столбец GP_city column обозначается как 0.
GROUPING_ID
GROUPING_ID вычисляет уровень группировки и возвращает целочисленное значение, уникальное для каждой комбинации значений группировки.
Пример GROUPING_ID, примененный к столбцу «страна, город», который также соответствует «column_expression» в списке GROUP BY.
Select country, city, sum(sales) as total_sales
GROUPING_ID(country, city) as GPID,
from sales_history
GROUP BY ROLLUP (country, city);GROUPING_ID(country, city) эквивалентна функции GROUPING(country) * pow(2,1) + GROUPING(city). Однако GROUPING_ID(country, city) возвращает целочисленное значение, тогда как GROUPING(country) + GROUPING(city) возвращает двоичную строку.
+--------+----------+-------------+----------------+
| country| city | total_sales | GPID (integer) |
+--------+----------+-------------+----------------+
| US | San Jose | 1000 | 0 |
| US | Fremont | 2000 | 0 |
| US | NULL | 3000 | 1 |
| Japan | Hiroshima| 5000 | 0 |
| Japan | Tokyo | 3000 | 0 |
| Japan | NULL | 4000 | 0 |
| Japan | NULL | 12000 | 1 |
| NULL | NULL | 15000 | 3 |
+--------+----------+-------------+----------------+
MAX
Возвращает максимальное значение из набора чисел. Ее единственным аргументом может быть числовой столбец или числовой результат функции или выражения, примененного к значению столбца. Строки с нулевым значением для указанного столбца игнорируются.
CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age int(2),
Phone int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
(2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
(3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
(4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
(5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');
SELECT Max(Age) FROM Customer;
Output
24MIN
Используется для поиска минимального значения в указанной группе значений.
Пример:
CREATE TABLE product
(
user_id int IDENTITY(100, 2) NOT NULL,
product_1 VARCHAR(10),
product_2 VARCHAR(10),
price int
);
INSERT product(product_1, price)
VALUES ('rice', 400);
INSERT product(product_2, price)
VALUES ('grains', 600);
SELECT MIN(price) FROM product;
Output
400STDEV
Возвращает стандартное отклонение набора чисел. Можно определить стандартное отклонение столбца оценок ниже 1 от заданной таблицы оценок учащихся с помощью функции STDDEV.
Создание таблицы:
CREATE TABLE StudentMarks
(
StudentId INT AUTO_INCREMENT,
StudentName VARCHAR(100) NOT NULL,
Roll INT NOT NULL,
Sub1Mark INT NOT NULL,
Sub2Mark INT NOT NULL,
Sub3Mark INT NOT NULL,
TotalMarks INT NOT NULL,
PRIMARY KEY(StudentId )
);Заполнение:
INSERT INTO StudentMarks
(StudentName, Roll, Sub1Mark, Sub2Mark, Sub3Mark, TotalMarks)
VALUES
('Amit Jana', 10100, 85, 80, 95, 260),
('Labanya Mallick', 11000, 81, 89, 95, 265),
('Virat Sharma', 12000, 75, 83, 90, 248),
('Sayani Samanta', 13000, 95, 90, 99, 284),
('Riyanka Panda', 14000, 70, 87, 88, 245),
('Ritika Shah', 15000, 78, 89, 90, 257);Команда:
SELECT * from StudentMarks;Стандартное отклонение столбца sub1mark:
SELECT STDDEV(Sub1Mark) as Sub1StandardDeviation
FROM StudentMarks;Выход:
7.930251502246881
SUM
SUM() важна при анализе данных, поскольку она суммирует ключевые показатели конкретных числовых столбцов.
| order_id| amount | order_date |
|---------|-------- |------------ |
| 1 | 100.00 | 2023-01-05 |
| 2 | 150.00 | 2023-02-10 |
| 3 | 75.50 | 2023-03-15 |
| 4 | 200.00 | 2022-12-20 |
Запрос:
SELECT SUM(amount)
FROM sales
WHERE YEAR(order_date) = 2023;
-- Comment the line below to show it doesn't affect the query.
-- AND customer_id = 1;Выход:
| SUM(amount) |
|------------- |
| 325.50 |
VAR
Вернет значение NULL, если в заданном выражении не найдено совпадающих строк.
DROP TABLE "MyProducts";
CREATE COLUMN TABLE "MyProducts"(
"Product_ID" VARCHAR(10),
"Product_Name" VARCHAR(100),
"Category" VARCHAR(100),
"Quantity" INTEGER,
"Price" DECIMAL(10,2),
PRIMARY KEY ("Product_ID") );
INSERT INTO "MyProducts" VALUES('P1','Shirts', 'Clothes', 32, 20.99);
INSERT INTO "MyProducts" VALUES('P2','Jackets', 'Clothes', 16, 99.49);
INSERT INTO "MyProducts" VALUES('P3','Trousers', 'Clothes', 30, 32.99);
INSERT INTO "MyProducts" VALUES('P4','Coats', 'Clothes', 5, 129.99);
INSERT INTO "MyProducts" VALUES('P5','Purse', 'Accessories', 3, 89.49);
SELECT VAR("Price") FROM "MyProducts";Этот пример возвращает 2128,67 — разницу значений в столбце Price.
Заключение
- Агрегатные функции в SQL обрабатывают группу значений и возвращают единый результат.
- Они часто используются с GROUP BY для обобщения сгруппированных данных.
- Агрегатная функция работает только с ненулевыми значениями (кроме COUNT).
- Самые популярные агрегатные функции — MIN(), MAX(), COUNT(), AVG() и SUM().