使用 match_recognize 填写"missing"值

Question

这里的一个问题，关于SO，要求解决一个相当常见的用例，即用"以前"（基于定义的排序标准）行中的非"丢失"值填充"丢失"（基于已定义的丢失标准）值。

我通常处理这个问题的方法是

• 将"丢失"的值重新映射（decode/case）到NULL
• 对非"缺失"值使用last_value()分析函数，其中ignore nulls和所有前面行的窗口一直到定义的顺序中的当前行

也就是说，给定（取自原始帖子）的my_table行的输入集。。。

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表示{2,3,4}的X），查询。。。

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

Lukas Eder用model子句提出了一个漂亮的替代解决方案，处理了这样一个事实，即在他的情况下，model操作是按所需顺序从第一行到最后一行归纳工作的（基于我对他的查询执行计划中的SQL MODEL ORDERED操作的观察），导致行CCD_ 12具有当正在处理行n+1时已经填充的"缺失"值。

Lukas的一条评论还提到了使用Oracle 12c的match_recognize子句的可能性。我试图让这个（对我来说是全新的）条款得到理解并发挥作用，但却失败了。所以

悬赏！：-）

我将为上述问题提供一个最优雅的基于match_recognize的解决方案。禁止使用pivot、unpivot、自联接、model、分析、聚合、PL/SQL等对数据进行所有预处理（视图、内联视图、with子句等）。只允许使用标准标量SQL函数。我对在基础my_table数据源上工作的纯match_recognize感兴趣。

Answer 1

我认为使用纯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子句都是为了解决不同的问题，在这些问题中，分析函数没有帮助，尽管有很好的优化，但它们都比分析函数更重。

Answer 2

模式中的条件确实是按整行计算的，因此必须为每种可能性定义一个条件。然后可以使用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
);