tgoop.com/sqlhub/2018
Create:
Last Update:
Last Update:
🧩 Продвинутая задача по SQL (Oracle): найти «бычьи серии» продаж и момент разворота
Задача
Есть таблица продаж по дням:
sales(day_date DATE, customer_id NUMBER, amount NUMBER)
Нужно для каждого клиента найти интервалы из не меньше 3 подряд идущих дней, где сумма amount строго возрастает каждый день, а на следующий день после интервала происходит разворот вниз (т.е. amount меньше, чем в последний день серии). Для каждого такого интервала вернуть:
- customer_id
- start_date, end_date серии
- length (длина серии в днях)
- last_amount (сумма в последний день серии)
- drop_amount (сумма в день разворота)
- drop_pct (процент падения относительно last_amount)
Решение (Oracle 12c+): используем MATCH_RECOGNIZE
SELECT *
FROM sales
MATCH_RECOGNIZE (
PARTITION BY customer_id
ORDER BY day_date
MEASURES
FIRST(day_date) AS start_date,
LAST(day_date) AS end_date,
COUNT(A.*) AS length,
LAST(amount) AS last_amount,
NEXT(amount) AS drop_amount,
ROUND( (LAST(amount) - NEXT(amount)) / NULLIF(LAST(amount),0) * 100, 2 ) AS drop_pct
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A{3,} D)
DEFINE
A AS ( PREV(amount) IS NULL OR amount > PREV(amount) ),
D AS amount < PREV(amount)
);
Пояснение
- PATTERN (A{3,} D) — ищем подпоследовательность из минимум трёх строго возрастающих дней A, за которой сразу идёт день падения D.
- DEFINE A — рост относительно предыдущего дня в группе клиента.
- DEFINE D — падение относительно предыдущего дня (последнего A).
- MEASURES — извлекаем границы серии и метрики, NEXT(amount) берёт сумму в день разворота.
- AFTER MATCH SKIP PAST LAST ROW — не пересекаем серии.
Бонус: защита от «лестниц» с пропусками дат
Если в данных бывают пропуски дней, а вам нужны подряд идущие даты, добавьте проверку календарной последовательности:
DEFINE
A AS ( (PREV(amount) IS NULL OR amount > PREV(amount))
AND (PREV(day_date) IS NULL OR day_date = PREV(day_date) + 1) ),
D AS ( amount < PREV(amount) AND day_date = PREV(day_date) + 1 )
Зачем так делать
MATCH_RECOGNIZE — мощный инструмент Oracle для поиска сложных паттернов по времени (распознавание трендов, разрывов, «голова-плечи», аномалий). Он заменяет громоздкие CTE с аналитиками и делает запрос короче, быстрее и точнее при работе с последовательностями.
@sqlhub
BY Data Science. SQL hub
Share with your friend now:
tgoop.com/sqlhub/2018