История цен в SQL: находим частые изменения и резкие скачки

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

Отделите запись снимка от изменения

Система может сохранять цену каждый день, даже если она не менялась. Количество записей не равно числу изменений. Для каждой строки нужна предыдущая цена того же товара. LAG получает её в порядке effective_at и устойчивого ID записи.

Если у одного товара несколько валют или каналов, включите их в разбиение окна. Иначе переход между рублёвой и валютной ценой станет ложным скачком. В примере один прайс, одна валюта, цена задана целым числом копеек.

Считайте окно до фильтра периода

Чтобы оценить первую ноябрьскую цену, нужна последняя цена до ноября. Поэтому LAG рассчитывается по доступной истории, а ограничение отчётным месяцем применяется на следующем шаге. Фильтрация истории до окна потеряет это сравнение.

При одинаковом времени ID даёт устойчивый технический порядок. Он должен соответствовать правилу истории источника. Если две строки имеют одинаковую силу и неизвестный порядок, сначала разрешите конфликт, а не трактуйте произвольную последовательность как бизнес-изменение.

WITH prices(id,sku,effective_at,cents) AS (
 VALUES (1,'A','2026-10-31',10000),(2,'A','2026-11-01',12000),
 (3,'A','2026-11-05',12000),(4,'A','2026-11-10',9000),
 (5,'B','2026-10-31',5000),(6,'B','2026-11-03',5000)
), history AS (
 SELECT *,LAG(cents) OVER(PARTITION BY sku ORDER BY effective_at,id) AS previous
 FROM prices
), changes AS (
 SELECT *,CASE WHEN previous<>0 THEN 1.0*ABS(cents-previous)/ABS(previous) END AS jump
 FROM history WHERE effective_at>='2026-11-01' AND effective_at<'2026-12-01'
 AND previous IS NOT NULL AND cents<>previous
)
SELECT sku,COUNT(*) AS changes,ROUND(MAX(jump),4) AS max_jump,
 SUM(CASE WHEN previous=0 THEN 1 ELSE 0 END) AS from_zero
FROM changes GROUP BY sku
HAVING COUNT(*)>=2 OR MAX(jump)>=0.2 OR SUM(previous=0)>0
ORDER BY sku;

Прочитайте два типа сигнала

В примере A меняется со 100 до 120, затем снимок повторяет 120, после чего цена снижается до 90. Получаются два изменения, максимальный абсолютный относительный скачок 25%. У B цена остаётся неизменной, и товар не попадает в исключения.

Относительный скачок вычисляется как ABS(new-old)/ABS(old). Для нулевой прежней цены доля не определена. Запрос сохраняет число таких переходов отдельно: их надо проверить, даже если обычный процентный порог не сработал.

Настройте пороги под действие сотрудника

В примере сигнал возникает при двух изменениях за период, скачке не меньше 20% или переходе от нулевой цены. Это учебные пороги, а не универсальная норма. Согласуйте, что делает ответственный: проверяет ошибку загрузки, подтверждает акцию или исправляет прайс.

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

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

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

От запроса к рабочей витрине

В платном курсе вы разберёте оконные функции, исторические значения и сверку отчётов в SQLite. На собственных запросах отработаете равные даты, пропуски и дубли через автоматическую проверку. Это помогает превратить LAG из отдельного приёма в проверяемый контроль изменений прайс-листа.

Откройте программу платного курса «Продвинутый SQL: сложные запросы и задачи для собеседований».

Продолжить разбор: Последняя запись на клиента в SQL: как разрешить ничью по времени.