Window function: giữ chi tiết mà vẫn tính được tổng hợp

Giám đốc vận hành GDev Mart hỏi một câu tưởng đơn giản: "Với mỗi đơn hàng, cho tôi biết nó chiếm bao nhiêu phần trăm doanh thu của cửa hàng đó, và nó là đơn lớn thứ mấy trong cửa hàng". Bạn viết GROUP BY store_id và nhận lại một dòng cho mỗi cửa hàng: tổng doanh thu có rồi, nhưng chi tiết từng đơn biến mất. Muốn lấy lại chi tiết, bạn phải nối bảng với chính nó, một truy vấn dài và chậm.

Hãy dự đoán trước khi đọc tiếp: có cách nào để mỗi dòng đơn hàng vẫn còn nguyên, nhưng đồng thời "nhìn thấy" tổng của cả cửa hàng nằm cạnh nó không? Nếu có, bạn nghĩ nó phải nói cho SQL biết những gì? Viết ra hai điều bạn nghĩ cần khai báo.

Sau bài này, bạn có thể

• Giải thích vì sao GROUP BY làm mất chi tiết từng đơn còn window function giữ nguyên số dòng.
• Viết OVER (PARTITION BY ... ORDER BY ...) để gắn tổng nhóm, tỷ trọng và thứ hạng vào từng dòng.
• Chọn đúng giữa ROW_NUMBER, RANK và DENSE_RANK khi có đồng hạng.
• Dự đoán và sửa lũy kế sai do frame mặc định RANGE bằng ROWS.

Cần biết trước: SELECT, WHERE, GROUP BY và JOIN cơ bản trong PostgreSQL; PostgreSQL chạy sẵn từ bài "Lab tuần 1: Dựng PostgreSQL và Python bằng Docker Compose".

Mô hình tư duy 1: GROUP BY gom dòng lại, window giữ dòng nguyên

GROUP BY co nhiều dòng thành một. Window function thì tính một giá trị dựa trên một nhóm dòng "liên quan" nhưng không co kết quả: số dòng ra bằng số dòng vào, và giá trị tính được gắn thêm vào từng dòng. Cú pháp luôn có OVER (...).

Cách đọc hình: cùng một đầu vào 6 dòng, hai kết quả khác hẳn. Nhánh phải trả lời trọn câu hỏi của giám đốc vì mỗi đơn vẫn còn, kèm tổng cửa hàng ngay bên cạnh.

Mô hình tư duy 2: window gồm ba phần, PARTITION, ORDER và FRAME

Giải đáp cho câu hỏi đầu bài: bạn cần nói với SQL (1) "nhóm dòng liên quan" là gì, và (2) thứ tự các dòng trong nhóm nếu thứ tự quan trọng. Đó là hai trong ba phần của một cửa sổ:

• PARTITION BY: chia dòng thành các nhóm độc lập (mỗi cửa hàng một nhóm). Bỏ qua nó thì cả bảng là một nhóm.
• ORDER BY: sắp xếp dòng bên trong nhóm; cần cho xếp hạng, tích lũy và LAG.
• Frame (ROWS BETWEEN ...): với mỗi dòng, cửa sổ rộng ra bao nhiêu dòng xung quanh, ví dụ "từ đầu nhóm tới dòng hiện tại" hay "6 dòng trước và dòng hiện tại".

Dữ liệu mẫu (số tiền tính theo nghìn đồng) để theo dõi bằng tay:

Tạo bảng và nạp dữ liệu mẫu để chạy lại mọi ví dụ trong bài (PostgreSQL):

CREATE TABLE orders_demo (order_id int, store_id int, amount int, created_date date);
INSERT INTO orders_demo VALUES
  (1, 7, 120, '2026-09-01'),
  (2, 7, 80, '2026-09-01'),
  (3, 7, 200, '2026-09-02'),
  (4, 12, 50, '2026-09-01'),
  (5, 12, 70, '2026-09-02'),
  (6, 12, 70, '2026-09-02');

Tổng nhóm và tỷ trọng

SELECT order_id, store_id, amount,
       SUM(amount) OVER (PARTITION BY store_id) AS store_total,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY store_id), 1) AS pct_of_store
FROM orders_demo
ORDER BY order_id;

Kiểm tra bằng tay: cửa hàng 7 có 120 + 80 + 200 = 400, nên đơn 1 chiếm 120/400 = 30%. Cửa hàng 12 có 50 + 70 + 70 = 190, nên đơn 4 chiếm 50/190 ≈ 26,3%.

Xếp hạng: ba hàm, ba cách xử lý đồng hạng

Hãy tự dự đoán trước: với cửa hàng 12, hai đơn 5 và 6 cùng 70, nếu xếp hạng giảm dần theo amount, ROW_NUMBER, RANK và DENSE_RANK sẽ cho các số nào?

SELECT order_id, store_id, amount,
       ROW_NUMBER() OVER w AS row_number,
       RANK() OVER w AS rank,
       DENSE_RANK() OVER w AS dense_rank
FROM orders_demo
WINDOW w AS (PARTITION BY store_id ORDER BY amount DESC)
ORDER BY store_id, row_number;

Giải đáp: ROW_NUMBER luôn đánh số 1, 2, 3 không trùng, nên hai dòng hòa nhận 1 và 2 (thứ tự giữa hai dòng hòa là tùy ý, có thể ngược lại). RANK cho hai dòng hòa cùng hạng 1 rồi nhảy cóc sang 3. DENSE_RANK cho hai dòng hòa cùng hạng 1 rồi tiếp tục liền mạch là 2.

Cách đọc hình: với cùng chuỗi giá trị đã sắp giảm dần 70, 70, 50, ba hàm trả ba chuỗi hạng khác nhau. Khi cần "top 3 đơn", ROW_NUMBER cho đúng 3 dòng; RANK có thể trả nhiều hơn 3 nếu có hòa.

Cái bẫy kinh điển của frame mặc định

Tích lũy theo thời gian là việc cực thường gặp. Hãy dự đoán trước: với cửa hàng 7, tổng lũy kế của đơn 1 là bao nhiêu khi viết SUM(amount) OVER (PARTITION BY store_id ORDER BY created_date)?

SELECT order_id, store_id, created_date, amount,
       SUM(amount) OVER (PARTITION BY store_id ORDER BY created_date) AS running_default,
       SUM(amount) OVER (PARTITION BY store_id ORDER BY created_date, order_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows
FROM orders_demo
ORDER BY store_id, created_date, order_id;

Giải đáp: nhiều người đoán 120, nhưng cột running_default cho 200. Khi có ORDER BY mà không ghi frame, SQL dùng RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, trong đó "dòng hiện tại" nghĩa là mọi dòng cùng giá trị sắp xếp (gọi là peer). Đơn 1 và 2 cùng ngày nên là peer, cả hai thấy tổng 120 + 80 = 200. Cột running_rows dùng ROWS đếm theo dòng vật lý và thêm order_id làm tiêu chí phá hòa, nên cho lũy kế từng dòng đúng như kỳ vọng.

Dòng trước, trung bình trượt và chia nhóm: LAG, AVG với ROWS, NTILE

Phần tự kiểm tra và bài tập dùng thêm ba kiểu window nữa. Hãy dự đoán trước: với dữ liệu mẫu, trung bình trượt 2 dòng (dòng trước và dòng hiện tại) của đơn 3 là bao nhiêu, và LAG(amount) của đơn 4 là gì?

SELECT order_id, store_id, amount,
       LAG(amount) OVER w AS prev_amount,
       amount - LAG(amount) OVER w AS diff,
       ROUND(AVG(amount) OVER (PARTITION BY store_id ORDER BY created_date, order_id
                               ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 1) AS avg_2
FROM orders_demo
WINDOW w AS (PARTITION BY store_id ORDER BY created_date, order_id)
ORDER BY store_id, order_id;

Giải đáp: đơn 3 có avg_2 là (80 + 200) / 2 = 140. Đơn 4 là dòng đầu của cửa hàng 12 nên LAG trả NULL, và hiệu diff cũng là NULL (mọi phép tính với NULL đều ra NULL). Ở dòng đầu nhóm, frame 1 PRECEDING chỉ có một dòng nên trung bình bằng chính nó. NTILE(n) chia các dòng đã sắp xếp thành n nhóm gần bằng nhau: NTILE(2) OVER (ORDER BY amount DESC, order_id) đưa đơn 3, 1, 2 vào nhóm 1 và đơn 5, 6, 4 vào nhóm 2.

Cách đọc hình: hai nhánh dẫn tới hai kết quả khác nhau. Quy tắc thực hành: khi viết tích lũy hoặc trung bình trượt, luôn ghi ROWS rõ ràng và thêm một cột duy nhất (như order_id) vào ORDER BY để kết quả xác định.

Những hiểu lầm thường gặp

• "Window function chạy trước WHERE." Ngược lại: WHERE lọc dòng trước, rồi window tính trên những dòng còn lại. Muốn lọc theo kết quả window (ví dụ rn <= 3) phải bọc trong CTE hoặc truy vấn con, vì window không dùng được trực tiếp trong WHERE.
• "PARTITION BY giống GROUP BY." Chúng chia nhóm giống nhau nhưng GROUP BY co dòng còn PARTITION BY giữ dòng.
• "RANK và ROW_NUMBER thay thế được cho nhau." Chỉ khi không có giá trị hòa; khi có hòa chúng cho kết quả khác.

Tự kiểm tra (có đáp án)

Câu 1. Muốn lấy 3 đơn lớn nhất mỗi cửa hàng và đúng 3 dòng ngay cả khi có hòa, bạn chọn hàm nào, và bọc ở đâu? Đáp án: ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY amount DESC, order_id) trong một CTE, rồi lọc rn <= 3 ở truy vấn ngoài vì không dùng được window trong WHERE.

Câu 2. Với dữ liệu mẫu, LAG(amount) OVER (PARTITION BY store_id ORDER BY created_date, order_id) cho đơn 3 là bao nhiêu và cho đơn 1 là bao nhiêu? Đáp án: đơn 3 nhận 80 (số tiền của đơn 2, dòng ngay trước nó trong cửa hàng 7); đơn 1 là dòng đầu nhóm nên nhận NULL.

Câu 3. Vì sao running_default của đơn 6 bằng 190 trong khi running_rows của nó cũng bằng 190, nhưng của đơn 5 lại khác nhau? Đáp án: đơn 5 và 6 cùng ngày nên là peer; frame mặc định (RANGE) cho cả hai thấy 190, còn ROWS cộng theo thứ tự dòng nên đơn 5 chỉ thấy 50 + 70 = 120.

Nếu chỉ nhớ 3 điều

1. GROUP BY co dòng, window giữ dòng; window cần OVER (PARTITION BY ... ORDER BY ...).
2. ROW_NUMBER, RANK, DENSE_RANK khác nhau ở cách xử lý đồng hạng; chọn theo nhu cầu "đúng N dòng" hay "giữ hạng hòa".
3. Với tích lũy và trung bình trượt, luôn ghi ROWS và phá hòa trong ORDER BY để tránh cái bẫy frame RANGE mặc định.

Nguồn tham khảo

• PostgreSQL 18: 3.5. Window Functions, kiểm tra ngày 2026-10-28
• PostgreSQL 18: 9.22. Window Functions, kiểm tra ngày 2026-10-28