Skip to main content
The Rank transformation component lets you determine the rank of a value in a group of values, and add this to your data as a new column. It supports multiple SQL window functions: RANK, DENSE_RANK, CUME_DIST, PERCENT_RANK, and ROW_NUMBER. For more information, read the following documentation:

​
Use case

This component can be used to highlight the highest and lowest values in your data, identify duplicate data, and rank values by percentile. For example, you can use it to:
  • Identify top-performing ads using RANK or DENSE RANK.
  • Remove duplicate data from your dataset by partitioning and sorting data, then using ROW NUMBER = 1.
  • Segment your data by percentile, to analyze data from different customer demographics.
When sampling data from a Rank component, if the input dataset is very large, the sample output may not display rows in the correct order. This is a display issue only—the data in this component has been ranked correctly.

​
Properties

​
string
required
A human-readable name for the component.
​
boolean
required
Defines whether the component passes all input columns into the output.
​
object selector
Select columns to partition the data by. The calculation is then performed on each partition. If you select multiple columns, the data is partitioned by the unique combination of values across all selected columns.To use grid variables, toggle Use Grid Variable on. For more information, read Grid variables.
​
column editor
required
Order input columns within the partitioned data. Drag to reorder, then choose the following:
  • Ascending
  • Descending
  • Nulls First
  • Nulls Last
Nulls First and Nulls Last are not available for Google BigQuery.
Toggle Text mode on at the bottom of the dialog to open a multi-line editor that lets you add items in a single block. For more information, read Text mode.To use grid variables, toggle Use Grid Variable on. For more information, read Grid variables.
​
column editor
required
Select a 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.
Then, add the name of the output column that the window function will create.Toggle Text mode on at the bottom of the dialog to open a multi-line editor that lets you add items in a single block. For more information, read Text mode.To use grid variables, toggle Use Grid Variable on. For more information, read Grid variables.