Oracle get difference in Average of Current and Previous group (partition)

Question

I am using Oracle 12.1.0.2.0 I want difference in average of current group(partition) - average of previous group(partition) My code to get current group Average is

    with rws as (
  select rownum x, mod(rownum, 2) y from dual connect by level <= 10
), avgs as (
  select x, y, avg(x) over (partition by y) mean from rws
)
  select x, y, mean
  from avgs;

Now I want something like :

X   Y  MEAN  PREV_MEAN  MEAN_DIFF  
4   0  6                           
8   0  6                           
2   0  6                           
6   0  6                           
10  0  6                           
9   1  5     6          -1         
7   1  5                        
3   1  5                         
1   1  5                         
5   1  5                
2   2  3     5          -3
3   2  3                
5   2  3                
1   2  3                
4   2  3                

1. AVG( this partitioned group) - Avg( previous partition group) In this case I need ( 5 - 6 ) to compute in GROUP_MEAN_DIFFERENCE column.

2. Also How can I get mean difference always w.r.t first group. In the example above I need (5 - 6) and (3 - 6)

Can you please assist?

Answer 1

Use the function lag() with ignore nulls clause:

select id, val, av, av - lag(av ignore nulls) over (order by id) diff
  from (select id, val, 
               case when row_number() over (partition by id order by null) = 1 
                    then avg(val) over (partition by id) end av 

          from t)
  order by id

Test:

with t (id, val) as (select 1, 44.520 from dual union all
                     select 1, 47.760 from dual union all
                     select 1, 50.107 from dual union all
                     select 1, 48.353 from dual union all
                     select 1, 47.640 from dual union all
                     select 2, 48.353 from dual union all
                     select 2, 50.447 from dual union all
                     select 2, 51.967 from dual union all
                     select 2, 45.800 from dual union all
                     select 2, 46.913 from dual )
select id, val, av, av - lag(av ignore nulls) over (order by id) diff
  from (select id, val, 
               case when row_number() over (partition by id order by null) = 1 
                    then avg(val) over (partition by id) end av 

          from t)
  order by id

Output:

ID   VAL      AV       DIFF
---  -------  -------  -------
1     44.520   47.676   
1     47.760
1     50.107        
1     48.353        
1     47.640    
2     48.353   48.696     1.02
2     50.447        
2     51.967        
2     45.800    
2     46.913