Сегментация клиентов в SQL с помощью CASE WHEN

1. Сначала получите по одной строке для каждого клиента

Сегментация касается клиентов, а таблица транзакций — покупок, поэтому первым шагом всегда является совокупность. Никакая маркировка не работает, пока уровень детализации не будет правильным.

SELECT customer_id, COUNT(*) AS purchases, SUM(amount) AS spend,
       MAX(purchase_date) AS last_seen
FROM purchases GROUP BY 1;

C2  | 3 | 470 | 2026-03-14
C1  | 3 | 350 | 2026-03-05
C7  | 2 | 300 | 2026-03-19
C6  | 2 | 270 | 2026-03-08
C3  | 2 | 210 | 2026-02-18
C10 | 1 | 190 | 2026-03-04
C4  | 1 | 150 | 2026-01-21
C12 | 1 | 145 | 2026-03-25
C8  | 1 | 130 | 2026-02-17
C11 | 1 |  85 | 2026-03-12
C9  | 1 |  70 | 2026-02-24
C5  | 1 |  60 | 2026-01-28

Двенадцать строк, а их сумма равна 2430, то есть вся таблица покупок. Эти три столбца являются классическими компонентами сегментации клиентов: как недавно, как часто, насколько. Вам не обязательно использовать все три, и вам нужно их вычислить, прежде чем вы сможете их объединить.

Сделайте это как именованный шаг, а не вложите его, чтобы количество клиентов можно было проверить самостоятельно; аргумент в пользу этого находится в подзапрос к CTE, и здесь это имеет значение, потому что все, что находится ниже по потоку, делится на это количество.

2. CASE: метка — это просто столбец.

CASE считывает список условий сверху вниз и возвращает значение, присвоенное первому условию, которое является истинным.

CASE WHEN spend >= 300 THEN 'High'
     WHEN spend >= 150 THEN 'Mid'
     ELSE 'Low'
END AS segment

На выходе получается обычный текстовый столбец. В этом вся идея, и это то, что стоит усвоить: сегмент — это не особый вид объекта, это значение, вычисляемое для каждой строки, что означает, что его можно выбирать, фильтровать, объединять, подсчитывать и, что наиболее полезно, группировать. Механика самого выражения, включая разницу между простой и искомой формами, находится в руководство по ДЕЛУ.

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

3. Группировать по метке

Положите CASE в списке выбора и сгруппируйте его, и сегментация завершена.

WITH c AS (SELECT customer_id, SUM(amount) AS spend FROM purchases GROUP BY 1)
SELECT CASE WHEN spend >= 300 THEN 'High'
            WHEN spend >= 150 THEN 'Mid'
            ELSE 'Low' END AS segment,
       COUNT(*) AS customers, SUM(spend) AS spend
FROM c GROUP BY 1;

High | 3 | 1120
Mid  | 4 |  820
Low  | 5 |  490

Три, четыре и пять клиентов, в сумме получается двенадцать. Их расходы составляют 2430. Проверьте один вручную: Высокий — C2 при 470, C1 при 350 и C7 при 300, что равно 1120.

А теперь вывод, в чем вообще смысл это делать: на трёх клиентов из двенадцати, четверти из них, приходится 1120 из 2430, что составляет 46% всех расходов. Это предложение, согласно которому кто-то может действовать. «У нас двенадцать клиентов» — нет.

Два синтаксических замечания. GROUP BY 1 означает группировку по первому выбранному столбцу, что позволяет избежать повторения всего CASE; большинство движков также позволяют группировать по псевдонимам. И если ваш вариант настаивает на полном выражении в обоих местах, настал момент переместить его в CTE и сгруппировать по полученному имени столбца, а не писать его дважды, потому что две копии расходятся.

4. Порядок удаления сегмента

Перед пояснением: те же три полосы, только первые два условия поменяны местами. Предскажите три счета.

CASE WHEN spend >= 150 THEN 'Mid'
     WHEN spend >= 300 THEN 'High'
     ELSE 'Low' END

Mid | 7 | 1940
Low | 5 |  490

Два ряда, а не три. Высокий уровень не уменьшился, он прекратил свое существование: каждый клиент, который мог бы быть высоким, сначала прошел тест 150 и получил пометку «Средний». Здесь нет ни ошибок, ни предупреждений, ни пустой строки Высокий, на которую стоит обратить внимание, поскольку группа без строк не создает ни одной строки.

Именно последняя часть делает это скорее опасным, чем просто неправильным. Сегмент, сообщающий ноль, по крайней мере, будет виден на странице. Сегмент, которого просто нет, выглядит как сегментация с двумя полосами, а общее количество по-прежнему составляет двенадцать клиентов и 2430.

CASE принимает первое истинное условие, а не самое лучшее или наиболее конкретное, поэтому перекрывающиеся условия разрешаются полностью в том порядке, в котором вы их написали. Правило: сначала самый строгий, что для числовых полос означает начало от верхнего порога и движение вниз.

Одно и то же правило остановки распространяется на людей на каждом языке, где оно есть. Если вы встретили это в электронной таблице, это тот же механизм, что и вложенный IF против IFS, и исправление идентично.

5. Каждая строка должна где-то приземляться

Перед пояснением: вот полосы без ELSE, а средняя граница установлена ​​на 151, а не на 150. Скажите, что произойдет с клиентом, который потратил ровно 150.

CASE WHEN spend >= 300 THEN 'High'
     WHEN spend >= 151 THEN 'Mid' END

High | 3 | 1120
Mid  | 3 |  670
NULL | 6 |  640

Появился третий сегмент, названный ничем. CASE без условия соответствия и без ELSE возвращает NULL, и GROUP BY собирает все значения NULL в одну группу, поэтому шесть клиентов и 640 расходов теперь находятся в корзине без имени.

Это привело к двум отдельным ошибкам, и обе стоит увидеть независимо. Пропавший без вести ELSE означает, что все непревзойденное становится нулевым, а не попадает в группу. 151 означает, что между полосами существует разрыв, поэтому клиент, потративший ровно 150 спичек, не имеет ни одной, что является проблемой границ, которая есть в каждой схеме группирования и о которой большинство схем группирования не задумывались.

Далее следуют два правила, и они короткие. Всегда пишите ELSE. и заставить полосы соприкасаться: если один заканчивается на 150, следующий начинается на 150, что выражается как >= 150, поэтому между ними некуда провалиться.

Полезная привычка, пока сегментация еще разрабатывается: ELSE сказать 'check this' вместо того, чтобы назвать настоящую группу. Тогда все, чего вы не ожидали, будет помечено как вопрос, а не просто присоединится к нижней группе.

6. Два измерения одновременно

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

frequency | value | customers | spend
often     | High  |         2 |   820
twice     | High  |         1 |   300
twice     | Mid   |         2 |   480
once      | Mid   |         2 |   340
once      | Low   |         5 |   490

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

Читайте форму, а не отдельные ячейки. Каждый, кто купил один раз, имеет средний или низкий уровень, а каждый, кто купил как минимум дважды, поэтому в этих данных значение и частота движутся вместе. Это тот тип утверждения, который не может дать одномерная сегментация, и именно поэтому матрица стоит дополнительных усилий. CASE.

Два практических замечания. Пустые комбинации не отображаются как нулевые строки, они просто не отображаются, поэтому, если матрица собирается в отчет, сначала присоедините ее к сетке всех комбинаций. И не переходите к трем измерениям: двадцать семь ячеек на двенадцать клиентов — это не сегментация, это список с дополнительными шагами.

7. Выбранные вами пороговые значения или выбранные вами данные.

300 и 150 появились из ниоткуда. Альтернативой является предоставление права распределения решать, при этом NTILE, который сортирует строки и разрезает их на группы одинакового размера.

NTILE(3) OVER (ORDER BY spend DESC)

third | customers | lowest | highest | spend
    1 |         4 |    270 |     470 |  1390
    2 |         4 |    145 |     210 |   695
    3 |         4 |     60 |     130 |   345

Четыре, четыре и четыре по конструкции. Теперь сравните два метода по каждому клиенту:

customer | spend | fixed | thirds | agree
C2       |   470 | High  | High   | yes
C1       |   350 | High  | High   | yes
C7       |   300 | High  | High   | yes
C6       |   270 | Mid   | High   | no
C3       |   210 | Mid   | Mid    | yes
C10      |   190 | Mid   | Mid    | yes
C4       |   150 | Mid   | Mid    | yes
C12      |   145 | Low   | Mid    | no
C8       |   130 | Low   | Low    | yes
C11      |    85 | Low   | Low    | yes
C9       |    70 | Low   | Low    | yes
C5       |    60 | Low   | Low    | yes

Десять согласны, двое нет. C6 при 270 — это Средний при фиксированных границах и Высокий ниже третей; C12 при 145 — это Низкий ниже фиксированного и Средний ниже трети. Ни один из методов не является неправильным, и если к этим двум клиентам относятся по-разному, причиной является выбор метода.

Что использовать, зависит от того, для чего предназначен сегмент. Фиксированные отключения правы, когда число означает что-то за пределами данных: уровень расходов, при котором скидка становится выгодной, порог в контракте. Они стабильны во времени и могут оставить полосу пустой. Трети или квартили подходят для относительных вопросов типа «кто наши лучшие клиенты», они всегда заполняют каждую полосу и движутся: клиент может изменить сегмент, потому что изменились другие клиенты, не покупая ничего по-другому.

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

8. Примирение

Один запрос, выполняемый каждый раз, и он улавливает все, что есть на этой странице.

customers_in | customers_out | spend_in | spend_out
          12 |            12 |     2430 |      2430

Клиенты, входящие в сегментацию, по отношению к клиентам, выходящим из нее, и то же самое касается денег. Равенство по обоим показателям означает, что каждый клиент попал ровно в один сегмент, и никакие расходы не были созданы или потеряны.

Здесь отображается каждая ошибка на этой странице. Пропавший без вести ELSE не меняет итоговые значения, поскольку нули по-прежнему группируются, но добавляет сегмент с именем Ничего, поэтому проверьте список сегментов, а также суммы. Дублированный ключ клиента раздувается customers_out. Соединение добавляется до того, как раздувается маркировка. spend_out. И фильтр, примененный внутри сегментации, но не ко входу, делает customers_out меньшего размера, который люди пишут специально, а потом забывают.

Соответствующая проверка — это проверка из четвертого раздела: каждый определенный вами сегмент должен появиться в выходных данных. Сравните количество возвращаемых отдельных сегментов с количеством ярлыков в вашем CASE. Меньше означает, что один недоступен.

Представьте на мгновение свою собственную сегментацию. Если бы целый уровень был недоступен со дня его написания, есть ли в отчете что-нибудь, что бы об этом говорило?

Краевые случаи, нарушающие сегментацию

Шесть, которые стоит знать.

Нулевые значения в объединяемом столбце. NULL >= 300 неизвестно, а не ложно, поэтому клиент без затрат проходит все условия и попадает в ELSE и помечен как Низкий, как если бы они потратили немного. Сначала проверьте это: WHEN spend IS NULL THEN 'unknown'.

Клиенты, у которых вообще нет транзакций. Их нет в таблице покупок, поэтому их нет в сегментации. Если вопрос касается всех клиентов, начните с таблицы клиентов и оставьте объединение, иначе отклоняющиеся клиенты будут невидимы.

Окно, в котором измеряются расходы. Сегментация без фильтра по дате использует всю историю, поэтому клиент, который был крупным три года назад, превосходит в рейтинге того, который является крупным сейчас. Если это имеет значение, укажите окно в названии сегмента, например «расходы за последние двенадцать месяцев».

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

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

Определение жизни в пяти местах. Если несколько отчетов сегментируют клиентов, CASE копируется, и копии перемещаются. Поместите его в представление, чтобы у определения был один дом, и прочитайте временная таблица против просмотра для торговли, которая с этим связана.