Window function overview

Window function results are dependent on the order and grouping of rows. Therefore, to achieve correct results, you must force a sort in every grouping level at or above the level where the function executes.

Sigma defines the “window” by the grouping level, and calculates results only 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 the 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:

ArrayAgg
Aggregates a column's row values into a single array; one result for each window
CumulativeAvg
Calculates the average of a column's row values, based on the window size and definition
CumulativeCorr
Calculates the numerical average of a column up to and including the current value
CumulativeCount
Counts the number of non-Null values of a column, up and including the current row value
CumeDist
Calculates the cumulative distribution of a column
CumulativeMax

Calculates the maximum value of a column up to and including the current row value

CumulativeMin
Calculates the minimum value of a column up to and including the current row value
CumulativeStdDev
Calculates the standard deviation of a column up to and including the current row value
CumulativeSum
Calculates the sum of the input column up to and including the current row value
CumulativeVariance
Calculates the variance of a column up to and including the current row value
FillDown
Replaces all Null values within a column with the closest, prior non-Null value
First
Returns the first row value of a column
FirstNonNull
Returns the first non-Null row value of a column
Lag
Accesses data in preceding rows in the same result set, without having to join the table to itself.
Last
Returns the last row value in a column or grouping
LastNonNull
Returns the last non-Null value in a column or grouping
Lead
Accesses data in subsequent rows in the same result set, without having to join the table to itself
ListAgg
Aggregates a column's row values into a single list, for each window; returns a text column
ListAggDistinct
Aggregates a column's distinct (non-duplicate) row values into a single list, for each window; returns a text column 
MovingAvg
Calculates the numerical average of a column within a moving window
MovingCorr
Counts the number of non-Null values n a moving window
MovingCount
Calculates the correlation coefficient of two numerical columns within a moving window; see Pearson (or bivariate) correlation coefficient
MovingMax
Finds the maximum value of a column within a moving window
MovingMin
Finds the minimum value of a column within a moving window
MovingStdDev
Calculates the standard deviation of a column within a moving window
MovingSum
Calculates the sum of a column in a moving window
MovingVariance
Calculates the statistical variance of a column in a moving window
Nth
Repeats the Nth value of a column for every row in the column
Ntile
Assigns the specified rank, in order, to the column rows of a column; approximately equal number of rows for each rank
Rank
Assigns ranks to unique values in a column, from rank 1 onwards; skips duplicate values
RankDense
Assigns ranks to all values in a column, from rank 1 onwards; assigns same rank to duplicate values
RankPercentile
Ranks the rows in the table by percentile 
RowNumber
Numbers 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.
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 sort order of rows in the window.