Window functions overview

The results of a window function depend on the order and grouping of rows in the data. To achieve the results that you want, you must force a sort in every grouping level at or above the level where the function runs.

Sigma defines the βwindowβ by the grouping level, and only calculates results from the rows within the window. For example, if you group a table of city populations by State and then Rank it by city size of population, you get an independent ranking for each State. Without a grouping, the βwindowβ is the entire table.

For most window functions, the results depend on the sort order of rows within the window. Many window functions only work if the table is uniquely sorted by a column in the same βwindowβ as the function. To ensure a unique sort order without duplicate values, use multi-column sorting criteria to clearly define how to handle duplicate values.

See Window function categories for more detailed groupings of window functions.

Sigma supports the following window functions:

ArrayAggAggregates a column's row values into a single array; one result for each window.
CumulativeAvgCalculates the average of a column's row values, based on the window size and definition.
CumulativeCorrCalculates the numerical average of a column up to and including the current value.
CumulativeCountCounts the number of non-Null values of a column, up to and including the current row value.
CumeDistCalculates the cumulative distribution of a column.
CumulativeMaxCalculates the maximum value of a column up to and including the current row value.
CumulativeMinCalculates the minimum value of a column up to and including the current row value.
CumulativeStdDevCalculates the standard deviation of a column up to and including the current row value.
CumulativeSumCalculates the sum of the input column up to and including the current row value.
CumulativeVarianceCalculates the variance of a column up to and including the current row value.
FillDownReplaces all Null values within a column with the closest prior non-Null value.
FirstReturns the first row value of a column.
FirstNonNullReturns the first non-Null row value of a column.
LagAccesses data in preceding rows in the same result set, without having to join the table to itself.
LastReturns the last row value in a column or grouping.
LastNonNullReturns the last non-Null value in a column or grouping.
LeadAccesses data in subsequent rows in the same result set, without having to join the table to itself.
ListAggAggregates a column's row values into a single list. For each window, returns a text column.
ListAggDistinctAggregates a column's distinct (non-duplicate) row values into a single list. For each window, returns a text column.
MovingAvgCalculates the numerical average of a column within a moving window.
MovingCorrCounts the number of non-Null values in a moving window.
MovingCountCalculates the correlation coefficient of two numerical columns within a moving window. See Pearson (bivariate) correlation coefficient.
MovingMaxFinds the maximum value of a column within a moving window.
MovingMinFinds the minimum value of a column within a moving window.
MovingStdDevCalculates the standard deviation of a column within a moving window.
MovingSumCalculates the sum of a column in a moving window.
MovingVarianceCalculates the statistical variance of a column in a moving window.
NthRepeats the nth value of a column for every row in the column.
NtileAssigns the specified rank, in order, to the column rows of a column, approximately equal number of rows for each rank.
RankAssigns ranks to unique values in a column, from rank 1 onwards. Skips duplicate values.
RankDenseAssigns ranks to all values in a column, from rank 1 onwards. Assigns the same rank to duplicate values.
RankPercentileRanks the rows in the table by percentile.
RowNumberNumbers the table rows, starting with 1.

Window function categories

The categories of window functions are:

Cumulative window functions
Cumulative window functions return aggregates of all rows, up to and including the current row.
Sort the window before applying cumulative window functions to ensure correct results.
Moving window functions
Moving window functions aggregate the results of a window that brackets the current row. Identify the window size by specifying the number of rows before and after the current row, and constrain it by grouping. The number of rows that define the window size must be either an integer or a an integer parameter; it cannot be a result of a calculation.
Sort the window before applying cumulative window functions, to ensure correct results.
Shifting window functions
Shifting window functions either shift the values in a column or repeat a specified value. The functions are useful when comparing performance for similar time intervals. For an example, see [Year Over Year Sales by Month](doc:create-and-edit-period-over-period-analysis).
Sort the window before applying cumulative window functions to ensure correct results.
Ranking window functions
Ranking window functions rank each row based on values in the specified column.
Ranking functions are independent of row sort order in the window.