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

1. 引言

在處理大規(guī)模數(shù)據(jù)集時，Hive SQL 提供了強大的窗口函數(shù)（Window Function），如 ROW_NUMBER()，用于為結(jié)果集中的每一行分配唯一的行號。當(dāng)與 PARTITION BY 和 ORDER BY 結(jié)合使用時，ROW_NUMBER() 可以幫助解決許多復(fù)雜的分析任務(wù)，例如去重、排名和分頁查詢等。本文將詳細(xì)介紹如何結(jié)合這三個元素來實現(xiàn)高效的SQL查詢。

2. 語法結(jié)構(gòu)

2.1 ROW_NUMBER()

• 定義：為分區(qū)內(nèi)的每一行分配一個唯一的行號。
• 用法：

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

2.2 PARTITION BY

• 作用：定義了窗口函數(shù)應(yīng)用于哪些邏輯分組或分區(qū)。
• 字段意義：指定用來分組的列，所有具有相同值的行會被歸入同一組。
• 示例：PARTITION BY department_id 表示按部門ID分組。

2.3 ORDER BY

• 作用：確定行號分配的順序。
• 字段意義：定義排序規(guī)則，可以是一個或多個字段，并可指定升序 (ASC) 或降序 (DESC)。
• 示例：ORDER BY salary DESC 按薪資從高到低排序。

3. 使用場景與實際案例

為了更好地展示 ROW_NUMBER() 結(jié)合 PARTITION BY 和 ORDER BY 的使用方法，還是要 show case 的，下面通過具體 🌰 來解釋如何在不同的業(yè)務(wù)需求下應(yīng)用這些功能。

3.1 數(shù)據(jù)去重

在某些情況下，數(shù)據(jù)集中可能存在重復(fù)記錄，而我們只希望保留特定條件下的一條記錄（如最新的記錄）。這時可以使用 ROW_NUMBER() 來為每組記錄分配行號，并選擇行號為1的記錄以達(dá)到去重的目的。

案例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 確保每組內(nèi)的交易按時間降序排列，因此最新的交易會獲得行號1。

3.2 獲取排名

當(dāng)需要根據(jù)某個標(biāo)準(zhǔn)對數(shù)據(jù)進(jìn)行排序并計算相對排名時，比如找出每個月銷售額最高的前N名銷售員或每個部門內(nèi)薪資最高的員工，可以利用 ROW_NUMBER() 函數(shù)結(jié)合 PARTITION BY 和 ORDER BY 來實現(xiàn)。

案例2：員工薪資排名（按部門）

假設(shè)有一個名為 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 將數(shù)據(jù)分為不同部門的組；ORDER BY salary DESC 在每個部門內(nèi)根據(jù)薪資從高到低排序。

案例3：月度銷售冠軍（按產(chǎn)品類別）

假設(shè)有如下表格 sales，包含以下字段：

• sale_id: 銷售記錄ID
• product_category: 產(chǎn)品類別
• salesperson: 銷售人員名字
• sales_amount: 銷售金額
• month: 月份

需求：計算每個月每個產(chǎn)品類別的銷售冠軍。

具體操作如下：

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 創(chuàng)建了基于產(chǎn)品類別和月份的分區(qū)；ORDER BY sales_amount DESC 確保了每個分區(qū)內(nèi)銷售額最高的銷售人員會被排在最前面。

3.3 分頁查詢

當(dāng)處理大量數(shù)據(jù)時，可能需要分批次地展示結(jié)果集。例如，在網(wǎng)頁上顯示搜索結(jié)果時，通常每次只加載一部分?jǐn)?shù)據(jù)。此時，可以通過 ROW_NUMBER() 來實現(xiàn)分頁效果。

案例4：獲取第101到200條記錄

• 假設(shè)你有一個大表 large_table，并且想要獲取該表中第101到200條記錄（假設(shè)表中有一列 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 子句篩選出所需的分頁范圍。

---

通過上述場景和對應(yīng)的案例，可以看到 ROW_NUMBER() 結(jié)合 PARTITION BY 和 ORDER BY 是多么強大且靈活。它不僅能夠解決常見的數(shù)據(jù)分析問題，還能提高查詢效率，使得復(fù)雜的數(shù)據(jù)處理變得更加直觀和簡便。