CASE: категории и сегменты

Бран · продюсер «Хроник Пограничья»

Говорят, в играх половину денег приносят киты. У нас так же? Разложи покупки на мелкие, обычные и китовые и скажи, кто сколько приносит.

В прошлом уроке мы фильтровали группы. Но все наши группы до сих пор были значениями, которые уже лежат в базе: платформа, класс, гильдия. Столбца «размер чека» в purchases нет. Есть только сумма в рублях, а Бран думает словами: «мелкий», «кит». Чтобы ответить на его языке, категорию нужно построить самому.

Сортировочная горка

На почте посылки едут по ленте и проходят ворота одни за другими. Первые ворота: «легче килограмма? налево, в мелкие». Кто не прошёл, едет дальше. Вторые ворота: «легче десяти килограммов? в средние». Всё, что доехало до конца, считается крупным. Каждая посылка сворачивает в первые подходящие ворота и дальше не едет.

CASE работает так же:

CASE
  WHEN условие_1 THEN значение_1
  WHEN условие_2 THEN значение_2
  ELSE значение_по_умолчанию
END

Условия проверяются сверху вниз. Первое истинное решает, остальные база уже не проверяет. Если ни одно не подошло, берётся ELSE.

Новый столбец из старого

CASE это выражение, как amount_rub * 2 или round(...). Значит, его можно поставить в SELECT и дать имя. Разложим покупки по размеру чека. Договоримся о границах: меньше 300 ₽ мелкий чек, от 300 до 2000 ₽ обычный, 2000 ₽ и больше китовый.

SELECT id, amount_rub,
       CASE
         WHEN amount_rub < 300  THEN 'мелкий'
         WHEN amount_rub < 2000 THEN 'обычный'
         ELSE 'кит'
       END AS check_size
FROM purchases
LIMIT 5;

Покупка за 999 ₽ не проходит первые ворота (999 < 300 ложь) и проходит вторые: «обычный». Писать во второй ветке amount_rub >= 300 AND amount_rub < 2000 не нужно. Раз строка дошла до второй ветки, первая уже не подошла.

Сегменты и GROUP BY

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

SELECT CASE
         WHEN amount_rub < 300  THEN 'мелкий'
         WHEN amount_rub < 2000 THEN 'обычный'
         ELSE 'кит'
       END AS check_size,
       count(*)        AS purchases,
       sum(amount_rub) AS revenue
FROM purchases
GROUP BY check_size;

 check_size | purchases | revenue
------------+-----------+-----------
 мелкий     |       717 | 118933.00
 обычный    |       490 | 425430.00
 кит        |        43 | 182070.00

Вот и ответ Брану. 43 китовые покупки из 1250 принесли 182 тыс. ₽, четверть сырой выручки. 717 мелких покупок, больше половины всех, дали всего 16 процентов денег.

Так делается сегментация: правило на языке бизнеса превращается в CASE, а GROUP BY считает метрики по каждому сегменту. Тем же приёмом разложим персонажей по опыту: до 20-го уровня новичок, с 20-го по 49-й середняк, с 50-го ветеран.

SELECT CASE
         WHEN level < 20 THEN 'новичок'
         WHEN level < 50 THEN 'середняк'
         ELSE 'ветеран'
       END AS segment,
       count(*) AS characters
FROM characters
GROUP BY segment;

 segment  | characters
----------+------------
 новичок  |       4283
 середняк |       1123
 ветеран  |        196