Ordered Analytical Functions
Ordered analytical functions provide support for many common operations in analytical processing and data mining that require an ordered set of results rows or depend on values in a previous row. Ordered analytical functions enable and expedite the processing of queries containing On Line Analytical Processing (OLAP) style decision support requests.
For example, computing a seven-day running sum requires:
Benefits
Ordered analytical functions extend the Teradata Database query execution engine with the concept of an ordered set and with the ability to use the values from multiple rows in computing a new value.
The result of an ordered analytical function is handled the same as any other SQL expression. It can be a result column or part of a more complex arithmetic expression within its SELECT.
Each of the ordered analytical functions permit you to specify the sort ordering column or columns on which to sort the rows retrieved by the SELECT statement. The sort order and any other input parameters to the functions are specified the same as arguments to other SQL functions and can be any normal SQL expression.
Ordered Analytical Calculations at the SQL Level
Performing ordered analytical computations at the SQL level rather than through a higher-level OLAP calculation engine provides four distinct advantages.
Teradata Warehouse Miner
You need not directly code SQL queries to take advantage of ordered analytical functions. Both Teradata Database and many third-party query management and analytical tools have full access to the Teradata SQL ordered analytical functions. Teradata Warehouse Miner, for example, a tool that performs data mining preprocessing inside the database engine, relies on these features to perform functions in the database itself rather than requiring data extraction.
Teradata Warehouse Miner includes approximately 40 predefined data mining functions in SQL based on the Teradata SQL-specific functions. For example, the Teradata Warehouse Miner FREQ function uses the Teradata SQL-specific functions CSUM, RANK, and QUALIFY to determine frequencies.
Example
The following example shows how the SQL query to calculate a frequency of gender to marital status would appear using Teradata Warehouse Miner.
SELECT gender, marital_status, xcnt,xpct
,CSUM(xcnt, xcnt DESC, gender, marital_status) AS xcum_cnt
,CSUM(xpct, xcnt DESC, gender, marital_status) AS xcum_pct
,RANK(xcnt DESC, gender ASC, marital_status ASC) AS xrank
FROM
(SELECT gender, marital_status, COUNT(*) AS xcnt
,100.000 * xcnt / xall (FORMAT 'ZZ9.99') AS xpct
FROM customer_table A,
(SELECT COUNT(*) AS xall
FROM customer_table) B
GROUP BY gender, marital_status, xall
HAVING xpct >= 1) T1
QUALIFY xrank <= 8
ORDER BY xcnt DESC, gender, marital_status
The result for this query looks like the following table.
|
gender |
marital_status |
xcnt |
xpct |
xcum_cnt |
xcum_pct |
xrank |
F
|
Married
|
3910093
|
36.71
|
3910093
|
36.71
|
1
|
M
|
Married
|
2419511
|
22.71
|
6329604
|
59.42
|
2
|
F
|
Divorced
|
1612130
|
15.13
|
7941734
|
74.55
|
3
|
M
|
Divorced
|
1412624
|
3.26
|
9354358
|
87.81
|
4
|
F
|
Single
|
491224
|
4.61
|
9845582
|
92.42
|
5
|
F
|
Widowed
|
319881
|
3.01
|
10165463
|
95.43
|
6
|
M
|
Single
|
319794
|
3.00
|
10485257
|
98.43
|
7
|
M
|
Widowed
|
197131
|
1.57
|
10652388
|
100.00
|
8
|