Saturday, 21 February 2015

Teradata OLAP Functions

What are OLAP functions? There are some difference between Aggregate functions and OLAP functions.

When OLAP functions are combined with standard SQL within the data warehouse, they provide the ability to analyze large amounts of historical, business transactions from the past through the present. Plus, they provide the ability to project possible future values.

The OLAP functions are the cousins of the aggregate functions, but are very different in their use. 

Like traditional aggregates, OLAP functions operate on groups of rows and permit qualification and filtering of the group result. 

Unlike aggregates, OLAP functions also return the individual row detail data and not just the final aggregated value.


Command
Description
CSUM
Cumulative sum of a referenced value, for a range or dimension.
MSUM
Computation of a moving sum of a referenced value, based on a specified window.
MAVG
Computation of a moving average of a referenced value, based on a specified window.
MDIFF
Computation of a moving difference between two referenced data values, based on a specified window.
MLINREG
Computation of a moving linear regression between two referenced data values, based on a specified window.
RANK
Ranking based on high order or low order of a referenced data value, based on a specified value.
QUANTILE
Categorize a referenced data value, based on a number of partitions.
SAMPLE
Returns only a sample of all rows that would otherwise be returned.
RANDOM
Generates a random number within a specified range.

No comments:

Post a Comment