Window Functions Trong SQL: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

1. Window Functions Là Gì? Khác GROUP BY Ở Đâu? #

Window Functions thực hiện phép tính trên một tập hợp dòng (window) liên quan đến dòng hiện tại, mà KHÔNG gom dòng lại như GROUP BY. Nghĩa là bạn vẫn giữ nguyên chi tiết từng dòng, đồng thời có thêm cột tính toán (ranking, running total, so sánh với dòng trước).

Đây là tính năng mạnh nhất của SQL analytics. Nếu GROUP BY trả lời câu hỏi "Tổng doanh thu theo tháng là bao nhiêu?", Window Functions trả lời "Mỗi đơn hàng chiếm bao nhiêu % doanh thu tháng đó?" — chi tiết hơn rất nhiều.

2. ROW_NUMBER — Đánh Số Thứ Tự #

ROW_NUMBER() gán số thứ tự 1, 2, 3... cho mỗi dòng trong partition. Không bao giờ trùng số. Ứng dụng: tìm dòng mới nhất per group (top-1 per partition), phân trang, xóa duplicate.

-- Tìm đơn hàng gần nhất của mỗi khách hàng
WITH ranked AS (
    SELECT *, 
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date DESC
        ) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

3. RANK Và DENSE_RANK — Xếp Hạng #

RANK() và DENSE_RANK() xếp hạng nhưng xử lý tie khác nhau. RANK(): 1,2,2,4 (bỏ hạng 3). DENSE_RANK(): 1,2,2,3 (không bỏ hạng). Dùng khi muốn top-N nhưng cho phép tie — ví dụ top 3 sản phẩm bán chạy nhất.

SELECT 
    product_name,
    SUM(quantity) AS total_sold,
    RANK() OVER (ORDER BY SUM(quantity) DESC) AS rank_with_gap,
    DENSE_RANK() OVER (ORDER BY SUM(quantity) DESC) AS rank_no_gap
FROM order_items
GROUP BY product_name;

4. LAG Và LEAD — So Sánh Với Dòng Trước/Sau #

LAG(col, n) lấy giá trị dòng trước n bước. LEAD(col, n) lấy giá trị dòng sau n bước. Cực kỳ hữu ích để tính growth rate, so sánh tháng này vs tháng trước, tìm gaps trong dữ liệu.

-- Tính growth rate tháng này so với tháng trước
SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) 
        / LAG(revenue) OVER (ORDER BY month) * 100, 1
    ) AS growth_pct
FROM monthly_sales;

5. NTILE — Phân Nhóm Đều #

NTILE(n) chia kết quả thành n nhóm bằng nhau. Rất hữu ích cho phân tích percentile: NTILE(4) = quartiles, NTILE(10) = deciles. Ứng dụng: phân khúc khách hàng theo spending, phân nhóm điểm thi.

-- Phân khúc khách hàng thành 4 nhóm theo spending
SELECT 
    customer_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
    -- 1 = top 25% (VIP), 4 = bottom 25%
FROM customer_summary;

6. SUM/AVG OVER — Running Total Và Moving Average #

Kết hợp aggregate functions với OVER + frame clause để tính running total, moving average. Frame clause (ROWS BETWEEN) định nghĩa khoảng tính toán.

SELECT 
    order_date,
    amount,
    -- Running total: cộng dồn từ đầu
    SUM(amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    -- Moving average 7 ngày
    AVG(amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7day
FROM daily_sales;

7. FIRST_VALUE, LAST_VALUE, NTH_VALUE #

Lấy giá trị đầu tiên, cuối cùng, hoặc thứ N trong window. FIRST_VALUE(col) OVER (PARTITION BY ... ORDER BY ...) lấy giá trị top-1 mà không cần subquery. Lưu ý: LAST_VALUE cần frame clause UNBOUNDED FOLLOWING.

8. Kết Luận #

Window Functions là skill SQL quan trọng nhất cho Data Analyst. Thành thạo ROW_NUMBER (top-N per group), LAG/LEAD (comparisons), SUM/AVG OVER (running calculations) là đủ để giải quyết 80% bài toán analytics phức tạp. Luôn dùng CTE (WITH) để code dễ đọc.