Rank
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.
Properties
Name = string
A human-readable name for the component.
Include Input Columns = boolean
Defines whether the component passes all input columns into the output.
Partition Data = dual listbox
Defines how the input data is partitioned to perform the rank calculation. The calculation is then performed on each partition.
Ordering Within Partitions = column editor
Order input columns within the partitioned data. You can drag to reorder. Choose the following value:
- Ascending
- Descending
- Nulls First
- Nulls Last
Functions = column editor
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.