OLAP - Part 1 - CSUM

• OLAP stands for On-Line Analytical Processing.
   
• Following are important OLAP functions:

·         RANK - (Rankings) 

·         QUANTILE - (Quantiles) 

·         CSUM - (Cumulation) 

·         MAVG - (Moving Averages) 

·         MSUM - (Moving Sums) 

·         MDIFF - (Moving Differences) 

·         MLINREG - (Moving Linear Regression)  

• OLAP functions are similar to aggregate functions in the sense that they operate on groups of rows. However they are different from aggregate functions in the sense that they show detail rows as well. This is not possible with aggregate functions.

Cumulative Sum:

1. Cumulative sum (CSUM) computes a running or cumulative total of a column’s value.

2. Syntax: CSUM(columnname,sortlist)

Here the sortlist is used to sort the data by some column before cumulative sum is performed. Default sequence is ascending.

3. Example:

Suppose we have a table employee with data as below
 

Employeeid	DepartmentNo	Salary	Hiredate
8	400	8000	1/1/1992
2	200	2000	1/1/1998
6	200	6000	3/1/1997
4	400	4000	1/1/1996
12	400	12000	8/1/1999
1	100	1000	1/1/1999
3	300	3000	1/1/1997
10	200	10000	1/1/1998
7	300	7000	6/1/1996
11	300	11000	5/1/1999
9	100	9000	4/1/1999
5	100	5000	2/1/1998

When we execute the below query following is the result we get:

SELECT employeeid,salary,CSUM(salary,hiredate) from employee;

Employeeid	Salary	Hiredate	CSum(Salary,Hiredate)
8	8000	1/1/1992	8000
4	4000	1/1/1996	12000
7	7000	6/1/1996	19000
3	3000	1/1/1997	22000
6	6000	3/1/1997	28000
10	10000	1/1/1998	38000
2	2000	1/1/1998	40000
5	5000	2/1/1998	45000
1	1000	1/1/1999	46000
9	9000	4/1/1999	55000
11	11000	5/1/1999	66000
12	12000	8/1/1999	78000

It is very important to note that CSUM needs a sorting sequence which is first used to sort the data in table and then use it to perform cumulative sum on the column mentioned.

SELECT CSUM(salary) from table1 gives an error.

Note:

SELECT CSUM(salary,salary) from table1 is also a valid query.

Data is first sorted by Salary column and then used to perform CSUM.

We know that default sequence is ascending . However we can change the sequence to descending also.

SELECT employeeid,salary,hiredate,CSUM(salary,hiredate desc) from employee;

Employeeid	Salary	Hiredate	CSum(Salary,Hiredate DESC)
12	12000	8/1/1999	12000
11	11000	5/1/1999	23000
9	9000	4/1/1999	32000
1	1000	1/1/1999	33000
5	5000	2/1/1998	38000
10	10000	1/1/1998	48000
2	2000	1/1/1998	50000
6	6000	3/1/1997	56000
3	3000	1/1/1997	59000
7	7000	6/1/1996	66000
4	4000	1/1/1996	70000
8	8000	1/1/1992	78000