Teradata OLAP Functions
OLAP functions are similar to aggregate functions except that the aggregate functions will return only one value. In contrast, the OLAP function will provide the individual rows in addition to the aggregates.
The OLAP functions are built into the Teradata database to provide data mining capabilities and trend analysis.
These functions provide processing not available using the standard aggregation. The OLAP functions give the result of their operation and display the detail data values used in the function.
The detail row data is displayed as part of the answer set since the output is in row format and not in a report format like WITH.
The OLAP functions may be performed on all tables or views and used to populate tables in conjunction with INSERT/SELECT. The most significant difference is that these functions can be used in Queryman, unlike WITH.
Following is the syntax of the OLAP function.
NOTE: Aggregation functions can be SUM, COUNT, MIN, MAX, and AVG.
Consider the following Salary table of the employees.
Following is an example to find the cumulative sum or running total of NetPay on the Salary table.
Records are sorted by Emp_Id, and the cumulative sum is calculated on the NetPay column.
After executing the above query, it produces the following output.
A QUANTILE function is used to divide rows into several partitions of roughly the same number of rows in each partition. The percentile is the QUANTILE most commonly used in business.
By default, both the QUANTILE column and the QUANTILE value itself will be output in ascending sequence.
In some cases, the ORDER BY clause may be used to reorder the output for display. Here the order of the output does not change the meaning of the output, unlike a summation where the values are being added together and all need to appear in the proper sequence.
Following is the syntax of the QUANTILE function.
RANK function orders the records based on the column provided. The RANK function can also filter the number of records returned based on the Rank.
The RANK function allows a column to be evaluated and compared based on either high or low order, against all other rows to create the output set.
The order will be sorted by default in the descending sequence of the ranking column, which correlates to descending Rank.
The output of the RANK function is the highest or the lowest data values in the column, depending on the sort requested.
Following is the syntax to use the RANK function.
Consider the following Employee table.
The following query orders the records of the employee table by Joined Date and assigns the ranking on Joined Date.
When the above query is executed, it produces the following output.
PARTITION BY clause groups the data by the columns defined in the PARTITION BY clause and performs the OLAP function within each group. Following is an example of the query that uses the PARTITION BY clause.
When the above query is executed, it produces the following output. We can see that the Rank is reset for each Department.