A query that compares the LAG value and fills the sub column with data if there is a difference?

WITH A AS (
    SELECT 'GOLD' AS Title, 1 AS RNUM, 555.4 AS VALUE1, null AS DIFF, null AS LAG FROM DUAL UNION ALL
    SELECT 'GOLD' AS Title, 2 AS RNUM, 555.4 AS VALUE1, 0 AS DIFF, 555.4 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 3 AS RNUM, 555.4 AS VALUE1, 0 AS DIFF, 555.4 AS LAG FROM DUAL UNION ALL
 SELECT 'GOLD' AS Title, 4 AS RNUM, 556 AS VALUE1, 0.6 AS DIFF, 555.4 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 5 AS RNUM, 556 AS VALUE1, 0 AS DIFF, 556 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 6 AS RNUM, 556 AS VALUE1, 0 AS DIFF, 556 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 7 AS RNUM, 556.7 AS VALUE1, 0.7 AS DIFF, 556 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 8 AS RNUM, 556.7 AS VALUE1, 0 AS DIFF,556.7 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 9 AS RNUM, 557.3 AS VALUE1, 0.6 AS DIFF, 556.7 AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 1 AS RNUM, 400.3 AS VALUE1, null AS DIFF, null AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 2 AS RNUM, 401.3 AS VALUE1, 1.0 AS DIFF, 400.3 AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 3 AS RNUM, 401.3 AS VALUE1, 0 AS DIFF, 401.3 AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 4 AS RNUM, 401.3 AS VALUE1, 0 AS DIFF, 401.3 AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 5 AS RNUM, 402.2 AS VALUE1, 0.9 AS DIFF, 401.3 AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 6 AS RNUM, 403.2 AS VALUE1, 1.0 AS DIFF, 402.2 AS LAG FROM DUAL
)

Using A, I want to get the same result as B.

If the data in the DIFF column is greater than 0 (or according to a condition), I want to fill the value in the AccMaxNo column with the RNUM value in the DIFF column.

    A

QUERY B

CodePudding user response：

You can use the COUNT window function, as in this example:

WITH cnt AS (
  SELECT
    A.*, 
    COUNT(NULLIF(DIFF, 0)) OVER (PARTITION BY TITLE ORDER BY RNUM DESC) AS CNT
  FROM A
)
SELECT 
  TITLE,
  RNUM,
  VALUE1,
  DIFF,
  LAG,
  MAX(RNUM) OVER (PARTITION BY TITLE, CNT) AS AccMaxNo
FROM cnt
ORDER BY TITLE, RNUM

fb<>fiddle

CodePudding user response：

From Oracle 12, you can preform row-by-row processing using MATCH_RECOGNIZE:

SELECT title,
       rnum,
       value1,
       value1 - lag AS diff,
       lag,
       MAX(rnum) OVER (PARTITION BY title, mno) AS accmaxno
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY title
  ORDER BY rnum
  MEASURES
    PREV(value1) AS lag,
    MATCH_NUMBER() AS mno
  ALL ROWS PER MATCH
  PATTERN ((^ first_row | same_value)* any_row)
  DEFINE
    same_value AS PREV(value1) = value1
)

Which, for the sample data:

CREATE TABLE table_name (Title, RNUM, VALUE1) AS
SELECT 'GOLD',   1, 555.4 FROM DUAL UNION ALL
SELECT 'GOLD',   2, 555.4 FROM DUAL UNION ALL
SELECT 'GOLD',   3, 555.4 FROM DUAL UNION ALL
SELECT 'GOLD',   4, 556 FROM DUAL UNION ALL
SELECT 'GOLD',   5, 556 FROM DUAL UNION ALL
SELECT 'GOLD',   6, 556 FROM DUAL UNION ALL
SELECT 'GOLD',   7, 556.7 FROM DUAL UNION ALL
SELECT 'GOLD',   8, 556.7 FROM DUAL UNION ALL
SELECT 'GOLD',   9, 557.3 FROM DUAL UNION ALL
SELECT 'SILVER', 1, 400.3 FROM DUAL UNION ALL
SELECT 'SILVER', 2, 401.3 FROM DUAL UNION ALL
SELECT 'SILVER', 3, 401.3 FROM DUAL UNION ALL
SELECT 'SILVER', 4, 401.3 FROM DUAL UNION ALL
SELECT 'SILVER', 5, 402.2 FROM DUAL UNION ALL
SELECT 'SILVER', 6, 403.2 FROM DUAL;

Outputs:

fiddle