More Ranking Functions
1. More Ranking Functions
Let's explore some more window functions! First up, `DENSE_RANK`.2. RANK vs. DENSE_RANK
`DENSE_RANK` is just like `RANK`; it provides a ranking to each record based on the order of some field. However, `DENSE_RANK` handles ties without gaps. To show the difference between `RANK` and `DENSE_RANK`, let's rank gym member's workout durations. `DENSE_RANK`'s syntax is identical to `RANK`, but the results look a bit different. For a workout duration of 68, `RANK` returns 4. We can think of `RANK` as "counting" the total number of records before it and returning the next sequential value. `DENSE_RANK` returns 3. Think of `DENSE_RANK` as counting the number of **unique** ranks before it, and returning the next sequential value. `DENSE_RANK` is quite useful for answering the question "what is the length of the third-longest workout".3. NTH_VALUE
Another useful window function is `NTH_VALUE`. `NTH_VALUE` returns the specified value from the N'th record in a window. It's similar to `FIRST/LAST_VALUE`, but now, we can specify "N". The first value passed into `NTH_VALUE` is the value to return from the N'th row. "N" is the row number to retrieve. Here, an `ORDER BY` is required; this determines the ranking of records. Like with the other functions we've used, we can create windows using `PARTITION BY`.4. NTH_VALUE
To find the second-longest workout duration for each gym location, we can use `NTH_VALUE`. We pass the `workout_duration`, followed by `2` to `NTH_VALUE`. We create a window by `gym_location`, and order by `workout_duration` is descending order.5. NTH_VALUE
The results look like this; we've successfully created a window using `gym_location`, and included the `workout_duration` field. We also have a new column called `second_longest_workout`. `second_longest_workout` returns the `workout_duration` for the record with a row number equal to 2. For New York, the second longest `workout_duration` is 68; this is the value that's returned. For LA, it's 67, and for Miami, 61.6. Putting it all together
Let's put it all together. Here, we've written a query using `NTH_VALUE`, `RANK`, and `DENSE_RANK`. Like before, we're defining a window by `gym_location`, and ranking by `workout_duration`, longest-to-shortest.7. Putting it all together
Take a peek at the results for Los Angeles. For LA, the second-longest workout is 67 minutes, which we determined using `NTH_VALUE`. Now, take a look at the record with a `workout_duration` of 63. Using `RANK`, the value returned is 4. However, `DENSE_RANK` returns 3. These tools help to answer different questions. `RANK` would be useful for a fitness leader board, where members could track their progress against other members. However, `DENSE_RANK` would make answering the question "what is the third-longest workout duration" for that gym possible.8. Let's practice!
Great! Time to take your new skills for a test-drive with some hands-on exercises.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.