Встроенные и пользовательские функции в SQL

Play ITЗагрузка интерактивного демо…

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

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

---

Встроенные и пользовательские функции в SQL

Общая база: функции в коде. В SQL — COUNT(), SUM(), OVER на уровне запроса.

Агрегатные схлопывают группу (GROUP BY). Оконные считают по окну строк, но не убирают строки из результата. Порядок этапов: Принципы SQL-движка.

---

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

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

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

SELECT COUNT(*) AS TotalUsers FROM Users;

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

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

SELECT AVG(Age) AS AvgAge FROM Users;

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

SELECT SUM(Amount) AS TotalSales FROM Orders;

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

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

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

SELECT CategoryId, COUNT(*) AS QuestionCount
FROM Вопросы
GROUP BY CategoryId;

Важно:

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

---

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

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

Критерий	Агрегат с GROUP BY	Оконная функция с OVER
Число строк на выходе	Одна на группу	Столько же, сколько на входе
Детализация	Теряется	Сохраняется
Типичная задача	"Сколько всего по отделам?"	"К каждой продаже — доля от отдела"
Фильтр по результату	HAVING	Подзапрос или CTE

Логический порядок в запросе (упрощённо): FROM → WHERE → GROUP BY → HAVING → оконные функции (OVER) → SELECT → ORDER BY → LIMIT. Оконные выражения нельзя использовать в WHERE того же уровня — только в SELECT, во внешнем запросе или в CTE. Подробнее о порядке этапов: Принципы SQL-движка.

Шпаргалка

Внешняя памятка по синтаксису: 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, AVG, COUNT) или специальная оконная функция (ROW_NUMBER, LAG).
• OVER (...) — маркер оконной функции; без него SUM() свернёт строки через GROUP BY.
• PARTITION BY — разбиение на независимые окна (аналог группировки без схлопывания строк).
• ORDER BY — порядок строк внутри окна; нужен для рангов, накопительных сумм и LAG/LEAD.
• frame_clause — какие строки окна участвуют в расчёте для текущей строки.

frame_clause — рамка окна:

Рамка	Смысл
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	Накопительный итог
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW	Текущая и две предыдущие (скользящее окно из 3 строк)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING	Текущая, предыдущая и следующая
RANGE …	Смещение по значению столбца сортировки (удобно для дат)

ROWS считает физические строки; RANGE — строки с близкими значениями ключа сортировки. Пример накопительного итога:

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

Именованное окно (когда одна спецификация OVER нужна нескольким функциям):

SELECT
    department,
    employee,
    salary,
    AVG(salary) OVER w AS dept_avg,
    RANK() OVER w AS dept_rank
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

---

Ранжирование и нумерация строк

★ Функции ранжирования возвращают позицию строки внутри окна.

Функция	Поведение при равенстве	Пропуск номеров
ROW_NUMBER()	Разные номера (порядок недетерминирован без ORDER BY)	Нет
RANK()	Одинаковый ранг	Да (1, 1, 3…)
DENSE_RANK()	Одинаковый ранг	Нет (1, 1, 2…)
NTILE(n)	Делит окно на n групп (квартили при n=4)	—

Примеры:

-- Глобальный рейтинг зарплат
ROW_NUMBER() OVER (ORDER BY salary DESC)

-- Ранг внутри отдела
RANK() OVER (PARTITION BY department ORDER BY salary DESC)

-- Квартили по выручке
NTILE(4) OVER (ORDER BY revenue DESC)

Типичная задача — top-N в каждой группе (аналог "первые 3 строки на листе Excel"):

Код ITЗагрузка примера кода…

Оконные функции вычисляются до фильтрации по их результату, поэтому для WHERE rn = 1 нужен CTE или подзапрос.

---

Скользящие и накопительные суммы

Накопительный итог — сумма от начала окна до текущей строки:

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_stats;

Скользящее среднее за последние 7 дней:

AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS seven_day_avg

Скользящая сумма за 3 последних заказа клиента (схема shop_data):

SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) OVER (
        PARTITION BY o.customer_id
        ORDER BY o.order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS revenue_last_3_orders
FROM shop_data.orders o
JOIN shop_data.order_items oi ON oi.order_id = o.order_id;

---

Доли, проценты и распределение

Доля строки от суммы по группе — через деление на оконный SUM:

SELECT
    category,
    name,
    price,
    ROUND(
        100.0 * price / SUM(price) OVER (PARTITION BY category),
        2
    ) AS percent_of_category
FROM shop_data.products;

Доля от общего итога — SUM(...) OVER () без PARTITION BY:

ROUND(100.0 * amount / SUM(amount) OVER (), 2) AS percent_of_total

Статистические функции распределения:

PERCENT_RANK() OVER (ORDER BY salary)   -- относительный ранг 0…1
CUME_DIST() OVER (ORDER BY salary)      -- доля строк ≤ текущей
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)  -- медиана (PostgreSQL)

PERCENT_RANK и CUME_DIST — оконные; PERCENTILE_CONT/PERCENTILE_DISC в PostgreSQL и SQL Server вызываются как агрегат с OVER или в группировке.

---

Сравнение с соседними строками (LAG, LEAD и др.)

★ Функции смещения читают значения из других строк того же окна:

Функция	Назначение
LAG(col, offset, default)	Значение из предыдущей строки
LEAD(col, offset, default)	Значение из следующей строки
FIRST_VALUE(col)	Первое значение в окне
LAST_VALUE(col)	Последнее в окне (часто нужна рамка ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
NTH_VALUE(col, n)	n-е значение в окне

Примеры:

-- Изменение выручки относительно предыдущего дня
SELECT
    order_date,
    daily_revenue,
    LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS prev_day,
    daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS day_delta
FROM daily_stats;

-- Сумма первого заказа клиента рядом с каждым последующим
FIRST_VALUE(order_total) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
) AS first_order_amount

Третий аргумент LAG/LEAD — значение по умолчанию, если соседней строки нет (начало партиции или конец).

---

Аналитические отчёты без Excel

Типичные листы Excel и их SQL-эквиваленты:

Задача в Excel	Конструкция SQL
Сумма по группе в каждой строке	SUM(...) OVER (PARTITION BY …)
Накопительный итог по дате	SUM(...) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)
Доля от категории / от общего	деление на SUM(...) OVER (PARTITION BY …) или OVER ()
Топ-3 в каждой категории	ROW_NUMBER() + CTE + WHERE rn <= 3
Сравнение с прошлым месяцем	LAG(..., 1) OVER (ORDER BY month)
Скользящее среднее за неделю	AVG(...) OVER (ROWS 6 PRECEDING)
ABC-анализ (накопленная доля 80 %)	накопительный % + фильтр по порогу

Отчёт "выручка по месяцам с ростом к прошлому месяцу":

Код ITЗагрузка примера кода…

Готовый результат можно выгрузить в CSV или подключить к BI (Metabase, Power BI, Grafana) — логика остаётся в SQL, повторяема и версионируема. Примеры для аналитика: Оконные функции в аналитике.

---

Практика — разбор кейсов

Кейсы ниже работают на схеме shop_data и в SQL-тренажёре.

Кейс 1. Накопительная сумма заказов клиента

Сколько клиент потратил к каждой дате заказа (аналог running total в Excel):

Код ITЗагрузка примера кода…

Кейс 2. Самый дорогой товар в категории рядом с каждым товаром

SELECT
    category,
    name,
    price,
    MAX(price) OVER (PARTITION BY category) AS category_max,
    price = MAX(price) OVER (PARTITION BY category) AS is_top_in_category
FROM shop_data.products;

Кейс 3. Пагинация через ROW_NUMBER

SELECT * FROM (
    SELECT
        product_id,
        name,
        price,
        ROW_NUMBER() OVER (ORDER BY product_id) AS rn
    FROM shop_data.products
) t
WHERE rn BETWEEN 21 AND 40;

Дополнительная практика: демобаза demo (авиакомпания) (доли пассажиров), вопросы для самопроверки, SQL — реальные кейсы (JOIN, GROUP BY, суммы на схеме магазина), примеры SQL в лаборатории.

---

Справочник оконных функций

Группа	Функции
Ранжирование	ROW_NUMBER, RANK, DENSE_RANK, NTILE
Агрегаты с OVER	SUM, AVG, COUNT, MIN, MAX
Смещение	LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
Распределение	PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DISC

Полный синтаксис ANSI и отличия СУБД: справочник SQL. Поддержка — PostgreSQL, SQL Server (2005+), Oracle, MySQL 8+, SQLite 3.25+.

---