OLAP functions in Teradata

What are Teradata OLAP functions?

OLAP, Online Analytical Processing help gain insights into the data coming from different means or sources. OLAP helps in performing the execution of the ad-hoc and analytical queries that could be easy and complex. OLAP provides us with the analysis of the data and provides the recommendations and ideas that would help develop the business.

OLAP's functionality is usually performed by using SQL programming language by making use of the extensions available to SQL statements and the functions. Many OLAP functions perform a specific functionality on the available data, retrieving the results accordingly as per the user query. We need to utilize the functions within the SQL statements, which will then return the results as needed based on the query. 

OLAP functions are the functions that are similar to the aggregate functions with a slight difference. With respect to the general aggregate function, the output will be only one value, which means the only value will be returned. When the OLAP functions are used to evaluate the data, the OLAP function will help us with the individual rows and the aggregates. The OLAP functions will provide or display the results of the data values in the most precise format and will give accurate results. The OLAP functions will be performed on all the tables or the views. We have a few sets of OLAP functions that are most commonly used in business-oriented organizations. Let us have a quick review of them.

Want to become a certified Teradata Professional? visit here to get the Teradata Online Training from HKR Trainings

Syntax of an OLAP function:

As OLAP functions are operated using SQL language and statements, this would make us understand that the OLAP functions would usually run based on the rows in the table. The INSERT/SELECT is used to populate the tables. Below is the general syntax of an OLAP function.

OVER

([PARTITION BY] [ORDER BY columnname] [ROWS BETWEEN 

UNBOUNDED PRECEDING AND UNBOUND FOLLOWING)

In the above syntax, the aggregation function could be SUM, COUNT, MIN, MAX, and AVG.

Let us get a clear understanding with an example using some data available.

With the above data, we can retrieve the data by writing the OLAP function in the below format

SELECT
Employee_ID, Net_pay,
SUM(Net_pay) OVER(ORDER BY Emp_Id ROWS
UNBOUNDED PRECEDING) as TotalSalary
FROM Salary;

On the execution of the above query, the output will be returned as follows.

Teradata OLAP Functions

As we have the idea of the syntax and its working, let us understand about the OLAP Functions in Teradata. 

QUANTILE Function: 

A QUANTILE Function is one among the OLAP functions that is specifically used to divide the rows into different partitions of the same number of rows in each partition. Most of the business make use of the QUANTILE Function. By default, the QUANTILE column and QUANTILE value itself will be the output represented in the ascending order. In some scenarios, the ORDER BY clause is used to reorder the output for display purposes. 

Syntax of QUANTILE function:
SELECT QUANTILE (, , [DESC/ASC]
FROM
[QUALIFY QUANTILE () {< I > I = I <= I >=} ];

[OR]

QUANTILE ( quantile_constant, sortlist)
Quantile_constant : it is the constant used to define the number of quantile partitions
Sortlist: it is the column reference used for the partition and to sort the output.

Example to represent QUANTILE Function:

The syntax used to represent the QUANTILE function for the above data is:

SELECT salary, QUANTILE(100, salary DESC) FROM EDW_RESTORE_TABLES.TESTX

Salary Quantile(100,salary)

10000 0
20000 20
30000 40
40000 60
50000 80                                                                                                                                                                                                                                                               
                                                                                                                         

RANK Function:

Rank Function in Teradata will help in the ordering of the records based on the columns provided. The RANK function also helps in filtering the number of records returned based on the rank. The RANK function will provide the flexibility to evaluate each column and compare them based on the highest or lowest order. By defaut, the order will be in the descending order. 

Teradata Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

Syntax of Rank function:

RANK() OVER

([PARTITION BY columnlist ] [ORDER BY columnlist ] [DESC I ASC]

Example of RANK function: 

Let us consider the below data

If you want to Explore more about Teradata? then read our updated article - Teradata Tutorial

The syntax for the RANK function would be:

SELECT EmployeeID, JoinedDate, RANK()

OVER (ORDER by JoinedDate) as Senority

FROM Employee;

The output will be represented as:

Subscribe to our YouTube channel to get new updates..!

Subscribe

CSUM

CSUM is the OLAP function in Teradata is used to compute a running or cumulative total of the column’s value. It helps in returning the cumulative value.

Syntax of CSUM:

CSUM (columnname, sortlist)

Example for CSUM:

The syntax would be : SELECT salesdate, sales, CSUM(sales, salesdate)FROM daily_sales;

The output for the above example would be represented by considering the sales date, sales and csum.

MAVG: 

MAVG refers to Moving Average. MAVG is one of the OLAP function which helps in moving average on a column. The number of rows used for aggregation operation is called as query width.

Syntax for MAVG:

MAVG(colnam, n, sortlist)

Colname: It specifies the column on which the moving average is computed.

N: Refers to the number of rows

Sortlist: It refers to the column which determine the sequencing of the rows.

Example of MAVG:

 SELECT EMPLOYEEID, SALARY, MAVG (SALARY, 2, EMPLOYEEID) FROM

EDW_RESTORE_TABLES.TESTY

MSUM

MSUM is one of the OLAP function in Teradata which helps in calculation of the Moving sum on a column.

Syntax of MSUM: MSUM (colname, n, sortlist)

Colname: It specifies the column on which the moving sum is computed.

N: Refers to the number of rows

Sortlist: It refers to the column which determines the sequencing of the rows.

Example of MSUM:

SELECT salesdate, itemid, sales, MSUM(sales, 3, salesdate)

WHERE itemid = 10

FROM daily_sales;

The output will be represented as follows:

salesdate   itemid      sales               MSum

98/01/01    10  150.00    150.00

98/01/02    10 200.00       350.00   Sum of 2 rows

98/01/03    10 250.00             600.00 Sum of 3 rows starting row 1

98/01/05    10    350.00              800.00 Sum of 3 rows starting row 2

98/01/10     10 550.00           1150.00 Sum of 3 rows starting row 3

98/01/21    10 150.00             1050.00

98/01/25    10    200.00              900.00   Sum of 3 rows

98/01/31    10    100.00              450.00

98/02/01    10    150.00              450.00

98/02/03    10   250.00              500.00

98/02/06    10    350.00            750.00

98/02/17     10 550.00         1150.00

98/02/20     10 450.00         1350.00

98/02/27    10 350.00         1350.00

Frequently Asked Teradata Interview Questions & Answers For Beginners

MDIFF

MDIFF is the OLAP function in Teradata that helps in calculating the moving difference of a specified column based on the defined query width. The width (n) will help in determining how many rows back should the count be done.

Syntax of MDIFF: 

MDIFF (colname, n , sortlist)

Colname: It specifies the column on which the moving difference is computed.

N: Refers to the number of rows

Sortlist: It refers to the column which determines the sequencing of the rows.

Example of MDIFF:

SELECT salesdate, itemid, sales, MDIFF(sales, 3, salesdate)

FROM daily_sales;

The Output will be represented as:

salesdate   itemid    sales MDiff

98/01/01    10 150.00   ? Null because there is no value 3 rows above

98/01/02    10     200.00   ? Null because there is no value 3 rows above

98/01/03    10 250.00   ? Null because there is no value 3 rows above

98/01/05    10 350.00   200.00 Difference this row and row 1

98/01/10    10 550.00   350.00 Difference this row and row 2

98/01/21    10 150.00   -100.00

98/01/25    10 200.00   -150.00

98/01/31    10     100.00   -450.00

98/02/01    10 150.00       .00

98/02/03    10 250.00     50.00  Difference of 2 rows

98/02/06    10 350.00   250.00

98/02/17    10 550.00   400.00

98/02/20    10 450.00   200.00

98/02/27    10 350.00       .00

Conclusion

The OLAP functions in Teradata will help the organizations perform the analytics and understand the trends going through the daily basis. Hence, learning and understanding the OLAP functions in the current business standpoint. I hope this tutorial is helpful and makes you understand the concept of utilizing the functions in the daily basis scenarios that come up. I would recommend you undergo the training and certification of Teradata, which would help you gain in-depth knowledge in the business intelligence world. This would also help you in finding the best opportunities as a part of career improvements and developments. Learn more and gain more knowledge, which will help you attain the right position in this fast technological world.

Related Articles:

1. COALESCE In Teradata
   CSUM Teradata