Агрегация данных: COUNT, SUM, AVG, GROUP BY

В прошлом уроке вы научились извлекать и фильтровать данные. Это базовый навык для любого антифрод-исследователя. Но часто нам нужно не просто получить список записей, а обобщить информацию, чтобы увидеть общую картину или выявить аномалии. Для этого в SQL используют агрегатные функции.

Зачем агрегировать данные в антифроде?

Представьте, что вы анализируете тысячи транзакций. Просматривать каждую по отдельности неэффективно. Агрегация позволяет:

• Оценить объёмы мошенничества: Сколько денег украли за период? Сколько подозрительных операций зафиксировали?
• Найти паттерны: Какое среднее количество транзакций совершает мошенник? Какова максимальная сумма одной подозрительной операции?
• Сравнить группы: Отличается ли средний чек мошеннических операций от легитимных?

Агрегатные функции применяют к набору строк, а они возвращают одно значение. Давайте рассмотрим основные.

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

COUNT() – Считаем количество

Функция COUNT() считает количество строк или непустых значений в столбце.

• COUNT(*): Считает общее количество строк в таблице или группе, включая NULL.
• COUNT(столбец): Считает количество непустых значений в указанном столбце.
• COUNT(DISTINCT столбец): Считает количество уникальных непустых значений в указанном столбце.

Пример 1: Сколько всего транзакций?

SELECT COUNT(*)
FROM transactions;

Пример 2: Сколько уникальных пользователей совершили транзакции?

SELECT COUNT(DISTINCT user_id)
FROM transactions;

Совет: COUNT(DISTINCT) очень полезен для выявления аномалий. Например, когда один и тот же user_id используют для необычно большого количества уникальных операций.

SUM() – Суммируем значения

Функция SUM() вычисляет сумму числовых значений в указанном столбце. Она игнорирует NULL.

Пример: Какова общая сумма всех транзакций?

SELECT SUM(amount)
FROM transactions;

AVG() – Вычисляем среднее значение

Функция AVG() вычисляет среднее арифметическое числовых значений в указанном столбце. Она также игнорирует NULL.

Пример: Каков средний чек транзакции?

SELECT AVG(amount)
FROM transactions;

GROUP BY – Группируем данные

Агрегатные функции становятся по-настоящему мощными в сочетании с оператором GROUP BY. Он позволяет разделить результат на группы по значениям одного или нескольких столбцов, а затем применить агрегатную функцию к каждой группе отдельно.

Синтаксис:

SELECT столбец_группировки, агрегатная_функция(столбец)
FROM таблица
WHERE условие_фильтрации
GROUP BY столбец_группировки
ORDER BY столбец_группировки;

Важно: Все столбцы в SELECT, которые не являются агрегатными функциями, должны быть перечислены в GROUP BY.

Пример 1: Общая сумма транзакций по каждому пользователю

SELECT user_id, SUM(amount) AS total_amount
FROM transactions
GROUP BY user_id;

Здесь мы получаем сумму транзакций для каждого уникального user_id.

Пример 2: Количество подозрительных транзакций по типу мошенничества

Предположим, у нас есть столбцы fraud_type (тип мошенничества) и is_fraud (флаг мошенничества).

SELECT fraud_type, COUNT(*) AS fraud_count
FROM transactions
WHERE is_fraud = TRUE
GROUP BY fraud_type;

Этот запрос покажет, сколько мошеннических транзакций относится к каждому типу.

Пример 3: Средний чек и количество транзакций по городам

SELECT city, AVG(amount) AS avg_amount, COUNT(*) AS transaction_count
FROM transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY city
ORDER BY transaction_count DESC;

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

Практическое задание

Представьте, что у вас есть таблица payments со следующими столбцами:

• payment_id (уникальный идентификатор платежа)
• user_id (идентификатор пользователя)
• amount (сумма платежа)
• status (статус платежа: 'completed', 'pending', 'failed', 'fraud')
• payment_date (дата платежа)

Задание:

1. Напишите SQL-запрос, который покажет общее количество платежей со статусом 'fraud' за последний месяц (предположим, текущая дата 2025-03-15, и вам нужен период с 2025-02-15 по 2025-03-15).
2. Напишите SQL-запрос, который для каждого пользователя (user_id) покажет:
   • общее количество его платежей
   • сумму его платежей
   • количество его мошеннических платежей (status = 'fraud')
   • среднюю сумму его платежей Отсортируйте результат по убыванию количества мошеннических платежей.

Попробуйте решить эти задачи самостоятельно. Это поможет закрепить полученные знания. Уверен, у вас получится! 💪

Агрегация данных — мощный инструмент для выявления трендов и аномалий, что является основой для обнаружения мошенничества. В следующем разделе мы углубимся в то, как объединять данные из разных таблиц, что позволит вам строить ещё более сложные и информативные запросы.