Bài 7: Window Functions

Bài 4 dạy GROUP BY — gộp nhiều hàng thành 1 hàng duy nhất mỗi nhóm, mất hết chi tiết từng hàng gốc. Window function (hàm cửa sổ) giải quyết đúng nhược điểm đó: tính toán dựa trên 1 "cửa sổ" các hàng liên quan, nhưng giữ nguyên số hàng gốc — mỗi hàng vẫn là chính nó, chỉ có thêm 1 cột tính toán dựa trên các hàng xung quanh. Bài này cũng quay lại giải đúng bài toán self-join phức tạp ở Bài 3 ("khoảng cách giữa 2 đơn hàng liên tiếp") bằng LAG() — gọn hơn hẳn.

1. OVER (PARTITION BY): Tính Toán Trên Cửa Sổ Mà Không Gộp Hàng

Cú pháp cơ bản: hàm_cửa_sổ() OVER (PARTITION BY cột_chia_nhóm ORDER BY cột_sắp_xếp). PARTITION BY chia dữ liệu thành các nhóm (giống GROUP BY về mặt khái niệm), nhưng thay vì gộp mỗi nhóm thành 1 hàng, hàm cửa sổ tính toán cho từng hàng riêng lẻ trong phạm vi nhóm của nó.

Tình huống 1 — Xếp hạng khách hàng theo chi tiêu, TRONG TỪNG quốc gia:

WITH customer_spend AS (
  SELECT c.customer_id, c.full_name, c.country, COALESCE(SUM(o.total_amount), 0) AS total_spent
  FROM customers c
  LEFT JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, c.full_name, c.country
)
SELECT full_name, country, total_spent,
       RANK() OVER (PARTITION BY country ORDER BY total_spent DESC) AS rank_in_country
FROM customer_spend
ORDER BY country, rank_in_country;

Kết quả giữ nguyên đủ 12 hàng (12 khách hàng) — khác hẳn GROUP BY country sẽ chỉ còn 6 hàng (6 quốc gia). Việt Nam có 7 khách, xếp hạng rõ ràng từ 1 (Nguyễn Minh Anh, 554.93) tới 7 (Vũ Đức Thắng, 37.98); các quốc gia chỉ có đúng 1 khách (Úc, Singapore, Hàn Quốc...) đều hiển thị hạng 1 một cách hiển nhiên. Đây chính là điểm mạnh cốt lõi của PARTITION BY: xếp hạng trong phạm vi từng nhóm, không phải xếp hạng toàn cục — khách Việt Nam hạng 7 vẫn có thể chi tiêu nhiều hơn khách hạng 1 ở quốc gia khác, và điều đó hoàn toàn không ảnh hưởng tới kết quả.

Tình huống 2 — Xếp hạng sản phẩm theo doanh thu, TRONG TỪNG danh mục:

WITH product_rev AS (
  SELECT p.product_id, p.product_name, p.category, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  INNER JOIN products p ON oi.product_id = p.product_id
  GROUP BY p.product_id, p.product_name, p.category
)
SELECT product_name, category, revenue,
       RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank_in_category
FROM product_rev
ORDER BY category, rank_in_category;

Standing Desk hạng 1 trong Office (699.98), 27-inch Monitor hạng 1 trong Electronics (749.97) — mỗi danh mục có bảng xếp hạng riêng, tính song song trong cùng 1 câu lệnh duy nhất.

2. ROW_NUMBER, RANK, DENSE_RANK: 3 Cách Đánh Số Khác Nhau Khi Có Đồng Hạng

Cả 3 hàm đều đánh số thứ tự theo ORDER BY trong OVER(...), nhưng xử lý đồng hạng (tie) hoàn toàn khác nhau. Dataset TechMart có sẵn 3 cặp đơn hàng đồng giá trị — tình huống hoàn hảo để thấy khác biệt:

SELECT order_id, total_amount,
       ROW_NUMBER() OVER (ORDER BY total_amount DESC) AS rn,
       RANK()       OVER (ORDER BY total_amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY total_amount DESC) AS drnk
FROM orders
ORDER BY total_amount DESC
LIMIT 8;

2 đơn 8 và 18 đồng giá 349.99: ROW_NUMBER() vẫn cố ép mỗi hàng 1 số duy nhất (1, 2 — thứ tự giữa 2 hàng đồng hạng phụ thuộc thứ tự vật lý, không đáng tin cậy nếu không có tiêu chí phá thế hoà rõ ràng). RANK() cho cả 2 cùng hạng 1, rồi nhảy cóc sang hạng 3 cho đơn tiếp theo (bỏ qua hạng 2 — vì đã có 2 hàng chiếm hạng 1). DENSE_RANK() cũng cho cả 2 cùng hạng 1, nhưng đơn tiếp theo nhận hạng 2 — không nhảy cóc, không bỏ số nào.

3. LAG/LEAD: Truy Cập Hàng Trước/Sau Trong Cùng Partition

LAG(cột) lấy giá trị cột ở hàng trước hàng hiện tại (theo ORDER BY trong OVER); LEAD(cột) lấy hàng sau. Đây chính là công cụ giải gọn bài toán "khoảng cách giữa 2 đơn liên tiếp" mà Bài 3 phải dùng self-join phức tạp để giải:

SELECT customer_id, order_id, order_date,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date,
       julianday(order_date) - julianday(
         LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
       ) AS days_since_prev
FROM orders
WHERE customer_id IS NOT NULL
ORDER BY customer_id, order_date;

So với self-join ở Bài 3 (orders o1 INNER JOIN orders o2 ON o1.customer_id = o2.customer_id AND o1.order_id < o2.order_id, rồi phải tự suy luận cặp nào thực sự "liên tiếp"), LAG() giải quyết trực tiếp và chính xác — không cần suy luận, không sinh ra các cặp không liên tiếp cần lọc bỏ. Đơn đầu tiên mỗi khách hàng có prev_order_date IS NULL (không có hàng trước trong partition của nó) — hành vi tương tự biên của mảng.

Tình huống 2 — Dùng LEAD để so sánh với đơn tiếp theo: viết lại cùng bài toán theo hướng ngược — mỗi đơn kèm ngày của đơn sau nó:

SELECT customer_id, order_id, order_date,
       LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_date
FROM orders
WHERE customer_id IS NOT NULL
ORDER BY customer_id, order_date;

Kết quả tương đương LAG() ở trên nhưng nhìn từ hướng ngược lại — đơn cuối cùng mỗi khách có next_order_date IS NULL thay vì đơn đầu tiên. Chọn LAG hay LEAD tuỳ câu hỏi kinh doanh: "đơn này cách đơn trước bao lâu" dùng LAG, "khách có quay lại mua tiếp không, bao lâu sau" dùng LEAD.

4. Running Total & Moving Average: Frame Cửa Sổ (ROWS BETWEEN)

Mặc định, OVER (ORDER BY ...) tính trên khung "từ đầu partition tới hàng hiện tại" — đây chính là công thức running total (tổng luỹ kế). Khai báo rõ ràng bằng ROWS BETWEEN giúp kiểm soát chính xác khung tính toán:

SELECT order_id, order_date, total_amount,
       SUM(total_amount) OVER (
         ORDER BY order_date, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY order_date, order_id;

UNBOUNDED PRECEDING nghĩa là "từ hàng đầu tiên của toàn bộ tập kết quả", CURRENT ROW là điểm dừng — mỗi hàng cộng dồn thêm giá trị của chính nó vào tổng các hàng trước. Đơn đầu tiên (2025-11-05) có running total = 56.97 (chính nó); tới đơn thứ 8 (2026-01-14, 349.99) running total đã lên 1272.89.

Tình huống 2 — Moving average (trung bình trượt) 3 đơn gần nhất: thay vì cộng dồn toàn bộ lịch sử, chỉ tính trung bình N hàng gần nhất — làm mượt biến động ngắn hạn để thấy xu hướng dài hạn rõ hơn:

SELECT order_id, order_date, total_amount,
       ROUND(AVG(total_amount) OVER (
         ORDER BY order_date, order_id
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_3
FROM orders
ORDER BY order_date, order_id;

2 PRECEDING AND CURRENT ROW nghĩa là "2 hàng trước cộng với hàng hiện tại" — đúng 3 hàng (trừ 2 hàng đầu tiên của toàn bộ tập, nơi chưa đủ 2 hàng trước, SQLite tự động chỉ tính trung bình trên số hàng thực sự có sẵn thay vì báo lỗi).

Bảng So Sánh: Window Function vs GROUP BY

Sân chơi tương tác: SQL Workbench Trên Dataset TechMart

Thử đổi PARTITION BY sang cột khác, hoặc đổi khung ROWS BETWEEN để thấy running total/moving average thay đổi ngay lập tức.

Đang tải...

⬇ Tải file schema + dữ liệu mẫu TechMart (.sql) — chạy được trực tiếp bằng sqlite3 CLI hoặc DB Browser for SQLite trên máy bạn.