Window functions

Window functions perform operations across an entire table, table grouping (grouped rows), or defined window of rows. Sigma supports cumulative, moving, shifting, and ranking window functions.

Cumulative window functions

Cumulative window functions evaluate a specified column in a table or grouping and return the running total or cumulative value for all rows up to and including the current row. This differs from aggregate values that calculate a summary value for the entire table or grouping.

CumulativeAvgCalculates the running average up to and including the current row.
CumulativeCorrCalculates the correlation coefficient between dependent and independent data columns up to and including the current row.
CumulativeCountCounts the number of non-null values up to and including the current row.
CumeDistCalculates the cumulative distribution of values relative to the current row value.
CumulativeMaxReturns the largest value up to and including the current row.
CumulativeMinReturns the smallest value up to and including the current row.
CumulativeStdDevCalculates the standard deviation of values up to and including the current row.
CumulativeSumCalculates the sum of values up to and including the current row.
CumulativeVarianceCalculates the variance of a column up to and including the current row.

Moving window functions

Moving window functions evaluate a specified column and return a value based on a defined window of rows that moves in relation to the current row.

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.

Shifting window functions

Shifting window functions evaluate a specified column in a table or grouping and return the value from a row that shifts in relation to the current row.

FillDownReplaces all `null` values in a column or grouping with the closest prior non-null value.
FirstReturns the first row value of a column or grouping.
FirstNonNullReturns the first non-null value from a column or grouping.
LagReturns the value from a preceding offset row in a column or grouping.
LastReturns the last row value in a column or grouping.
LastNonNullReturns the last non-null value in a column or grouping.
LeadReturns the value from a subsequent offset row in a column or grouping.
NthReturns the value from the nth row of a column or grouping.

Ranking window functions

Ranking window functions evaluate a specified column in a table or grouping and assign a rank to each row.

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.