🖥 Задача (Oracle SQL, продвинутая): управление запасами с MODEL, скользящим спросом и детектом «stockout»
Таблицы:
• items(item_id NUMBER, init_qty NUMBER) — стартовый остаток по товару
• movements(item_id NUMBER, ts DATE, qty NUMBER) — движения: приход (qty>0) и расход (qty
Нужно:
1) Построить помесячный/подневный календарь по каждому item_id между мин(ts) и max(ts).
2) Посчитать ежедневный итоговый остаток (onhand), применяя суммарные дневные движения к старту, даже если в конкретный день не было событий.
3) Найти «окна нулевого остатка» (stockout): количество, самую длинную протяжённость и дату первого пополнения после самого длинного окна.
4) Спрогнозировать риск обнуления в ближайшие 14 дней при текущем тренде: взять 7-дневное скользящее среднее спроса (по расходам), спроецировать остаток и отметить товары, которые уйдут в ноль.
-- Демоданные
WITH items AS (
SELECT 101 item_id, 50 init_qty FROM dual UNION ALL
SELECT 102, 5 FROM dual
),
movements_raw AS (
SELECT 101 item_id, DATE'2025-08-01' ts, 30 qty FROM dual UNION ALL -- приход
SELECT 101, DATE'2025-08-02', -20 FROM dual UNION ALL -- расход
SELECT 101, DATE'2025-08-04', -40 FROM dual UNION ALL -- перерасход → stockout
SELECT 101, DATE'2025-08-06', 60 FROM dual UNION ALL -- пополнение
SELECT 102, DATE'2025-08-01', - 2 FROM dual UNION ALL
SELECT 102, DATE'2025-08-03', - 3 FROM dual UNION ALL
SELECT 102, DATE'2025-08-05', 5 FROM dual
),
-- 1) Свернём движения по дням (может быть несколько событий в день)
movements AS (
SELECT item_id, ts, SUM(qty) qty_per_day
FROM movements_raw
GROUP BY item_id, ts
),
-- 1) Календарь на ежедневной сетке для каждого товара
date_bounds AS (
SELECT item_id,
LEAST(MIN(ts), TRUNC(SYSDATE)) AS dmin,
GREATEST(MAX(ts), TRUNC(SYSDATE)) AS dmax
FROM movements
GROUP BY item_id
),
calendar AS (
SELECT b.item_id, (b.dmin + LEVEL - 1) d
FROM date_bounds b
CONNECT BY LEVEL
AND PRIOR item_id = item_id
AND PRIOR SYS_GUID() IS NOT NULL
),
-- 2) Посчитаем ежедневный остаток MODEL-ом
onhand AS (
SELECT item_id, d, onhand, qty_per_day
FROM (
SELECT c.item_id,
c.d,
NVL(m.qty_per_day, 0) qty_per_day,
i.init_qty
FROM calendar c
JOIN items i USING(item_id)
LEFT JOIN movements m ON m.item_id = c.item_id AND m.ts = c.d
)
MODEL
PARTITION BY (item_id)
DIMENSION BY (d)
MEASURES (init_qty, qty_per_day, CAST(NULL AS NUMBER) AS onhand)
RULES SEQUENTIAL ORDER (
onhand[MIN(d)] = init_qty[MIN(d)] + qty_per_day[MIN(d)],
onhand[FOR d FROM MIN(d)+1 TO MAX(d)] =
onhand[CV(d)-1] + qty_per_day[CV(d)]
)
),
-- 3) Найдём окна нулевого остатка и их длины
stockout_runs AS (
SELECT *
FROM onhand
MATCH_RECOGNIZE (
PARTITION BY item_id
ORDER BY d
MEASURES
FIRST(d) AS start_d,
LAST(d) AS end_d,
COUNT(*) AS days_zero
ONE ROW PER MATCH
PATTERN (z+)
DEFINE
z AS onhand = 0
)
),
-- 3) Для самого длинного окна найдём дату первого пополнения после него
longest_zero AS (
SELECT s.item_id, s.start_d, s.end_d, s.days_zero,
LEAD(s.end_d) OVER (PARTITION BY s.item_id ORDER BY s.days_zero, s.end_d) dummy
FROM (
SELECT s.*, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY days_zero DESC, end_d) rn
FROM stock
0
Показать ещё
