Skip to Main Content
  • Questions

  • How to Create Missing Records with Analytical Functions

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Braga.

Asked: July 14, 2017 - 10:16 am UTC

Last updated: July 19, 2017 - 10:42 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi AskTom Team,

I am having some trouble figuring out a query to do the following:

I have a staging table populated by an external system. This table stores information about how much an item sold during a day. If an item hasn't sold anything during a day the external system creates a record with quantity sold = 0.

-- small sample for staging table (sales until day 10)
create table sales_staging as 
select * from (
-- item 1:
select 1 item, 1  day_of_month, 20 qty_sold from dual union all
select 1 item, 2  day_of_month,  0 qty_sold from dual union all
select 1 item, 3  day_of_month, 10 qty_sold from dual union all
select 1 item, 4  day_of_month, 20 qty_sold from dual union all
select 1 item, 5  day_of_month, 20 qty_sold from dual union all
select 1 item, 6  day_of_month, 20 qty_sold from dual union all
select 1 item, 7  day_of_month, 35 qty_sold from dual union all
select 1 item, 8  day_of_month, 40 qty_sold from dual union all
select 1 item, 9  day_of_month, 20 qty_sold from dual union all
select 1 item, 10 day_of_month,  2 qty_sold from dual union all
-- item 2:
select 2 item, 1  day_of_month, 10 qty_sold from dual union all
select 2 item, 2  day_of_month, 21 qty_sold from dual union all
select 2 item, 3  day_of_month, 33 qty_sold from dual union all
select 2 item, 7  day_of_month, 20 qty_sold from dual union all -- days 4,5,6 are missing! so 20 is the total sales qty for days 4,5,6 and 7
select 2 item, 8  day_of_month, 14 qty_sold from dual union all
select 2 item, 9  day_of_month, 20 qty_sold from dual union all
select 2 item, 10 day_of_month, 20 qty_sold from dual
);


However, sometimes the external system sometimes doesn't send the sales info for one or more days for a particular item, and it does when a new record for that item is create where the column qty_sold contains the total quantity sold during the missing days and the day identified by the new record.

I have to create a query that detects the mentioned missing days and creates extra records for those days.

qty_sold for those records should be divided across the missing days and the record that contains the aggregated sales qty (in the small sample days 4,5,6 and 7 for item 2)

So, considering the sample above, the query should return all records present in the sales_staging table
with 3 extra records:
2 item, 4 day_of_month, 5(=20/4) qty_sold
2 item, 5 day_of_month, 5(=20/4) qty_sold
2 item, 6 day_of_month, 5(=20/4) qty_sold
and with the day 7 record's qty_sold also updated:
2 item, 6 day_of_month, 5(=20/4) qty_sold


How would you create this query, using analytical functions, and knowing that the staging table contains thousands of records, and performance is a key issue?


Thank you in advance for your help and advice!


and Chris said...

There's several steps to take. Let's break them down.

First you need a data source containing all the. This could be a table or you favourite row generation technique. e.g.:

select rownum dy from dual connect by level <= 10


Next, for each item in the source, find the number of days between the current row and the previous. You'll need this later to divide the quantity by the number of missing days. You can do this with lag, like:

day_of_month - lag(day_of_month, 1, 1) over (
  partition by item order by day_of_month
) missing_days


Note: the final 1 is the default, so this assumes you always start from the 1st of the month...

You then need outer join these two together. But not just any outer join. Oh no. You'll want a partitioned outer join. Doing this by item means you have all the days for all the items:

with rws as (
  select rownum dy from dual connect by level <= 10
), stg as (
  select ss.*,
         day_of_month - lag(day_of_month, 1, 1) over (
           partition by item order by day_of_month
         ) missing_days
  from   sales_staging ss
)
  select ss.*, rws.dy
  from   rws
  left join stg ss partition by (item) 
  on     dy = day_of_month;

      ITEM DAY_OF_MONTH   QTY_SOLD MISSING_DAYS         DY
---------- ------------ ---------- ------------ ----------
         1            1         20            0          1
         1            2          0            1          2
         1            3         10            1          3
         1            4         20            1          4
         1            5         20            1          5
         1            6         20            1          6
         1            7         35            1          7
         1            8         40            1          8
         1            9         20            1          9
         1           10          2            1         10
         2            1         10            0          1
         2            2         21            1          2
         2            3         33            1          3
         2                                               4
         2                                               5
         2                                               6
         2            7         20            4          7
         2            8         14            1          8
         2            9         20            1          9
         2           10         20            1         10


Now you need to "fill-in" the missing quantities.

Enter first_value.

You can use this to look forward in your result set to find the next non-null value. To do this you need a window clause. This will work from your current row to the end of the set (for the item). Do this for the quantity and the difference you calculated earlier:

first_value(qty_sold) ignore nulls over (
  partition by item order by dy
  rows between current row and unbounded following
)


Just make sure you include the "ignore nulls" clause. Or you'll still have all those pesky missing values!

Now all you need to do is divide these quantities by the days between them (making sure you map the start day in your set to 1 to avoid divide-by-zero!) Put it all together and you get:

create table sales_staging as 
select * from (
-- item 1:
select 1 item, 1  day_of_month, 20 qty_sold from dual union all
select 1 item, 2  day_of_month,  0 qty_sold from dual union all
select 1 item, 3  day_of_month, 10 qty_sold from dual union all
select 1 item, 4  day_of_month, 20 qty_sold from dual union all
select 1 item, 5  day_of_month, 20 qty_sold from dual union all
select 1 item, 6  day_of_month, 20 qty_sold from dual union all
select 1 item, 7  day_of_month, 35 qty_sold from dual union all
select 1 item, 8  day_of_month, 40 qty_sold from dual union all
select 1 item, 9  day_of_month, 20 qty_sold from dual union all
select 1 item, 10 day_of_month,  2 qty_sold from dual union all
-- item 2:
select 2 item, 1  day_of_month, 10 qty_sold from dual union all
select 2 item, 2  day_of_month, 21 qty_sold from dual union all
select 2 item, 3  day_of_month, 33 qty_sold from dual union all
select 2 item, 7  day_of_month, 20 qty_sold from dual union all -- days 4,5,6 are missing! so 20 is the total sales qty for days 4,5,6 and 7
select 2 item, 8  day_of_month, 14 qty_sold from dual union all
select 2 item, 9  day_of_month, 20 qty_sold from dual union all
select 2 item, 10 day_of_month, 20 qty_sold from dual
);

with rws as (
  select rownum dy from dual connect by level <= 10
), stg as (
  select ss.*,
         day_of_month - lag(day_of_month, 1, 1) over (
           partition by item order by day_of_month
         ) missing_days
  from   sales_staging ss
), qty as (
  select ss.*, rws.dy,
         first_value(qty_sold) ignore nulls over (
           partition by item order by dy
           rows between current row and unbounded following
         ) nv,
         first_value(missing_days) ignore nulls over (
           partition by item order by dy
           rows between current row and unbounded following
         ) mdays
  from   rws
  left join stg ss partition by (item) 
  on     dy = day_of_month
)
  select q.* ,
         nv / case when mdays = 0 then 1 else mdays end qty
  from   qty q
  order  by item , dy;

      ITEM DAY_OF_MONTH   QTY_SOLD MISSING_DAYS         DY         NV      MDAYS        QTY
---------- ------------ ---------- ------------ ---------- ---------- ---------- ----------
         1            1         20            0          1         20          0         20
         1            2          0            1          2          0          1          0
         1            3         10            1          3         10          1         10
         1            4         20            1          4         20          1         20
         1            5         20            1          5         20          1         20
         1            6         20            1          6         20          1         20
         1            7         35            1          7         35          1         35
         1            8         40            1          8         40          1         40
         1            9         20            1          9         20          1         20
         1           10          2            1         10          2          1          2
         2            1         10            0          1         10          0         10
         2            2         21            1          2         21          1         21
         2            3         33            1          3         33          1         33
         2                                               4         20          4          5
         2                                               5         20          4          5
         2                                               6         20          4          5
         2            7         20            4          7         20          4          5
         2            8         14            1          8         14          1         14
         2            9         20            1          9         20          1         20
         2           10         20            1         10         20          1         20


Note you'll need to tweak this so you can go over month boundaries. The easiest way is to convert those day_of_month values to real dates...

More on partitioned outer joins:
http://www.oracle-developer.net/display.php?id=312

Rating

  (6 ratings)

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

Comments

Great!

Braga Simoes, July 14, 2017 - 11:19 pm UTC

Wow! Many many thanks!

Fantastic solution- I am ashamed to say that I've never used partition outer join before! And it exists since 10g! Great feature!

However I forgot to mention a particular case that I think that cannot be solved with a partition outer join.

There are some items that are brand new and had only started to be sold in the middle of month (for instance, day 4).

For those particular items, flagged as new_item_flag = 'Y', the first record of a month is assumed to be the first day that the item was exposed in the stores and sold some quantities. If the first record is day 4 the query is not supposed to retrieve the missing days 1,2, and 3. Only gaps in the staging table AFTER the first staging record.

Sample data of this scenario:

create table sales_staging as 
select * from (
-- item 1:
select 1 item, 1  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 2  day_of_month,  0 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 3  day_of_month, 10 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 4  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 5  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 6  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 7  day_of_month, 35 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 8  day_of_month, 40 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 9  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 10 day_of_month,  2 qty_sold, 'N' new_item_flag from dual union all
-- item 2: 
select 2 item, 1  day_of_month, 10 qty_sold, 'N' new_item_flag from dual union all
select 2 item, 2  day_of_month, 21 qty_sold, 'N' new_item_flag from dual union all
select 2 item, 3  day_of_month, 33 qty_sold, 'N' new_item_flag from dual union all
select 2 item, 7  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all -- days 4,5,6 are missing! so 20 is the total sales qty for days 4,5,6 and 7
select 2 item, 8  day_of_month, 14 qty_sold, 'N' new_item_flag from dual union all
select 2 item, 9  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 2 item, 10 day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
-- item 3 -> flagged as new item in the store starting from day 4
select 1 item, 4  day_of_month, 20 qty_sold, 'Y' new_item_flag from dual union all
select 1 item, 5  day_of_month, 20 qty_sold, 'Y' new_item_flag from dual union all
select 1 item, 6  day_of_month, 20 qty_sold, 'Y' new_item_flag from dual union all
/* 
I only want to deal with missing days 7 and 8, since it will be assumed 
that  the item just arrived at the stores at the 4th of the month
*/
select 1 item, 9  day_of_month, 30 qty_sold, 'Y' new_item_flag from dual union all 
select 1 item, 10 day_of_month,  2 qty_sold, 'Y' new_item_flag from dual
);


I've created a query based on your approach that creates the missing records only for the relevant gaps. However,
I am not sure if this solution is okay in terms of performance (I am particularly worried with the join in the query that creates the missing records). Could you please give your opinion or point out a better solution/approach?

with rws as (
  select rownum dy from dual connect by level <= 10
), 
/* in order to exemplify I am only dealing with items that are new */
stg as (
  select ss.*,
         day_of_month - lag(day_of_month, 1) over (
           partition by item order by day_of_month
         ) missing_days
  from   sales_staging ss
  where  ss.new_item_flag = 'Y'
)
select item, 
       qty_sold,
       case coalesce(missing_days,1)
                when 1 then qty_sold
                else qty_sold / missing_days
       end new_qty_sold,
       day_of_month,
       missing_days
from stg
union all
-- Create missing rows
select stg.item,
       stg.qty_sold,
       (stg.qty_sold / missing_days) new_qty_sold,
       rws.dy day_of_month,
       stg.missing_days
from stg
    /* particularly worried with this join here */
    join rws
        on rws.dy between stg.day_of_month - missing_days + 1  and stg.day_of_month -1
where stg.missing_days > 1
order by item, day_of_month;


Thank you again for your great help!
Chris Saxon
July 17, 2017 - 9:48 am UTC

If you're worried about performance: TEST!

Run the query on your full data set and see if it's "fast enough". If it's not, start digging in by getting the execution plan for the query. You read about how to do this at:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

If you need help once you've got the plan post it here and we'll see what we can do.

Also, I'm not sure I fully understand the additional requirement. Do you only want to show items that have new_item_flag = 'Y' in the data set?

Or from the 1st if new_item_flag is N for all rows for the item. Otherwise from the first date where it's Y?

Braga Simões, July 17, 2017 - 10:32 am UTC

Hi Chris,

Thanks for the valuable reply. I'll let you know if the query is not fast enough.

Regarding your questions (sorry for not being clear):

" Do you only want to show items that have new_item_flag = 'Y' in the data set? "
No, I want to show both. But in order to illustrate the missing requirement I was querying items with new_item_flag = 'Y'

"Or from the 1st if new_item_flag is N for all rows for the item. Otherwise from the first date where it's Y? "
That's it!



Chris Saxon
July 17, 2017 - 2:54 pm UTC

Got it. You get to use another cool function:

min(...) keep (dense_rank first order by ...)

to do this! :)

The code you need is:

min(day_of_month) keep (
  dense_rank first order by new_item_flag desc
) over (partition by item)


So... WTF is that doing?!

First this sorts your rows by the order by clause. It then preserves the rows with the first value in this data set. Because this orders by new_item_flag desc, this is the Y rows (if there are any) or the N rows (if there aren't).

It then passes these rows to min, finding the day_of_month of the first Y or N as appropriate.

OK, but how does that help?

You can stick with the partitioned outer join. And use the first_value method to ensure all the "missing" rows have this value.

Finally ensure that the day is >= this value you've calculated:

drop table sales_staging  purge;
create table sales_staging as 
select * from (
-- item 1:
select 1 item, 1  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 2  day_of_month,  0 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 3  day_of_month, 10 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 4  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 5  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 6  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 7  day_of_month, 35 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 8  day_of_month, 40 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 9  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 1 item, 10 day_of_month,  2 qty_sold, 'N' new_item_flag from dual union all
-- item 2: 
select 2 item, 1  day_of_month, 10 qty_sold, 'N' new_item_flag from dual union all
select 2 item, 2  day_of_month, 21 qty_sold, 'N' new_item_flag from dual union all
select 2 item, 3  day_of_month, 33 qty_sold, 'N' new_item_flag from dual union all
select 2 item, 7  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all -- days 4,5,6 are missing! so 20 is the total sales qty for days 4,5,6 and 7
select 2 item, 8  day_of_month, 14 qty_sold, 'N' new_item_flag from dual union all
select 2 item, 9  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
select 2 item, 10 day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
-- item 3 -> flagged as new item in the store starting from day 4
select 3 item, 4  day_of_month, 20 qty_sold, 'Y' new_item_flag from dual union all
select 3 item, 5  day_of_month, 20 qty_sold, 'Y' new_item_flag from dual union all
select 3 item, 6  day_of_month, 20 qty_sold, 'Y' new_item_flag from dual union all
/* 
I only want to deal with missing days 7 and 8, since it will be assumed 
that  the item just arrived at the stores at the 4th of the month
*/
select 3 item, 9  day_of_month, 30 qty_sold, 'Y' new_item_flag from dual union all 
select 3 item, 10 day_of_month,  2 qty_sold, 'Y' new_item_flag from dual
);
with rws as (
  select rownum dy from dual connect by level <= 10
), stg as (
  select ss.*,
         day_of_month - lag(day_of_month, 1) over (
           partition by item order by day_of_month
         ) missing_days, 
         min(day_of_month) keep (
           dense_rank first order by new_item_flag desc
         ) over (partition by item) mn_dy
  from   sales_staging ss
), qty as (
  select ss.*, rws.dy, 
         first_value(qty_sold) ignore nulls over (
           partition by item order by dy
           rows between current row and unbounded following
         ) nv,
         first_value(missing_days) ignore nulls over (
           partition by item order by dy
           rows between current row and unbounded following
         ) mdays,
         min(mn_dy) over (partition by item) mndy
  from   rws
  left join stg ss partition by (item) 
  on     dy = day_of_month
)
  select dy, mndy, nv, 
         round(nv / case when mdays = 0 then 1 else mdays end, 2) qty
  from   qty q
  where  dy >= mndy
  order  by item , dy;

        DY       MNDY         NV        QTY
---------- ---------- ---------- ----------
         1          1         20         20
         2          1          0          0
         3          1         10         10
         4          1         20         20
         5          1         20         20
         6          1         20         20
         7          1         35         35
         8          1         40         40
         9          1         20         20
        10          1          2          2
         1          1         10         10
         2          1         21         21
         3          1         33         33
         4          1         20          5
         5          1         20          5
         6          1         20          5
         7          1         20          5
         8          1         14         14
         9          1         20         20
        10          1         20         20
         4          4         20         20
         5          4         20         20
         6          4         20         20
         7          4         30         10
         8          4         30         10
         9          4         30         10
        10          4          2          2


PS - I'm guessing the extra rows are supposed to be item 3, right? ;)

Awesome!!!!

A reader, July 17, 2017 - 9:37 pm UTC

I had no idea about partition joins. I wish I knew about this sooner because it would have saved me a lot of headaches from doing SQL "gymnastics" when writing queries like this.
Chris Saxon
July 18, 2017 - 9:57 am UTC

Glad you got this sorted :)

Braga Simões, July 18, 2017 - 1:41 pm UTC

Chris, what a great final solution! Thanks a million!

Besides learning about partitioned outer joins I also learned about the keep clause for analytical functions! Sometimes it seems that the power of SQL has no limits :)

you wrote "PS - I'm guessing the extra rows are supposed to be item 3, right? ;)"
That's right! The query results are exactly what I needed, and the performance is perfect!

Thank you!
Chris Saxon
July 18, 2017 - 1:51 pm UTC

"Sometimes it seems that the power of SQL has no limits :)"

Too right! ;)

Other options

Rajeshwaran, Jeyabal, July 19, 2017 - 4:33 am UTC

Sorry for late in this game, Here is an other option using SQL MODEL Clause.

demo@ORA11G> select item,r day_of_month,qty_sold,
  2       qty_sold/case when x2 -x1 is null or
  3                 x2 - x1 = 0 then 1
  4              else x2 - x1 end new_qty_sold
  5  from sales_staging
  6  model
  7    partition by(item)
  8    dimension by (day_of_month r)
  9    measures( qty_sold , 0 x1,0 x2 )
 10    rules (
 11      x1[for r from 1 to 10 increment 1] =
 12          case when qty_sold[cv()] is null and qty_sold[cv()-1] is not null then
 13             cv(r)-1
 14              when qty_sold[cv()] is null and qty_sold[cv()-1] is null then
 15             x1[cv(r)-1]
 16             when qty_sold[cv()] is not null and qty_sold[cv()-1] is null then
 17             x1[cv(r)-1] else qty_sold[cv()] end ,
 18      x2[any] order by r desc = case when qty_sold[cv()] is null and qty_sold[cv()+1] is not null then
 19             cv(r)+1
 20             when qty_sold[cv()] is null and qty_sold[cv()+1] is null then
 21             x2[cv(r)+1]
 22             when qty_sold[cv()] is not null and qty_sold[cv()-1] is null then cv(r)
 23             else qty_sold[cv()] end ,
 24     qty_sold[any] order by r desc = case when qty_sold[cv()] is null then qty_sold[cv()+1]
 25                        else qty_sold[cv()] end
 26      )
 27  order by item,r ;

      ITEM DAY_OF_MONTH   QTY_SOLD NEW_QTY_SOLD
---------- ------------ ---------- ------------
         1            1         20           20
         1            2          0            0
         1            3         10           10
         1            4         20           20
         1            5         20           20
         1            6         20           20
         1            7         35           35
         1            8         40           40
         1            9         20           20
         1           10          2            2
         2            1         10           10
         2            2         21           21
         2            3         33           33
         2            4         20            5
         2            5         20            5
         2            6         20            5
         2            7         20            5
         2            8         14           14
         2            9         20           20
         2           10         20           20

20 rows selected.

demo@ORA11G>

Chris Saxon
July 19, 2017 - 10:42 am UTC

That doesn't implement the additional "if new_item_flag is Y, start from the first date this is set" requirement...

added the "if new_item_flag is Y " condition to it.

Rajeshwaran, Jeyabal, July 19, 2017 - 2:25 pm UTC

Took the above SQL Model clause and made few enhancements to it.

demo@ORA11G> create table sales_staging as
  2  select * from (
  3  -- item 1:
  4  select 1 item, 1  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
  5  select 1 item, 2  day_of_month,  0 qty_sold, 'N' new_item_flag from dual union all
  6  select 1 item, 3  day_of_month, 10 qty_sold, 'N' new_item_flag from dual union all
  7  select 1 item, 4  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
  8  select 1 item, 5  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
  9  select 1 item, 6  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
 10  select 1 item, 7  day_of_month, 35 qty_sold, 'N' new_item_flag from dual union all
 11  select 1 item, 8  day_of_month, 40 qty_sold, 'N' new_item_flag from dual union all
 12  select 1 item, 9  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
 13  select 1 item, 10 day_of_month,  2 qty_sold, 'N' new_item_flag from dual union all
 14  -- item 2:
 15  select 2 item, 1  day_of_month, 10 qty_sold, 'N' new_item_flag from dual union all
 16  select 2 item, 2  day_of_month, 21 qty_sold, 'N' new_item_flag from dual union all
 17  select 2 item, 3  day_of_month, 33 qty_sold, 'N' new_item_flag from dual union all
 18  select 2 item, 7  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all -- days 4,5,6 are missing! so 20 is the total sales qty for days 4,5,6 and 7
 19  select 2 item, 8  day_of_month, 14 qty_sold, 'N' new_item_flag from dual union all
 20  select 2 item, 9  day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
 21  select 2 item, 10 day_of_month, 20 qty_sold, 'N' new_item_flag from dual union all
 22  -- item 3 -> flagged as new item in the store starting from day 4
 23  select 3 item, 4  day_of_month, 20 qty_sold, 'Y' new_item_flag from dual union all
 24  select 3 item, 5  day_of_month, 20 qty_sold, 'Y' new_item_flag from dual union all
 25  select 3 item, 6  day_of_month, 20 qty_sold, 'Y' new_item_flag from dual union all
 26  /*
 27  I only want to deal with missing days 7 and 8, since it will be assumed
 28  that  the item just arrived at the stores at the 4th of the month
 29  */
 30  select 3 item, 9  day_of_month, 30 qty_sold, 'Y' new_item_flag from dual union all
 31  select 3 item, 10 day_of_month,  2 qty_sold, 'Y' new_item_flag from dual
 32  );

Table created.

demo@ORA11G> with rws as (
  2    select rownum dy from dual connect by level <= 10
  3  ), stg as (
  4    select ss.*,
  5           day_of_month - lag(day_of_month, 1) over (
  6             partition by item order by day_of_month
  7           ) missing_days,
  8           min(day_of_month) keep (
  9             dense_rank first order by new_item_flag desc
 10           ) over (partition by item) mn_dy
 11    from   sales_staging ss
 12  ), qty as (
 13    select ss.*, rws.dy,
 14           first_value(qty_sold) ignore nulls over (
 15             partition by item order by dy
 16             rows between current row and unbounded following
 17           ) nv,
 18           first_value(missing_days) ignore nulls over (
 19             partition by item order by dy
 20             rows between current row and unbounded following
 21           ) mdays,
 22           min(mn_dy) over (partition by item) mndy
 23    from   rws
 24    left join stg ss partition by (item)
 25    on     dy = day_of_month
 26  )
 27    select dy, mndy, nv,
 28           round(nv / case when mdays = 0 then 1 else mdays end, 2) qty
 29    from   qty q
 30    where  dy >= mndy
 31    order  by item , dy;

        DY       MNDY         NV        QTY
---------- ---------- ---------- ----------
         1          1         20         20
         2          1          0          0
         3          1         10         10
         4          1         20         20
         5          1         20         20
         6          1         20         20
         7          1         35         35
         8          1         40         40
         9          1         20         20
        10          1          2          2
         1          1         10         10
         2          1         21         21
         3          1         33         33
         4          1         20          5
         5          1         20          5
         6          1         20          5
         7          1         20          5
         8          1         14         14
         9          1         20         20
        10          1         20         20
         4          4         20         20
         5          4         20         20
         6          4         20         20
         7          4         30         10
         8          4         30         10
         9          4         30         10
        10          4          2          2

27 rows selected.


And the MODEL clause implementation goes like this.

demo@ORA11G> select *
  2  from (
  3  select item,r day_of_month,qty_sold,
  4          qty_sold/case when x2 -x1 is null or
  5                    x2 - x1 = 0 then 1
  6                 else x2 - x1 end new_qty_sold , min_dm
  7     from sales_staging
  8     model
  9       partition by(item)
 10       dimension by (day_of_month r)
 11       measures( qty_sold , 0 x1,0 x2, min(day_of_month) over( partition by item ) as min_dm )
 12       rules (
 13         x1[for r from 1 to 10 increment 1] =
 14             case when qty_sold[cv()] is null and qty_sold[cv()-1] is not null then
 15                cv(r)-1
 16                 when qty_sold[cv()] is null and qty_sold[cv()-1] is null then
 17                x1[cv(r)-1]
 18                when qty_sold[cv()] is not null and qty_sold[cv()-1] is null then
 19                x1[cv(r)-1] else qty_sold[cv()] end ,
 20         x2[any] order by r desc = case when qty_sold[cv()] is null and qty_sold[cv()+1] is not null then
 21                cv(r)+1
 22                when qty_sold[cv()] is null and qty_sold[cv()+1] is null then
 23                x2[cv(r)+1]
 24                when qty_sold[cv()] is not null and qty_sold[cv()-1] is null then cv(r)
 25                else qty_sold[cv()] end ,
 26        qty_sold[any] order by r desc = case when qty_sold[cv()] is null then qty_sold[cv()+1]
 27                           else qty_sold[cv()] end ,
 28        min_dm[any] order by r = case when min_dm[cv()] is null then min_dm[cv()-1]
 29                                else min_dm[cv()] end
 30            )
 31        )
 32  where day_of_month >= min_dm
 33  order by item,day_of_month ;

      ITEM DAY_OF_MONTH   QTY_SOLD NEW_QTY_SOLD     MIN_DM
---------- ------------ ---------- ------------ ----------
         1            1         20           20          1
         1            2          0            0          1
         1            3         10           10          1
         1            4         20           20          1
         1            5         20           20          1
         1            6         20           20          1
         1            7         35           35          1
         1            8         40           40          1
         1            9         20           20          1
         1           10          2            2          1
         2            1         10           10          1
         2            2         21           21          1
         2            3         33           33          1
         2            4         20            5          1
         2            5         20            5          1
         2            6         20            5          1
         2            7         20            5          1
         2            8         14           14          1
         2            9         20           20          1
         2           10         20           20          1
         3            4         20           20          4
         3            5         20           20          4
         3            6         20           20          4
         3            7         30           10          4
         3            8         30           10          4
         3            9         30           10          4
         3           10          2            2          4

27 rows selected.

demo@ORA11G>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

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