Status
Done
Text
Quick reference notes and key concepts for MySQL window functions implementation.
SQL Window Functions: Complete Guide with Sakila Database Examples
Ranking Window Functions
These functions assign ranks or row numbers to rows within a partition.
- ROW_NUMBER()
- Assigns a unique sequential integer to each row within a partition.
- Example:
ROW_NUMBER() OVER (PARTITION BY group ORDER BY order_column) - RANK()
- Assigns a rank to each row within a partition, with gaps for ties.
- Example:
RANK() OVER (PARTITION BY group ORDER BY order_column) - DENSE_RANK()
- Assigns a rank to each row within a partition, without gaps for ties.
- Example:
DENSE_RANK() OVER (PARTITION BY group ORDER BY order_column) - NTILE()
- Divides rows into a specified number of buckets (e.g., quartiles, deciles).
- Example:
NTILE(4) OVER (PARTITION BY group ORDER BY order_column) - PERCENT_RANK()
- Calculates the relative rank of a row as a percentage (0 to 1).
- Example:
PERCENT_RANK() OVER (PARTITION BY group ORDER BY order_column) - CUME_DIST()
- Calculates the cumulative distribution of a value within a partition (0 to 1).
- Example:
CUME_DIST() OVER (PARTITION BY group ORDER BY order_column)
Introduction to Window Functions
Window functions perform calculations across a set of rows that are related to the current row, without collapsing the result set like aggregate functions do. They are called "window" functions because they operate on a "window" of rows defined by the OVER clause.
‣
1. ROW_NUMBER()
‣
2. RANK()
‣
3. DENSE_RANK()
‣
4. NTILE()
‣
5. PERCENT_RANK()
‣
6. CUME_DIST()
Summary Comparison Table
Function | Handles Ties | Gaps in Sequence | Range | Best Used For |
ROW_NUMBER() | Unique numbers | N/A | 1 to N | Unique sequential IDs, pagination |
RANK() | Same rank | Yes (gaps) | 1 to N | Competition rankings, meaningful gaps |
DENSE_RANK() | Same rank | No gaps | 1 to N | Continuous tiers, counting levels |
NTILE(n) | Distributes evenly | N/A | 1 to n | Equal-sized groups, quartiles |
PERCENT_RANK() | Same value | N/A | 0 to 1 | Percentile position, relative rank |
CUME_DIST() | Same value | N/A | >0 to 1 | Cumulative distribution, thresholds |
Key Takeaways
- ROW_NUMBER(): Always unique, perfect for pagination and removing duplicates
- RANK(): Reflects ties with gaps, like Olympic medal standings
- DENSE_RANK(): Reflects ties without gaps, good for tier classifications
- NTILE(): Splits data into equal buckets, ideal for segmentation
- PERCENT_RANK(): Shows relative position, answers "better than X%"
- CUME_DIST(): Shows cumulative percentage, answers "X% are at or below this"
‣
Additional Tips
Practice Exercises
Try these exercises with the Sakila database:
- Exercise 1: Find the 3rd most rented film using DENSE_RANK()
- Exercise 2: Divide films into 5 equal groups by length using NTILE()
- Exercise 3: Find customers in the top 10% by spending using PERCENT_RANK()
- Exercise 4: For each category, find films that are longer than 75% of films in that category using CUME_DIST()
- Exercise 5: Number all payments sequentially by payment date using ROW_NUMBER()
This guide covers the six main ranking window functions in SQL. Practice with the Sakila database examples to master these powerful analytical tools!