Rank

Dark

Light

Rank Component

Allows the user to determine the rank of a value in a group of values, output as a new column. The function used depends on the data warehouse as below:

Snowflake: Snowflake window function
Redshift: Redshift window function
BigQuery: BigQuery window function
Synapse: Transact SQL ranking function
Delta Lake: Databricks SQL window function

Properties

Snowflake Properties
Property	Setting	Description
Name	Text	A human-readable name for the component.
Include Input Columns	Yes/No	Defines whether the component passes all input columns into the output.
Partition Data	List of Columns	Defines how the input data is partitioned in order to perform the rank calculation. The calculation is then performed on each partition.
Ordering within partitions	Input Column	The input column name for sorting within the partitioned data. Note: you can drag to reorder
Ordering within partitions	Ordering	The order of the sorting: Ascending (Asc) or Descending (Desc)
Functions	Window Function	Rank - determines the rank of a value in a group of values. Dense Rank - determines the rank of a value in a group of values. The Dense Rank function differs from rank in one respect: If two or more rows tie, there is no gap in the sequence of ranked values. Cumulative Distribution - determines the cumulative distribution of a value within a window or partition. Percent Rank - Calculates the percent rank of a given row. Row Number - Determines the ordinal number of the current row within a group of rows, counting from 1.
Functions	Output Column	The name of the output column that the window function will create.

Redshift Properties
Property	Setting	Description
Name	Text	A human-readable name for the component.
Include Input Columns	Yes/No	Defines whether the component passes all input columns into the output.
Partition Data	List of Columns	Defines how the input data is partitioned in order to perform the rank calculation. The calculation is then performed on each partition.
Ordering within partitions	Input Column	The input column name for sorting within the partitioned data. Note: you can drag to reorder
Ordering within partitions	Ordering	The order of the sorting: Ascending (Asc) or Descending (Desc)
Functions	Window Function	Rank - determines the rank of a value in a group of values. More... Dense Rank - determines the rank of a value in a group of values. The Dense Rank function differs from rank in one respect: If two or more rows tie, there is no gap in the sequence of ranked values. More... Cumulative Distribution - determines the cumulative distribution of a value within a window or partition. More... Percent Rank - Calculates the percent rank of a given row. More... Row Number - Determines the ordinal number of the current row within a group of rows, counting from 1. More...
Functions	Output Column	The name of the output column that the window function will create.

BigQuery Properties
Property	Setting	Description
Name	Text	A human-readable name for the component.
Include Input Columns	Yes/No	Defines whether the component passes all input columns into the output.
Partition Data	List of Columns	Defines how the input data is partitioned in order to perform the rank calculation. The calculation is then performed on each partition.
Ordering within partitions	Input Column	The input column name for sorting within the partitioned data. Note: you can drag to reorder
Ordering within partitions	Ordering	The order of the sorting: Ascending (Asc) or Descending (Desc)
Functions	Window Function	Rank - determines the rank of a value in a group of values. Dense Rank - determines the rank of a value in a group of values. The Dense Rank function differs from rank in one respect: If two or more rows tie, there is no gap in the sequence of ranked values. Cumulative Distribution - determines the cumulative distribution of a value within a window or partition. Percent Rank - Calculates the percent rank of a given row. Row Number - Determines the ordinal number of the current row within a group of rows, counting from 1.
Functions	Output Column	The name of the output column that the window function will create.

Synapse Properties
Property	Setting	Description
Name	Text	A human-readable name for the component.
Include Input Columns	Yes/No	Defines whether the component passes all input columns into the output.
Partition Data	List of Columns	Defines how the input data is partitioned in order to perform the rank calculation. The calculation is then performed on each partition.
Ordering within partitions	Input Column	The input column name for sorting within the partitioned data. Note: you can drag to reorder
Ordering within partitions	Ordering	The order of the sorting: Ascending (Asc) or Descending (Desc)
Functions	Window Function	Rank - determines the rank of a value in a group of values. Dense Rank - determines the rank of a value in a group of values. The Dense Rank function differs from rank in one respect: If two or more rows tie, there is no gap in the sequence of ranked values. Cumulative Distribution - determines the cumulative distribution of a value within a window or partition. Percent Rank - Calculates the percent rank of a given row. Row Number - Determines the ordinal number of the current row within a group of rows, counting from 1.
Functions	Output Column	The name of the output column that the window function will create.

Delta Lake Properties
Property	Setting	Description
Name	Text	A human-readable name for the component.
Include Input Columns	Select	Defines whether the component passes all input columns into the output.
Partition Data	Column Select	Defines how the input data is partitioned in order to perform the rank calculation. The calculation is then performed on each partition.
Ordering within partitions	Input Column	The input column name for sorting within the partitioned data. Note: you can drag to reorder.
Ordering within partitions	Ordering	The order of the sorting: Ascending (Asc) or Descending (Desc)
Functions	Window Function	Cumulative Distribution: determines the cumulative distribution of a value within a window or partition. Dense Rank: determines the rank of a value in a group of values. The Dense Rank function differs from rank in one respect: If two or more rows tie, there is no gap in the sequence of ranked values. Percent Rank: calculates the percent rank of a given row. Rank: determines the rank of a value in a group of values. Row Number: determines the ordinal number of the current row within a group of rows, counting from 1.
Functions	Output Column	The name of the output column that the window function will create.

Strategy

Snowflake: Generates a select statement with a window function in line using the OVER keyword. For more information, read Window Functions.

Amazon Redshift: Generates a select statement with a window function in line using the OVER keyword. For more information, read Window functions.

Google BigQuery: Generates a select statement with a window function in line using the OVER keyword. For more information, read Window functions.

Azure Synapse: Generates a select statement with a window function in line using the OVER keyword. For more information, read Window functions.

Delta Lake on Databricks: Generates a select statement with a window function in line using the OVER keyword. For more information, read Window functions.

Example

In this example we wish to determine the longest flight by airtime for a given year. Luckily, we have a table full of flight data already. The job is set up as below.

The filter component is used to filter any null values from the data where airtime was not properly recorded. The remaining rows pass on to the Rank component that is set up as shown below.

'Include Input Columns' is set to Yes so that all input columns are also output. The data is partitioned by year; in this case, this means that our output data will rank airtimes for the year 2000 followed by airtimes for the year 1999 and so on.

The 'Ordering within Partitions' property is the key to our ranking and is set to rank by airtime, descending.

Finally, the 'Rank' function is chosen and the new output column is called 'Airtime Rank'. This new column will show the rank of that particular row for that year.