PostgreSQL 中使用 ROW_NUMBER 進行分頁詳解

首页 » 蟒蛇 » PostgreSQL 中使用 ROW_NUMBER 進行分頁詳解

在 PostgreSQL 中對大型結果集進行分頁乍看之下似乎很簡單，但要有效率且正確地完成分頁（尤其是在排序列中存在並列值時），需要的不僅僅是在查詢中新增 LIMIT/OFFSET。 視窗函數 ROW_NUMBER() 是解決此問題的最通用工具之一，同時還能解鎖一系列額外的分析案例，例如排名、前 N 個查詢或重複檢測。

本指南深入探討如何使用 ROW_NUMBER() 本文將介紹 PostgreSQL 中的分頁機制，探討其底層運作方式、與其他排名函數的差異、效能影響以及其他主要資料庫引擎在類似模式下的行為。 我們還將探討一些棘手的實際場景，例如當排序列包含重複項時，如何進行基於遊標的分頁以及如何組合使用它們。 ROW_NUMBER() 使用 CTE、連接和子查詢來編寫簡潔、可用於生產環境的 SQL。

PostgreSQL ROW_NUMBER() 視窗函數實際執行的操作

其核心， ROW_NUMBER() 是一個視窗函數，它將一個唯一的、連續的整數分配給結果集中的每一行，從 1 開始，每次遞增 1，中間沒有間隙。 您可以根據視窗的定義方式，將此編號套用到整個結果，也可以針對每組行重新開始編號。

PostgreSQL 的通用語法如下所示：

ROW_NUMBER() OVER ( [PARTITION BY partition_expression] ORDER BY order_expression )

內部有兩個部分 OVER 子句控制行號的分配方式： PARTITION BY （可選）將結果集拆分為獨立的群組，並且 ORDER BY （必填）定義每個分割區內的順序，該順序決定哪一行取得哪個編號。

如果你省略 PARTITION BY此函數將整個結果集視為一個單獨的分區，並簡單地按照指定的順序對所有行進行編號。 一旦你添加 PARTITION BY每個分區中的行編號都從 1 開始，這對於按類別排名、每組前 N 個查詢和分組去重非常有用。

與排名函數不同，例如 RANK() or DENSE_RANK(), ROW_NUMBER() 完全忽略並列情況，並且從不重複數字，即使排序列中的行具有相同的值。 這使其成為確定性分頁和精確行切片的理想選擇。

建立直覺的基本 ROW_NUMBER() 範例

使用前 ROW_NUMBER() 對於分頁來說，透過簡單的範例來觀察其實際效果會很有幫助，其目標只是以可控的方式對行進行編號。 想像一張桌子 employees 列 id, name, department 以及 salary.

若要指派一個按薪資降序排列的全域行號，您可以這樣寫：

SELECT
id,
name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

此查詢傳回所有員工，並按薪資從高到低排序。 row_num = 1 對於薪水最高的員工來說， 2 第二個，依此類推，沒有間隙，也沒有重複值。 除非你延長合約期限，否則薪資並列的情況會被任意打破。 ORDER BY 增加列。

如果您需要在每個部門內重新開始行編號，則可以合併 PARTITION BY - ORDER BY:

SELECT
id,
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

在這裡，每個部門都有自己的 1、2、3、… 序列，因此您可以透過後續篩選輕鬆找到「每個部門的收入最高者」。 row_num = 1 在外部查詢或 CTE 中。 這種模式是許多按群組查找前 N 個結果的查詢的基礎。

為了將編號邏輯與篩選邏輯清晰地分離，通常的做法是將視窗函數包裝在 CTE 或子查詢中，然後在外部 SELECT 中篩選產生的行號。 這一點尤其重要，因為視窗函數不能直接在…中使用。 WHERE 定義它們的同一個 SELECT 語句的子句。

使用 ROW_NUMBER() 實作經典的基於頁面的分頁

最直接的分頁方法是使用 ROW_NUMBER() 在 PostgreSQL 中，方法是計算每一行的行索引，然後請求與所需頁面對應的數值範圍。 有時這被用作另一種替代方案 OFFSET/LIMIT而且，從 SQL Server 或 Oracle 移植分頁程式碼時也能很好地運作。

假設您想要一個包含大小為 的結果的頁面。 @PageSize 頁碼 @PageNumber （從 0 開始的索引）。 典型的 T-SQL 查詢語句如下所示：

SELECT PK_ID, Truco, Descripcion
FROM (
SELECT
PK_ID,
Truco,
Descripcion,
ROW_NUMBER() OVER (ORDER BY Truco, PK_ID) AS RowNumber
FROM TrucosInformaticos
) AS Trucos
WHERE RowNumber BETWEEN (@PageSize * @PageNumber) + 1
AND @PageSize * (@PageNumber + 1);

同樣的邏輯可以直接應用在 PostgreSQL 中－你只需要調整參數語法，如果需要的話，還可以將其包裝在函數中而不是預存程序中。 本質是：計算 ROW_NUMBER() 然後按與頁面邊界對應的數值間隔對行進行切片。

例如，在 PostgreSQL 中，對於一個固定頁面，你可能會這樣寫：

WITH ranked_posts AS (
SELECT
id,
title,
ROW_NUMBER() OVER (ORDER BY title, id) AS row_num
FROM posts
)
SELECT id, title
FROM ranked_posts
WHERE row_num BETWEEN 11 AND 20;

這將返回按指定順序排列的第 11 行到第 20 行。 ORDER BY title, id這樣就等於給了你第二頁，頁面大小為 10。 與普通的 OFFSET 相比，最大的優勢在於行號是明確的，可以與附加邏輯結合使用，例如連接、篩選或進行進一步的分析。

當排序列有重複項時，使用遊標式分頁

基於偏移量的分頁很容易理解，但它在大表上可能會導致效能問題，而且當底層資料在請求之間發生變化時，它也會變得脆弱。 基於遊標的分頁（也稱為鍵集分頁）旨在透過使用最後一個看到的項目作為錨點而不是絕對偏移量來解決這個問題。

當排序所依據的欄位包含重複值時，事情就會變得棘手。 考慮一個模式 posts 以及 comments:

CREATE TABLE posts(
id uuid PRIMARY KEY,
title varchar(255) NOT NULL
);

CREATE TABLE comments(
id uuid PRIMARY KEY,
post_id uuid NOT NULL REFERENCES posts
);

假設你先建立一個查詢，按評論數降序排列貼文：

SELECT p.*, COUNT(c.id) AS comments_count
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count DESC;

對於基於遊標的分頁，人們可能會傾向於選擇最多某個數量級的內容。 comments_count 然後應用閾值 LIMIT:

WITH cte AS (
SELECT p.*, COUNT(c.id) AS comments_count
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count DESC
)
SELECT *
FROM cte
WHERE comments_count <= (
SELECT comments_count FROM cte WHERE id = '00000000-0000-0000-0000-000000000003'
)
LIMIT 3;

當多個帖子共享相同內容時，就會出現問題。 comments_count. 如果兩個帖子的計數均為 2，並且您的遊標指向其中一個帖子，則使用 <= 包括第二頁上的這兩行，同時使用 < 跳過所有計數相同的行，並且跳得太遠，錯過了一些你期望看到的帖子。

這是使用遊標分頁按非唯一鍵排序的典型症狀——如果遊標只對非唯一值進行編碼，資料庫就無法確定地將資料集切到一組並列值的「中間」。 為了安全地定義遊標，你需要一個唯一且穩定的排序規則。

一種解決方法是透過將評論數與主鍵結合起來，例如透過字串拼接，創建一個合成的、唯一的排序鍵：

WITH cte AS (
SELECT
p.,
COUNT(c.id) AS comments_count,
CONCAT(COUNT(c.id), ':', p.id) AS comments_count_id
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count_id DESC
)
SELECT *
FROM cte
WHERE comments_count_id < (
SELECT comments_count_id
FROM cte
WHERE id = '00000000-0000-0000-0000-000000000003'
)
LIMIT 3;

透過建構複合密鑰，例如 '2:00000000-...-0003'這樣，你就能確保排序完全唯一，並且可以放心地說“給我幾行”， comments_count_id 小於錨點」且無歧義。 這與一直以來包含的理念是一樣的。 id 在您的 ORDER BY 作為決勝因素。

實際上，你不必將資料連接到字串；你可以直接使用多列。 ORDER BY 並將它們編碼到應用層的遊標物件中。 從資料庫角度來看，重要的是總排序結果在每次呼叫之間都是唯一的且可重複的。

使用 ROW_NUMBER() 進行分頁與使用 LIMIT 和 OFFSET 進行分頁的比較

PostgreSQL 支援經典 LIMIT 以及 OFFSET 語法開箱即用，對於許多中小型結果集來說，使用起來完全沒問題。 您只需指定要跳過的行數和要傳回的行數即可。

然而，基於偏移量的分頁有兩個很大的缺點：效能和穩定性。 As OFFSET 即使資料量不斷增長，PostgreSQL 仍然需要掃描並丟棄所有先前的行才能開始傳回結果，這對於大型資料集來說成本很高。而且，如果請求之間的資料發生變化，頁面可能會“錯位”，導致顯示重複行或跳過行。

使用 ROW_NUMBER() 分頁功能允許你一次獲取行索引，然後對其進行清晰的切片：

WITH numbered_products AS (
SELECT
product_id,
product_name,
price,
ROW_NUMBER() OVER (ORDER BY product_name) AS row_number
FROM products
)
SELECT product_id, product_name, price
FROM numbered_products
WHERE row_number BETWEEN 11 AND 20
ORDER BY product_name;

這種模式很容易理解：它首先為每個產品分配其在排序清單中的位置，然後外部查詢取得第 11 行到第 20 行。 只要底層資料在執行和頁面消費之間沒有變化，你就能獲得邏輯序列的穩定片段。

這就是說， ROW_NUMBER()基於分頁的機制也不是提升效能的萬全之策。 資料庫仍然需要對所有符合條件的行應用視窗函數來分配編號，因此對於非常大的表，這可能與使用較大的 OFFSET 值一樣耗費資源。它的優點在於與額外的篩選條件結合使用，或者當您需要基於行號的確定性邏輯（而不僅僅是簡單的分頁）時。

視窗函數分頁在不同資料庫引擎中的行為方式

視窗函數，例如 ROW_NUMBER() 這些都是標準化的 SQL 功能，但每個資料庫引擎都會針對分頁模式進行不同的最佳化。 有些產品可以識別「帶有視窗函數的 top-N」查詢，並使用索引存取提前停止掃描；而有些產品則會每次都靜默地處理整個資料集。

考慮以下典型的 top-N / 分頁式查詢 ROW_NUMBER 在有序索引上 sales 表：

SELECT *
FROM (
SELECT
sales.*,
ROW_NUMBER() OVER (ORDER BY sale_date DESC, sale_id DESC) AS rn
FROM sales
) AS tmp
WHERE rn BETWEEN 11 AND 20
ORDER BY sale_date DESC, sale_id DESC;

在這裡， ROW_NUMBER 根據定義的順序統計行數 OVER 從句，以及外部 WHERE 將結果限制在特定頁面（第 11-20 行）。 這在邏輯上等同於一個前 N 個查詢加上一個偏移量。

例如，Oracle 能夠識別停止條件並使用索引。 sale_date 以及 sale_id 實現「流水線前 N 個」行為。 其執行計劃可能顯示 WINDOW NOSORT STOPKEY這表示引擎不需要額外的排序，一旦產生了所請求視窗的上限，就會停止。

並非所有機構都支持這種優化方式。 某些版本的 PostgreSQL 和其他引擎（如 MySQL、MariaDB 和 Db2）在基於視窗函數的模式中不會提前停止索引掃描，這意味著它們仍然會處理比提供所請求頁面嚴格必要的行數更多。

最近的 PostgreSQL 版本（15 及更高版本）改進了視窗函數的效能和最佳化，但主要版本之間的行為仍然可能有所不同。 務必檢查執行計劃。 EXPLAIN (ANALYZE) 看看你的資料庫是否能夠利用索引並提前停止，還是正在掃描和排序整個結果集。

將 ROW_NUMBER() 與 DISTINCT、CTE 和子查詢結合使用

使用時常見的陷阱 ROW_NUMBER() 以及 DISTINCT 視窗函數是在重複資料消除步驟之前計算的。 這會導致結果混亂，因為看似重複的值仍然會獲得不同的行號。

例如，如果您嘗試列舉來自某個資料集的不同價格 products 包含如下單一查詢的表：

SELECT DISTINCT price,
ROW_NUMBER() OVER (ORDER BY price) AS rn
FROM products;

你可能會驚訝地發現有多行有相同的內容。 price 但不同 rn 因為視窗函數之前已對所有行運行過，所以會產生這些值。 DISTINCT 從最終投影中刪除重複項。

處理此問題的穩健方法是先將不同的值物化（透過 CTE 或子查詢），然後再應用 ROW_NUMBER() 除此之外：

WITH prices AS (
SELECT DISTINCT price
FROM products
)
SELECT price,
ROW_NUMBER() OVER (ORDER BY price) AS rn
FROM prices;

或者，您也可以直接在查詢中使用子查詢。 FROM 條款，達到同樣的效果。 關鍵在於明確決定哪個結果集是“視窗”，即從哪個結果集進入“視窗”區域。 ROW_NUMBER() 應該可以正常運行，如果你需要獨特性，那就先建立這個集合。

這種模式對於分頁任務非常方便，例如「取得價格第三高的產品」或「列舉不同價格及其行號，然後選擇特定價格」。 您首先可以透過以下方式取得獨特的排序價格： ROW_NUMBER() 然後按您關心的特定等級加入或篩選。

ROW_NUMBER() 用於排名、前 N 個結果和刪除重複項

雖然我們的重點是分頁，但如果不提及這一點，那就太可惜了。 ROW_NUMBER() 是一個非常棒的數據排名和去重工具。 許多用於分頁的模式也可以用作排名邏輯。

若要取得每個類別的前 N ​​筆記錄，您可以按類別進行分區，並按價格降序等指標對行進行排名：

WITH ranked_products AS (
SELECT
product_id,
product_name,
category_id,
price,
ROW_NUMBER() OVER (
PARTITION BY category_id
ORDER BY price DESC
) AS rank
FROM products
)
SELECT product_id, product_name, category_id, price
FROM ranked_products
WHERE rank <= 2;

這將返回每個類別中最昂貴的兩件產品。 然後你可以加入一個 categories 使用表格 USING (category_id) 或使用明確連接來顯示人類可讀的名稱。

對於重複資料刪除， ROW_NUMBER() 經常與…一起使用 PARTITION BY 將每組中除第一個出現項目之外的所有出現項標記為重複項。 考慮一個簡單的表格：

CREATE TABLE items (
id INT,
name VARCHAR
);

假設您插入了多個重複的名稱，並且想要刪除多餘的副本，同時保留每個名稱的最小 ID。 首先可以找出重複項：

SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_number
FROM items
ORDER BY id;

任何一行 row_number > 1 這是一個重複項。然後您可以使用 CTE 和 DELETE 刪除聲明：

DELETE FROM items
WHERE id IN (
WITH ranked_items AS (
SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_number
FROM items
)
SELECT id
FROM ranked_items
WHERE row_number > 1
);

運行此操作後，從以下選項中選擇 items 只會顯示不同的名稱，每個值保留一行代表行。 這是一種簡潔、宣告式的去重方法，同時也能精確控制要保留哪一行。

在分頁情境中，ROW_NUMBER()、RANK() 和 DENSE_RANK() 的差異

PostgreSQL 提供了幾種排名視窗函數： ROW_NUMBER(), RANK()以及 DENSE_RANK(). 雖然它們都能分配有序數字，但當排序後的欄位中出現並列數字時，它們的行為卻有所不同。

主要區別在於：

• ROW_NUMBER() 始終為每一行分配一個唯一的整數，即使出現平局；數字嚴格按順序排列（1、2、3、4、…）。
• RANK() 對相同的值賦予相同的排名，但會跳過並列之後的數字（例如，1、2、2、4 – 缺少排名 3）。
• DENSE_RANK() 對於並列情況，排名也相同，但不跳過數字（1、2、2、3）。

對於分頁， ROW_NUMBER() 通常來說，這是最安全的選擇，因為它保證每個數字正好佔一行，這自然對應於 1-10、11-20 等頁面範圍。 如果你使用 RANK() or DENSE_RANK()由於存在並列情況，最終頁面行數可能少於或多於預期。

另一方面，對於報告競賽結果等用例，如果數值相同，則必須顯示相同的位置。 RANK() or DENSE_RANK() 比……更好地表達意圖 ROW_NUMBER(). 您仍然可以對這些結果進行分頁，但需要注意的是，「位置」不再直接對應實際的行號。

實用技巧、常見迷思與效能注意事項

使用時 ROW_NUMBER() 對於分頁和分析，一些好的實踐可以避免一些不易察覺的錯誤和不必要的效能問題。 它們大多可以歸結為明確和確定性。

始終明確定義 ORDER BY 在 - 的里面 OVER() 條款。 如果沒有它，PostgreSQL 可以隨意以任何順序返回行以用於視窗函數，即使底層資料相同，行號在執行之間也可能會變更。

盡可能在表末尾添加一個唯一列（通常是主鍵）。 ORDER BY 名單。 這樣就將排序變成了完全排序，避免了並列帶來的歧義，這對於基於遊標的分頁和可預測的前 N ​​個結果至關重要。

不要指望直接在視窗中使用視窗函數。 WHERE 同一個 SELECT 語句的子句。 相反，將它們封裝在 CTE 或子查詢中，並在外部查詢中對派生列進行篩選。這種模式簡單易用，可重複使用，並且保持 SQL 的可讀性。

分頁時，盡可能優先依索引列排序。 任何一種 ORDER BY 以及 ROW_NUMBER() 依賴排序或索引掃描；對於大型表來說，正確的索引可以使處理時間從毫秒縮短到秒。

合併時要小心 PARTITION BY 部分引擎支援分頁。 在某些資料庫產品和版本中，在視圖或子查詢中使用分區視窗函數可能會停用原本可用的停止鍵最佳化，導致引擎處理不必要的行數。因此，使用真實數據進行測試並閱讀查詢計劃至關重要。

對於非常大的數據集和高度動態的數據，請考慮結合使用 ROW_NUMBER() 為「管理員風格」檢視提供分頁，為使用者導向的端點提供基於遊標的鍵集分頁。 這樣，您就可以在 API 或 UI 中獲得確定性的工具查詢和高效率、穩定的導航。

從整體來看， ROW_NUMBER() 它不僅僅是一個分頁技巧：它是一個強大的分析建立模組，可讓您在 PostgreSQL 和其他主要 SQL 引擎中使用相同的底層邏輯對結果集進行編號、排名、切片和清理。 掌握它——以及對…的紮實理解 OVER(), PARTITION BY以及與 RANK() 以及 DENSE_RANK() – 為您提供非常靈活的工具包，可在實際應用中實現高效的分頁、前 N 個查詢和強大的去重功能。