3.07. Функции

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВНЕ ДЛЯ НОВИЧКОВВ РАЗРАБОТКЕ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

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

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

★ Агрегатные функции – это специальные функции в SQL, которые выполняют вычисления над группой значений, и возвращают одно (итоговое) значение. Они используются для анализа данных: подсчёт, суммирование, усреднение, нахождение максимума/минимума среди набора и другое.

Обычно агрегатные функции применяются так:

вместе с SELECT;
с GROUP BY для группировки результатов;
игнорируют значения NULL, если специально не указано иное.

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

Функция	Назначение	Пример
`COUNT()`	Считает количество записей	`SELECT COUNT(*) FROM Users;`
`SUM()`	Суммирует значения столбца	`SELECT SUM(Price) FROM Orders;`
`AVG()`	Вычисляет среднее значение	`SELECT AVG(Age) FROM Users;`
`MIN()`	Находит минимальное значение	`SELECT MIN(Price) FROM Products;`
`MAX()`	Находит максимальное значение	`SELECT MAX(Age) FROM Users;`

Примеры использования:

Подсчёт всех пользователей:

SELECT COUNT(*) AS TotalUsers FROM Users;

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

Средний возраст пользователей:

SELECT AVG(Age) AS AvgAge FROM Users;

Общая сумма продаж:

SELECT SUM(Amount) AS TotalSales FROM Orders;

Минимальная и максимальная зарплата:

SELECT MIN(Salary), MAX(Salary) FROM Employees;

Использование с GROUP BY – количество вопросов по категориям:

SELECT CategoryId, COUNT(*) AS QuestionCount
FROM Questions
GROUP BY CategoryId;

Важно:

COUNT(*) считает все строки;
COUNT(column) считает только непустые (NOT NULL) значения в указанном столбце;
Агрегатные функции обычно нельзя использовать напрямую в WHERE, но можно в HAVING (при использовании GROUP BY). Агрегатные функции работают после фильтрации строк, поэтому для них нужно HAVING.
В запросах с GROUP BY в SELECT должны быть либо агрегатные функции, либо поля из GROUP BY - если в SELECT есть агрегатная функция, все неагрегированные столбцы должны быть в GROUP BY.

Оконные функции

★ Оконные функции – мощный инструмент, который позволяет выполнять сложный анализ данных без сложных подзапросов или группировок, сохраняя при этом все исходные строки.

Оконная функция – функция, которая вычисляет значения по группе строк, относящихся к текущей строке, не сводя всё к одной строке (как делает GROUP BY), а сохраняя контекст всех строк.

Хорошая шпаргалка по оконным функциям есть здесь:

https://learnsql.com/blog/sql-window-functions-cheat-sheet/

Допустим, у нас есть таблица Sales:

SaleID	Product	Amount
1	Apple	100
2	Banana	50
3	Apple	80
4	Banana	70

Для каждой продажи мы хотим показать её сумму и общую сумму по продукту. Без оконных функций нам придётся делать JOIN между исходной таблицей и результатом агрегации – громоздко. С оконной функцией:

SELECT 
    Product,
    Amount,
    SUM(Amount) OVER (PARTITION BY Product) AS TotalPerProduct
FROM Sales;

Результат:

Product	Amount	TotalPerProduct
Apple	100	180
Banana	50	120
Apple	80	180
Banana	70	120

Все строки на месте, но каждая содержит агрегированное значение по своей группе.

Синтаксис:

function_name(...) OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [frame_clause]
)

function_name(...) – агрегатная или специальная оконная функция (SUM, ROW_NUMBER, RANK).
OVER (...) – указывает, что это оконная функция.
PARTITION BY ... – аналог GROUP BY, но не сворачивает данные. Разбивает данные на группы (партиции), но не объединяет строки в одну, все исходные строки остаются на месте, а внутри каждой партиции работает сама функция:

SUM(Amount) OVER (PARTITION BY Product)

ORDER BY ... – определяет порядок строк внутри окна, нужен для расчёта накопленных итогов, присвоения рангов, работы с фреймами:

AVG(Amount) OVER (PARTITION BY Product ORDER BY SaleDate)

frame_clause – определяет, какие строки включаются в окно. Необязательный. Частые варианты - ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (от начала до текущей строки), ROWS BETWEEN 2 PRECEDING AND CURRENT ROW (три последние строчки), RANGE (диапазон значений):

SUM(Amount) OVER (
    PARTITION BY Product 
    ORDER BY SaleDate 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal

Прочие функции

★ Функции ранжирования возвращают позицию/ранг внутри окна:

ROW_NUMBER() нумерует строки в порядке сортировки:

ROW_NUMBER() OVER (ORDER BY Salary DESC)

RANK() назначает ранг, пропуская следующие числа при совпадении:

RANK() OVER (PARTITION BY Department ORDER BY Salary DESC)

DENSE_RANK() аналогичен RANK, но без пропусков.
NTILE(n) – делит набор на n равных частей:

NTILE(4) OVER (ORDER BY Score DESC)

★ Функции смещения позволяют обращаться к соседним строкам:

LAG(column, offset, default_value) возвращает значение из предыдущей строки:

LAG(Salary, 1, 0) OVER (ORDER BY Date)

LEAD(column, offset, default_value) возвращает значение из следующей строки:

LEAD(Score) OVER (PARTITION BY StudentId ORDER BY ExamDate)

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

Оконные функции​

Прочие функции​

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

Оконные функции

Прочие функции