Window function overview
Window functions results dependent on the order and grouping of rows. You must therefore apply sort to every grouping level at or above the level where the function executes, to achieve correct results.
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 multicolumn 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 nonNull 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 nonNull value
 First
 Returns the first row value of a column
 FirstNonNull
 Returns the first nonNull 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 nonNull 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 (nonduplicate) 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 nonNull 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 N^{th} 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 upto 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.