EXAMPLE - RANK Functions

This example demonstrates the following two functions:

  • RANK - Generates a ranked order of values, ranked within a group.
    • If there are three tie values in a group, the next ranking is three more than the tie values.
    • See RANK Function.
  • DENSERANK - Generates a ranked order of values, ranked within a group.
    • If there are three tie values in a group, the next ranking is one more than the tie values.
    • See DENSERANK Function.

Source:

The following dataset contains lap times for three racers in a four-lap race. Note that for some racers, there are tie values for lap times.

RunnerLapTime
Dave172.2
Dave273.31
Dave372.2
Dave470.85
Mark171.73
Mark271.73
Mark372.99
Mark470.63
Tom174.43
Tom270.71
Tom371.02
Tom472.98


Transform:

You can apply the RANK() function to the Time column, grouped by individual runner:

window value: RANK() group: Runner order: Time

You can use the DENSERANK() function on the same column, grouping by runner:

window value: DENSERANK() group: Runner order: Time

Results:

After renaming the columns, you have the following output:

RunnerLapTimeRankRank-Dense
Mark470.6311
Mark171.7322
Mark271.7322
Mark372.9943
Tom270.7111
Tom371.0222
Tom472.9833
Tom174.4344
Dave470.8511
Dave172.222
Dave372.222
Dave273.3143

Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation
Need help? Visit our support page.