Use of Oracle Analytic Functions In Year-Over-Year Analysis
This step involves computing the variance between data of the two years, the percentages and the running totals and year to date (YTD) computations using SUM(..) OVER(PARTITION BY .. ORDER BY ..) from the query output (q_m12) as shown below. Since there will be data greater than zero for previous year months, DECODE(sales,0,1,sales) is not necessary. The step by step analysis is as listed below.
1. For data creation refer to - Data Creation Script
2. Two year summary data refer to - Two Year Summary
3. Create current year versus last year data using LAG() OVER (PARITITION BY .. ORDER BY .. ) refer to - LAG() function

SELECT q_m12.year_id, q_m12.cy_sales, q_m12.py_value py_sales,
   q_m12.cy_sales - q_m12.py_value growth_amt,
   ROUND(100*(q_m12.cy_sales/q_m12.py_value-1),3) monthly_pct,
   SUM(q_m12.cy_sales) OVER (PARTITION BY q_m12.c_year
   ORDER BY q_m12.month_id) cy_sales_ytd,
   SUM(q_m12.py_value) OVER (PARTITION BY q_m12.c_year
   ORDER BY q_m12.month_id) py_sales_ytd,
   SUM(q_m12.cy_sales) OVER (PARTITION BY q_m12.c_year
   ORDER BY q_m12.month_id) -
   SUM(q_m12.py_value) OVER (PARTITION BY q_m12.c_year
   ORDER BY q_m12.month_id) growth_ytd,
   ROUND(100*(SUM(q_m12.cy_sales) OVER (PARTITION BY q_m12.c_year
   ORDER BY q_m12.month_id)/
   SUM(q_m12.py_value) OVER (PARTITION BY q_m12.c_year
   ORDER BY q_m12.month_id)-1),3) ytd_pct
FROM
  (SELECT SUBSTR(q_m24.year_id,1,4) c_year, q_m24.year_id, q_m24.month_id,
    q_m24.sales cy_sales,
    LAG(q_m24.sales,1,0)
      OVER(PARTITION BY q_m24.month_id ORDER BY q_m24.year_id) py_value
  FROM
    (SELECT TO_NUMBER(q_fc.year_id) year_id, q_fc.month_id, fss.sales_amount sales
    FROM fact_sales_summary fss,
     (SELECT TO_CHAR(SYSDATE,'yyyy')||LPAD(level,2,'0') year_id, level month_id
     FROM DUAL CONNECT BY LEVEL <= 12
     UNION
     SELECT TO_CHAR(SYSDATE,'yyyy')-1||LPAD(level,2,'0') year_id, level month_id
     FROM DUAL CONNECT BY LEVEL <= 12
     ) q_fc
    WHERE q_fc.year_id = fss.month_id (+)
    ) q_m24
  ) q_m12
WHERE q_m12.c_year = TO_CHAR(SYSDATE,'YYYY');



Back

Oracle registered trademark of Oracle Corporation.
Last Revised On: 10/27/2013

  74108