OLAP or On Line Analytical Processing functions are knows as “Ordered” analytical functions in Teradata environment.  These functions provides support for many decision support  operations in data mining environments that require an ordered set of results set or depend on values from a previous row. These functions are categorized as follows:

• Window aggregate functions

• Rank function

• Distribution function

• Row number function

• Teradata SQL-specific functions

Advantages of Analytical Functions: Why someone would like to use analytical functions without making any sense of it, let’s discuss the reasons.

• Faster Processing: - As these functions are database specific and processed by query execution engine, processing takes lesser time than in case of using external sorting mechanism.

• Elimination of the need to use external tools which might need exporting large data sets to another environment. Thus coding effort is less too.

Now let’s see which function is used for what. Though most of these functions are aggregate functions, later you would see the usage is different.

SUM/CSUM     : - These functions are used to compute a cumulative sum of a particular group of rows. SUM also can be used to simply calculate group sum. For moving sum use MSUM.

COUNT          : - To calculate cumulative or moving count.

AVG              : - Similarly to compute the moving average use the AVG or MAVG function.

MDIFF           : - To see the difference between the current row (column) and the preceding nth row (column) value. If you want to see the sales numbers (increasing or decreasing) on a daily basis, use this function.

MLINREG        : - To project the next value in a series based on the data pattern present in the series.

QUANTILE      : - To divide the result set into partitions with equal number of rows present in each partition.

RANK            : - This function is used to display the ordered rank of all rows in a particular group.

PERCENT_RANK:- To find out relative rank of a row in a group use PERCENT_RANK.

ROW_NUMBER:- To get the sequential row number of the row within its data subset.

MAX/MIN       : - To calculate the maximum or minimum cumulative value in a group.                  

Popularity: 34% [?]

Our Random Articles

• New Year Wishes 2010
• New Year Wishes
• How to use EXTRACT function with date and time columns
• How to Post on a Separate/New Page in WordPress
• Usage of Macro in Teradata

More Links