RANK và DENSE_RANK

Bạn sẽ học được

• RANK() xếp hạng có khoảng trống khi đồng hạng.
• DENSE_RANK() xếp hạng không khoảng trống.
• PERCENT_RANK() và CUME_DIST() khi cần vị trí tương đối.
• Chọn hàm nào cho báo cáo nào.
• Vì sao ROW_NUMBER không thay được RANK trong bài toán xếp hạng.

Khái niệm cốt lõi

Ba hàm xếp hạng khác nhau đúng ở cách xử lý đồng hạng. Dùng cùng dữ liệu đếm đơn của khách để thấy rõ:

WITH counts AS (
  SELECT c.name, COUNT(o.id) AS n
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.name
)
SELECT name, n,
       ROW_NUMBER() OVER (ORDER BY n DESC) AS row_number,
       RANK()       OVER (ORDER BY n DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY n DESC) AS dense_rank
FROM counts
ORDER BY n DESC, name;

Mười một khách, kết quả:

Ba cách đọc cùng một dữ liệu:

• ROW_NUMBER cho 11 số khác nhau, nhưng thứ tự trong nhóm đồng hạng là tuỳ ý.
• RANK cho ba khách 2 đơn cùng hạng 2, rồi nhảy sang hạng 5 — vì đã có bốn người đứng trước.
• DENSE_RANK cũng cho hạng 2 nhưng người kế tiếp là hạng 3 — không tính khoảng trống.

Công thức rất dễ nhớ: RANK = số dòng đứng trước + 1. DENSE_RANK = số mức giá trị khác nhau đứng trước + 1.

Khi nào dùng cái nào:

Hai hàm ít dùng nhưng hữu ích khi cần vị trí tương đối thay vì số nguyên:

WITH counts AS (
  SELECT c.name, COUNT(o.id) AS n
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.name
)
SELECT name, n,
       ROUND(PERCENT_RANK() OVER (ORDER BY n DESC)::numeric, 2) AS pct_rank,
       ROUND(CUME_DIST()    OVER (ORDER BY n DESC)::numeric, 2) AS cume_dist
FROM counts
ORDER BY n DESC, name;

PERCENT_RANK của Bình Trần là 0 (đứng đầu); ba khách 2 đơn là 0,10; bảy khách 1 đơn là 0,40. Công thức: (rank - 1) / (tổng số dòng - 1) = (2-1)/10 với hạng 2.

CUME_DIST là tỷ lệ dòng có giá trị nhỏ hơn hoặc bằng dòng hiện tại: Bình Trần 1/11 ≈ 0,09; nhóm hạng 2 có 4/11 ≈ 0,36; nhóm hạng 5 đạt 11/11 = 1,00. Dùng CUME_DIST khi câu hỏi là "bao nhiêu phần trăm khách có số đơn không nhiều hơn người này".

Một điểm dễ sai: ORDER BY n DESC khiến giá trị lớn đứng đầu, nên hạng 1 là người mua nhiều nhất. Nếu quên DESC, hạng 1 lại là người mua ít nhất — câu lệnh vẫn chạy, chỉ là báo cáo ngược.

Đồng hạng không chỉ xảy ra với số đếm. Giá sản phẩm hiện tại đều khác nhau nên RANK và DENSE_RANK trùng nhau trên products:

SELECT name, price,
       RANK()       OVER (ORDER BY price DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products
ORDER BY price DESC;

Chín dòng với hai cột giống hệt nhau: 1, 2, 3, …, 9. Đây là bài kiểm tra nhanh để nhận ra dữ liệu của bạn có đồng hạng hay không — nếu hai cột khác nhau, có ít nhất hai dòng bằng nhau ở cột sắp xếp.

Ví dụ chạy được

Top 3 khách theo số đơn, dùng RANK để không bỏ sót người đồng hạng:

WITH counts AS (
  SELECT c.name, COUNT(o.id) AS n
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.name
), ranked AS (
  SELECT *, RANK() OVER (ORDER BY n DESC) AS hang
  FROM counts
)
SELECT name, n, hang
FROM ranked
WHERE hang <= 3
ORDER BY hang, name;

Kết quả bốn dòng: Bình Trần hạng 1 với 3 đơn; An, Chi, Giang hạng 2 với 2 đơn. Nếu đề nói "ba người mua nhiều nhất", RANK trả bốn — đúng về mặt thể thao nhưng có thể không đúng yêu cầu kinh doanh. Hãy hỏi lại: cần đúng ba dòng hay cần mọi người thuộc top ba hạng?

Cùng câu hỏi với DENSE_RANK <= 3 sẽ trả mười một dòng — vì hạng 3 là bảy khách 1 đơn. Đây là ví dụ rõ nhất cho việc chọn hàm sai làm báo cáo sai hoàn toàn dù cú pháp đúng.

Xếp hạng sản phẩm trong danh mục, kèm hạng toàn cửa hàng:

SELECT category, name, price,
       RANK() OVER (PARTITION BY category ORDER BY price DESC) AS hang_trong_danh_muc,
       RANK() OVER (ORDER BY price DESC) AS hang_toan_cua_hang
FROM products
ORDER BY hang_toan_cua_hang;

Nồi chiên không dầu vừa hạng 1 Gia dụng vừa hạng 1 toàn cửa hàng. Bàn phím cơ hạng 1 Điện tử nhưng hạng 2 toàn cửa hàng. Hai cửa sổ khác nhau trên cùng một dòng dữ liệu — điều GROUP BY không làm được.

Tìm các mức giá bị trùng (kiểm tra dữ liệu trước khi làm báo cáo):

SELECT price, COUNT(*) AS so_san_pham,
       DENSE_RANK() OVER (ORDER BY price DESC) AS muc_gia
FROM products
GROUP BY price
ORDER BY price DESC;

Chín dòng, mỗi dòng một mức giá, so_san_pham đều bằng 1 — dữ liệu mẫu không có hai sản phẩm cùng giá. Trên dữ liệu thật, đây là câu đầu tiên nên chạy trước khi quyết định dùng RANK hay DENSE_RANK.

Lọc theo CUME_DIST để lấy nhóm khách hàng đầu:

WITH counts AS (
  SELECT c.name, COUNT(o.id) AS n
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.name
), ranked AS (
  SELECT *, CUME_DIST() OVER (ORDER BY n DESC) AS cd
  FROM counts
)
SELECT name, n, ROUND(cd::numeric, 2) AS cd
FROM ranked
WHERE cd <= 0.4
ORDER BY n DESC, name;

Bốn dòng — Bình Trần và ba khách 2 đơn. Ngưỡng 0,4 cắt đúng sau nhóm hạng 2 vì 4/11 ≈ 0,36 còn nhóm kế tiếp là 11/11 = 1,0. Cách lọc này hay dùng cho "nhóm 40% khách hàng tốt nhất" trong phân tích RFM.

Thực hành

1. Chạy câu ba hàm trên 11 khách. Xác nhận rank có các giá trị 1, 2, 5 còn dense_rank chỉ có 1, 2, 3.
2. Đổi ORDER BY n DESC thành ORDER BY n và giải thích vì sao Hà Bùi vẫn không xuất hiện.
3. Lấy RANK <= 2 và DENSE_RANK <= 2 — cả hai trả bốn dòng. Lấy <= 3 — RANK trả bốn, DENSE_RANK trả mười một. Giải thích chênh lệch.
4. Tính PERCENT_RANK cho sản phẩm theo giá và kiểm tra sản phẩm rẻ nhất có giá trị 1,00.
5. Viết câu trả lời cho câu hỏi kinh doanh "ba khách hàng mua nhiều nhất" bằng cả hai cách và nêu rủi ro của mỗi cách.

Lỗi thường gặp

Lỗi khó phát hiện nhất là lỗi thứ nhất: câu lệnh chạy đúng, số liệu đúng, nhưng số dòng sai so với yêu cầu. Trước khi viết, hãy xác định rõ đề cần "đúng N dòng" hay "mọi người thuộc N hạng đầu" — hai câu này cần hai hàm khác nhau.

Tóm tắt

• ROW_NUMBER luôn duy nhất; RANK đồng hạng và nhảy; DENSE_RANK đồng hạng nhưng liền mạch.
• RANK = số dòng trước + 1; DENSE_RANK = số mức giá trị trước + 1.
• PERCENT_RANK và CUME_DIST cho vị trí tương đối, hữu ích khi lọc theo tỷ lệ phần trăm.
• Chọn hàm theo ý nghĩa báo cáo, không theo thói quen.
• Luôn thêm tiêu chí phá hoà nếu cần kết quả ổn định giữa các lần chạy.