There's more...

There are some OLAP functions that we can use, such as:

• Cumulative sum (CSUM): It computes a running or cumulative total of a column’s value. Consider the following example code block:

/*CSUM select*/
SELECT EMP_ID , CSUM(SALARY,EMP_ID) FROM EMP_SAL;

CSUM(COLUMN_1) will result in an error as CSUM needs two columns as arguments. The first column is used for sorting the sequence and the second column is then used to perform the cumulative sum. Check the code for syntax for CSUM:

/*Syntax for CSUM*/
CSUM(columnname,sortlist)

• Moving Average (MAVG): Used to calculate the moving average on a column. The number of rows used for the aggregation operation is called as query width, as shown in the code:

/*MAVG*/
SELECT EMP_ID,SAL, MAVG (SAL,2,EMP_ID) FROM EMP_SAL

• RANK: A function that assigns a ranking order to rows in a qualified answer set, as shown in the code:

/*RANK*/
SELECT str_id, prod_id, SAL, 
RANK() OVER (ORDER BY SAL DESC) AS Rank_Sales 
FROM SAL_TBL 
QUALIFY rank_sales <= 5;

• MDIFF: Returns the moving difference between the current row-column value and the preceding nth value, as shown in the code:

/*MDIFF*/
SELECT str_id, SAL_MONTH,Target_sale,mdiff(Target_sale,1,str_id)FROM SAL_TBL

•  Percent_Rank: Its value represents the portion of rows in the answer set which precede any given row in the list. It is always a value between 0.0 and 1.0 inclusive, as shown in the code:

/*Percent_RANK*/
SELECT str_id, emp_name, sal, 
RANK()  OVER (ORDER BY sal DESC) AS Rank_Sales,
PERCENT_RANK() OVER (ORDER BY sal DESC) AS Pct_Rank_Sales
FROM SAL_TBL