SQL Model - Cummulative Sum

SQL Model Cummulative Sum Using SUM() OVER()

The query below shows the use of SUM() OVER(PARTITION BY ... ORDER BY ... ROWS UNBOUNDED PRECEDING) in Oracle SQL Model syntax to compute cummulative sum for use in data warehousing data analytics.

SELECT
 cc_tld, yr year_, wiget, csum cummulative_sum
FROM 
 (SELECT 'CA' cc_tld, 2009 yr, 12050 wiget 
  FROM DUAL
 UNION
  SELECT 'IN' cc_tld, 2009 yr, 38415 wiget 
  FROM DUAL
 UNION
  SELECT 'US' cc_tld, 2009 yr, 35175 wiget
  FROM DUAL
 UNION
  SELECT 'CA' cc_tld, 2010 yr, 13135 wiget
  FROM DUAL
 UNION
  SELECT 'IN' cc_tld, 2010 yr, 39780 wiget
  FROM DUAL
 UNION
  SELECT 'US' cc_tld, 2010 yr, 34935 wiget
  FROM DUAL
 UNION
  SELECT 'IN' cc_tld, 2011 yr, 41295 wiget
  FROM DUAL
 UNION
  SELECT 'US' cc_tld, 2011 yr, 43145 wiget
  FROM DUAL)
 MODEL
  DIMENSION BY (cc_tld, yr)
  MEASURES (wiget, 0 csum) 
  RULES (csum[any, any]= 
        SUM(wiget)
        OVER (PARTITION BY cc_tld
              ORDER BY yr 
              ROWS UNBOUNDED PRECEDING))
ORDER BY cc_tld, yr;

SQL Model Cummulative Sum Output

Cummulative sum of sequential numbers using Iteration

Oracle registered trademark of Oracle Corporation.

Last Revised On: July 18, 2014

74028