ROW_NUMBER、RANK和DENSE_RANK区别

在SQL中，窗口函数是一种强大的工具，用于执行复杂的数据分析任务。其中，ROW_NUMBER()、RANK() 和 DENSE_RANK() 是三种常用的窗口函数，它们在处理排名和排序问题时具有不同的特性和用途。本文将详细介绍这三种函数的区别，包括它们的基本概念、语法结构、应用场景以及实际操作示例，帮助读者更好地理解和掌握这些功能。

一、ROW_NUMBER、RANK和DENSE_RANK概述

1）定义

1. ROW_NUMBER(): 为每一行生成一个唯一的行号，不会出现重复的行号。

2. RANK(): 为每一行生成一个排名，相同值的行会得到相同的排名，但下一个排名会跳过相应的数字。

3. DENSE_RANK(): 为每一行生成一个排名，相同值的行会得到相同的排名，但下一个排名不会跳过数字。

2）特点

1. ROW_NUMBER(): 唯一性高，适用于需要唯一标识的情况。

2. RANK(): 排名跳跃，适用于需要精确排名的情况。

3. DENSE_RANK(): 排名连续，适用于需要连续排名的情况。

二、ROW_NUMBER、RANK和DENSE_RANK的基本语法

1. 基本语法

ROW_NUMBER()ROW_NUMBER() OVER ( [PARTITION BY column_list] ORDER BY sort_expression [ASC | DESC] )
RANK()RANK() OVER ( [PARTITION BY column_list] ORDER BY sort_expression [ASC | DESC] )
DENSE_RANK()DENSE_RANK() OVER ( [PARTITION BY column_list] ORDER BY sort_expression [ASC | DESC] )

1. 参数说明

PARTITION BY: 用于将数据分成多个分区，每个分区内的行号重新开始计数。格式: PARTITION BY column_list

ORDER BY: 用于指定行号的排序规则。格式: ORDER BY sort_expression [ASC | DESC]

三、ROW_NUMBER、RANK和DENSE_RANK的应用场景

1. 数据排名

ROW_NUMBER()

场景: 适用于需要唯一标识的情况，例如生成唯一序列号。

示例:

SELECT 
    employee_id, 
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

解释: 按 salary 降序排列，并生成唯一行号。

RANK()

场景: 适用于需要精确排名的情况，例如竞赛排名。

示例:

SELECT 
    competitor_id, 
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM competition_results;

解释: 按 score 降序排列，并生成排名，相同分数的行会得到相同的排名。

DENSE_RANK()

场景: 适用于需要连续排名的情况，例如员工绩效排名。

示例:

SELECT 
    employee_id, 
    performance_score,
    DENSE_RANK() OVER (ORDER BY performance_score DESC) AS dense_rank
FROM employee_performance;

解释: 按 performance_score 降序排列，并生成连续排名。

1. 数据去重

ROW_NUMBER()场景: 适用于需要去除重复记录的情况。

示例:

WITH ranked_data AS (
    SELECT 
        customer_id, 
        order_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS row_num
    FROM orders
)
SELECT 
    customer_id, 
    order_date
FROM ranked_data
WHERE row_num = 1;

解释: 按 customer_id 分区，并在每个分区内按 order_date 降序排列，取出最新的订单日期。

1. 数据分组

ROW_NUMBER()场景: 适用于需要对数据进行分组处理的情况。

示例:

SELECT 
    department_id, 
    COUNT(*) AS employee_count,
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rank
FROM employees
GROUP BY department_id;

解释: 按 department_id 分组，并统计每个部门的员工数量，最后按员工数量降序排列生成排名。

1. 生成序列号

ROW_NUMBER()场景: 适用于需要为数据生成唯一序列号的情况。

示例:

SELECT 
    employee_id, 
    ROW_NUMBER() OVER (ORDER BY employee_id) AS sequence_number
FROM employees;

解释: 按 employee_id 升序排列，并生成序列号。

1. 统计排名

RANK()场景: 适用于需要统计竞赛排名的情况。

示例:

SELECT 
    competitor_id, 
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM competition_results;

解释: 按 score 降序排列，并生成排名，相同分数的行会得到相同的排名。

1. 生成唯一标识

ROW_NUMBER()场景: 适用于需要生成唯一标识的情况。

示例:

SELECT 
    employee_id, 
    ROW_NUMBER() OVER (ORDER BY employee_id) AS unique_id
FROM employees;

解释: 按 employee_id 升序排列，并生成唯一标识。

1. 统计分组

ROW_NUMBER()场景: 适用于需要统计分组的情况。

示例:

SELECT 
    department_id, 
    COUNT(*) AS employee_count,
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rank
FROM employees
GROUP BY department_id;

解释: 按 department_id 分组，并统计每个部门的员工数量，最后按员工数量降序排列生成排名。

四、ROW_NUMBER、RANK和DENSE_RANK的实际操作示例

1. 基础用法

ROW_NUMBER()

场景: 最简单的用法，不带任何子句。

示例:

SELECT 
    employee_id, 
    ROW_NUMBER() OVER (ORDER BY employee_id) AS row_num
FROM employees;

解释: 按 employee_id 升序排列，并生成行号。

RANK()

场景: 最简单的用法，不带任何子句。

示例:

SELECT 
    employee_id, 
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

解释: 按 salary 降序排列，并生成排名。

DENSE_RANK()

场景: 最简单的用法，不带任何子句。

示例:

SELECT 
    employee_id, 
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

解释: 按 salary 降序排列，并生成连续排名。

1. 结合 PARTITION BY 子句

ROW_NUMBER()

场景: 按某一列进行分区，并在每个分区内生成行号。

示例:

SELECT 
    employee_id, 
    department_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS row_num
FROM employees;

解释: 按 department_id 分区，并在每个分区内按 employee_id 升序排列，生成行号。

RANK()

场景: 按某一列进行分区，并在每个分区内生成排名。

示例:

SELECT 
    employee_id, 
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

解释: 按 department_id 分区，并在每个分区内按 salary 降序排列，生成排名。

DENSE_RANK()

场景: 按某一列进行分区，并在每个分区内生成连续排名。

示例:

SELECT 
    employee_id, 
    department_id,
    salary,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM employees;

解释: 按 department_id 分区，并在每个分区内按 salary 降序排列，生成连续排名。

1. 结合 ORDER BY 子句

ROW_NUMBER()

场景: 按某一列进行排序，并生成行号。

示例:

SELECT 
    employee_id, 
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

解释: 按 salary 降序排列，并生成行号。

RANK()

场景: 按某一列进行排序，并生成排名。

示例:

SELECT 
    employee_id, 
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

解释: 按 salary 降序排列，并生成排名。

DENSE_RANK()

场景: 按某一列进行排序，并生成连续排名。

示例:

SELECT 
    employee_id, 
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

解释: 按 salary 降序排列，并生成连续排名。

1. 结合 PARTITION BY 和 ORDER BY 子句

ROW_NUMBER()

场景: 按某一列进行分区，并在每个分区内按另一列排序，生成行号。

示例:

SELECT 
    employee_id, 
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

解释: 按 department_id 分区，并在每个分区内按 salary 降序排列，生成行号。

RANK()

场景: 按某一列进行分区，并在每个分区内按另一列排序，生成排名。

示例:

SELECT 
    employee_id, 
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

解释: 按 department_id 分区，并在每个分区内按 salary 降序排列，生成排名。

DENSE_RANK()

场景: 按某一列进行分区，并在每个分区内按另一列排序，生成连续排名。

示例:

SELECT 
    employee_id, 
    department_id,
    salary,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM employees;

解释: 按 department_id 分区，并在每个分区内按 salary 降序排列，生成连续排名。

ROW_NUMBER()、RANK() 和 DENSE_RANK() 是SQL中常用的窗口函数，它们在处理排名和排序问题时具有不同的特性和用途。本文详细介绍了这三种函数的基本概念、语法结构、应用场景以及实际操作示例，涵盖了基础用法、结合 PARTITION BY 和 ORDER BY 子句的应用场景。通过本文的介绍，读者可以更好地理解并掌握这些函数的使用方法，从而在实际工作中灵活运用这些功能，提高数据处理的效率和准确性。希望本文提供的信息能够帮助读者更好地理解和应用这些函数，从而编写出更高效、更可靠的SQL查询。

声明：所有来源为“聚合数据”的内容信息，未经本网许可，不得转载！如对内容有异议或投诉，请与我们联系。邮箱：marketing@think-land.com