Untitled

gagarin_1982

Feb 27th, 2025

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

PostgreSQL 2.99 KB | None | 0 0

raw download clone embed print report

WITH
-- Параметры для заданного периода времени
Parameters AS (
SELECT
'2024-01-01'::DATE AS start_date,
'2024-12-31'::DATE AS end_date
),
-- CTE для фильтрации заказов за заданный период времени
FilteredOrders AS (
SELECT
o.customer_id,
o.order_date,
o.total_amount,
o.order_id
FROM
orders o
JOIN Parameters p ON o.order_date BETWEEN p.start_date AND p.end_date
),
-- CTE для расчета давности последнего заказа (Recency)
Recency AS (
SELECT
fo.customer_id,
MIN(fo.order_date) AS last_order_date,
DATEDIFF(DAY, MIN(fo.order_date), CURRENT_DATE) AS recency,
RANK() OVER (ORDER BY DATEDIFF(DAY, MIN(fo.order_date), CURRENT_DATE) ASC) AS recency_rank -- Добавляем ранжирование
FROM
FilteredOrders fo
GROUP BY
fo.customer_id
),
-- CTE для расчета частоты заказов (Frequency)
Frequency AS (
SELECT
fo.customer_id,
COUNT(fo.order_id) AS frequency,
RANK() OVER (ORDER BY COUNT(fo.order_id) DESC) AS frequency_rank -- Добавляем ранжирование
FROM
FilteredOrders fo
GROUP BY
fo.customer_id
),
-- CTE для расчета финансовой ценности (Monetary)
Monetary AS (
SELECT
fo.customer_id,
SUM(fo.total_amount) AS monetary,
RANK() OVER (ORDER BY SUM(fo.total_amount) DESC) AS monetary_rank -- Добавляем ранжирование
FROM
FilteredOrders fo
GROUP BY
fo.customer_id
)
-- Финальный запрос для вывода результатов RFM-анализа
SELECT
c.customer_name,
r.recency,
r.recency_rank, -- Добавляем ранг давности
f.frequency,
f.frequency_rank, -- Добавляем ранг частоты
m.monetary,
m.monetary_rank, -- Добавляем ранг финансовой ценности
r.recency_rank + f.frequency_rank + m.monetary_rank AS rfm_total_score -- Добавляем общую оценку RFM
FROM
customers c
JOIN Recency r ON c.customer_id = r.customer_id
JOIN Frequency f ON c.customer_id = f.customer_id
JOIN Monetary m ON c.customer_id = m.customer_id
ORDER BY
rfm_total_score ASC, -- Сортируем по общей оценке RFM
r.recency ASC, -- По давности (чем меньше, тем лучше)
f.frequency DESC, -- По частоте (чем больше, тем лучше)
m.monetary DESC; -- По финансовой ценности (чем больше, тем лучше)

Add Comment

Please, Sign In to add comment