🖥 Задача (Oracle SQL, продвинутая): управление запасами с MODEL, скользящим спросом и детектом «stockout»

Таблицы:
• items(item_id NUMBER, init_qty NUMBER) — стартовый остаток по товару
• movements(item_id NUMBER, ts DATE, qty NUMBER) — движения: приход (qty>0) и расход (qty<0), событий может быть несколько в день

Нужно:
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 <= b.dmax - b.dmin + 1
  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