Windowing Functions

SAQL now supports windowing, using a syntax inspired by SQL. 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:

1<windowfunction>(<projection expression>) over (<row range> partition by <reset groups> order by <order clause>) as <label>

When using ranking functions, use the following syntax:

1<rankfunction> over([..] partition by <reset groups> order by <order clause>) as <label>

Where:

windowfunction

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

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:

1a = load "dataset";
2b = foreach a generate sum(sum(sales)) over([.. 0] partition by all order by all);

Multiple Resets and Multiple Orders

Multiple resets and multiple orders are valid. For example:

1sum(sum(Sales)) over([-2 .. 0] partition by (OrderDate_Year, OrderDate_Quarter) order by OrderDate_Year)
2       
3sum(sum(Sales)) over([-2 .. 0] partition by (Year, Quarter) order by (Year asc, sum(Sales) desc))

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:

1a = load "dataset1";
2b = load "dataset2";
3c = group a by column1, b by column2;
4d = foreach c generate sum(sum(a[sales])) over([.. 0] partition by b[column2] order by all)

Note

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.

1q = load "dataset";
2q = group q by (OrderDate_Year, OrderDate_Quarter);
3q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sum_amt, sum(sum(Sales)) over([.. 0] partition by all order by (OrderDate_Year, OrderDate_Quarter)) as r_sum;

Running Totals By Year

Running total resets on every year.

1q = load "dataset";
2q = group q by (OrderDate_Year, OrderDate_Quarter);
3q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sum_amt, sum(sum(Sales)) over([.. 0] partition by OrderDate_Year order by (OrderDate_Year, OrderDate_Quarter)) as r_sum;

Min Sales Trailing 3 Quarters (Moving Min)

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

1q = load "dataset";
2q = group q by (OrderDate_Year, OrderDate_Quarter);
3q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sumSales, min(sum(Sales)) over([-2 .. 0] partition by OrderDate_Year order by (OrderDate_Year, OrderDate_Quarter)) as m_min;

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.

1q = load "dataset";
2q = group q by (OrderDate_Year, OrderDate_Quarter);
3q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sumSales, (sum(Sales) * 100) / sum(sum(Sales)) over([..] partition by OrderDate_Year) as p_tot;

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.

1q = load "dataset";
2q = group q by (OrderDate_Year, OrderDate_Quarter);
3q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sumSales, sum(Sales) - sum(sum(Sales)) over([-1 .. -1] partition by OrderDate_Year order by (OrderDate_Year, OrderDate_Quarter)) as diff;

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

1q = load "dataset";
2q = group q by (Year, Quarter);
3q = foreach q generate Year, Quarter, sum(Sales) as sum_amt, rank() over([..] partition by Year order by sum(Sales)) as rank;

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.

1q = load "dataset";
2q = group q by (Year, Quarter);
3q = foreach q generate Year, Quarter, sum(Sales) as sum_amt, rank() over([..] partition by Year order by sum(Sales)) as rank;
4q = filter q by rank <= 3;