Oracle 12c: Gap Filling using Analytic Functions (IT Toolbox Blogs)



A most challenging query has occupied my attention the past few weeks. Not a super long query (just 250 lines), but the underlying data structure and data contents require significant wrangling.

 

Last time I discussed a technique I used to keep the execution time at about 3 minutes instead of uncounted hours, so you already know the query uses recursion to construct the bill-of-materials.

 

A follow-on challenge is filling-in the frequent gaps in certain codes (after the recursion is complete). I looked at this issue a bit back in 2012 (thankfully), but my current query caused me to press the gap-filling technique further.

 

 


 

 

Here’s some sample data:

 

WITH
sampleData ( r#, value ) AS (              SELECT
              1, NULL  FROM DUAL UNION ALL SELECT
              2, 'Z'   FROM DUAL UNION ALL SELECT
              3, 'A'   FROM DUAL UNION ALL SELECT
              4, NULL  FROM DUAL UNION ALL SELECT
              5, 'B'   FROM DUAL UNION ALL SELECT
              6, NULL  FROM DUAL )
SELECT r#, value
  FROM sampledata
 ORDER BY 1;

 R# V
--- -
  1
  2 Z
  3 A
  4
  5 B
  6

 

For this I need to “pull” the values most of the time; for example: row 1 should have the same value of ‘Z’ as the second row. Since there can be no gaps, I have to “push” the final ‘B’ value from row 5 to six also.

 

There’s the 3-step process I developed, and the 2-step alternative I found somewhere, but (regrettably) didn’t attribute. It’s probably on AskTom somewhere.

 

 


 

 

Here’s my 3-step:

 

WITH firstpass AS (
SELECT r#, value
     , MIN(CASE WHEN value IS NOT NULL THEN r# END)OVER
          (ORDER BY r# DESC) AS pull_r#
  FROM sampleData
 ORDER BY r#
), secondpass AS (
SELECT r#, value
     , pull_r#
     , MAX(pull_r#)OVER
          (ORDER BY r# ASC) smooth_r#
  FROM firstpass
 ORDER BY r#
)
SELECT r#
     , CASE WHEN value IS NOT NULL THEN r# END as valued_r#
     , pull_r#
     , smooth_r#
     , value
     , MAX(value)OVER(PARTITION BY smooth_r#) gapfree_values
  from secondpass
 order by r#;

   R#  VALUED_R#    PULL_R#  SMOOTH_R# V G
----- ---------- ---------- ---------- - -
    1                     2          2   Z
    2          2          2          2 Z Z
    3          3          3          3 A A
    4                     5          5   B
    5          5          5          5 B B
    6                                5   B

 

Thankfully, I had my incomplete earlier version to work from; otherwise I don’t know how long it would have taken to develop.

 

 


 

 

The unattributed clever 2-step process uses something I rarely explore, the ROWS BETWEEN clause (and IGNORE NULLS starting in version 10gR2):

 

WITH firstpass AS (
SELECT r#, value
     , FIRST_VALUE(value IGNORE NULLS)OVER
           (ORDER BY r# desc
             ROWS BETWEEN CURRENT ROW
                      AND UNBOUNDED FOLLOWING) PULL_VALUE
  FROM sampleData
 ORDER BY r# )
SELECT r#, value
     , pull_value
     , FIRST_VALUE(pull_value IGNORE NULLS)OVER
           (ORDER BY r# asc
             ROWS BETWEEN CURRENT ROW
                      AND UNBOUNDED FOLLOWING) SMOOTH_VALUE
  FROM firstpass
 ORDER BY r#;

   R# V P S
----- - - -
    1     Z
    2 Z Z Z
    3 A A A
    4   A A
    5 B B B
    6   B B

 

My co-workers tell me I have a firm grasp of Oracle’s analytic functions. Experiences like this remind me I’m just third-rate or 4th-rate compared to the Oracle Aces and Oak Table experts of the world.

 

<END>

 

The image is Lake Tazawa in Akita PrefectureJapan — simply because it reminds me to keep calm and enjoy the journey. Public Domain, https://commons.wikimedia.org/w/index.php?curid=41828

 

 
FIRST_VALUE
 
MIN
 
MAX
 
Analytic Functions
 
Oracle 10g

Source: SANS ISC SecNewsFeed @ March 6, 2017 at 10:08AM

0