nop77svk - 2 years ago 164

SQL Question

A question here, on SO, asked for resolving a rather common use case of filling in "missing" (based on defined criteria of being missing) values with non-"missing" values from "previous" (based on defined criteria of ordering) rows.

My usual approach to this problem is to

- remap (/
`decode`

) the "missing" values to NULLs,`case`

- use the analytic function on the non-"missing"-value, with
`last_value()`

and the window of all preceding rows up to the current row in the defined ordering.`ignore nulls`

I.e., given a (taken from the original post) input set of rows

`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

..., an ordering of

`order by ord asc`

`case when colX <= 0 then null else colX end`

`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;

... would yield the desired result of ...

`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 proposed a beautiful alternative solution with the

`model`

`model`

`SQL MODEL ORDERED`

`n`

`n+1`

One of Lukas's comments also mentioned the possibility of using the

`match_recognize`

I'm offering a small bounty to the most elegant

`match_recognize`

`with`

`pivot`

`unpivot`

`model`

`match_recognize`

`my_table`

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

I don't think you can accomplish the result using pure `MATCH_RECOGNIZE`

clause. Simply because the values `col2`

, `col3`

, `col4`

are independent on each other but the pattern is evaluated per entire row. So if multiple symbols can be matched (multiple columns have zero or non-zero values in your case) only the first one is matched as classifier and the others are ignored.

For single column there is simple solution:

```
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
)
```

Anyway, analytic functions are definitely the best option in your case. Both `MODEL`

and `MATCH_RECOGNIZE`

clauses are meant to solve different problems where analytic functions will not help and although there are well optimized both are more heavy weight than analytic functions.

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**