Описательная статистика: медиана, перцентили и выбросы

Фрея · риск-менеджер необанка «Монетка»

Мне нужен лимит на подозрительные операции. Сколько тратит обычный клиент за раз? Только без «средней температуры по больнице». И скажи, какие покупки точно ненормальные.

Новый заказчик и новая база. Фрея отвечает за риски необанка «Монетка» и любит цифры без воды. В базе bank клиенты (clients), их карты (cards), полгода операций по картам (transactions) с кодом категории продавца и статусом, а ещё таблицы теста кешбэка, к которым мы придём в следующем уроке. Код категории это MCC: 5411 продукты, 5812 рестораны, 4111 транспорт, 5732 электроника. У операции три статуса: approved прошла, declined банк отклонил, reversed прошла и потом отменена.

Траты описываем только по approved: отклонённая операция это деньги, которые клиент не потратил.

Средняя температура по больнице

В палате десять пациентов. У девяти 36,6, у одного 41. Средняя температура 37,04: формально «все слегка температурят», на деле девять здоровы и один в беде. Среднее чувствительно к крайним значениям, медиана нет.

Медиана это значение, которое стоит ровно посередине, если выстроить все операции по сумме. В PostgreSQL её считают агрегатом с сортировкой внутри:

SELECT count(*) AS transactions,
       round(avg(amount_rub), 2) AS mean,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY amount_rub) AS median
FROM transactions
WHERE status = 'approved';

 transactions |  mean   | median
--------------+---------+--------
        68241 | 1583.60 |    783

Среднее почти вдвое выше медианы. 73% операций меньше среднего: его тянут вверх редкие покупки электроники до 172 628 ₽ и снятие наличных. «Обычный клиент тратит за раз около 1600 ₽» это неправда, обычная операция около 800 ₽.

Перцентили: где кончается обычное

Медиана это частный случай перцентиля, 50-й. Для лимитов полезнее 95-й: граница, выше которой лежат только 5% операций.

В PostgreSQL две функции перцентиля, и разница важна:

• percentile_cont(0.95) интерполирует: если нужная позиция падает между двумя значениями, вернёт точку между ними, например 4316,649999… ₽ у ресторанов. Такого чека никто не платил;
• percentile_disc(0.95) возвращает ближайшее настоящее значение из ряда: 4317 ₽.

Для отчёта о чеках берём percentile_disc. Считаем по категориям, потому что «крупная покупка» в метро и в магазине электроники это разные суммы:

mcc	категория	операций	медиана, ₽	p95, ₽
5411	продукты	23 233	832	2 787
4111	транспорт	9 649	63	138
5814	фастфуд и кофе	8 078	441	1 103
5812	рестораны	5 902	1 542	4 317
6011	наличные	2 021	5 407	15 205
5732	электроника	1 053	9 738	46 952

Один общий лимит на все категории бессмыслен: для метро подозрителен уже чек в 300 ₽, для электроники нормален чек в 40 000 ₽.