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.
Source: SANS ISC SecNewsFeed @ March 6, 2017 at 10:08AM