[Tech Blog #17 | Data Analytics] Làm Chủ SQL Window Functions: ROW_NUMBER, RANK, DENSE_RANK, Tuyệt Chiêu LAG/LEAD & Tư Duy CTE | Thai Duong Le Nguyen

1. Đặt vấn đề: Giới hạn của GROUP BY truyền thống

Trong [Tech Blog #05], chúng ta đã biết mệnh đề GROUP BY gom nhiều dòng dữ liệu lại thành một dòng tổng hợp duy nhất.
Tuy nhiên, trong phân tích kinh doanh thực tế, bạn sẽ liên tục gặp những bài toán "oái oăm" sau:

"Hãy tìm Top 3 nhân viên có doanh số cao nhất của TỪNG phòng ban" (Top N per group).
"Tính tỷ lệ tăng trưởng doanh thu của tháng này so với tháng trước (MoM Growth)".
"Tính doanh thu tích lũy dồn từ đầu tháng đến ngày hiện tại (Running Total)".

Nếu chỉ dùng GROUP BY, bạn sẽ gặp bế tắc: vì khi đã gom nhóm theo phòng ban, toàn bộ chi tiết từng nhân viên sẽ bị triệt tiêu, bạn không thể hiển thị đồng thời cả thông tin cá nhân lẫn số liệu xếp hạng cùng lúc.

Để giải quyết bài toán này, SQL cung cấp một vũ khí tối thượng mang tên Window Functions (Hàm cửa sổ).

2. Bản chất của Window Functions: "Cửa sổ trượt" giữ nguyên chi tiết

Sự khác biệt cốt lõi nhất giữa GROUP BY và Window Functions:

GROUP BY: Gộp $N$ dòng thành 1 dòng (Thu gọn dữ liệu).
WINDOW FUNCTION: Tính toán trên một tập hợp các dòng liên quan, nhưng giữ nguyên $N$ dòng ban đầu!

Cú pháp kinh điển:
<Hàm tính toán> OVER (
    PARTITION BY <Cột chia nhóm>
    ORDER BY <Cột sắp xếp>
)

PARTITION BY: Chia tập dữ liệu thành các "phòng ban" hay "hộp dữ liệu" nhỏ (tương tự như GROUP BY).
ORDER BY bên trong OVER(): Sắp xếp thứ tự các dòng bên trong từng chiếc hộp đó để xếp hạng hoặc tính toán lũy kế.

3. Bộ ba hàm xếp hạng: ROW_NUMBER vs. RANK vs. DENSE_RANK

Giả sử trong một cuộc thi chạy, có 4 vận động viên với thành tích thời gian lần lượt là: 10 giây, 10 giây, 12 giây, 15 giây.
Khi xếp hạng, 3 hàm này sẽ cho ra kết quả hoàn toàn khác nhau:

Vận động viên	Thời gian	`ROW_NUMBER()`	`RANK()`	`DENSE_RANK()`
A	10s	1	1 (Đồng hạng 1)	1 (Đồng hạng 1)
B	10s	2 (Gán bừa 1 trong 2)	1 (Đồng hạng 1)	1 (Đồng hạng 1)
C	12s	3	3 (Nhảy cóc hạng 2!)	2 (Hạng 2 liền mạch!)
D	15s	4	4	3

Cách nhớ nhanh:

ROW_NUMBER(): Đánh số thứ tự dòng đơn thuần (1, 2, 3, 4...). Bất chấp giá trị bằng nhau, mỗi dòng nhận 1 số riêng biệt.
- Ứng dụng: Dùng để khử trùng lặp (Deduplication) – chỉ lấy dòng có row_num = 1.
RANK(): Nếu bằng điểm thì đồng hạng, nhưng vị trí tiếp theo sẽ bị nhảy cóc (1, 1, 3...). Giống như trao huy chương Olympic (có 2 người giải Vàng thì người tiếp theo nhận giải Đồng, không có giải Bạc).
DENSE_RANK(): Nếu bằng điểm thì đồng hạng, nhưng vị trí tiếp theo không bị nhảy cóc (1, 1, 2, 3...). Chữ "Dense" nghĩa là đặc / khít khao.
- Ứng dụng: Tìm Top 3 mức lương cao nhất công ty (nếu 5 người cùng nhận mức lương cao nhất 50 triệu thì họ đều là hạng 1, mức lương 40 triệu tiếp theo vẫn là hạng 2).

4. Tuyệt chiêu điều hướng: LAG() và LEAD()

LAG(column, n): Lấy giá trị của dòng phía trước $n$ bước (Quay ngược về quá khứ).
LEAD(column, n): Lấy giá trị của dòng phía sau $n$ bước (Nhìn về tương lai).

Ứng dụng kinh điển: Tính tỷ lệ tăng trưởng doanh thu so với tháng trước: $\text{Tăng trưởng MoM (\%)} = \frac{\text{Doanh thu tháng này} - \text{LAG(Doanh thu tháng trước)}}{\text{LAG(Doanh thu tháng trước)}} \times 100$

5. Tư duy CTE (Common Table Expressions): Tạm biệt Subquery nhung nhúc

Nhiều người viết SQL thường lồng Subquery vào nhau thành 4–5 tầng khiến code thụt ra thụt vào rối như mớ bòng bong.
Cú pháp CTE (WITH ... AS (...)) cho phép bạn đặt tên cho từng bảng tạm thời gian thực, đọc code tuần tự từ trên xuống dưới hệt như đọc một câu chuyện logic:

WITH data_cleaned AS (
    SELECT ... FROM raw_table WHERE ...
),
ranked_data AS (
    SELECT *, ROW_NUMBER() OVER (...) as rank_num FROM data_cleaned
)
SELECT * FROM ranked_data WHERE rank_num = 1;

6. Thực hành thực chiến: Giải quyết 3 bài toán kinh doanh bằng SQL

Dưới đây là một kịch bản truy vấn phân tích bán hàng hoàn chỉnh kết hợp cả CTE, DENSE_RANK(), LAG(), và hàm tính tổng lũy kế (Running Total):

-- =============================================================================
-- BÀI TOÁN 1: TÍNH TỐC ĐỘ TĂNG TRƯỞNG DOANH THU THEO THÁNG (MOM GROWTH)
-- =============================================================================
WITH monthly_revenue AS (
    -- Bước 1: Tính tổng doanh thu từng tháng
    SELECT 
        DATE_TRUNC('month', order_date) AS sales_month,
        SUM(total_amount) AS revenue
    FROM 
        orders
    WHERE 
        status = 'COMPLETED'
    GROUP BY 
        DATE_TRUNC('month', order_date)
),

revenue_with_lag AS (
    -- Bước 2: Dùng LAG() để kéo doanh thu của tháng trước về cùng 1 dòng
    SELECT 
        sales_month,
        revenue,
        LAG(revenue, 1) OVER (ORDER BY sales_month) AS prev_month_revenue,
        
        -- Bước 3: Tính doanh thu lũy kế dồn tích từ đầu năm đến tháng hiện tại (Running Total)
        SUM(revenue) OVER (
            ORDER BY sales_month 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_running_revenue
    FROM 
        monthly_revenue
)

-- Bước 4: Tính tỷ lệ % tăng trưởng MoM
SELECT 
    TO_CHAR(sales_month, 'YYYY-MM') AS month,
    revenue AS current_revenue,
    prev_month_revenue,
    cumulative_running_revenue,
    ROUND(
        ((revenue - prev_month_revenue) / prev_month_revenue::NUMERIC) * 100, 
        2
    ) AS mom_growth_pct
FROM 
    revenue_with_lag
ORDER BY 
    sales_month;


-- =============================================================================
-- BÀI TOÁN 2: LẤY TOP 2 SẢN PHẨM BÁN CHẠY NHẤT CỦA MỖI DANH MỤC (TOP N PER GROUP)
-- =============================================================================
WITH ranked_products AS (
    SELECT 
        category_name,
        product_name,
        SUM(quantity_sold) AS total_sold,
        
        -- Dùng DENSE_RANK() để xếp hạng sản phẩm trong TỪNG danh mục riêng biệt
        DENSE_RANK() OVER (
            PARTITION BY category_name 
            ORDER BY SUM(quantity_sold) DESC
        ) AS rank_in_category
    FROM 
        fact_sales s
        JOIN dim_product p ON s.product_key = p.product_key
    GROUP BY 
        category_name,
        product_name
)

-- Lọc lấy Top 2 mỗi danh mục (Mệnh đề WHERE chỉ lọc được sau khi CTE đã tính xong RANK)
SELECT 
    category_name,
    rank_in_category,
    product_name,
    total_sold
FROM 
    ranked_products
WHERE 
    rank_in_category <= 2
ORDER BY 
    category_name, 
    rank_in_category;

7. Câu hỏi phỏng vấn thực tế (Interview Q&A)

Câu 1 (Mức độ: Entry / Junior)

Câu hỏi: "Phân biệt sự khác nhau giữa RANK() và DENSE_RANK()? Khi nào bạn bắt buộc phải dùng ROW_NUMBER()?"

Gợi ý trả lời:
*"Cả RANK() và DENSE_RANK() đều xếp các giá trị bằng điểm nhau vào cùng một thứ hạng.
Tuy nhiên, điểm khác biệt là:

RANK() sẽ nhảy cóc thứ hạng tiếp theo (ví dụ: 1, 1, 3) tương ứng với số lượng giá trị trùng lặp.

DENSE_RANK() sẽ giữ thứ hạng liền mạch không nhảy cóc (ví dụ: 1, 1, 2).
Trường hợp bắt buộc dùng ROW_NUMBER(): Khi cần thực hiện bài toán Lọc khử trùng lặp (Deduplication) hoặc phân trang (Pagination). Trong bài toán khử trùng, mỗi bản ghi bắt buộc phải nhận một số thứ tự duy nhất để chúng ta có thể lọc WHERE row_num = 1 nhằm lấy đúng 1 bản ghi duy nhất và loại bỏ các bản ghi trùng lặp còn lại."*

Câu 2 (Mức độ: Junior / Mid-level)

Câu hỏi: "Tại sao chúng ta không thể đặt trực tiếp Window Function vào trong mệnh đề WHERE (ví dụ: WHERE ROW_NUMBER() OVER (...) = 1) mà bắt buộc phải dùng CTE hoặc Subquery?"

Gợi ý trả lời:
"Lý do nằm ở Thứ tự thực thi logic (Logical Execution Order) của SQL Engine (đã học ở [Tech Blog #05]):
Mệnh đề WHERE được thực thi ở Bước 2 để lọc các dòng dữ liệu thô ban đầu, trong khi Window Function được tính toán ở Bước 5 (cùng cấp với SELECT), sau khi dữ liệu đã được gom nhóm và lọc thô.
Tại thời điểm mệnh đề WHERE chạy, cửa sổ dữ liệu chưa hề được khởi tạo và chưa có giá trị xếp hạng để lọc. Do đó, chúng ta bắt buộc phải đưa Window Function vào bên trong một CTE (WITH ... AS) hoặc Subquery để Database tính toán xong thứ hạng trước, sau đó câu truy vấn bên ngoài mới có thể dùng WHERE để lọc theo thứ hạng đó."

8. Key Takeaways (Ghi nhớ nhanh)

Window Function không làm co dữ liệu: Nó tính toán tổng hợp trên từng nhóm (PARTITION BY) nhưng vẫn giữ nguyên từng dòng bản ghi ban đầu.
Quy tắc 3 hàm xếp hạng:
- ROW_NUMBER: Đánh số 1, 2, 3, 4 (Dùng để khử trùng dữ liệu).
- RANK: Đồng hạng nhưng nhảy cóc 1, 1, 3 (Xếp giải thi đấu).
- DENSE_RANK: Đồng hạng nhưng liền mạch 1, 1, 2 (Top N kinh doanh, bảng lương).
LAG() là chìa khóa so sánh kỳ trước: Dùng ngay LAG(metric, 1) để tính tăng trưởng theo tháng (MoM) hoặc theo năm (YoY).
Viết SQL chuẩn chuyên nghiệp bằng CTE: Luôn thay thế các Subquery lồng nhau bằng cú pháp WITH ... AS để code sáng sủa, dễ đọc và dễ bảo trì.