Windowed Aggregate (Generic) - Teradata Warehouse Miner

Teradata® Warehouse Miner™ User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.6
Published
November 2018
Language
English (United States)
Last Update
2018-12-07
dita:mapPath
gxn1538171534877.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

The Windowed Aggregate SQL element provides a means of generically associating “window” options with any standard aggregation function, and beginning with Teradata Release 13.10, with any User Defined Function (UDF) that has been written to support Window options. Window options include Cumulative, Group, Moving and Remaining, as described in more detail below. Note that in the case of a UDF, the window options supported depend on the options specifically implemented in the UDF, which the user must keep in mind when selecting desired window options. When dragging a Windowed Aggregate function into a variable, the following tree element is created.

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

The aggregate function or UDF to apply Window options to is specified using the last node under the Windowed Aggregate element, initially labeled (empty –Aggregate Function or UDF). Simply drag an aggregate function from the Aggregation category or a User Defined Function element from the Other category into this node. Then supply any properties necessary clicking Properties and setting the argument or arguments underneath the aggregation function node appropriately. Sort expressions can be built up in the Sort Expressions folder, and Partition Columns can be built up in the Partition Columns folder, along with an optional Reset When expression as the last element in the folder. The Aggregation Styles of Cumulative, Group, Moving or Remaining and their associated options, are enabled through the Properties panel. Double-click on Windowed Aggregate (Generic), or highlight it and click Properties.

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

The options displayed above are defined below. Note that, when specifying Window options for a UDF, only those aggregation styles that have been specifically coded in the UDF will function properly.
  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