Trino SQL Bài học 27: Window Functions: Các hàm xếp hạng (ROW_NUMBER, RANK, DENSE_RANK)

Mục tiêu bài học:

• Phân biệt rõ ràng hành vi xử lý dữ liệu trùng lặp (Ties) của 3 hàm xếp hạng phổ biến nhất.

• Nắm vững cách sử dụng mệnh đề ORDER BY bắt buộc bên trong OVER().

• Giải quyết bài toán kinh điển trong phân tích dữ liệu: "Lấy Top N bản ghi cho mỗi nhóm".

• Ứng dụng ROW_NUMBER để loại bỏ dữ liệu trùng lặp (Deduplication).

1. Điểm chung của các hàm xếp hạng

Các hàm xếp hạng không thực hiện tính toán cộng trừ nhân chia như SUM hay AVG. Nhiệm vụ duy nhất của chúng là gắn một con số thứ tự (Rank) cho từng dòng dữ liệu dựa trên một tiêu chí sắp xếp nào đó.

Quy tắc bắt buộc: Vì đã nói đến "thứ tự", mệnh đề OVER() của các hàm này bắt buộc phải có ORDER BY. Nếu bạn để trống OVER(), Trino sẽ báo lỗi vì nó không biết phải xếp hạng dựa trên tiêu chí nào. Bạn có thể dùng thêm PARTITION BY để xếp hạng riêng cho từng nhóm.

2. ROW_NUMBER(): Số thứ tự độc nhất và tàn nhẫn

ROW_NUMBER() là hàm đơn giản nhất và được sử dụng nhiều nhất trong giới Data Engineering. Logic: Nó đánh số thứ tự từ 1 đến N cho các dòng. Nếu có 2 dòng có giá trị bằng nhau (Hòa / Tie), nó vẫn "tàn nhẫn" ép một dòng là số 1, dòng kia là số 2 một cách ngẫu nhiên (hoặc dựa trên thứ tự đọc ổ cứng). Không bao giờ có 2 dòng trùng số thứ tự.

SQL

-- Đánh số thứ tự các đơn hàng theo giá trị từ cao xuống thấp
SELECT 
    orderkey, 
    totalprice,
    ROW_NUMBER() OVER (ORDER BY totalprice DESC) AS row_num
FROM tpch.tiny.orders
LIMIT 5;

3. RANK(): Xếp hạng có nhảy số (Chuẩn Olympic)

RANK() xử lý các trường hợp hòa nhau (Ties) theo cách công bằng hơn ROW_NUMBER. Logic: Nếu 2 dòng có giá trị bằng nhau, chúng sẽ nhận cùng một thứ hạng. Tuy nhiên, thứ hạng của người tiếp theo sẽ bị nhảy cóc (bỏ trống số lượng hạng tương ứng với số người hòa). Đây là cách trao huy chương trong thể thao.

Ví dụ: Điểm số là 10, 9, 9, 8. Thứ hạng sẽ là: 1, 2, 2, 4 (Không có hạng 3).

SQL

-- Xếp hạng khách hàng theo số dư tài khoản
SELECT 
    custkey, 
    acctbal,
    RANK() OVER (ORDER BY acctbal DESC) AS rank_pos
FROM tpch.tiny.customer;

4. DENSE_RANK(): Xếp hạng liền mạch

DENSE_RANK() (Xếp hạng dày đặc) cũng giống như RANK(), nó cho phép các giá trị bằng nhau đồng hạng. Logic: Khác biệt duy nhất là nó không nhảy cóc con số tiếp theo. Chuỗi thứ hạng luôn liên tục.

Ví dụ: Điểm số là 10, 9, 9, 8. Thứ hạng sẽ là: 1, 2, 2, 3.

5. Bảng so sánh trực quan

Giả sử chúng ta có 5 đơn hàng với giá trị tương ứng, hãy xem 3 hàm này xử lý ra sao:

6. Bài toán kinh điển 1: "Top N mỗi nhóm"

Yêu cầu: Tìm ra 3 khách hàng có số dư tài khoản cao nhất cho TỪNG phân khúc thị trường (mktsegment).

Đây là lúc ta kết hợp PARTITION BY (để chia nhóm theo phân khúc) và ORDER BY (để xếp hạng theo số dư) bên trong OVER(). Nhớ lại bài 26, Window Functions không được đặt trong WHERE. Chúng ta bắt buộc phải dùng Truy vấn con (Subquery/CTE) để lọc.

SQL

-- Bọc logic tính toán vào CTE (Truy vấn tạm tên là ranked_customers)
WITH ranked_customers AS (
    SELECT 
        mktsegment,
        name,
        acctbal,
        DENSE_RANK() OVER (
            PARTITION BY mktsegment 
            ORDER BY acctbal DESC
        ) AS rank_in_segment
    FROM tpch.tiny.customer
)
-- Dùng WHERE ở truy vấn chính để lọc lấy Top 3
SELECT * 
FROM ranked_customers 
WHERE rank_in_segment <= 3
ORDER BY mktsegment, rank_in_segment;

7. Bài toán kinh điển 2: Lọc trùng (Deduplication) với ROW_NUMBER

Trong hệ thống Big Data/Data Lake, dữ liệu đổ về từ Kafka thường bị trùng lặp (Duplicate Records) do lỗi mạng (ví dụ cùng một đơn hàng nhưng bị đẩy lên hệ thống 2 lần). ROW_NUMBER là công cụ sắc bén nhất để dọn dẹp rác này. Bằng cách nhóm theo Khóa chính (Primary Key) và sắp xếp theo thời gian cập nhật mới nhất, ta chỉ giữ lại dòng có row_num = 1.

SQL

WITH deduplicated_data AS (
    SELECT 
        orderkey,
        totalprice,
        orderstatus,
        -- Nhóm theo mã đơn, cái nào cập nhật sau cùng (DESC) sẽ đứng số 1
        ROW_NUMBER() OVER (
            PARTITION BY orderkey 
            ORDER BY last_updated_time DESC
        ) AS rn
    FROM raw_orders_table
)
SELECT orderkey, totalprice, orderstatus
FROM deduplicated_data
WHERE rn = 1; -- Trảm toàn bộ các bản ghi trùng lặp (rn > 1)