Select Page

# Window Functions

Window Functions are special functions where the result is dependent on the order and grouping of rows. The “window” for a Window Function is defined by the grouping at that level. The result of a window function is determined only by the rows within the window. For example, given a table of city populations, if you grouped by State and then Rank'ed the cities by Population, you would have an independent ranking for each State. If there is no grouping, the “window” is the entire table.

For most Window Functions, the results are dependent on how the rows within the window are sorted. Many Window Functions will only work properly if the table is uniquely sorted by a column in the same “window” as the Window Function. To ensure that there is a unique sort order without any duplicate values, it is often useful to use multi-column sorting criteria to clearly define how to handle duplicate values.

The complexity of Window Functions makes them a little harder to use, but they are also a very powerful tool.

#### Cumulative Window Functions

Cumulative Window Functions are aggregate functions that apply to all of the rows up-to and including the current row.

The window that Cumulative Window Functions are applied in must be uniquely sorted for the function to work properly.

#### Moving Window Functions

Moving Window Functions are aggregate functions which apply to a “window” bracketing the current row. A common moving Window Function  is a “moving average”.

The window is specified as a certain number of rows above and below the current row, constrained by the grouping.

The window that Moving Window Functions are applied in must be uniquely sorted for the function to work properly.

#### Shifting Window Functions

Shifting Window functions either shift the values in a column or repeat a specified value. The functions are useful when calculating month over month data.

The window that Shifting Window Functions are applied in must be uniquely sorted for the function to work properly.

#### Ranking Window Functions

Ranking Window Functions apply a rank to each row based on the criteria in a row provided to the function.

Ranking functions are not dependent on the sort order of the rows in the window it is applied in.

Compute the cumulative distribution of the input column. The cumulative distribution is a number between 0 and 1. The last row is always 1.
Calculates the numerical average of the column up to and including the current value.
Calculates the correlation coefficient of the column up to and including the current value.
Counts the number of non-null values up and including to the current value.
Calculates the maximum across the values up to and including the current value.
Calculates the minimum across the values up to and including the current value.
Calculates the standard deviation across the values up to and including the current value.
Calculates the sum across the values up to and including the current value.
Calculates the variance across the values up to and including the current value.
Replaces a column’s null values with the closest, prior non-null value.
Repeats the first row value of the given column for every row in the output column.
Shift a column downward.
Repeat the last value of the given column for every row in a column.
Shift a column upward.
Joins the values in a column into a single value. Each window of values within a column will be combined into a single list of values.
Calculates the numerical average in a moving window.
Calculates the correlation coefficient in a moving window.
Count values in a moving window. Null values are not counted.
Find the maximum value within a moving window.
Find the minimum value within a moving window.
Calculate the standard deviation within a moving window.
Sum the values in a moving window.
Calculates the statistical variance in a moving window.
Repeat the nth value of the the given column for every row in a column.
Assign the rows of a column to a given number of ranks.
Rank the rows in the table using the input column. Begins with 1. Identical values are assigned identical rank. Defaults to sort Ascending.
Rank the rows in the table using the input column. Begins with 1. Identical values are assigned identical rank, but no gaps are inserted into the number sequence. Defaults to sort ascending.
Rank the rows in the table by percentile using the input column.
Number the rows in the table using the input column. Begins with 1.

We are rewriting the rules of analytics. Sigma empowers domain experts to join the data conversation, answer the toughest questions, and drive insights.