具有动态参数的 Oracle Lag 函数

我有一个具体的问题。我有一个包含无效值的表。 我需要替换无效值（此处0）与之前的值大于0.

困难是，使用更新或插入对我来说是不合适的（游标和更新就可以了）。我唯一的方法是使用 Select 语句。

当我使用lag(col1, 1)- 当情况发生时，我只得到一列具有正确值的函数。

select col1, col2 realcol2,  
(case 
  when col2 = 0 then 
    lag(col2,1,1) over (partition by col1 order by col1 )
  else
   col2
  end ) col2,     
col3 realcol3,
(case 
  when col3 = 0 then 
    lag(col3,1,1) over (partition by col1 order by col1 )
  else
   col3
  end ) col3
from test_table 

内容TEST_TABLE:

---------------------------
 Col1 | Col2 | Col3 | Col4
---------------------------
  A   |  0   |  1   |  5
  B   |  0   |  4   |  0
  C   |  2   |  0   |  0
  D   |  0   |  0   |  0
  E   |  3   |  5   |  0
  F   |  0   |  3   |  0
  G   |  0   |  3   |  1
  A   |  0   |  1   |  5
  E   |  3   |  5   |  0

预期查询结果：

---------------------------
 Col1 | Col2 | Col3 | Col4
---------------------------
  A   |  0   |  1   |  5
  B   |  0   |  4   |  5
  C   |  2   |  4   |  5
  D   |  2   |  4   |  5
  E   |  3   |  5   |  5
  F   |  3   |  3   |  5
  G   |  3   |  3   |  1
  A   |  3   |  1   |  5
  E   |  3   |  5   |  5

我假设还有一个额外的列col0其中包含您的数据的明显排序标准，因为您的col1示例数据的排序不正确（重复的、尾随的值A and E).

我喜欢MODEL用于此类目的的条款。以下查询产生预期结果：

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 * FROM t
MODEL
  DIMENSION BY (row_number() OVER (ORDER BY col0) rn)
  MEASURES (col1, col2, col3, col4)
  RULES (
    col2[any] = DECODE(col2[cv(rn)], 0, NVL(col2[cv(rn) - 1], 0), col2[cv(rn)]),
    col3[any] = DECODE(col3[cv(rn)], 0, NVL(col3[cv(rn) - 1], 0), col3[cv(rn)]),
    col4[any] = DECODE(col4[cv(rn)], 0, NVL(col4[cv(rn) - 1], 0), col4[cv(rn)])
  )

Result:

RN   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

SQLFiddle http://sqlfiddle.com/#!4/9eecb7/6723

关于 MODEL 子句与基于窗口函数的方法的注释

虽然上面的内容看起来很酷（或者很可怕，这取决于你的观点），但你当然应该更喜欢使用基于窗口函数的方法，正如其他优雅的答案所揭示的那样nop77svk（使用LAST_VALUE() IGNORE NULLS) https://stackoverflow.com/a/34160956/521799 or MT0（使用LAG() IGNORE NULLS) https://stackoverflow.com/a/34163217/521799. 我在这篇博文中更详细地解释了这些答案 http://blog.jooq.org/2015/12/17/how-to-fill-sparse-data-with-the-previous-non-empty-value-in-sql/.