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.
Updated 2 months ago