Bài 6 — SQL nâng cao — Window Functions & CTE

Mở đầu — vì sao bài này quan trọng

Ở Bài 5, bạn đã nắm vững bộ ba nền tảng SELECT, JOIN, GROUP BY — đủ để trả lời phần lớn câu hỏi "có bao nhiêu", "tổng bằng bao nhiêu", "nhóm theo cái gì". Nhưng trong thực tế công việc của một Technical BA, bạn sẽ liên tục gặp những câu hỏi mà GROUP BY thuần túy không trả lời nổi:

"Cho tôi xem top 3 sản phẩm bán chạy nhất trong mỗi danh mục" — không phải top 3 toàn hệ thống.
"Tỷ lệ hủy đơn tháng này so với tháng trước thay đổi bao nhiêu phần trăm?"
"Doanh thu lũy kế (cộng dồn) theo từng ngày trong tháng để vẽ biểu đồ tăng trưởng."
"Khách hàng này là lần mua thứ mấy trong lịch sử của họ?"

Đây chính là ranh giới phân tách một BA biết viết SQL cơ bản với một Technical BA thực thụ. Khi bạn ngồi họp với team Data hoặc Backend và nói "tôi cần chỉ số xếp hạng trong từng nhóm, dùng ROW_NUMBER() partition theo category là ra", bạn lập tức được nhìn nhận khác — bạn nói cùng ngôn ngữ với họ, và quan trọng hơn, bạn tự kiểm chứng được số liệu thay vì chờ đợi và phụ thuộc.

Bài 6 tập trung vào hai công cụ mạnh nhất của SQL phân tích: CTE (Common Table Expression) giúp bạn viết truy vấn phức tạp một cách rõ ràng, đọc được; và Window Functions (hàm cửa sổ) giúp bạn tính toán xếp hạng, so sánh, cộng dồn mà không làm mất chi tiết từng dòng. Đây là hai kỹ năng xuất hiện gần như chắc chắn trong mọi bài kiểm tra SQL khi phỏng vấn vị trí Data BA hoặc Analytics BA tại các công ty như Tiki, Shopee, MoMo, VNG hay các ngân hàng số.

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

CTE — Common Table Expression

CTE là một "bảng tạm có tên" mà bạn định nghĩa ngay đầu câu truy vấn bằng từ khóa WITH, rồi dùng lại nó ở phần SELECT chính phía dưới. Hãy hình dung nó như việc bạn đặt một biến trung gian trong lập trình thay vì nhồi nhét mọi thứ vào một dòng dài ngoằng.

-- Tỷ lệ hủy đơn theo từng tháng năm 2026
WITH monthly AS (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') AS thang,
        COUNT(*) AS tong_don,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS don_huy
    FROM orders
    WHERE created_at >= '2026-01-01'
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
)
SELECT
    thang,
    tong_don,
    don_huy,
    ROUND(don_huy * 100.0 / tong_don, 2) AS ty_le_huy_pct
FROM monthly
ORDER BY thang;

Nếu không có CTE, bạn buộc phải lồng (nest) một subquery vào trong câu lệnh chính, đọc rất rối. CTE tách phần "chuẩn bị dữ liệu" (đếm đơn, đếm đơn hủy theo tháng) ra khỏi phần "tính toán cuối" (tính tỷ lệ phần trăm). Khi đồng nghiệp hoặc chính bạn ba tháng sau đọc lại, logic hiện ra rõ ràng từng tầng.

Một CTE có thể có nhiều khối, nối nhau bằng dấu phẩy, và khối sau dùng được kết quả của khối trước:

WITH doanh_thu_ngay AS (
    SELECT DATE(created_at) AS ngay, SUM(total_amount) AS doanh_thu
    FROM orders WHERE status = 'completed'
    GROUP BY DATE(created_at)
),
trung_binh AS (
    SELECT AVG(doanh_thu) AS dt_tb FROM doanh_thu_ngay
)
SELECT d.ngay, d.doanh_thu, t.dt_tb,
       d.doanh_thu - t.dt_tb AS chenh_lech_so_voi_tb
FROM doanh_thu_ngay d CROSS JOIN trung_binh t
ORDER BY d.ngay;

Window Functions — hàm cửa sổ

Đây là điểm khác biệt then chốt so với GROUP BY. Khi bạn dùng GROUP BY, mỗi nhóm bị gộp lại thành một dòng — bạn mất chi tiết từng giao dịch. Window Functions thì ngược lại: nó tính toán trên một "cửa sổ" các dòng liên quan nhưng vẫn giữ nguyên từng dòng gốc.

Cấu trúc chung:

HAM_CUA_SO() OVER (
    PARTITION BY <cot_chia_nhom>
    ORDER BY <cot_sap_xep>
)

PARTITION BY — chia dữ liệu thành các nhóm (giống GROUP BY nhưng không gộp dòng). Nếu bỏ qua, cả bảng là một cửa sổ.
ORDER BY — sắp xếp trong từng nhóm, quyết định thứ tự cho các hàm xếp hạng và cộng dồn.

Nhóm hàm xếp hạng:

ROW_NUMBER() — đánh số thứ tự 1, 2, 3, 4... không trùng, kể cả khi giá trị bằng nhau.
RANK() — xếp hạng có nhảy cóc: hai hạng nhất đồng điểm thì cả hai là 1, người tiếp theo là 3.
DENSE_RANK() — xếp hạng không nhảy cóc: hai hạng nhất là 1, người tiếp theo là 2.

-- Top 3 sản phẩm bán chạy nhất TRONG MỖI danh mục
WITH ban_hang AS (
    SELECT category_id, product_id, SUM(quantity) AS so_luong_ban
    FROM order_items GROUP BY category_id, product_id
),
xep_hang AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY category_id
            ORDER BY so_luong_ban DESC
        ) AS hang
    FROM ban_hang
)
SELECT * FROM xep_hang WHERE hang <= 3;

Nhóm hàm offset (so sánh dòng kề):

LAG(cot, n) — lấy giá trị của dòng trước đó n vị trí.
LEAD(cot, n) — lấy giá trị của dòng kế sau n vị trí.

Đây là cách bạn so sánh "tháng này với tháng trước" mà không cần tự JOIN bảng với chính nó:

WITH dt_thang AS (
    SELECT DATE_FORMAT(created_at, '%Y-%m') AS thang,
           SUM(total_amount) AS doanh_thu
    FROM orders WHERE status = 'completed'
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
)
SELECT thang, doanh_thu,
    LAG(doanh_thu) OVER (ORDER BY thang) AS dt_thang_truoc,
    ROUND(
        (doanh_thu - LAG(doanh_thu) OVER (ORDER BY thang))
        * 100.0 / LAG(doanh_thu) OVER (ORDER BY thang), 2
    ) AS tang_truong_pct
FROM dt_thang;

Nhóm hàm tổng hợp trượt: SUM(), AVG(), COUNT() cũng dùng được với OVER() để tính lũy kế (running total) hoặc trung bình trượt (moving average):

SELECT ngay, doanh_thu,
    SUM(doanh_thu) OVER (ORDER BY ngay) AS doanh_thu_luy_ke
FROM doanh_thu_ngay;

Tình huống thực tế

Tình huống 1 — Tiki: tìm top sản phẩm trong từng ngành hàng

Một sàn thương mại điện tử kiểu Tiki có hàng nghìn danh mục: Sách, Điện thoại, Gia dụng, Thời trang... Team Category Management yêu cầu BA cung cấp top 5 sản phẩm bán chạy nhất của từng ngành hàng trong quý vừa rồi để quyết định ngân sách quảng cáo.

Một BA chưa biết Window Functions thường khổ sở: viết một câu lấy top toàn hệ thống, rồi lọc thủ công từng danh mục trên Excel — mất cả buổi và dễ sai. BA giỏi chỉ cần ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY so_luong_ban DESC) rồi lọc hang <= 5. Một câu truy vấn, chạy trong vài giây, ra đúng top 5 cho cả 3.000 danh mục cùng lúc.

Bài học: khi nghe thấy cụm từ "top N trong mỗi nhóm", phản xạ đầu tiên phải là Window Function partition theo nhóm đó. Đây là mẫu câu hỏi (pattern) xuất hiện dày đặc trong phân tích bán lẻ.

Tình huống 2 — MoMo: phát hiện giao dịch lặp đáng ngờ

Đội Risk của một ví điện tử như MoMo cần phát hiện hành vi bất thường: cùng một người dùng thực hiện nhiều giao dịch trong khoảng thời gian quá ngắn — dấu hiệu của bot hoặc gian lận. Câu hỏi nghiệp vụ: "Với mỗi giao dịch, khoảng cách thời gian tới giao dịch liền trước của chính người đó là bao lâu? Nếu dưới 5 giây, gắn cờ nghi vấn."

Đây là bài toán kinh điển cho LAG:

WITH gd AS (
    SELECT user_id, txn_id, created_at,
        LAG(created_at) OVER (
            PARTITION BY user_id ORDER BY created_at
        ) AS gd_truoc
    FROM transactions
    WHERE DATE(created_at) = '2026-06-26'
)
SELECT user_id, txn_id, created_at,
    TIMESTAMPDIFF(SECOND, gd_truoc, created_at) AS khoang_cach_giay
FROM gd
WHERE TIMESTAMPDIFF(SECOND, gd_truoc, created_at) < 5;

PARTITION BY user_id đảm bảo việc so sánh chỉ diễn ra trong phạm vi từng người dùng — giao dịch của người A không bị so nhầm với người B. Kết quả này được BA đưa vào spec để team Backend dựng quy tắc real-time cảnh báo.

Bài học: PARTITION BY là chìa khóa để mỗi nhóm được tính toán độc lập. Quên mất PARTITION BY là lỗi nghiệp vụ nghiêm trọng nhất — số liệu vẫn "ra" nhưng sai hoàn toàn.

Tình huống 3 — Chuỗi F&B The Coffee House: doanh thu lũy kế và trung bình trượt

Phòng Tài chính của một chuỗi cà phê muốn dashboard hiển thị doanh thu cộng dồn từ đầu tháng (để biết đã đạt bao nhiêu phần trăm mục tiêu) và trung bình trượt 7 ngày (để làm mượt biến động cuối tuần, nhìn ra xu hướng thật).

WITH dt_ngay AS (
    SELECT DATE(created_at) AS ngay, SUM(total_amount) AS doanh_thu
    FROM orders
    WHERE created_at >= '2026-06-01' AND status = 'completed'
    GROUP BY DATE(created_at)
)
SELECT ngay, doanh_thu,
    SUM(doanh_thu) OVER (ORDER BY ngay) AS luy_ke_thang,
    ROUND(AVG(doanh_thu) OVER (
        ORDER BY ngay
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 0) AS tb_truot_7ngay
FROM dt_ngay
ORDER BY ngay;

Mệnh đề ROWS BETWEEN 6 PRECEDING AND CURRENT ROW định nghĩa cửa sổ trượt gồm dòng hiện tại và 6 dòng trước — đúng 7 ngày. Đây là frame (khung cửa sổ) mà nhiều BA chưa biết, nhưng nó cực kỳ hữu ích cho mọi báo cáo xu hướng.

Bài học: Window Functions không chỉ để xếp hạng. Khả năng cộng dồn và tính trung bình trượt biến SQL thành công cụ vẽ biểu đồ tăng trưởng mà không cần xuất sang Excel.

Hướng dẫn từng bước

Khi đứng trước một yêu cầu phân tích phức tạp, hãy theo quy trình sau:

Đọc kỹ yêu cầu nghiệp vụ và gạch chân từ khóa. "Trong mỗi" → PARTITION BY. "Top N" → hàm xếp hạng. "So với kỳ trước" → LAG/LEAD. "Lũy kế / cộng dồn" → SUM() OVER (ORDER BY...).

Tách dữ liệu thô thành các tầng bằng CTE. Tầng đầu thường là gom nhóm cơ bản (GROUP BY). Đừng cố viết tất cả trong một câu — chia nhỏ giúp bạn debug từng phần.

Chạy thử từng CTE riêng lẻ. Tạm thời thay phần SELECT chính bằng SELECT * FROM ten_cte để kiểm tra mỗi tầng cho ra đúng dữ liệu mong đợi trước khi ghép.

Áp Window Function vào tầng dữ liệu đã sạch. Xác định rõ ba thành phần: hàm nào, PARTITION BY cột nào, ORDER BY cột nào.

Lọc trên cột Window ở tầng ngoài. Quan trọng: bạn không thể dùng WHERE hang <= 3 ngay trong cùng câu lệnh tạo ra hang, vì WHERE chạy trước khi Window Function được tính. Phải bọc thêm một CTE/subquery rồi mới lọc.

Kiểm chứng số tổng. So sánh tổng doanh thu trong kết quả với một câu SUM đơn giản độc lập. Nếu lệch, bạn đã nhân đôi dòng ở đâu đó (thường do JOIN).

Lỗi thường gặp & mẹo

Nhầm Window Function với GROUP BY. Nhớ: GROUP BY gộp dòng, Window giữ nguyên dòng. Nếu bạn cần cả chi tiết từng giao dịch lẫn một con số tổng hợp đứng cạnh nó, đó là Window Function.

Dùng WHERE để lọc cột xếp hạng cùng tầng. Đây là lỗi số một. WHERE ROW_NUMBER() OVER(...) <= 3 sẽ báo lỗi cú pháp. Luôn bọc Window Function trong một CTE rồi lọc ở tầng ngoài.

Quên PARTITION BY. Khi bỏ quên, hàm chạy trên toàn bảng thay vì từng nhóm. Truy vấn vẫn chạy, số vẫn ra, nhưng sai về mặt nghiệp vụ — loại lỗi nguy hiểm nhất vì không có thông báo lỗi.

ROW_NUMBER vs RANK vs DENSE_RANK. Khi có giá trị đồng hạng (ví dụ hai sản phẩm bán bằng nhau), ba hàm cho kết quả khác nhau. Hỏi rõ nghiệp vụ: "trường hợp hòa nhau thì xử lý thế nào?" trước khi chọn.

NULL từ LAG/LEAD ở dòng đầu/cuối. Dòng đầu tiên không có "dòng trước" nên LAG trả về NULL, kéo theo phép chia ra NULL. Dùng COALESCE hoặc lọc bỏ tùy ngữ cảnh, và luôn ghi rõ cách xử lý trong spec.

Mẹo cho BA: bạn không cần là người viết câu SQL cuối cùng đưa vào production. Nhưng khi bạn đọc hiểu và mô tả được logic này trong tài liệu yêu cầu — "chỉ số xếp hạng partition theo chi nhánh, sắp xếp theo doanh thu giảm dần" — bạn giúp Dev hiểu chính xác ý mình, giảm vòng lặp làm đi làm lại.

Bài tập thực hành

Giả sử bạn có bảng orders(order_id, customer_id, branch_id, total_amount, status, created_at). Hãy viết truy vấn cho từng yêu cầu:

CTE cơ bản: Tính tỷ lệ đơn hoàn thành (completed) trên tổng số đơn của từng chi nhánh trong tháng 6/2026.

Xếp hạng trong nhóm: Tìm top 3 khách hàng chi tiêu nhiều nhất trong mỗi chi nhánh. Suy nghĩ: nên dùng ROW_NUMBER hay DENSE_RANK nếu có hai khách hàng chi tiêu bằng nhau?

So sánh kỳ: Tính doanh thu từng tháng và phần trăm tăng trưởng so với tháng liền trước, dùng LAG.

Lũy kế: Với một chi nhánh bất kỳ, tính doanh thu cộng dồn theo ngày trong tháng 6.

Thử thách: Đánh số "đây là đơn hàng thứ mấy" của mỗi khách hàng theo thời gian (ROW_NUMBER partition theo customer_id), rồi lọc ra các khách hàng đặt đơn đầu tiên trong tháng 6 (đơn số 1) — đây chính là tập khách hàng mới.

Hãy chạy thử trên một cơ sở dữ liệu thật (MySQL 8+ hoặc PostgreSQL đều hỗ trợ Window Functions). Tự kiểm chứng bằng cách so tổng số liệu với câu GROUP BY đơn giản.

Tóm tắt

CTE và Window Functions là hai công cụ nâng bạn từ "BA biết viết SQL" lên "Technical BA tự phân tích được". CTE (WITH ... AS) giúp bạn chia truy vấn phức tạp thành các tầng rõ ràng, dễ đọc, dễ debug. Window Functions (... OVER (PARTITION BY ... ORDER BY ...)) cho phép tính xếp hạng, so sánh dòng kề, cộng dồn và trung bình trượt mà vẫn giữ nguyên chi tiết từng dòng — điều GROUP BY không làm được.

Ba phản xạ cần khắc cốt ghi tâm: "top N trong mỗi nhóm" → ROW_NUMBER partition; "so với kỳ trước" → LAG/LEAD; "lũy kế" → SUM() OVER. Luôn nhớ bọc Window Function trong CTE trước khi lọc, và đừng bao giờ quên PARTITION BY. Khi thành thạo, bạn không chỉ trả lời được những câu hỏi phân tích khó nhất mà còn viết được spec chính xác để team Data và Backend hiểu ngay ý mình — đó là giá trị cốt lõi của một Technical BA.