Window Functions: nền tảng của SQL phân tích

Nếu chỉ được học một nhóm tính năng SQL để làm phân tích dữ liệu nghiêm túc, thì đó là window function (hàm cửa sổ). Xếp hạng khách hàng, so sánh giao dịch với kỳ trước, tính số dư luỹ kế, chia nhóm phần trăm — tất cả đều gọn gàng chỉ với một mệnh đề OVER. Bài mở đầu series "SQL phân tích nâng cao" này dựng nền tảng: bạn sẽ hiểu window khác GROUP BY ở đâu, nắm chắc từng họ hàm, và tránh những cái bẫy khiến kết quả sai âm thầm.

Vì sao cần window function

Hãy bắt đầu bằng một yêu cầu nghiệp vụ quen thuộc ở NCB: "Với mỗi khách hàng, cho tôi biết số dư tài khoản của họ và đồng thời số dư trung bình của cả thành phố họ ở, để so sánh."

Với GROUP BY, bạn buộc phải chọn: hoặc giữ từng dòng chi tiết (mất giá trị trung bình theo nhóm), hoặc gom nhóm (mất chi tiết từng khách). Muốn có cả hai, kiểu cũ phải tự JOIN bảng với một truy vấn con đã GROUP BY — dài dòng và dễ sai khoá join. Window function giải quyết trọn vẹn: nó tính trên một "cửa sổ" các dòng liên quan nhưng KHÔNG gộp các dòng lại. Mỗi dòng đầu vào vẫn cho ra đúng một dòng đầu ra, kèm thêm cột giá trị tính theo nhóm.

Điểm mấu chốt: GROUP BY giảm số dòng (fold), window mở rộng thông tin trên số dòng nguyên vẹn (annotate).

Cú pháp OVER

Một window function luôn có dạng:

func(...) OVER (
    PARTITION BY <cột chia nhóm>
    ORDER BY    <cột sắp xếp>
    <frame: ROWS/RANGE BETWEEN ... AND ...>
)

Ba thành phần trong ngoặc OVER đều tùy chọn nhưng ý nghĩa rất khác nhau:

• PARTITION BY chia dữ liệu thành các phân vùng độc lập; hàm tính lại từ đầu cho mỗi phân vùng. Bỏ trống nghĩa là toàn bộ bảng là một cửa sổ.
• ORDER BY sắp xếp các dòng bên trong mỗi phân vùng. Nó quyết định thứ tự cho ranking và cho các hàm offset (LAG/LEAD), đồng thời kích hoạt frame ngầm định.
• frame (ROWS/RANGE) giới hạn chính xác dòng nào được đưa vào tính toán — sẽ bàn kỹ ở SQL nâng cao 2 — Frames & running totals; bài này chỉ chạm tới phần cần thiết.

Ví dụ đơn giản nhất — không PARTITION BY, không ORDER BY — cửa sổ là toàn bảng:

-- ▶ Chạy được
SELECT account_no, balance,
       SUM(balance) OVER () AS total_balance,
       ROUND((balance / SUM(balance) OVER ())::numeric, 4) AS share
FROM accounts;

Mỗi dòng vẫn là một tài khoản, nhưng có thêm tổng số dư toàn hệ thống và tỉ trọng của tài khoản đó. Đây chính là thứ GROUP BY không làm được trong một bước.

Ba họ hàm cửa sổ

Window function chia thành ba nhóm theo mục đích:

Nhóm 1 — Ranking

Đây là nhóm được dùng nhiều nhất. Hãy xếp hạng khách hàng theo số dư trong từng thành phố:

-- ▶ Chạy được
SELECT c.city, c.full_name, a.balance,
       ROW_NUMBER() OVER (PARTITION BY c.city ORDER BY a.balance DESC) AS row_num,
       RANK()       OVER (PARTITION BY c.city ORDER BY a.balance DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY c.city ORDER BY a.balance DESC) AS dense_rnk
FROM customers c
JOIN accounts a ON a.customer_id = c.id
ORDER BY c.city, a.balance DESC;

Sự khác biệt giữa ba hàm chỉ lộ ra khi có giá trị bằng nhau (ties):

• ROW_NUMBER đánh số 1, 2, 3, 4... duy nhất, không bao giờ trùng. Hai dòng cùng số dư vẫn nhận hai số khác nhau (thứ tự giữa chúng là không xác định trừ khi ORDER BY thêm tie-breaker).
• RANK cho hai dòng bằng nhau cùng hạng, rồi nhảy cóc: 1, 2, 2, 4. Bỏ trống hạng 3.
• DENSE_RANK cũng cho cùng hạng khi bằng nhau nhưng không nhảy cóc: 1, 2, 2, 3.

Quy tắc chọn: cần đúng một dòng đại diện mỗi nhóm (ví dụ lấy top-1 giao dịch) → ROW_NUMBER. Làm bảng xếp hạng có thứ hạng thật (huy chương, "top 3 theo doanh số") → RANK hoặc DENSE_RANK tùy bạn muốn giữ hay bỏ khoảng trống hạng.

NTILE(n) chia các dòng đã sắp xếp thành n nhóm gần bằng nhau — công cụ phân khúc kinh điển. Chia khách thành 4 nhóm tứ phân vị theo số dư:

-- ▶ Chạy được
SELECT c.full_name, a.balance,
       NTILE(4) OVER (ORDER BY a.balance DESC) AS quartile
FROM customers c
JOIN accounts a ON a.customer_id = c.id
ORDER BY a.balance DESC;

Nhóm quartile = 1 là 25% khách có số dư cao nhất — ứng viên VIP. Nếu tổng số dòng không chia hết cho n, các nhóm đầu nhận nhiều hơn một dòng.

PERCENT_RANK và CUME_DIST trả về vị trí tương đối dạng 0..1 — hữu ích khi so sánh phân phối mà không phụ thuộc số lượng dòng. Nhớ ép ::numeric trước khi ROUND vì chúng trả về double precision:

-- ▶ Chạy được
SELECT c.full_name, a.balance,
       ROUND(PERCENT_RANK() OVER (ORDER BY a.balance)::numeric, 3) AS pct_rank,
       ROUND(CUME_DIST()    OVER (ORDER BY a.balance)::numeric, 3) AS cume_dist
FROM customers c
JOIN accounts a ON a.customer_id = c.id
ORDER BY a.balance;

PERCENT_RANK = (hạng − 1) / (số dòng − 1); dòng nhỏ nhất luôn bằng 0. CUME_DIST = tỉ lệ dòng có giá trị ≤ dòng hiện tại; luôn > 0. Muốn nói "khách này giàu hơn 90% khách còn lại", CUME_DIST là thứ bạn cần.

Nhóm 2 — Aggregate làm window

Mọi hàm tổng hợp quen thuộc (SUM, AVG, COUNT, MIN, MAX) đều có thể gắn OVER (...) để trở thành window. Đây là cầu nối tự nhiên nhất từ GROUP BY. Quay lại yêu cầu ở đầu bài — số dư của khách kèm trung bình thành phố:

-- ▶ Chạy được
SELECT c.city, c.full_name, a.balance,
       ROUND(AVG(a.balance) OVER (PARTITION BY c.city)::numeric, 2) AS city_avg,
       ROUND((a.balance - AVG(a.balance) OVER (PARTITION BY c.city))::numeric, 2) AS diff_from_avg,
       COUNT(*) OVER (PARTITION BY c.city) AS cust_in_city
FROM customers c
JOIN accounts a ON a.customer_id = c.id
ORDER BY c.city, a.balance DESC;

Chỉ một truy vấn, không JOIN phụ, mỗi dòng khách vẫn giữ nguyên và có thêm ba cột ngữ cảnh nhóm. Lưu ý: AVG trả về double precision, nên phải ép ::numeric trước khi ROUND hai tham số — đây là lỗi phổ biến khi chuyển từ MySQL sang PostgreSQL.

Cảnh báo quan trọng: khi aggregate-as-window có ORDER BY, ý nghĩa thay đổi hoàn toàn — nó trở thành running total (tổng luỹ kế) chứ không còn là tổng cả nhóm, vì frame ngầm định lúc đó là "từ đầu phân vùng đến dòng hiện tại". Chi tiết ở SQL nâng cao 2. Trong bài này, các ví dụ aggregate cố ý không thêm ORDER BY để giữ nghĩa "tổng/trung bình cả phân vùng".

Nhóm 3 — Offset và định vị

Nhóm này truy cập dòng khác trong cùng phân vùng — nền tảng của phân tích chuỗi thời gian.

LAG(col, n) lấy giá trị của dòng lùi n bước (mặc định 1); LEAD nhìn tới trước. So sánh mỗi giao dịch với giao dịch liền trước của cùng tài khoản:

-- ▶ Chạy được
SELECT t.account_id, t.created_at, t.amount,
       LAG(t.amount) OVER (PARTITION BY t.account_id ORDER BY t.created_at) AS prev_amount,
       t.amount - LAG(t.amount) OVER (PARTITION BY t.account_id ORDER BY t.created_at) AS delta
FROM transactions t
ORDER BY t.account_id, t.created_at;

Giao dịch đầu tiên của mỗi tài khoản có prev_amount = NULL (không có dòng trước) → delta cũng NULL. Muốn thay bằng 0, dùng tham số mặc định: LAG(t.amount, 1, 0).

FIRST_VALUE, LAST_VALUE, NTH_VALUE lấy giá trị tại vị trí cố định trong cửa sổ. Đây là nơi cạm bẫy frame xuất hiện rõ nhất. Với ORDER BY, frame ngầm định chỉ chạy tới dòng hiện tại (RANGE UNBOUNDED PRECEDING AND CURRENT ROW), nên LAST_VALUE sẽ trả về... chính dòng hiện tại chứ không phải dòng cuối phân vùng — sai với trực giác. Phải khai báo frame đầy đủ:

-- ▶ Chạy được
SELECT t.account_id, t.created_at, t.amount,
       FIRST_VALUE(t.amount) OVER w AS first_amt,
       LAST_VALUE(t.amount)  OVER w AS last_amt
FROM transactions t
WINDOW w AS (
    PARTITION BY t.account_id
    ORDER BY t.created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY t.account_id, t.created_at;

Truy vấn trên cũng minh hoạ WINDOW clause đặt tên: định nghĩa cửa sổ một lần với WINDOW w AS (...) rồi tái dùng qua OVER w. Khi một truy vấn có nhiều window function chung một định nghĩa, cách này giảm lặp lại và tránh sai lệch giữa các bản sao.

Lấy "top-N mỗi nhóm" — mẫu kinh điển

Yêu cầu "top giao dịch của mỗi tài khoản" gần như không thể làm gọn bằng GROUP BY thuần, nhưng là một dòng với ROW_NUMBER. Vì không thể dùng window function trực tiếp trong WHERE (nó được tính sau WHERE), ta bọc trong một CTE hoặc subquery rồi lọc ở ngoài:

-- ▶ Chạy được
WITH ranked AS (
    SELECT t.account_id, t.amount, t.created_at,
           ROW_NUMBER() OVER (PARTITION BY t.account_id ORDER BY t.amount DESC) AS rn
    FROM transactions t
)
SELECT account_id, amount, created_at
FROM ranked
WHERE rn = 1
ORDER BY amount DESC;

Muốn top-3 mỗi tài khoản, đổi thành WHERE rn <= 3. Đây là lý do phải hiểu thứ tự thực thi logic: FROM → WHERE → GROUP BY → HAVING → SELECT (window ở đây) → ORDER BY. Window function chạy ở bước SELECT, sau khi lọc và gộp xong, nên không thể tham chiếu trong WHERE của cùng cấp truy vấn.

Cạm bẫy thường gặp

• LAST_VALUE sai vì frame — như đã nói, luôn khai báo ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING nếu muốn giá trị cuối cùng thật sự. Đây là lỗi số một với người mới.
• Quên tie-breaker cho ROW_NUMBER — nếu ORDER BY không phân định hết, thứ tự giữa các dòng bằng nhau là không xác định và có thể đổi giữa các lần chạy. Thêm cột phụ (ví dụ ... ORDER BY a.balance DESC, a.id) để kết quả ổn định.
• NULL trong ORDER BY — PostgreSQL mặc định NULLS LAST khi DESC và NULLS FIRST khi ASC. Điều này ảnh hưởng cả ranking lẫn LAG/LEAD. Nêu rõ NULLS FIRST/LAST khi cần chắc chắn.
• Nhầm nghĩa aggregate khi thêm ORDER BY — SUM(x) OVER (PARTITION BY g) là tổng cả nhóm; thêm ORDER BY biến nó thành luỹ kế. Nếu vô tình thêm, con số sẽ khác hoàn toàn mà không báo lỗi.
• Hiệu năng — mỗi cụm PARTITION BY/ORDER BY khác nhau có thể buộc thêm một bước sort. Dùng chung một định nghĩa window (qua WINDOW clause) giúp planner tái dùng sort. Index khớp với cột partition/order đôi khi giúp tránh sort — xem thêm pg-01-architecture về cách Postgres thực thi. Với bảng giao dịch lớn, cân nhắc lọc bớt bằng WHERE trước khi áp window.
• Không lọc được window trong WHERE — luôn cần lớp bọc CTE/subquery như mẫu top-N ở trên.

Use case thực tế

Bối cảnh: Khối Khách hàng cá nhân NCB muốn dựng bảng xếp hạng khách VIP theo địa bàn để chi nhánh mỗi thành phố biết ai là khách quan trọng nhất của mình, đồng thời phát hiện giao dịch lớn bất thường của từng tài khoản phục vụ giám sát.

Bước 1 — Xếp hạng khách theo tổng số dư trong từng thành phố. Một khách có thể có nhiều tài khoản, nên trước hết gộp tổng số dư mỗi khách (SUM(balance) với GROUP BY), rồi xếp hạng bằng window RANK theo phân vùng thành phố:

-- ▶ Chạy được
WITH cust_bal AS (
    SELECT c.city, c.id, c.full_name, SUM(a.balance) AS total_bal
    FROM customers c
    JOIN accounts a ON a.customer_id = c.id
    GROUP BY c.city, c.id, c.full_name
)
SELECT city, full_name, total_bal,
       RANK() OVER (PARTITION BY city ORDER BY total_bal DESC) AS city_rank,
       ROUND((total_bal
              / SUM(total_bal) OVER (PARTITION BY city))::numeric, 4) AS city_share
FROM cust_bal
ORDER BY city, city_rank;

Truy vấn kết hợp cả hai thế giới: GROUP BY gộp số dư nhiều tài khoản về mỗi khách, rồi window RANK và SUM OVER chú thích thứ hạng và tỉ trọng trong thành phố — trên đúng những dòng đã gộp. Kết quả cho phép chi nhánh Hà Nội, TP.HCM... mỗi nơi lọc city_rank <= 10 để có danh sách top-10 VIP của riêng mình, kèm city_share cho biết khách đó chiếm bao nhiêu phần trăm số dư địa bàn.

Diễn giải: giả sử tại một thành phố, khách hạng 1 có city_share = 0.35 — tức một mình họ nắm 35% số dư của cả địa bàn. Đây là tín hiệu tập trung rủi ro: nếu khách này rời đi, chi nhánh mất hơn một phần ba nguồn vốn huy động cá nhân. Con số này chỉ hiện ra khi giữ được chi tiết từng khách và ngữ cảnh nhóm cùng lúc — đúng thế mạnh của window.

Bước 2 — Phát hiện giao dịch đỉnh của mỗi tài khoản. Dùng mẫu top-N với ROW_NUMBER để lấy giao dịch lớn nhất từng tài khoản, kèm JOIN lấy tên khách:

-- ▶ Chạy được
WITH ranked AS (
    SELECT t.account_id, t.amount, t.created_at,
           ROW_NUMBER() OVER (PARTITION BY t.account_id ORDER BY t.amount DESC) AS rn
    FROM transactions t
)
SELECT c.full_name, a.account_no, r.amount, r.created_at
FROM ranked r
JOIN accounts a  ON a.id = r.account_id
JOIN customers c ON c.id = a.customer_id
WHERE r.rn = 1
ORDER BY r.amount DESC;

Danh sách trả về mỗi tài khoản đúng một dòng — giao dịch có giá trị lớn nhất của nó — kèm tên chủ tài khoản (nhờ JOIN qua accounts.customer_id → customers.id, vì transactions/accounts không lưu tên khách). Đội giám sát sắp xếp giảm dần theo amount để soi những giao dịch bất thường nhất toàn hệ thống, làm đầu vào cho quy trình cảnh báo AML. Cùng bộ dữ liệu, đổi rn = 1 thành rn <= 3 là có ngay top-3 mỗi tài khoản.

Hai truy vấn trên là bộ khung điển hình cho một dashboard "Khách hàng & Giám sát giao dịch": phần xếp hạng địa bàn liên hệ chặt với cách xây chỉ số & KPI, còn kỹ thuật running total và frame để tính số dư luỹ kế theo thời gian sẽ đến ở SQL nâng cao 2.

Ghi nhớ

• Window giữ nguyên số dòng, GROUP BY gộp dòng. Cần cả chi tiết lẫn giá trị nhóm trong một bước → window; chỉ cần con số tổng hợp → GROUP BY.
• Cú pháp lõi: func() OVER (PARTITION BY ... ORDER BY ... <frame>). PARTITION BY chia nhóm, ORDER BY sắp xếp trong nhóm và bật frame ngầm định.
• Ranking: ROW_NUMBER luôn duy nhất (dùng cho top-N); RANK nhảy cóc khi ties; DENSE_RANK không nhảy cóc. NTILE(n) chia n nhóm; PERCENT_RANK/CUME_DIST cho vị trí tương đối 0..1.
• Aggregate-as-window không có ORDER BY = tổng cả nhóm; thêm ORDER BY = luỹ kế — đừng nhầm, sai âm thầm.
• Offset: LAG/LEAD so kỳ trước/sau (cần ORDER BY, sinh NULL ở biên). LAST_VALUE cần frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING mới đúng.
• Không dùng được window trong WHERE cùng cấp → bọc trong CTE/subquery rồi lọc bên ngoài (mẫu top-N).
• Khi ROUND kết quả của AVG/PERCENT_RANK/STDDEV... trên Postgres, phải ép ::numeric trước.
• Đặt tên cửa sổ bằng WINDOW ... AS (...) để tái dùng và giúp planner gộp bước sort. Xem thêm pg-01-architecture về thực thi.

Nguồn tham khảo

• PostgreSQL Documentation — 3.5. Window Functions (phần Tutorial, giới thiệu khái niệm window)
• PostgreSQL Documentation — 4.2.8. Window Function Calls (cú pháp OVER, PARTITION BY, ORDER BY, frame)
• PostgreSQL Documentation — 9.22. Window Functions (các hàm dựng sẵn: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE)
• PostgreSQL Documentation — 7.2.5. Window Function Processing (thứ tự xử lý window trong truy vấn)
• ISO/IEC 9075 (SQL standard) — định nghĩa chuẩn window function trong SQL:2003 và các bản sau