Функции агрегирования SQL

В этой статье мы рассмотрим 10 наиболее часто используемых функций агрегации MYSQL, таких как COUNT, SUM, AVG, MIN, MAX, ROUND, GROUP BY, WITH ROLLUP, LIMIT, HAVING с практическими примерами.

COUNT

Он возвращает количество ненулевых значений в столбце или количество строк в таблице.

-- Сколько строк в таблице авторов?

SELECT COUNT(*) AS total_rows
FROM authors;

-- Сколько авторов в наборе данных?

SELECT COUNT(DISTINCT au_id) AS number_of_authors
FROM authors; 

-- Сколько авторов живет в Сан-Хосе или Солт-Лейк-Сити?

SELECT COUNT(au_id)
FROM authors
WHERE city IN ("San Jose", "Salt Lake City");

-- Сколько магазинов находится в штате Калифорния (Калифорния)?

SELECT COUNT(DISTINCT stor_id) AS "number of stores in CA"
FROM stores
WHERE state = "CA";

SUM

Он вычисляет сумму значений в столбце.

-- Узнайте общий объем продаж по количеству.

SELECT SUM(qty) AS sales_quantity
FROM sales;

-- Узнайте объем продаж по количеству для каждого магазина, затем отсортируйте в порядке убывания.

SELECT stor_id, SUM(qty) AS sales_quantity
FROM sales
GROUP BY stor_id
ORDER BY SUM(qty) DESC;

AVG

Он вычисляет среднее значение числового столбца.

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

SELECT COUNT(qty), AVG(qty) , SUM(qty)
FROM sales;

-- Узнайте, сколько книг есть у каждого издательства и их среднюю цену на книги.

SELECT pub_id, COUNT(title_id), AVG(price)
FROM titles
GROUP BY pub_id
ORDER BY COUNT(title_id) DESC;

MIN

Извлекает минимальное значение из столбца.

-- Узнайте минимальную цену на книги для каждого издательства.

SELECT pub_id,  MIN(price) AS minimum_price
FROM titles
GROUP BY pub_id;

MAX

Извлекает максимальное значение из столбца.

-- Узнайте максимальную цену книг для каждого издательства.

SELECT pub_id, MAX(price) AS maximum_price
FROM titles
GROUP BY pub_id;

ROUND

Используется для округления числового значения до заданного числа знаков после запятой.

-- Узнайте среднюю цену книг для каждого издательства, укажите их с двумя знаками после запятой.

SELECT pub_id,  ROUND(AVG(price),2) AS average_book_price
FROM titles
GROUP BY pub_id;

GROUP BY

Группирует строки на основе одного или нескольких столбцов и выполняет агрегирование по каждой группе.

-- Узнайте общий объем продаж с начала года для каждого издателя, затем отсортируйте в порядке убывания.

SELECT pub_id,  SUM(ytd_sales)
FROM titles
GROUP BY pub_id
ORDER BY SUM(ytd_sales) DESC;

-- Узнайте общий объем продаж с начала года для каждого издателя и каждого названия, затем отсортируйте в порядке убывания.

SELECT pub_id,  title,  SUM(ytd_sales)
FROM titles
GROUP BY pub_id, title;

WITH ROLLUP

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

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

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

SELECT pub_id, title,  SUM(ytd_sales)
FROM titles
GROUP BY pub_id, title
WITH ROLLUP;

LIMIT

Он ограничивает количество строк, возвращаемых запросом.

-- Узнайте топ-3 магазинов с наибольшим объемом продаж.

SELECT stor_id, SUM(qty)
FROM sales
GROUP BY stor_id
ORDER BY SUM(qty) DESC
LIMIT 3;

HAVING

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

Это похоже на предложение WHERE, но с предложением WHERE мы не можем использовать агрегатные функции!

-- Выберите магазины с количеством продаж более 50.

SELECT stor_id, SUM(qty)
FROM sales
GROUP BY stor_id
HAVING SUM(qty) > 50;

-- Выберите магазины с количеством продаж более 50, затем отсортируйте их в порядке убывания.

SELECT stor_id, SUM(qty)
FROM sales
GROUP BY stor_id
HAVING SUM(qty) > 50
ORDER BY SUM(qty) DESC;

Заключение

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