Ranking functions in SQL are useful for performing analytical queries and obtaining rankings based on specified criteria. Here are some commonly used SQL ranking functions:
ROW_NUMBER():
Assigns a unique number to each row based on the specified order.
SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees; |
RANK():
Assigns a rank to each row, with ties receiving the same rank, and leaving gaps in the ranking sequence.
SELECT department, AVG(salary), RANK() OVER (ORDER BY AVG(salary) DESC) AS department_rank FROM employees GROUP BY department; |
DENSE_RANK():
Similar to RANK(), but without gaps in the ranking sequence for tied values.
SELECT department, AVG(salary), DENSE_RANK() OVER (ORDER BY AVG(salary) DESC) AS department_dense_rank FROM employees GROUP BY department; |
NTILE():
Divides the result set into a specified number of roughly equal groups and assigns a group number to each row.
SELECT employee_id, salary, NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile FROM employees; |
PERCENT_RANK():
Calculates the relative rank of each row as a percentage.
SELECT department, AVG(salary), PERCENT_RANK() OVER (ORDER BY AVG(salary) DESC) AS department_percent_rank FROM employees GROUP BY department; |
CUME_DIST():
Calculates the cumulative distribution of a value within a group.
SELECT department, AVG(salary), CUME_DIST() OVER (ORDER BY AVG(salary) DESC) AS department_cume_dist FROM employees GROUP BY department; |
These ranking functions are part of the SQL window functions category and are particularly useful in analytical queries where you need to assign ranks or calculate distribution percentages based on certain criteria. Always adapt these queries to the specific requirements and structure of your dataset and database.
Commentaires