
DarkLight
Window Calculation

DarkLight
Overview
Allows the user to set up a calculation using a window function. Window functions operate on a specific subset ("window") of a data set.
For more information, read:
 Snowflake window functions documentation
 Amazon Redshift window functions documentation
 Google BigQuery window functions documentation
 Databricks on Databricks window functions documentation
 Azure Synapse Analytics window functions documentation
Properties
Property  Type  Description 

Name  Text  A humanreadable name for the component. 
Include Input Columns  Yes/No  Defines whether the component passes all input columns into the output. The default is Yes. 
Partition Data  List of Columns  Select the columns that will define how the input data is partitioned. The window calculation will be performed on each partition. 
Ordering within partitions  List of Columns  Select the columns that will be used to sort the partitioned data. For each column, select the sort order: Asc (sort ascending), Desc (sort descending) Nulls First (sort null values first), or Nulls Last (sort null values last). You can select multiple columns to create a complex sort. You can drag the selected columns to reorder the sort level if required. 
Lower Bound  Select  This property is visible after Ordering within partitions is set. Select which row of the partition the window calculation will start on. Options are: unbounded preceding: The window starts at the first row of the partition. current row: The window starts at the current row. offset preceding: The window starts a number of rows (offset) before the current row. This requires you to set the Lower Bound Offset property. 
Upper Bound  Select  This property is visible after Ordering within partitions is set. Select which row of the partition the window calculation will end on. Options are: unbounded following: The window ends at the last row of the partition. current row: The window ends at the current row. offset following: The window ends a number of rows (offset) after the current row. This requires you to set the Upper Bound Offset property. 
Lower Bound Offset  Integer  If the Lower Bound property is set to offset preceding, enter the number of rows before the current row that the window will start on. 
Upper Bound Offset  Integer  If the Upper Bound property is set to offset following, enter the number of rows after the current row that the window will end on. 
Functions  Select  Select a Window Function to apply to be performed on the rows contained in the window. See the list of supported functions below. Multiple functions can be selected. For each function, select the Input Column that the function will act on and the Output Column that the result will be written to. 
Strategy
Generates a SELECT
statement with a window function inline using the OVER
keyword. Window functions provide you with the ability to create analytical business queries more efficiently. Window functions operate on a partition or "window" of a data set, and return a value for every row in that window.
Supported functions
Matillion ETL currently supports the following window functions:
Window function  Description 

Any Value  Snowflake only. Returns some value of the expression from the group. For full details, read the documentation for Snowflake. 
Approximate Count (APPROX_COUNT [DISTINCT])  Snowflake only. Uses HyperLogLog to return an approximation of the distinct cardinality of the input. For full details, read the documentation for Snowflake. 
Array Aggregate (ARRAY_AGG [DISTINCT])  Snowflake only. Returns the input values, pivoted into an array. For full details, read the documentation for Snowflake. 
Average (AVG)  Returns the average (arithmetic mean) of the input column values in the window. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse. 
Bit AND Aggregate (BITAND_AGG)  Snowflake only. Returns the bitwise AND value of all nonNull numeric records in a group. For full details, read the documentation for Snowflake. 
Bit OR Aggregate (BITOR_AGG)  Snowflake only. Returns the bitwise OR value of all nonNull numeric records in a group. For full details, read the documentation for Snowflake. 
Bit XOR Aggregate (BITXOR_AGG)  Snowflake only. Returns the bitwise XOR value of all nonNull numeric records in a group. For full details, read the documentation for Snowflake. 
Conditional Change Event  Snowflake only. Returns a window event number for each row where the value of an argument is different from the value of the argument in the previous row. For full details, read the documentation for Snowflake. 
Conditional True Event  Snowflake only. Returns a window event number for each row within a window partition based on the result of a boolean argument. For full details, read the documentation for Snowflake. 
Count  Returns a count of the nonNull values for the specified field. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse. 
First Value  Given an ordered set of rows, returns the specified column value with respect to the first row in the window frame. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse. 
Hash Aggregate (HASH_AGG)  Snowflake only. Returns an aggregate signed 64bit hash value over the (unordered) set of input rows. For full details, read the documentation for Snowflake. 
Kurtosis  Snowflake only. Returns the population excess kurtosis of nonNull records. For full details, read the documentation for Snowflake. 
Last Value  Given an ordered set of rows, returns the specified column value with respect to the first row in the window frame. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse. 
List Aggregate (LISTAGG [DISTINCT])  Snowflake and Redshift only. Returns the concatenated input values, separated by a delimiter string. For full details, read the documentation for Snowflake or Redshift. 
Maximum (MAX)  Returns the maximum of the input expression values. The MAX function works with numeric values and ignores Null values. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse. 
Median  Snowflake and Redshift only. Calculate the median value for the range of values in a window or partition. Null values in the range are ignored. For full details, read the documentation for Snowflake or Redshift. 
Minimum (MIN)  Returns the minimum of the input expression values. The MIN function works with numeric values and ignores Null values. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse. 
Population Variance (VAR_POP)  Returns the sample variance of a set of numeric columns. For full details, read the documentation for Snowflake, Redshift, Databricks, or Synapse (VARP function). 
Sample Variance (VAR_SAMP)  Returns the sample variance of a set of numeric columns. For full details, read the documentation for Snowflake, Redshift, Databricks, or Synapse (VAR function). 
Standard Deviation (STDDEV)  Returns the standard deviation of a set of numeric values. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse (STDEV function). 
Standard Deviation Population (STDDEV_POP)  Returns the population standard deviation of a set of numeric values. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse (STDEVP function). 
Sum  Returns the sum of the input column in the window. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse. 
The following window functions are supported by the Rank component.
Window function  Description 

Cumulative Distribution (CUME_DIST)  Determines the cumulative distribution of a value within a window or partition. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse. 
Dense Rank  Determines the rank of a value in a group of values. If two or more rows tie, there is no gap in the sequence of ranked values. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse. 
Percent Rank  Calculates the percent rank of a given row. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse (NTILE function). 
Rank  Determines the rank of a value in a group of values. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse. 
Row Number  Determines the ordinal number of the current row within a group of rows, counting from 1. For full details, read the documentation for Snowflake, Redshift, BigQuery, Databricks, or Synapse. 