sql - 使用 match_recognize 填充 "missing"值

Question

A question here, on 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 for X of {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

Lukas Eder与 model 一起提出了一个漂亮的替代解决方案条款，与事实一起工作 model他的案例中的操作有效 inductively (基于我对他查询执行计划中 SQL MODEL ORDERED 操作的观察)，从第一行到最后一行，按所需的顺序，得到行 n在行 n+1 时已经填充了“缺失”值正在处理中。

Lukas 的评论之一还提到了使用 match_recognize 的可能性。 Oracle 12c 的条款。我试图让这个(对我来说完全陌生)条款被理解并且工作失败了。所以， ...

赏金优惠! :-)

我向最优雅的人提供小额赏金 match_recognize - 基于上述问题的解决方案。数据的所有预处理( View 、内嵌 View 、 with 子句、...)与 pivot , unpivot , 自连接, model 、分析、聚合、PL/SQL 等等，都是被禁止的。只允许使用标准的标量 SQL 函数。我对纯品感兴趣 match_recognize在基地工作 my_table数据源。

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子句旨在解决解析函数无济于事的不同问题，尽管优化得很好，但两者都比解析函数更重要。