Какие знаешь агрегирующие функции?

Агрегирующие функции SQL

Агрегирующие функции (aggregate functions) — это функции SQL, которые выполняют вычисления над наборами данных (множеством строк) и возвращают один результат. Это один из ключевых инструментов для аналитики и отчётов, который QA-инженер должен знать для проверки корректности подсчётов и статистики в приложении.

Основные агрегирующие функции

1. COUNT()

Подсчитывает количество строк или не-NULL значений.

Синтаксис:

COUNT(*) — считает все строки, включая NULL
COUNT(column) — считает только не-NULL значения
COUNT(DISTINCT column) — считает уникальные не-NULL значения

Примеры:

-- Количество всех заказов
SELECT COUNT(*) FROM orders;

-- Количество заказов с известным адресом доставки
SELECT COUNT(delivery_address) FROM orders;

-- Количество уникальных пользователей
SELECT COUNT(DISTINCT user_id) FROM orders;

Для QA: убедитесь, что при отображении статистики ("Всего заказов: 1234") число совпадает с COUNT(*).

2. SUM()

Суммирует значения в столбце.

Синтаксис:

SUM(column) — сумма всех значений
SUM(DISTINCT column) — сумма уникальных значений

Примеры:

-- Общая сумма всех заказов
SELECT SUM(amount) FROM orders;

-- Общая выручка по товару
SELECT SUM(price * quantity) FROM order_items;

-- Сумма по пользователю
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

Важно: SUM() игнорирует NULL значения.

3. AVG()

Вычисляет среднее арифметическое.

Синтаксис:

AVG(column) — среднее значение (сумма / количество не-NULL)
AVG(DISTINCT column) — среднее уникальных значений

Примеры:

-- Средняя стоимость заказа
SELECT AVG(amount) FROM orders;

-- Средняя оценка товара
SELECT product_id, AVG(rating) FROM reviews GROUP BY product_id;

Внимание: если все значения NULL, AVG() возвращает NULL.

4. MIN() и MAX()

Возвращают минимальное и максимальное значение.

Синтаксис:

MIN(column)
MAX(column)

Примеры:

-- Самый дешёвый и дорогой товар
SELECT MIN(price), MAX(price) FROM products;

-- Дата первого и последнего заказа пользователя
SELECT user_id, MIN(created_at), MAX(created_at) FROM orders GROUP BY user_id;

5. STRING_AGG() / GROUP_CONCAT()

Объединяет значения из множества строк в одну строку.

Синтаксис (PostgreSQL):

STRING_AGG(column, separator)

Синтаксис (MySQL):

GROUP_CONCAT(column SEPARATOR ',')

Примеры:

-- Список всех товаров в заказе
SELECT order_id, STRING_AGG(product_name, ', ') 
FROM order_items 
GROUP BY order_id;

GROUP BY — группировка данных

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

Синтаксис:

SELECT column1, AGG_FUNCTION(column2)
FROM table
GROUP BY column1;

Примеры:

-- Количество заказов по статусу
SELECT status, COUNT(*) as count
FROM orders
GROUP BY status;

-- Результат:
status    | count
delivered | 1250
pending   | 340
cancelled | 45

-- Общая сумма заказов по пользователю
SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id;

HAVING — фильтрация после агрегации

WHERE фильтрует ДО агрегации, HAVING фильтрует ПОСЛЕ.

Пример:

-- Пользователи, потративших более 10000 рублей
SELECT user_id, SUM(amount) as total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 10000;  -- Фильтр по результату агрегации

Различие:

-- WHERE — исключает NULL и отрицательные суммы ДО подсчёта
SELECT status, COUNT(*)
FROM orders
WHERE amount > 0  -- Фильтр до GROUP BY
GROUP BY status;

-- HAVING — фильтрует результаты агрегации
SELECT status, COUNT(*) as count
FROM orders
GROUP BY status
HAVING COUNT(*) > 100;  -- Фильтр после GROUP BY

Практические примеры для тестирования

Тест-кейс 1: Проверка общей суммы заказов

-- Вручную создаём 3 заказа: 100, 200, 300
-- Приложение должно показать: "Итого: 600 рублей"
SELECT SUM(amount) FROM orders WHERE user_id = 123;
-- Ожидание: 600

Тест-кейс 2: Проверка среднего рейтинга

-- 5 оценок: 5, 4, 3, 2, 1 → среднее 3
SELECT AVG(rating) FROM reviews WHERE product_id = 456;
-- Ожидание: 3.0

Тест-кейс 3: Проверка статистики по статусам

SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Ожидание: 
-- delivered: 50
-- pending: 30
-- cancelled: 5

Тест-кейс 4: NULL обработка

-- Если в таблице есть NULL значения
SELECT COUNT(*), COUNT(column), SUM(column)
FROM table_with_nulls;
-- COUNT(*) включит NULL
-- COUNT(column) и SUM(column) — исключат NULL

Оконные функции (Window Functions)

Модернизация агрегирующих функций с сохранением всех строк:

-- Вместо GROUP BY, который объединяет строки
SELECT user_id, SUM(amount) OVER (PARTITION BY user_id)
FROM orders;

-- Результат: сумма повторяется на каждой строке
user_id | amount | sum
1       | 100    | 300
1       | 200    | 300
2       | 150    | 150

Практическое применение в QA

Проверка целостности данных:

-- Убедиться, что нет потерянных денег
SELECT SUM(order_amount) FROM orders 
WHERE payment_confirmed = true;
-- Должно равняться сумме в системе платежей

Проверка статистики в UI:

-- Если на странице показано "123 заказа за сегодня"
SELECT COUNT(*) FROM orders 
WHERE DATE(created_at) = CURRENT_DATE;
-- Проверяем, совпадает ли с UI

Выявление аномалий:

-- Найти товары с нулевыми продажами
SELECT product_id, COUNT(*) as sold_count
FROM order_items
GROUP BY product_id
HAVING COUNT(*) = 0;  -- Никогда не покупалось

Важные замечания

1. NULL обработка: COUNT(*) считает NULL, но COUNT(column), SUM(), AVG() — нет
2. Производительность: агрегирующие функции могут быть медленными на больших таблицах — используй индексы
3. Точность: при работе с деньгами используй DECIMAL или NUMERIC, не FLOAT
4. Группировка: все столбцы в SELECT должны быть в GROUP BY или в агрегирующей функции

Агрегирующие функции — это основной инструмент для:

• Проверки корректности подсчётов
• Валидации данных в БД
• Аналитики и отчётов
• Выявления аномалий и ошибок