Hive SQL 窗口函数 `ROW_NUMBER() ` 案例分析

一文彻底搞懂 ROW_NUMBER() 和 PARTITION BY

1. 引言

在处理大规模数据集时，Hive SQL 提供了强大的窗口函数（Window Function），如 ROW_NUMBER()，用于为结果集中的每一行分配唯一的行号。当与 PARTITION BY 和 ORDER BY 结合使用时，ROW_NUMBER() 可以帮助解决许多复杂的分析任务，例如去重、排名和分页查询等。本文将详细介绍如何结合这三个元素来实现高效的SQL查询。

2. 语法结构

2.1 ROW_NUMBER()

• 定义：为分区内的每一行分配一个唯一的行号。
• 用法：

  ROW_NUMBER() OVER ([PARTITION BY <expr_list>] ORDER BY <expr_list>)
  

2.2 PARTITION BY

• 作用：定义了窗口函数应用于哪些逻辑分组或分区。
• 字段意义：指定用来分组的列，所有具有相同值的行会被归入同一组。
• 示例：PARTITION BY department_id 表示按部门ID分组。

2.3 ORDER BY

• 作用：确定行号分配的顺序。
• 字段意义：定义排序规则，可以是一个或多个字段，并可指定升序 (ASC) 或降序 (DESC)。
• 示例：ORDER BY salary DESC 按薪资从高到低排序。

3. 使用场景与实际案例

为了更好地展示 ROW_NUMBER() 结合 PARTITION BY 和 ORDER BY 的使用方法，还是要 show case 的，下面通过具体 🌰 来解释如何在不同的业务需求下应用这些功能。

3.1 数据去重

在某些情况下，数据集中可能存在重复记录，而我们只希望保留特定条件下的一条记录（如最新的记录）。这时可以使用 ROW_NUMBER() 来为每组记录分配行号，并选择行号为1的记录以达到去重的目的。

案例1：最新交易记录（按客户）

考虑一张名为 transactions 的表，包含以下字段：

• trans_id: 交易ID
• customer_id: 客户ID
• amount: 交易金额
• transaction_date: 交易日期

需求：找出每位客户的最近一次交易记录。

具体操作如下：

WITH LatestTransactions AS (SELECT trans_id,customer_id,amount,transaction_date,ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY transaction_date DESC) as rnFROM transactions
)
SELECT trans_id,customer_id,amount,transaction_date
FROM LatestTransactions
WHERE rn = 1;

• 解释：PARTITION BY customer_id 把所有交易按照客户分组；ORDER BY transaction_date DESC 确保每组内的交易按时间降序排列，因此最新的交易会获得行号1。

3.2 获取排名

当需要根据某个标准对数据进行排序并计算相对排名时，比如找出每个月销售额最高的前N名销售员或每个部门内薪资最高的员工，可以利用 ROW_NUMBER() 函数结合 PARTITION BY 和 ORDER BY 来实现。

案例2：员工薪资排名（按部门）

假设有一个名为 employees 的表，包含以下字段：

• emp_id: 员工ID
• name: 员工姓名
• department_id: 部门ID
• salary: 薪资

需求：为每个部门的员工按照薪资从高到低排序，并给出他们的排名。

具体操作如下：

WITH EmployeeRank AS (SELECT emp_id,name,department_id,salary,ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rankFROM employees
)
SELECT emp_id,name,department_id,salary,rank
FROM EmployeeRank;

• 解释：PARTITION BY department_id 将数据分为不同部门的组；ORDER BY salary DESC 在每个部门内根据薪资从高到低排序。

案例3：月度销售冠军（按产品类别）

假设有如下表格 sales，包含以下字段：

• sale_id: 销售记录ID
• product_category: 产品类别
• salesperson: 销售人员名字
• sales_amount: 销售金额
• month: 月份

需求：计算每个月每个产品类别的销售冠军。

具体操作如下：

WITH MonthlySalesLeaders AS (SELECT product_category,salesperson,month,sales_amount,ROW_NUMBER() OVER (PARTITION BY product_category, month ORDER BY sales_amount DESC) as rankFROM sales
)
SELECT product_category,salesperson,month,sales_amount
FROM MonthlySalesLeaders
WHERE rank = 1;

• 解释：PARTITION BY product_category, month 创建了基于产品类别和月份的分区；ORDER BY sales_amount DESC 确保了每个分区内销售额最高的销售人员会被排在最前面。

3.3 分页查询

当处理大量数据时，可能需要分批次地展示结果集。例如，在网页上显示搜索结果时，通常每次只加载一部分数据。此时，可以通过 ROW_NUMBER() 来实现分页效果。

案例4：获取第101到200条记录

• 假设你有一个大表 large_table，并且想要获取该表中第101到200条记录（假设表中有一列 id 可以用来排序）。

具体操作如下：

WITH PaginatedData AS (SELECT *,ROW_NUMBER() OVER (ORDER BY id) as row_numFROM large_table
)
SELECT *
FROM PaginatedData
WHERE row_num BETWEEN 101 AND 200;

• 解释：这里使用 ROW_NUMBER() 为每一行分配一个唯一的行号，并通过 WHERE 子句筛选出所需的分页范围。

---

通过上述场景和对应的案例，可以看到 ROW_NUMBER() 结合 PARTITION BY 和 ORDER BY 是多么强大且灵活。它不仅能够解决常见的数据分析问题，还能提高查询效率，使得复杂的数据处理变得更加直观和简便。