15.00 - Ordered Analytical Functions - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

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:

  • First, that rows be ordered by date.
  • Then, that the value for the running sum be computed by:
  • Adding the current row value to the value of the sum from the previous row, and
  • Subtracting the value from the row eight days ago.
  • 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.

  • Reduced programming effort.
  • Elimination of the need for external sort routines.
  • Elimination of the need to export large data sets to external tools because ordered analytical functions enable you to target the specific data for analysis within the warehouse itself by specifying conditions in the query.
  • Marked enhancement of analysis performance over the slow, single-threaded operations that external tools perform on large data sets.
  • 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