Window Functions¶
Window functions are functions which consume values from multiple records to produce a single output. They are similar to aggregate functions, but also have a focused window of analysis to compare to their partition window. Window functions are similar to scalar values to an end user, producing a single value for each input record. However, the consumption visibility for the production of each single record can be many records.
Window function signatures contain all the properties defined for aggregate functions. Additionally, they contain the properties below
| Property | Description | Required |
|---|---|---|
| Inherits | All properties defined for aggregate functions. | N/A |
| Window Type | STREAMING or PARTITION. Describes whether the function needs to see all data for the specific partition operation simultaneously. Operations like SUM can produce values in a streaming manner with no complete visibility of the partition. NTILE requires visibility of the entire partition before it can start producing values. | Optional, defaults to PARTITION |
When binding an aggregate function, the binding must include the following additional properties beyond the standard scalar binding properties:
| Property | Description | Required |
|---|---|---|
| Partition | A list of partitioning expressions. | False, defaults to a single partition for the entire dataset |
| Lower Bound | Bound Following(int64), Bound Trailing(int64) or CurrentRow. | False, defaults to start of partition |
| Upper Bound | Bound Following(int64), Bound Trailing(int64) or CurrentRow. | False, defaults to end of partition |
Aggregate Functions as Window Functions¶
Aggregate functions can be treated as a window functions with Window Type set to STREAMING.
AVG, COUNT, MAX, MIN and SUM are examples of aggregate functions that are commonly allowed in window contexts.