Функция задержки для поиска значения предыдущей записи с отсутствующей предыдущей записью

В Oracle 12 вы можете использовать MATCH_RECOGNIZE для построчной обработки:

SELECT branch,
       period,
       qty,
       COALESCE(prev_period_1_qty, 0) AS prev_period_1_qty,
       COALESCE(prev_period_2_qty, 0) AS prev_period_2_qty,
       COALESCE(prev_period_3_qty, 0) AS prev_period_3_qty
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY branch
  ORDER     BY period DESC
  MEASURES
    curr.period AS period,
    curr.qty    AS qty,
    prev1.qty   AS prev_period_1_qty,
    prev2.qty   AS prev_period_2_qty,
    prev3.qty   AS prev_period_3_qty
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN (curr prev1? prev2? prev3?)
  DEFINE
    prev1 AS curr.period - 1 = period,
    prev2 AS curr.period - 2 = period,
    prev3 AS curr.period - 3 = period
)
ORDER BY branch, period

Или, используя LAG:

SELECT branch,
       period,
       qty,
       CASE
       WHEN p1 = period - 1
       THEN q1 ELSE 0
       END AS prev_period_1_qty,
       CASE
       WHEN p1 = period - 2 THEN q1
       WHEN p2 = period - 2 THEN q2
       ELSE 0
       END AS prev_period_2_qty,
       CASE
       WHEN p1 = period - 3 THEN q1
       WHEN p2 = period - 3 THEN q2
       WHEN p3 = period - 3 THEN q3
       ELSE 0
       END AS prev_period_3_qty
FROM   (
  SELECT t.*,
         LAG(period, 1) OVER (PARTITION BY branch ORDER BY period) AS p1,
         LAG(period, 2) OVER (PARTITION BY branch ORDER BY period) AS p2,
         LAG(period, 3) OVER (PARTITION BY branch ORDER BY period) AS p3,
         LAG(qty, 1, 0) OVER (PARTITION BY branch ORDER BY period) AS q1,
         LAG(qty, 2, 0) OVER (PARTITION BY branch ORDER BY period) AS q2,
         LAG(qty, 3, 0) OVER (PARTITION BY branch ORDER BY period) AS q3
  FROM   table_name t
)

Что для примера данных:

CREATE TABLE table_name (BRANCH, PERIOD, QTY) AS
SELECT 105, 319, 17 FROM DUAL UNION ALL
SELECT 105, 320, 20 FROM DUAL UNION ALL
SELECT 105, 321, 32 FROM DUAL UNION ALL
SELECT 105, 322, 61 FROM DUAL UNION ALL
SELECT 107, 319, 17 FROM DUAL UNION ALL
SELECT 107, 321, 18 FROM DUAL UNION ALL
SELECT 107, 322, 16 FROM DUAL UNION ALL
SELECT 108, 319, 21 FROM DUAL UNION ALL
SELECT 108, 322, 27 FROM DUAL;

Оба выхода:

BRANCH	PERIOD	QTY	PREV_PERIOD_1_QTY	PREV_PERIOD_2_QTY	PREV_PERIOD_3_QTY
105	319	17	0	0	0
105	320	20	17	0	0
105	321	32	20	17	0
105	322	61	32	20	17
107	319	17	0	0	0
107	321	18	0	17	0
107	322	16	18	0	17
108	319	21	0	0	0
108	322	27	0	0	21

дб <> рабочий пример здесь