Windowed Count - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

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

Cumulative, Group, Moving and Remaining Count are supported within the Windowed Count function. This function derives a new column giving the cumulative, group, moving or remaining count of the number of rows or rows with non-null values of a value expression, when rows are sorted by a sort expression list. When one or more Partition Columns are specified, the counts are accumulated only over the rows in each partition (the start of a partition resets the accumulated count to 0). When dragging a Windowed Count function into a variable, the following tree element is created.

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

Sort expressions can be built up in the Sort Expressions folder, and Partition Columns can be built up in that folder. By default a windowed COUNT(*) is done, but another expression can be built up in its place, so that only rows with a non-null value of the expression are counted. The options to perform a Cumulative, Group, Moving or Remaining Count, and their associated options, are enabled through the Properties panel. Double-click on Windowed Count, or highlight it and click Properties.

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

These options are defined below for each of the four types of Windowed Count.
  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