sqloracle-databaseanalytic-functions

Lag function to find previous record value with missing previous record

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

Database : Oracle 12c

Example data:

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

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.

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
Solution

  • 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