IBM PureData System for Analytics, Version 7.1

Windowing

Analytic functions analyze a window of data. A window is a user-specified selection of rows (or a logical partition of a query that determines the set of rows) used to do certain calculations for the current row. Using windowing you can determine ranking, distribution of values, or moving averages and sums.

A window has three components:

Window partitioning

Groups all rows that have partition column values that are equal to the values in the specified row. The system returns each row of a partition that has an equal value on the set of specific rows in a table, rather than collapsing them into a single representative row as is the case with grouped aggregate functions.

Window ordering

Use to order rows within each partition. Because all rows in a partition have equal values in their partitioning columns, you usually order the rows by values in other columns.

Window framing

Defines the size of the window within a window partition. You can express it in physical terms (the number of rows) or in logical terms (a range of values). Window framing is also called window aggregation grouping.

Framing can be either row-based or range-based. Framing can specify anchored or floating frame endpoints. If the frame is anchored, you can specify unbounded preceding or unbounded following. If the frame is floating, you can specify an absolute row offset (for row-based frames) or as a delta from the order column value (for range-based frames) of the current row.

The frames for functions in the Window Aggregation family can also specify an exclusion as: exclude no others, exclude current row, exclude ties, or exclude group. For details about which functions support the exclude clause in syntax, see Netezza SQL analytic functions.