Giải thích về phân trang trong PostgreSQL với ROW_NUMBER

Trang chủ » Python » Giải thích về phân trang trong PostgreSQL với ROW_NUMBER

Việc phân trang các tập kết quả lớn trong PostgreSQL thoạt nhìn có vẻ đơn giản, nhưng để thực hiện hiệu quả và chính xác – đặc biệt khi có sự trùng lặp trong cột sắp xếp – cần nhiều hơn là chỉ thêm lệnh LIMIT/OFFSET vào truy vấn. Chức năng cửa sổ ROW_NUMBER() Đây là một trong những công cụ đa năng nhất giúp bạn giải quyết vấn đề này, đồng thời mở ra nhiều trường hợp sử dụng phân tích bổ sung như xếp hạng, truy vấn top-N hoặc phát hiện trùng lặp.

Hướng dẫn này sẽ đi sâu vào cách sử dụng ROW_NUMBER() Bài viết này sẽ đề cập đến cách thức hoạt động thực sự của phân trang trong PostgreSQL, sự khác biệt so với các chức năng xếp hạng khác, những ảnh hưởng về hiệu năng cần lưu ý và cách các công cụ cơ sở dữ liệu lớn khác xử lý các mẫu tương tự. Chúng ta cũng sẽ xem xét các tình huống thực tế phức tạp như phân trang dựa trên con trỏ khi cột sắp xếp chứa các giá trị trùng lặp và cách kết hợp chúng. ROW_NUMBER() Với CTE, các phép nối và truy vấn con, bạn có thể tạo ra câu lệnh SQL sạch, sẵn sàng cho môi trường sản xuất.

Chức năng thực sự của hàm cửa sổ ROW_NUMBER() trong PostgreSQL là gì?

Ở cốt lõi của nó, ROW_NUMBER() Đây là một hàm cửa sổ gán một số nguyên duy nhất, tuần tự cho mỗi hàng trong tập kết quả, bắt đầu từ 1 và tăng dần 1 mà không có khoảng trống. Việc đánh số này có thể được áp dụng cho toàn bộ kết quả hoặc được thiết lập lại cho từng nhóm hàng, tùy thuộc vào cách bạn xác định cửa sổ.

Cú pháp chung trong PostgreSQL trông như thế này:

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

Hai phần bên trong OVER Mệnh đề này kiểm soát cách gán số thứ tự hàng: PARTITION BY (tùy chọn) chia tập kết quả thành các nhóm độc lập và ORDER BY (Bắt buộc) xác định thứ tự trong mỗi phân vùng để quyết định hàng nào nhận số nào.

Nếu bạn bỏ qua PARTITION BYHàm này coi toàn bộ tập kết quả là một phân vùng duy nhất và chỉ đơn giản là đánh số tất cả các hàng theo thứ tự đã chỉ định. Sau khi bạn thêm PARTITION BYViệc đánh số hàng sẽ bắt đầu lại từ 1 trong mỗi phân vùng, điều này cực kỳ hữu ích cho việc xếp hạng theo danh mục, truy vấn top-N theo nhóm và loại bỏ trùng lặp theo nhóm.

Không giống như các hàm xếp hạng như RANK() or DENSE_RANK(), ROW_NUMBER() Hoàn toàn bỏ qua các giá trị trùng lặp và không bao giờ lặp lại một số, ngay cả khi các hàng có giá trị giống hệt nhau trong các cột sắp xếp. Điều đó khiến nó trở nên lý tưởng cho việc phân trang xác định và cắt hàng chính xác.

Ví dụ cơ bản về hàm ROW_NUMBER() để xây dựng trực giác

Trước khi sử dụng ROW_NUMBER() Đối với việc đánh số trang, sẽ hữu ích hơn nếu xem nó hoạt động trên các ví dụ đơn giản, trong đó mục tiêu chỉ là đánh số các hàng một cách có kiểm soát. Hãy tưởng tượng một cái bàn employees với các cột id, name, department và salary.

Để gán số thứ tự hàng toàn cục được sắp xếp theo mức lương giảm dần, bạn có thể viết như sau:

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

Truy vấn này trả về tất cả nhân viên, được sắp xếp theo mức lương từ cao nhất đến thấp nhất, kèm theo... row_num = 1 đối với nhân viên được trả lương cao nhất, 2 đối với cái thứ hai, và cứ thế tiếp tục, không có khoảng trống và không có giá trị lặp lại. Các thỏa thuận về lương sẽ bị hủy bỏ một cách tùy tiện trừ khi bạn gia hạn thỏa thuận. ORDER BY với các cột bổ sung.

Nếu bạn cần đánh số thứ tự hàng bắt đầu lại bên trong mỗi phòng ban, bạn sẽ kết hợp PARTITION BY với ORDER BY:

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

Ở đây, mỗi phòng ban được đánh số thứ tự riêng từ 1, 2, 3, …, vì vậy bạn có thể dễ dàng tìm ra “người có thu nhập cao nhất trong mỗi phòng ban” bằng cách lọc dữ liệu sau này. row_num = 1 trong một truy vấn bên ngoài hoặc CTE. Mô hình này là xương sống cho nhiều truy vấn top-N-per-group.

Để tách biệt rõ ràng logic đánh số khỏi logic lọc, người ta thường gói hàm cửa sổ trong một CTE hoặc truy vấn con, sau đó lọc các số thứ tự hàng được tạo ra trong câu lệnh SELECT bên ngoài. Điều này đặc biệt quan trọng vì các hàm cửa sổ không thể được sử dụng trực tiếp trong WHERE mệnh đề của cùng một câu lệnh SELECT định nghĩa chúng.

Sử dụng ROW_NUMBER() cho phân trang theo trang cổ điển.

Cách đơn giản nhất để thực hiện phân trang với ROW_NUMBER() Trong PostgreSQL, phương pháp là tính toán chỉ mục hàng cho mỗi hàng, sau đó yêu cầu một phạm vi số tương ứng với trang bạn muốn. Điều này đôi khi được sử dụng như một phương án thay thế cho OFFSET/LIMITvà cũng hoạt động tốt khi chuyển đổi mã phân trang từ SQL Server hoặc Oracle.

Giả sử bạn muốn một trang kết quả với kích thước @PageSize và số trang @PageNumber (Chỉ số bắt đầu từ 0). Một truy vấn T-SQL điển hình có dạng như sau:

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);

Nguyên tắc tương tự cũng áp dụng trực tiếp cho PostgreSQL – bạn chỉ cần điều chỉnh cú pháp tham số và, nếu muốn, gói nó trong một hàm thay vì một thủ tục lưu trữ. Tóm lại là: tính toán ROW_NUMBER() Thực hiện một lần, sau đó chia các hàng theo khoảng số tương ứng với ranh giới trang của bạn.

Ví dụ, trong PostgreSQL, đối với một trang cố định, bạn có thể viết như sau:

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;

Thao tác này trả về các hàng từ 11 đến 20 theo thứ tự được xác định bởi... ORDER BY title, idVề cơ bản, điều này sẽ cung cấp cho bạn trang thứ hai với kích thước trang là 10. Ưu điểm lớn so với hàm OFFSET thông thường là số thứ tự hàng được thể hiện rõ ràng và có thể kết hợp với các logic bổ sung, ví dụ như nối chuỗi, lọc hoặc thực hiện phân tích sâu hơn.

Phân trang theo kiểu con trỏ khi cột sắp xếp có dữ liệu trùng lặp.

Phân trang dựa trên độ lệch rất dễ hiểu, nhưng nó có thể gây ra các vấn đề về hiệu suất trên các bảng lớn và cũng trở nên dễ bị lỗi khi dữ liệu cơ bản thay đổi giữa các yêu cầu. Phân trang dựa trên con trỏ (còn gọi là phân trang theo tập hợp từ khóa) nhằm giải quyết vấn đề này bằng cách sử dụng mục được xem cuối cùng làm điểm neo thay vì độ lệch tuyệt đối.

Mọi chuyện trở nên phức tạp khi cột bạn dùng để sắp xếp chứa các giá trị trùng lặp. Hãy xem xét một lược đồ với posts và 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
);

Hãy tưởng tượng bạn tạo một truy vấn để sắp xếp các bài đăng theo số lượng bình luận giảm dần:

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;

Đối với việc phân trang dựa trên con trỏ, người ta có thể muốn chọn tối đa một số lượng nhất định. comments_count ngưỡng và sau đó áp dụng 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;

Vấn đề xuất hiện khi nhiều bài đăng có cùng nội dung. comments_count. Nếu cả hai bài đăng đều có số lượt xem là 2, và con trỏ của bạn trỏ vào một trong số chúng, thì sử dụng lệnh sau: <= bao gồm cả hai hàng được nối đó trên trang thứ hai, trong khi sử dụng < Nó bỏ qua tất cả các hàng có cùng số lượng và nhảy quá xa, bỏ sót một số bài đăng mà bạn mong đợi.

Đây là một triệu chứng điển hình của việc sắp xếp theo khóa không duy nhất trong phân trang con trỏ – cơ sở dữ liệu không thể phân chia tập dữ liệu một cách xác định “ở giữa” một nhóm các giá trị trùng lặp nếu con trỏ của bạn chỉ mã hóa giá trị không duy nhất. Bạn cần một thứ tự duy nhất và ổn định để định nghĩa con trỏ một cách an toàn.

Một giải pháp thay thế là tạo một khóa sắp xếp tổng hợp, duy nhất bằng cách kết hợp số lượng bình luận với khóa chính, ví dụ như thông qua phép nối chuỗi:

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;

Bằng cách xây dựng một khóa tổng hợp như '2:00000000-...-0003'Bạn đảm bảo thứ tự sắp xếp hoàn toàn độc nhất và có thể tự tin nói "hãy cho tôi các hàng với..." comments_count_id "Nhỏ hơn mỏ neo" một cách rõ ràng. Ý tưởng vẫn như mọi khi, bao gồm... id trong ORDER BY dùng làm tiêu chí phân định thắng thua khi hòa.

Trên thực tế, bạn không cần phải nối chuỗi; bạn chỉ cần sử dụng nhiều cột. ORDER BY và mã hóa chúng trong đối tượng con trỏ của bạn ở lớp ứng dụng. Từ phía cơ sở dữ liệu, điều quan trọng là thứ tự tổng thể phải là duy nhất và có thể tái tạo được giữa các lần gọi.

Phân trang bằng ROW_NUMBER() so với LIMIT và OFFSET

PostgreSQL hỗ trợ định dạng cổ điển. LIMIT và OFFSET Về mặt cú pháp, nó hoàn toàn phù hợp và tốt cho nhiều tập kết quả nhỏ đến trung bình. Bạn chỉ cần chỉ định số hàng cần bỏ qua và số hàng cần trả về.

Tuy nhiên, việc phân trang dựa trên OFFSET có hai nhược điểm lớn: hiệu năng và độ ổn định. As OFFSET Ngay cả khi kích thước tập dữ liệu tăng lên, PostgreSQL vẫn phải quét và loại bỏ tất cả các hàng trước đó trước khi có thể bắt đầu trả về kết quả, điều này trở nên tốn kém đối với các tập dữ liệu lớn. Và nếu dữ liệu thay đổi giữa các yêu cầu, các trang có thể "dịch chuyển" và hiển thị các bản ghi trùng lặp hoặc bỏ qua các hàng.

Sử dụng ROW_NUMBER() Đối với việc phân trang, nó cung cấp cho bạn một cách để hiện thực hóa chỉ mục hàng một lần và sau đó cắt nó một cách gọn gàng:

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;

Mô hình này khá dễ hiểu: trước tiên nó gán vị trí cho mỗi sản phẩm trong danh sách đã được sắp xếp, sau đó truy vấn bên ngoài sẽ lấy các hàng từ 11 đến 20. Miễn là dữ liệu cơ bản không thay đổi giữa thời điểm thực thi và thời điểm xem trang, bạn sẽ nhận được một phần ổn định của chuỗi logic.

Mà nói, ROW_NUMBER()Phân trang dựa trên tham số cũng không phải là giải pháp thần kỳ cho vấn đề hiệu suất. Cơ sở dữ liệu vẫn cần đánh giá hàm cửa sổ trên tất cả các hàng đủ điều kiện để gán số, vì vậy đối với các bảng cực lớn, điều này có thể tốn kém tương đương với một giá trị OFFSET lớn. Ưu điểm nổi bật của phương pháp này là khi kết hợp với các bộ lọc bổ sung hoặc khi bạn muốn logic xác định dựa trên số thứ tự hàng, vượt ra ngoài việc phân trang đơn thuần.

Cách thức hoạt động của phân trang hàm cửa sổ trên các công cụ cơ sở dữ liệu khác nhau

Các hàm cửa sổ như ROW_NUMBER() Đây là các tính năng SQL được tiêu chuẩn hóa, nhưng mỗi công cụ cơ sở dữ liệu tối ưu hóa chúng khác nhau cho các mẫu giống như phân trang. Một số sản phẩm có thể nhận dạng các truy vấn “top-N với hàm cửa sổ” và dừng quét sớm bằng cách sử dụng truy cập chỉ mục; những sản phẩm khác sẽ âm thầm xử lý toàn bộ tập dữ liệu mỗi lần.

Hãy xem xét truy vấn kiểu top-N / phân trang điển hình này bằng cách sử dụng ROW_NUMBER trên một chỉ mục được sắp xếp trong một sales bàn:

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;

Ở đây, ROW_NUMBER đếm số hàng theo thứ tự được xác định trong OVER điều khoản và bên ngoài WHERE Giới hạn kết quả trong một trang cụ thể (dòng 11-20). Về mặt logic, điều này tương đương với một truy vấn top-N kết hợp với một độ lệch.

Ví dụ, Oracle có khả năng nhận biết điều kiện dừng và sử dụng chỉ mục trên đó. sale_date và sale_id để triển khai hành vi “top-N trong pipeline”. Kế hoạch thực hiện của nó có thể cho thấy WINDOW NOSORT STOPKEYĐiều này cho thấy công cụ không cần sắp xếp thêm và sẽ dừng lại ngay khi tạo ra giới hạn trên của cửa sổ được yêu cầu.

Việc hỗ trợ cho loại tối ưu hóa này không phải là phổ biến. Một số phiên bản của PostgreSQL và các công cụ khác như MySQL, MariaDB và Db2 không dừng quá trình quét chỉ mục sớm trong các mô hình dựa trên hàm cửa sổ này, có nghĩa là chúng vẫn xử lý nhiều hàng hơn mức cần thiết để cung cấp trang được yêu cầu.

Các phiên bản PostgreSQL gần đây (từ 15 trở lên) đã cải thiện hiệu suất và tối ưu hóa hàm cửa sổ, nhưng hành vi vẫn có thể khác nhau giữa các phiên bản chính. Luôn kiểm tra kế hoạch thực thi với EXPLAIN (ANALYZE) Để kiểm tra xem cơ sở dữ liệu của bạn có thể tận dụng các chỉ mục và dừng sớm hay không, hoặc liệu nó có đang quét và sắp xếp toàn bộ tập kết quả hay không.

Kết hợp ROW_NUMBER() với DISTINCT, CTE và truy vấn con

Một lỗi thường gặp khi sử dụng ROW_NUMBER() bên cạnh DISTINCT Điều đó có nghĩa là hàm cửa sổ được đánh giá trước bước loại bỏ các bản sao trùng lặp. Điều này có thể dẫn đến kết quả gây nhầm lẫn, trong đó các giá trị dường như trùng lặp vẫn được đánh số thứ tự riêng biệt.

Ví dụ, nếu bạn cố gắng liệt kê các mức giá khác nhau từ một products bảng với một truy vấn duy nhất như sau:

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

Bạn có thể ngạc nhiên khi thấy nhiều hàng có cùng một giá trị. price nhưng khác rn các giá trị, bởi vì hàm cửa sổ đã được chạy trên tất cả các hàng trước đó DISTINCT Đã loại bỏ các mục trùng lặp khỏi bản chiếu cuối cùng.

Cách xử lý hiệu quả nhất là trước tiên hiện thực hóa các giá trị riêng biệt (thông qua CTE hoặc truy vấn con), sau đó áp dụng. ROW_NUMBER() Ngoài ra còn có:

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

Ngoài ra, bạn cũng có thể sử dụng truy vấn con trực tiếp trong đó. FROM điều khoản đó đạt được hiệu quả tương tự. Ý tưởng quan trọng là phải quyết định rõ ràng tập kết quả nào là "cửa sổ" mà ở đó... ROW_NUMBER() Nên hoạt động như bình thường, và nếu bạn cần tính độc đáo, hãy xây dựng bộ đó trước.

Mẫu này cực kỳ hữu ích cho các tác vụ phân trang như "lấy sản phẩm có giá cao thứ 3" hoặc "liệt kê các mức giá khác nhau theo số thứ tự hàng và sau đó chọn một mức giá cụ thể". Trước tiên, bạn có thể nhận được giá được sắp xếp riêng biệt bằng cách ROW_NUMBER() Sau đó, bạn có thể tham gia hoặc lọc theo thứ hạng cụ thể mà bạn quan tâm.

ROW_NUMBER() được sử dụng để xếp hạng, loại bỏ top-N và các bản sao trùng lặp.

Mặc dù trọng tâm của chúng ta là việc đánh số trang, nhưng sẽ thật thiếu sót nếu không đề cập đến điều đó. ROW_NUMBER() Đây là một công cụ tuyệt vời để xếp hạng và loại bỏ dữ liệu trùng lặp. Nhiều mẫu bạn sử dụng cho phân trang cũng có thể được dùng làm logic xếp hạng.

Để lấy N bản ghi hàng đầu cho mỗi danh mục, bạn có thể phân vùng theo danh mục và xếp hạng các hàng theo một chỉ số như giá giảm dầ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;

Thao tác này sẽ trả về hai sản phẩm đắt nhất trong mỗi danh mục. Sau đó, bạn có thể tham gia vào một categories bàn sử dụng USING (category_id) hoặc sử dụng phép nối rõ ràng để hiển thị tên dễ đọc đối với con người.

Để xóa các bản sao trùng lặp, ROW_NUMBER() thường được sử dụng kết hợp với PARTITION BY Đánh dấu tất cả các lần xuất hiện ngoại trừ lần xuất hiện đầu tiên trong mỗi nhóm là các bản sao trùng lặp. Hãy xem xét một bảng đơn giản sau:

CREATE TABLE items (
id INT,
name VARCHAR
);

Giả sử bạn chèn một số tên trùng lặp và muốn xóa các bản sao thừa trong khi vẫn giữ lại ID thấp nhất cho mỗi tên. Trước tiên, bạn có thể xác định các mục trùng lặp:

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

Bất kỳ hàng nào có row_number > 1 là bản sao. Sau đó, bạn có thể sử dụng CTE và một DELETE tuyên bố để loại bỏ chúng:

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
);

Sau khi chạy lệnh này, việc lựa chọn từ items Chỉ hiển thị các tên riêng biệt, với một hàng đại diện cho mỗi giá trị. Đây là một cách khai báo rõ ràng để loại bỏ các bản ghi trùng lặp, đồng thời kiểm soát chính xác hàng nào bạn muốn giữ lại.

So sánh ROW_NUMBER() và RANK() trong các kịch bản phân trang

PostgreSQL cung cấp một số hàm cửa sổ xếp hạng: ROW_NUMBER(), RANK()và DENSE_RANK(). Mặc dù tất cả chúng đều gán số thứ tự, nhưng chúng hoạt động khác nhau khi có sự trùng lặp trong các cột đã được sắp xếp.

Những điểm khác biệt quan trọng là:

• ROW_NUMBER() Luôn gán một số nguyên duy nhất cho mỗi hàng, ngay cả khi có sự trùng lặp; các số được đánh số tuần tự nghiêm ngặt (1, 2, 3, 4, …).
• RANK() Gán cùng thứ hạng cho các giá trị giống hệt nhau nhưng bỏ qua các số sau khi có sự trùng lặp (ví dụ: 1, 2, 2, 4 – thứ hạng 3 bị bỏ qua).
• DENSE_RANK() cũng cho cùng thứ hạng trong trường hợp hòa nhưng không bỏ qua các số (1, 2, 2, 3).

Để đánh số trang, ROW_NUMBER() Đây thường là lựa chọn an toàn nhất vì nó đảm bảo chính xác một hàng cho mỗi số, điều này phù hợp một cách tự nhiên với các phạm vi trang như 1-10, 11-20, v.v. Nếu bạn đã sử dụng RANK() or DENSE_RANK()Điều này có thể dẫn đến việc các trang có số hàng ít hơn hoặc nhiều hơn dự kiến ​​do sự trùng lặp.

Mặt khác, đối với các trường hợp sử dụng báo cáo như kết quả cuộc thi, nơi các giá trị bằng nhau phải có cùng vị trí, RANK() or DENSE_RANK() thể hiện ý định tốt hơn ROW_NUMBER(). Bạn vẫn có thể phân trang các kết quả đó, nhưng cần lưu ý rằng "vị trí" không còn tương ứng trực tiếp với số thứ tự hàng vật lý nữa.

Mẹo thực tế, những điều cần tránh và những yếu tố cần cân nhắc khi thực hiện.

Khi đang sử dụng ROW_NUMBER() Đối với phân trang và phân tích dữ liệu, một vài thực tiễn tốt sẽ giúp bạn tránh được những lỗi nhỏ khó phát hiện và những rắc rối không cần thiết về hiệu năng. Hầu hết chúng đều quy về tính rõ ràng và mang tính xác định.

Luôn luôn xác định rõ ràng ORDER BY Bên trong OVER() mệnh đề. Nếu không có nó, PostgreSQL có thể trả về các hàng theo bất kỳ thứ tự nào cho mục đích của hàm cửa sổ, và số thứ tự hàng của bạn có thể thay đổi giữa các lần thực thi ngay cả khi dữ liệu cơ bản là giống hệt nhau.

Khi có thể, hãy thêm một cột duy nhất (thường là khóa chính) vào cuối bảng. ORDER BY danh sách. Điều này biến thứ tự sắp xếp thành thứ tự toàn phần và tránh sự mơ hồ do các giá trị bằng nhau, điều này rất quan trọng đối với việc phân trang dựa trên con trỏ và cho kết quả top-N có thể dự đoán được.

Đừng kỳ vọng có thể sử dụng trực tiếp các hàm cửa sổ trong... WHERE mệnh đề của câu lệnh SELECT tương tự. Thay vào đó, hãy gói chúng trong một CTE hoặc truy vấn con và lọc trên cột được tạo ra trong truy vấn bên ngoài. Mẫu này đơn giản, có thể tái sử dụng và giúp mã SQL của bạn dễ đọc.

Khi phân trang, nên ưu tiên sắp xếp theo các cột được lập chỉ mục bất cứ khi nào có thể. Cả hai ORDER BY và ROW_NUMBER() Dựa vào việc sắp xếp hoặc quét chỉ mục; việc lập chỉ mục đúng cách có thể tạo ra sự khác biệt giữa mili giây và giây trên các bảng lớn.

Hãy cẩn thận khi kết hợp. PARTITION BY Một số công cụ tìm kiếm có chức năng phân trang. Trong một số sản phẩm và phiên bản cơ sở dữ liệu nhất định, việc sử dụng các hàm cửa sổ phân vùng bên trong các khung nhìn hoặc truy vấn con có thể vô hiệu hóa các tối ưu hóa khóa dừng khả dụng, khiến công cụ xử lý nhiều hàng hơn mức cần thiết. Việc kiểm tra với dữ liệu thực tế và đọc kế hoạch truy vấn là rất cần thiết.

Đối với các tập dữ liệu rất lớn và dữ liệu có tính biến động cao, hãy cân nhắc kết hợp. ROW_NUMBER() Phân trang cho các chế độ xem "kiểu quản trị" với phân trang theo bộ từ khóa dựa trên con trỏ cho các điểm cuối dành cho người dùng. Bằng cách đó, bạn vừa có được các truy vấn công cụ mang tính xác định, vừa có được khả năng điều hướng hiệu quả và ổn định trong API hoặc giao diện người dùng của mình.

Xét về tổng thể, ROW_NUMBER() Đây không chỉ là một thủ thuật phân trang: nó là một khối xây dựng phân tích mạnh mẽ cho phép bạn đánh số, xếp hạng, cắt lát và làm sạch các tập kết quả trong PostgreSQL và trên các công cụ SQL chính khác với cùng logic cơ bản. Nắm vững nó – cùng với sự hiểu biết vững chắc về OVER(), PARTITION BYvà những điểm khác biệt so với RANK() và DENSE_RANK() – Cung cấp cho bạn bộ công cụ rất linh hoạt để phân trang hiệu quả, truy vấn top-N và loại bỏ dữ liệu trùng lặp mạnh mẽ trong các ứng dụng thực tế.