/
IgorFedorov
/
RFM_customer_analysis
Обзор
Документация
Войти
/
IgorFedorov
/
RFM_customer_analysis
Код
Запросы
0
Задачи
Вики
Пакеты
0
Релизы
0
CI/CD
Аналитика
Безопасность
master
SQL/rfm_client.sql
64 строки
2 KB
IgorFedorov
Добавлено описание процедуры расчета.
29 сен 2025, 21:52
29 сен 2025, 21:52
aa4a760
Код
Авторство
О чём код?
WITH rfm_base AS ( select card, max(datetime)::date AS last_date, count(*) AS frequency, sum(summ - bonus_spent) AS monetary from bonuscheques where length(card) = 13 and datetime::date between '2021-12-01' and '2022-05-31' --[[and {{datetime}}]] group by card having count(*) > 1 ), rfm_calc as ( select card, max(last_date) over() - last_date AS recency, frequency, monetary from rfm_base ), rfm_clients as ( select card, recency, case when recency < 18 then '1' when recency < 61 then '2' else '3' end as "R", frequency, case when frequency > 5 then '1' when frequency > 2 then '2' else '3' end as "F", monetary, case when monetary > 5211 then '1' when monetary > 1390 then '2' else '3' end as "M" from rfm_calc ) select card as "Клиент", recency as "Давность покупки, дн.", "R", frequency as "Кол-во покупок", "F", monetary as "Потрачено средств", "M", case when "R" || "F" || "M" = '111' then 1 when "R" || "F" || "M" IN('112', '113', '121', '122', '123') then 2 when "R" || "F" || "M" IN('211', '212', '213', '221', '222') then 3 when "R" || "F" || "M" IN('131', '132', '133', '223', '231', '232', '233') then 4 when "R" || "F" || "M" IN('311', '312', '313', '321', '322') then 5 else 6 end as color from rfm_clients order by "R" || "F" || "M", 1