The DENSE_RANK function in Oracle is an analytic function used to assign a rank to each row within a result set, based on a specified ordering of the rows. Similar to the RANK function, it ranks rows based on a specified column (or columns), but it differs in how it handles ties.
1. What is the DENSE_RANK Function?
The DENSE_RANK function assigns a unique rank to each row, with no gaps in the rank numbering. If two rows have the same value in the ordering column(s), they are assigned the same rank, but the next rank will be the next consecutive number (unlike RANK, which skips subsequent ranks).
- The rank starts from 1.
- Tied rows receive the same rank.
- There are no gaps in the ranking sequence when there are ties.
2. Basic Syntax
The basic syntax for the DENSE_RANK function is:
SELECT column1, column2, DENSE_RANK() OVER (ORDER BY column_name) AS dense_rank
FROM table_name;
- DENSE_RANK() is the function.
- OVER clause is mandatory for specifying the windowing logic.
- ORDER BY column_name specifies the column(s) by which the ranking is determined.
- AS dense_rank is the alias for the calculated rank.
3. How DENSE_RANK Works
- Tied Values: When two rows have the same value in the column used for ranking, they are given the same rank.
- No Gaps in Ranking: The rank sequence is continuous. Unlike RANK, which skips the next rank after a tie, DENSE_RANK assigns the next rank as the next consecutive integer.
Example:
Let's use an example to illustrate how DENSE_RANK works:
Data:
|
Salesperson |
Sales Amount |
|
Alice |
1000 |
|
Bob |
1500 |
|
Carol |
1500 |
|
Dave |
1200 |
Query:
SELECT Salesperson, Sales_Amount,
DENSE_RANK() OVER (ORDER BY Sales_Amount DESC) AS Rank
FROM Sales;
Result:
|
Salesperson |
Sales Amount |
Rank |
|
Bob |
1500 |
1 |
|
Carol |
1500 |
1 |
|
Dave |
1200 |
2 |
|
Alice |
1000 |
3 |
- Bob and Carol have the same sales amount, so they both get Rank 1.
- The next rank (for Dave) is 2 (no gap between 1 and 2).
- Alice gets Rank 3.
4. DENSE_RANK vs RANK
The key difference between RANK and DENSE_RANK is how they handle ties:
- RANK: When there is a tie, the next rank is skipped.
- Example: If two rows are tied with rank 1, the next row will be ranked 3 (skipping rank 2).
- DENSE_RANK: When there is a tie, the next rank is the next consecutive number.
- Example: If two rows are tied with rank 1, the next row will be ranked 2 (no gaps).
Example Comparison:
For the same dataset:
Using RANK:
SELECT Salesperson, Sales_Amount,
RANK() OVER (ORDER BY Sales_Amount DESC) AS Rank
FROM Sales;
Result:
|
Salesperson |
Sales Amount |
Rank |
|
Bob |
1500 |
1 |
|
Carol |
1500 |
1 |
|
Dave |
1200 |
3 |
|
Alice |
1000 |
4 |
Using DENSE_RANK:
SELECT Salesperson, Sales_Amount,
DENSE_RANK() OVER (ORDER BY Sales_Amount DESC) AS Rank
FROM Sales;
Result:
|
Salesperson |
Sales Amount |
Rank |
|
Bob |
1500 |
1 |
|
Carol |
1500 |
1 |
|
Dave |
1200 |
2 |
|
Alice |
1000 |
3 |
- RANK skips the rank number 2 and starts with 3 after the tie.
- DENSE_RANK does not skip any ranks and proceeds with 2 after the tie.
5. Using PARTITION BY with DENSE_RANK
You can use the PARTITION BY clause to partition the data into groups and apply the DENSE_RANK function within each partition. This is useful when you want to rank items within categories or groups, such as ranking employees within different departments or ranking salespeople by region.
Syntax:
SELECT column1, column2,
DENSE_RANK() OVER (PARTITION BY partition_column ORDER BY order_column) AS dense_rank
FROM table_name;
Example:
If you want to rank employees within each department based on their salary:
SELECT Department_ID, Employee_ID, Salary,
DENSE_RANK() OVER (PARTITION BY Department_ID ORDER BY Salary DESC) AS Rank
FROM Employees;
This query will rank employees within each department, with ties handled by DENSE_RANK (no gaps in the ranking sequence).
6. Use Cases of DENSE_RANK
- Competitive Rankings: In scenarios like sports competitions or sales contests, you can use DENSE_RANK to ensure that the next rank follows directly after tied participants.
- Top N Reports: When ranking items (e.g., top 3 products or top 5 salespeople) in a way that there are no gaps in the rankings due to ties.
- Event Rankings: To rank events or results where tied performances should not cause missing ranks in the sequence.
7. Performance Considerations
- Indexes: When using DENSE_RANK on large datasets, creating indexes on the columns involved in the ORDER BY clause can improve query performance.
- Partitioning: If you're using PARTITION BY, ensure the data is evenly distributed across partitions to avoid performance bottlenecks.
- Sorting: The function requires sorting the data, which can be resource-intensive for large datasets.
8. DENSE_RANK with Multiple Columns
You can rank rows based on multiple columns in the ORDER BY clause. When there are ties in the first column, the function will move to the second column to break the tie.
Example:
SELECT Salesperson, Region, Sales_Amount,
DENSE_RANK() OVER (PARTITION BY Region ORDER BY Sales_Amount DESC, Salesperson) AS Rank
FROM Sales;
This query ranks salespeople within each region based on their sales amount. If two salespeople have the same sales amount, the rank is then determined by their name (or salesperson).
9. DENSE_RANK with Date Columns
You can also use DENSE_RANK to rank records based on date columns. This is useful for scenarios where you want to rank records in a time sequence.
SELECT Salesperson, Sales_Date, Sales_Amount,
DENSE_RANK() OVER (ORDER BY Sales_Date) AS Rank
FROM Sales;
This ranks sales records by date, with no gaps in the ranking for ties.
10. Handling NULL Values in DENSE_RANK
DENSE_RANK in Oracle treats NULL values as the lowest possible value in the ORDER BY clause by default. Therefore, rows containing NULL values will be ranked last unless specified otherwise using the NULLS FIRST or NULLS LAST option in the ORDER BY clause.
SELECT Salesperson, Sales_Amount,
DENSE_RANK() OVER (ORDER BY Sales_Amount DESC NULLS LAST) AS Rank
FROM Sales;
This places NULL values last in the ranking sequence.
11. DENSE_RANK in Subqueries and Joins
You can use DENSE_RANK in subqueries or join queries to rank data after combining tables.
SELECT Employee_ID, Salary, Department_ID
FROM (
SELECT Employee_ID, Salary, Department_ID,
DENSE_RANK() OVER (PARTITION BY Department_ID ORDER BY Salary DESC) AS Rank
FROM Employees
) AS Ranked_Employees
WHERE Rank <= 3;
This query finds the top 3 employees (ranked by salary) in each department.
12. Conclusion
The DENSE_RANK function in Oracle is a powerful tool for generating rankings where there are no gaps in the ranking sequence, even if multiple rows have the same value. It is particularly useful for applications involving competitive rankings, top-N reports, and handling ties in a way that does not leave gaps in the rank numbers. It also works well with partitions for ranking within specific groups, such as departments or regions.
No comments:
Post a Comment