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.
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.
-
Aggregation Style — CumulativeSecond Row Style
- None, or
- Current Row, or
- Value Preceding, or
- Value Following.
(If Value Preceding/Following)
Second Value — 0-n
- Aggregation Style — Group
-
Aggregation Style — MovingFirst 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
-
Aggregation Style — RemainingFirst Row Style
- Current Row, or
- Value Preceding, or
- Value Following.
(If Value Preceding/Following)
First Value — 0-n