Order Analytical Functions - Teradata Warehouse Miner

Ordered Analytical functions (previously known as OLAP or On Line Analytical Processing functions) are distinguished from other SQL functions in that they order the data being operated on before computing the function value, at times making use of “adjacent” observations. Most of the functions are standard SQL functions with a common form, but a few are non-standard Teradata specific functions included because they have no equivalent in some or all Teradata releases. These functions may not be mixed in the same analysis with aggregation functions such as average, and partitioning is not supported in these functions because of their use of the GROUP BY clause to perform partitioning. Some functions also contain Teradata Warehouse Miner specific enhancements, as noted in the individual function descriptions that follow.

All of the standard ordered analytical functions consist of a value expression enclosed in parentheses and an OVER construct composed of an optional PARTITION BY clause, an ORDER BY clause (required with all but group style aggregation), an optional RESET WHEN clause (beginning with Teradata 13.00) for performing conditional partitioning and possibly a ROWS clause (depending on the function), all within parentheses. The PARTITION BY clause is something like the GROUP BY clause in a simple aggregation, partitioning the rows into groups over which the function is separately applied. The PARTITION BY clause effectively causes the function to “start over” for each partitioned group of rows. An example of an ordered analytical function containing these components is given below.

AVG(sales) OVER (PARTITION BY territory
	ORDER BY month
	RESET WHEN sales < SUM(sales) OVER (ORDER BY…)
	ROWS 2 PRECEDING)

An alternate way to specify an ordered analytical function is to use the generic Windowed Aggregate SQL element, effectively defining an OVER clause, and associate with it any standard aggregation function or any User Defined Function (UDF) that was specifically written as an ordered analytical function. Ordered analytical UDFs are first supported in Teradata Release 13.10. Note that this significantly expands the number of available ordered analytical functions, effectively providing a “Windowed Standard Deviation” or a “Windowed Variance”, for example. For more information, refer to the description of the Windowed AggregateWindowed Aggregate (Generic) SQL element.