Search code examples
plsqloracle11g

Moving average for all unique records in Oracle PLSQL

I have a table structure as below

SQL> desc trx_mf_amfi_navs ;

Name                   Type          Nullable Default Comments 
---------------------- ------------- -------- ------- -------- 
SCHEME_CODE            NUMBER                                  
ISIN_DIV_PAYOUT_GROWTH VARCHAR2(100) Y                         
ISIN_DIV_REINVESTMENT  VARCHAR2(100) Y                         
SCHEME_NAME            VARCHAR2(200) Y                         
NET_ASSET_VALUE        NUMBER        Y                         
NAV_DATE               DATE

Below are the records for different mutual funds scheme and have displayed only 2 Scheme Code details enter image description here

I wrote a query as below

Select t1.scheme_code,
       t1.scheme_name,
       t1.net_asset_value,
       round(avg(t1.net_asset_value)
             over(partition by t1.scheme_code order by t1.scheme_code
                  rows between 3 preceding and current row),
             4) as "3Day_SMA"
  from trx_mf_amfi_navs t1
 where t1.scheme_code in ('118834', '118825')
   and t1.nav_date >= '01-FEB-2022'

Result as below from query enter image description here

I would like to have the output for all schemes with moving average for last nav_date for that scheme_code

Scheme_Code,Scheme_Name, NET_ASSET_VALUE,max(nav_date),3day_SMA

118825  Mirae Asset Large Cap Fund - Direct Plan - Growth   81.56   28-FEB-2022 81.1173
118834  Mirae Asset Emerging Bluechip Fund - Direct Plan - Growth   100.84  28-FEB-2022 99.7675

Your help will be highly appreciated

Solution

  • If I understood u right u are just trying to summarize your existing table with 3day_SMA to a table that contains only last day rows grouped by scheme_code. If that s what u are looking for then this should solve your problem:

     with
test1 as(
         Select t1.scheme_code,
                t1.scheme_name,
                t1.net_asset_value,
                t1.nav_date,
                round(avg(t1.net_asset_value)
                      over(partition by t1.scheme_code order by t1.scheme_code
                           rows between 3 preceding and current row),
                           4) as "3Day_SMA"
        from trx_mf_amfi_navs t1
        where t1.scheme_code in ('118834', '118825')
        and t1.nav_date >= '01-FEB-2022'
        )
        select * from test1 where (scheme_code,nav_date) in
        (select scheme_code,max(nav_date) from test1 group by scheme_code)

    Or with join on:

     Select t1.scheme_code,
        t1.scheme_name,
        t1.net_asset_value,
        t2.max_date,
        t1."3Day_SMA"
        from 
        (select scheme_code,scheme_name, net_asset_value,nav_date, round(avg(net_asset_value)
        over(partition by scheme_code order by scheme_code
        rows between 3 preceding and current row),4) as "3Day_SMA" from trx_mf_amfi_navs) t1,
        (select scheme_code,max(nav_date) max_date from trx_mf_amfi_navs group by scheme_code) t2
        where
        t1.scheme_code=t2.scheme_code
        and t1.scheme_code in ('118834', '118825')
        and t1.nav_date >= '01-FEB-2022'
        and t1.nav_date=t2.max_date