I have a requirement to fetch previous row or lag records where there are some missing previous ids.

Database : Oracle 12c

Example data:

I want the results in below format: If you see for branch 107 : the period 319 is missing and for branch 108 : 320,321 are missing. So if there are any missing previous records then the prev_period_<>_Qty columns should be 0.

Can you please help in achieving this.

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to do row-by-row processing:

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

Or, using 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
)

Which, for the sample data:

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;

Both output:

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

db<>fiddle here