Skip to Main Content
  • Questions
  • Number of rows of a value from an unbounded, ignore nulls NEXT_VALUE or FIRST_VALUE

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Michael.

Asked: November 15, 2016 - 3:06 pm UTC

Last updated: November 18, 2016 - 4:11 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

I understand the sql in the LiveSQL link. What I am wondering is how I find the number of rows the analytic function has to look to get the last/next value if IGNORE NULLS is part of the statement.

If I look at the second row (REPORT_MONTH = 01-FEB-16), I can visually see that its LAST_CATEGORY_A value of 100 comes from the row before it and that NEXT_CATEGORY_A's value comes from the row three rows later, but how do I return that information in the SQL statement?

Ultimately I want to create a view that replaces all of the NULL CATEGORY_A values with the average over the last and next actual values in the table. I.e.:
REPORT_MONTH CATEGORY_A
01-JAN-16 100
01-FEB-16 125
01-MAR-16 150
01-APR-16 175
01-MAY-16 200

I can do this if I know how many rows are between non-NULL records and if I know how many rows away from the last non-NULL record I am.

with LiveSQL Test Case:

and Connor said...

I'm sure others will jump in with easier ways, but here's one


SQL> with t as
  2  (
  3    select to_date('01-jan-2016') report_month, 100 category_a from dual
  4    union all
  5    select to_date('01-feb-2016') report_month, null category_a from dual
  6    union all
  7    select to_date('01-mar-2016') report_month, null category_a from dual
  8    union all
  9    select to_date('01-apr-2016') report_month, null category_a from dual
 10    union all
 11    select to_date('01-may-2016') report_month, 200 category_a from dual
 12    union all
 13      select to_date('01-jun-2016') report_month, 300 category_a from dual
 14      union all
 15      select to_date('01-jul-2016') report_month, null category_a from dual
 16      union all
 17      select to_date('01-aug-2016') report_month, null category_a from dual
 18      union all
 19      select to_date('01-sep-2016') report_month, null category_a from dual
 20      union all
 21      select to_date('01-oct-2016') report_month, 400 category_a from dual
 22  ),
 23  t1 as (
 24  select
 25    report_month,
 26    category_a,
 27    last_value(category_a) ignore nulls over (order by report_month range between unbounded preceding and current row) last_category_a,
 28    first_value(category_a) ignore nulls over (order by report_month range between current row and unbounded following) next_category_a ,
 29    nvl2(category_a, row_number() over (order by report_month),null) idx
 30  from t
 31  ),
 32  t2 as (
 33  select
 34    report_month,
 35    category_a,
 36    last_category_a,
 37    next_category_a,
 38    idx,
 39    max(idx) over ( order by report_month ) idx_full
 40  from t1
 41  ),
 42  t3 as (
 43  select
 44    report_month,
 45    category_a,
 46    last_category_a,
 47    next_category_a,
 48    count(*) over ( partition by idx_full ) as cnt,
 49    row_number() over ( partition by idx_full order by report_month) as seq
 50  from t2
 51  )
 52  select
 53    report_month,
 54    category_a,
 55    last_category_a,
 56    next_category_a,
 57    seq,
 58    cnt,
 59    case
 60      when category_a is not null then category_a
 61      else last_category_a + ( next_category_a - last_category_a ) * ( seq-1 ) / cnt
 62    end apportioned
 63  from t3;

REPORT_MO CATEGORY_A LAST_CATEGORY_A NEXT_CATEGORY_A        SEQ        CNT APPORTIONED
--------- ---------- --------------- --------------- ---------- ---------- -----------
01-JAN-16        100             100             100          1          4         100
01-FEB-16                        100             200          2          4         125
01-MAR-16                        100             200          3          4         150
01-APR-16                        100             200          4          4         175
01-MAY-16        200             200             200          1          1         200
01-JUN-16        300             300             300          1          4         300
01-JUL-16                        300             400          2          4         325
01-AUG-16                        300             400          3          4         350
01-SEP-16                        300             400          4          4         375
01-OCT-16        400             400             400          1          1         400

10 rows selected.

SQL>
SQL>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the response

Michael, November 16, 2016 - 1:29 pm UTC

After giving myself a mental break and coming back to it later, I came up with:
SELECT
REPORT_MONTH,
ATTR,
CATEGORY_A,
LAST_CATEGORY_A,
NEXT_CATEGORY_A,
ROW_NUMBER() OVER (PARTITION BY ATTR, LAST_CATEGORY_A ORDER BY REPORT_MONTH)-1 POSITION,
COUNT(*) OVER (PARTITION BY ATTR, LAST_CATEGORY_A) COUNTER,
LAST_CATEGORY_A + (NEXT_CATEGORY_A - LAST_CATEGORY_A) / COUNT(*) OVER (PARTITION BY ATTR, LAST_CATEGORY_A) * (ROW_NUMBER() OVER (PARTITION BY ATTR, LAST_CATEGORY_A ORDER BY REPORT_MONTH)-1) VALUE
FROM (
SELECT
REPORT_MONTH,
ATTR,
CATEGORY_A,
LAST_VALUE(CATEGORY_A) IGNORE NULLS OVER (PARTITION BY ATTR ORDER BY REPORT_MONTH RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) LAST_CATEGORY_A,
FIRST_VALUE(CATEGORY_A) IGNORE NULLS OVER (PARTITION BY ATTR ORDER BY REPORT_MONTH RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) NEXT_CATEGORY_A
FROM (
SELECT TO_DATE('01-JAN-2016') REPORT_MONTH, 'M' ATTR, 100 CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-FEB-2016') REPORT_MONTH, 'M' ATTR, NULL CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-MAR-2016') REPORT_MONTH, 'M' ATTR, NULL CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-APR-2016') REPORT_MONTH, 'M' ATTR, NULL CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-MAY-2016') REPORT_MONTH, 'M' ATTR, 200 CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-JAN-2016') REPORT_MONTH, 'F' ATTR, 100 CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-FEB-2016') REPORT_MONTH, 'F' ATTR, NULL CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-MAR-2016') REPORT_MONTH, 'F' ATTR, 160 CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-APR-2016') REPORT_MONTH, 'F' ATTR, NULL CATEGORY_A FROM DUAL
UNION ALL
SELECT TO_DATE('01-MAY-2016') REPORT_MONTH, 'F' ATTR, 200 CATEGORY_A FROM DUAL
ORDER BY 2
)
)
;

I do appreciate the response. I had a total mental block there.

Tried with pattern matching

Rajeshwaran, Jeyabal, November 16, 2016 - 2:31 pm UTC

Just tried this with pattern matching, i got this.

drop table t purge;
create table t as 
select to_date('01-jan-2016') report_month, 100 category_a from dual
  union all
 select to_date('01-feb-2016') report_month, null category_a from dual
  union all
 select to_date('01-mar-2016') report_month, null category_a from dual
  union all
 select to_date('01-apr-2016') report_month, null category_a from dual
  union all
 select to_date('01-may-2016') report_month, 200 category_a from dual
  union all
 select to_date('01-jun-2016') report_month, 300 category_a from dual
  union all
 select to_date('01-jul-2016') report_month, null category_a from dual
  union all
 select to_date('01-aug-2016') report_month, null category_a from dual
  union all
 select to_date('01-sep-2016') report_month, null category_a from dual
  union all
 select to_date('01-oct-2016') report_month, 400 category_a from dual


demo@ORA12C> select *
  2  from (
  3  select t.* ,
  4    last_value( category_a ignore nulls ) over(
  5                        order by report_month
  6                        rows between unbounded preceding
  7                        and current row) as last_value,
  8    first_value( category_a ignore nulls ) over(
  9                        order by report_month
 10                        rows between current row
 11                        and unbounded following ) as first_value
 12  from t
 13      )
 14  match_recognize(
 15      order by report_month
 16      measures
 17        running count(*) as seq,
 18        final count(*) as cnt,
 19        case when category_a is not null then category_a
 20            else last_value + (first_value - last_value) *
 21            (running count(*) -1) / final count(*) end as diff
 22      all rows per match
 23      pattern( strt down*)
 24      define
 25        down as prev(last_value) = last_value )
 26  /

REPORT_MONT        SEQ        CNT       DIFF CATEGORY_A LAST_VALUE FIRST_VALUE
----------- ---------- ---------- ---------- ---------- ---------- -----------
01-JAN-2016          1          4        100        100        100         100
01-FEB-2016          2          4        125                   100         200
01-MAR-2016          3          4        150                   100         200
01-APR-2016          4          4        175                   100         200
01-MAY-2016          1          1        200        200        200         200
01-JUN-2016          1          4        300        300        300         300
01-JUL-2016          2          4        325                   300         400
01-AUG-2016          3          4        350                   300         400
01-SEP-2016          4          4        375                   300         400
01-OCT-2016          1          1        400        400        400         400

10 rows selected.

demo@ORA12C>

With MODEL clause

Rajeshwaran, Jeyabal, November 17, 2016 - 8:14 am UTC

demo@ORA12C> select report_month,cata as category_a,last_cat,nxt_cat,seq,cnt,
  2         case when cata is not null then cata
  3              else last_cat + ( nxt_cat - last_cat) *
  4                    (seq-1)/cnt end as apportioned
  5  from t
  6  model
  7    dimension by ( row_number() over( order by report_month) rn )
  8    measures( category_a cata, 0 last_cat, 0 nxt_cat,report_month,0 seq,0 cnt)
  9    rules(
 10      last_cat[any] order by rn = case when cata[cv()] is not null
 11                                        then cata[cv()]
 12                                      else last_cat[cv()-1]
 13                                       end ,
 14      nxt_cat[any] order by rn desc = case when cata[cv()] is not null
 15                                         then cata[cv()]
 16                                      else nxt_cat[cv()+1]
 17                                         end ,
 18      seq[any] order by rn = case when cv(rn)=1 then 1
 19                                  when last_cat[cv()-1] = last_cat[cv()]
 20                                         then seq[cv()-1]+1
 21                                  when last_cat[cv()-1] <> last_cat[cv()]
 22                                         then 1 end ,
 23      cnt[any] order by rn = max(seq) keep(dense_rank first order by last_cat)[rn >= cv(rn)] )
 24  order by report_month
 25  /

REPORT_MONT CATEGORY_A   LAST_CAT    NXT_CAT        SEQ        CNT APPORTIONED
----------- ---------- ---------- ---------- ---------- ---------- -----------
01-JAN-2016        100        100        100          1          4         100
01-FEB-2016                   100        200          2          4         125
01-MAR-2016                   100        200          3          4         150
01-APR-2016                   100        200          4          4         175
01-MAY-2016        200        200        200          1          1         200
01-JUN-2016        300        300        300          1          4         300
01-JUL-2016                   300        400          2          4         325
01-AUG-2016                   300        400          3          4         350
01-SEP-2016                   300        400          4          4         375
01-OCT-2016        400        400        400          1          1         400

10 rows selected.

demo@ORA12C>

Connor McDonald
November 18, 2016 - 4:11 am UTC

nice stuff

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.