OLAP functions in Teradata

As of 2020, the organizations are significantly looking for business development. Business development and improvements are vital during this time, and this purely depends on the data and its reports, followed by analytics. OLAP, Online Analytical Processing will provide the results based on the database queries. The results will be delivered based on the queries and the functions you use based on your requirements. OLAP is a broader part of business intelligence, and it is essential to understand the concept of OLAP and its functions. In this blog, you will have a clear understanding of OLAP functions in Teradata

OLAP functions in Teradata - Table of Content

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.




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.

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

Employee_ID, Net_pay,
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:
[QUALIFY QUANTILE () {< I > I = I <= I >=} ];


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:

 QUANTILE Function

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


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

Syntax of Rank function:


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

Example of RANK function: 

Let us consider the below data

RANK function

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:

syntax for the RANK function

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


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.

Syntax of CSUM                                                                                                                                                                                                           


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.

Teradata Training

Weekday / Weekend Batches

Example of MAVG:





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 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


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.

Find our upcoming Teradata Training Online Classes

  • Batch starts on 7th Jun 2023, Weekday batch

  • Batch starts on 11th Jun 2023, Weekend batch

  • Batch starts on 15th Jun 2023, Weekday batch

Global Promotional Image


Request for more information

Research Analyst
As a senior Technical Content Writer for HKR Trainings, Gayathri has a good comprehension of the present technical innovations, which incorporates perspectives like Business Intelligence and Analytics. She conveys advanced technical ideas precisely and vividly, as conceivable to the target group, guaranteeing that the content is available to clients. She writes qualitative content in the field of Data Warehousing & ETL, Big Data Analytics, and ERP Tools. Connect me on LinkedIn.