Fill in the missing values ​​using match_recognize

Question

The question here, on SO , asked to resolve a fairly common precedent for filling in the "missing" (based on certain absence criteria) values ​​- the "missing" values ​​from the "previous" (based on certain ordering criteria) lines.

My usual approach to this problem is

• reassign ( decode/ case) "missing" values ​​to NULL,
• use the analytic function last_value()on the non-missing value with ignore nullsand the window of all previous lines up to the current line in a specific order.

Ie, given a (taken from the original message), introduces a set of lines my_tableof ...

ORD  COL1  COL2  COL3  COL4
---  ----  ----  ----  ----
  1     A     0     1     5
  2     B     0     4     0
  3     C     2     0     0
  4     D     0     0     0
  5     E     3     5     0
  6     F     0     3     0
  7     G     0     3     1
  8     A     0     1     5
  9     E     3     5     0

..., order by ord asc, "" (case when colX <= 0 then null else colX end X {2,3,4}), ...

select X.ord, X.col1,
    nvl(last_value(case when col2 > 0 then col2 end) ignore nulls over (order by ord), col2) as col2,
    nvl(last_value(case when col3 > 0 then col3 end) ignore nulls over (order by ord), col3) as col3,
    nvl(last_value(case when col4 > 0 then col4 end) ignore nulls over (order by ord), col4) as col4
from my_table X
order by ord;

... ...

ORD  COL1  COL2  COL3  COL4
---  ----  ----  ----  ----
  1     A     0     1     5
  2     B     0     4     5
  3     C     2     4     5
  4     D     2     4     5
  5     E     3     5     5
  6     F     3     3     5
  7     G     3     3     1
  8     A     3     1     5
  9     E     3     5     5

model, , model inductively ( SQL MODEL ORDERED ), , n, "" , n+1.

match_recognize Oracle 12c. ( ) . ,...

!: -)

match_recognize . (, , with ,...) pivot, unpivot, self-join, model, , , PL/SQL, , . SQL. match_recognize, my_table.

Answer 1

, , . , SUBSET.

WITH t(col0, col1, col2, col3, col4) AS (
  SELECT 1, 'A', 0, 1, 5 FROM DUAL UNION ALL
  SELECT 2, 'B', 0, 4, 0 FROM DUAL UNION ALL
  SELECT 3, 'C', 2, 0, 0 FROM DUAL UNION ALL
  SELECT 4, 'D', 0, 0, 0 FROM DUAL UNION ALL
  SELECT 5, 'E', 3, 5, 0 FROM DUAL UNION ALL
  SELECT 6, 'F', 0, 3, 0 FROM DUAL UNION ALL
  SELECT 7, 'G', 0, 3, 1 FROM DUAL UNION ALL
  SELECT 8, 'A', 0, 1, 5 FROM DUAL UNION ALL
  SELECT 9, 'E', 3, 5, 0 FROM DUAL
)
SELECT col0, col1, C02, C03, C04
FROM t
match_recognize(
  order by col0
  measures nvl(C02.col2,0) C02,
           nvl(C03.col3,0) C03,
           nvl(C04.col4,0) C04
  all rows per match
  pattern ((C234|C23|C24|C34|C2|C3|C4|X)+)
  subset C02 = (C234, C23, C24, C2),
         C03 = (C234, C23, C34, C3),
         C04 = (C234, C24, C34, C4)
  define C234 as col2>0 and col3>0 and col4>0,
         C23 as col2>0 and col3>0,
         C24 as col2>0 and col4>0,
         C34 as col3>0 and col4>0,
         C2 as col2>0, C3 as col3>0, C4 as col4>0
);

Answer 2

, , MATCH_RECOGNIZE. , col2, col3, col4 , . , ( ), , .

:

SELECT
    ORD, COL1, COL2R COL2
FROM
    my_table
MATCH_RECOGNIZE (
     ORDER BY ORD
     MEASURES
        NVL(LAST(V2.COL2), 0) AS COL2R
     ALL ROWS PER MATCH
     PATTERN ((V20*V2+V20*)+)
     DEFINE
        V2  AS  V2.COL2 > 0,
        V20 AS V20.COL2 = 0
     )

, , , . MODEL MATCH_RECOGNIZE , , , .