Windowing Functions

Windowing functions allow you to calculate data for a single group using aggregated data from adjacent groups. Windowing does not change the number of rows returned by the query. Windowing aggregates across groups rather than within groups and accepts any valid numerical projection on which to aggregate.

Windowing with an aggregate function uses the following syntax:

When using ranking functions, use the following syntax:

Where:

windowfunction

An aggregate function that supports windowing. Currently supported functions are avg, sum, min, max, count, median, percentile_disc, and percentile_cont.

rankfunction

Returns a rank value for each row in a partition. The following ranking functions are supported: rank(), dense_rank(), cume_dist() and row_number(). Refer to the Ranking Functions section for examples.

projection expression

The expression used to generate a projection from the values of specified columns.

row range

Row ranges are specified using the following syntax.

Range	Meaning
[.. 0]	From beginning to current row in the reset group.
[0 ..]	From current row to the last row in the reset group.
[-2 .. 0]	From two rows prior to current row. Window covers 3 rows.
[0 .. 2]	From current row to 2 rows ahead of current row. Windows covers 3 rows.
[-1 .. -1]	One row prior to current row. Window includes a single row.
[.. -2]	From beginning of reset group to 2 rows prior to current row.
[..]	Aggregates the entire reset group.

reset groups

The column(s) which reset windowing aggregation when their value(s) change. A reset group of all indicates no reset boundaries for the window aggregation.

order clause

Specify column(s) by which to sort. This orders the rows before the window function gets evaluated.

The order clause is not allowed on expressions where the row range is [..] and the window function is sum, avg, min, or max. For example, sum(sum(Sales)) over([..] partition by Year order by Quarter) is invalid.

Note

label

The output column name.

Notes

Grouped Queries

Windowing functionality is enabled only for grouped queries. The following is not valid:

Multiple Resets and Multiple Orders

Multiple resets and multiple orders are valid. For example:

Cogroups

Windowing functions can be used with cogroup queries. For example:

sum(sum(a[Sales])) over([-2 .. 0] partition by (a[Year], a[Quarter]) order by (a[Year] asc, sum(a[Sales]) desc))

Each Windowing function can be used with only 1 cogroup stream. The following is not valid:

Note

Refer to the Aggregate Functions topic for details on function usage.

Example - Dynamically Display Your Top Five Reps

The resulting graph shows the top-five reps in each country and displays each rep’s ranking.

Examples

Running Total (No Reset)

The following query calculates the running total of sum of sales every quarter, with "partition by all" denoting that the sum is not reset by any column.

Running Totals By Year

Running total resets on every year.

Min Sales Trailing 3 Quarters (Moving Min)

Finds the moving minimum values in the window of last two rows to current row.

Percentage Total

This query calculates the percentage of the quarter’s sales for the year. Row range [..] calculates the subtotals of each year, which is used in the formula to calculate the percentage.

Differences Along Year

This query calculates the growth of sales compared with the previous quarter, with [-1 .. -1] referring to the quarter before the quarter on the row. The blank spaces in the result table represent null values.

Ranking Functions

rank()

Assigns rank based on order. Repeats rank when the value is the same, and skips as many on the next non-match.

dense_rank()

Same as rank() but doesn’t skip values on previous repetitions.

cume_dist()

Calculates the cumulative distribution (relative position) of the data in the reset group.

row_number()

Assigns a number incremented by 1 for every row in the reset group.

Examples

The following table also shows result columns as if the dense_rank(), cume_dist() and row_number() functions were substituted for rank() in the previous code.

This query shows the top 3 performing quarters in a year.

This query shows the 95th percentile.

Refer to the Aggregate Functions topic for details on function usage.