Windowed Average - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.4
Published
July 2017
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
fcf1492702067123.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

Aggregation styles of Cumulative, Group, Moving and Remaining are supported by this function. Given a value expression, an aggregation style, first row style and value and second row style and value, along with a sort expression list and optional partition columns and “reset when” expression, this function derives a new column giving the cumulative, group, moving or remaining aggregation of the value expression over the specified rows. For rows that have less than the specified rows preceding it in the table or group, the function is computed using all preceding rows, and similarly for following rows. When one or more Partition Columns are specified, the values are computed separately over the rows in each partition. Any of the Rows options may be used to determine the type of aggregate to compute. When dragging a Windowed Average function into a variable, the following tree element is created.

Variable Creation > Input > Variables: SQL Elements pane - Logical > Windowed Average

Sort expressions can be built up in the Sort Expressions folder, and Partition Columns can be built up in that folder, followed optionally by a Reset When clause. The options to perform a Cumulative, Group, Moving or Remaining Average, and their associated row options, are enabled through the Properties panel. Double-click on Windowed Average, or highlight it and click Properties.

Variable Creation > Input > Variables: SQL Elements pane - Logical > Windowed Average Properties

These options are defined below for each of the four types of Windowed Averages.
  1. Aggregation Style — Cumulative
    Second Row Style
    • None, or
    • Current Row, or
    • Value Preceding, or
    • Value Following.

      (If Value Preceding/Following)

      Second Value — 0-n

  2. Aggregation Style — Group
  3. Aggregation Style — Moving
    First Row Style
    • Current Row, or
    • Value Preceding, or
    • Value Following.

      (If Value Preceding/Following)

      First Value — 0-n

      Second Row Style

    • None, or
    • Current Row, or
    • Value Preceding, or
    • Value Following.

      (If Value Preceding/Following)

      Second Value — 0-n

  4. Aggregation Style — Remaining
    First Row Style
    • Current Row, or
    • Value Preceding, or
    • Value Following.

      (If Value Preceding/Following)

      First Value — 0-n