Subquery lồng nhau, Self-Join hay Window Functions?
Đầu năm ngoái, dashboard doanh thu bên mình bắt đầu đuối sức khi lượng đơn chạm mốc 5 triệu bản ghi. Đỉnh điểm là mỗi sáng thứ Hai, team marketing kéo báo cáo top 3 đơn giá trị nhất kèm so sánh với đơn liền kề. CPU của con RDS PostgreSQL nhảy vọt lên 98%, query mất hơn 30 giây mới nhả kết quả (bài học quản trị tài nguyên tương tự như khi quản lý Database trên AWS RDS).
Đứng trước bài toán này, anh em backend thường cân nhắc 3 hướng đi:
- Correlated Subquery: Nhét subquery vào
SELECThoặcWHERE. Cách này dễ hình dung logic nhất lúc đầu. - Self-Join: Join bảng với chính nó qua mốc thời gian hoặc ID.
- Window Functions (Mệnh đề OVER + PARTITION BY): Tính toán trực tiếp trên từng phân vùng dữ liệu mà không làm gộp dòng.
Thực tế production: So sánh sau 6 tháng refactor
Sau khi benchmark trên bảng orders nặng khoảng 4.2 GB dữ liệu, sự chênh lệch lộ rõ:
1. Subquery và Self-Join: Viết thì nhanh, chạy thì nghẽn
Nhiều bạn mới làm hay chọn subquery vì tiện tay. Đổi lại, DB phải trả giá đắt:
- I/O quá tải: Correlated subquery ép engine lặp lại full table scan hàng triệu lần ($O(N^2)$). Buffer cache nhanh chóng bị nuốt sạch.
- Bảo trì cực nhọc: Để lấy giá trị đơn trước và đơn sau, câu lệnh phải tự join 3 lần. File SQL dài cả trang, người khác vào đọc rất khó nắm bắt.
2. Window Functions: Quét dữ liệu một lần, latency tụt sâu
Sau khi team chuyển toàn bộ sang Window Functions, query time giảm từ 28 giây xuống còn 340ms.
- Điểm cộng lớn: PostgreSQL chỉ quét bảng một lần (Single Pass Scan). Dữ liệu được gom vào bộ nhớ (work_mem) rồi tính toán luôn. Câu lệnh ngắn lại một nửa, logic rõ ràng.
- Điểm cần lưu ý: Thứ tự thực thi của Window Function nằm sau
WHERE,GROUP BYvàHAVING. Vì vậy, bạn không thể nhét trực tiếp hàm window vào mệnh đềWHEREđể lọc dữ liệu. Thay vào đó, bạn cần bọc câu lệnh qua một CTE (WITH ... AS) hoặc subquery ở tầng ngoài.
Khi nào nên dùng Window Functions?
Quy tắc ghi nhớ nhanh cho anh em:
- Cần thu gọn dòng (chỉ cần một con số tổng kết cho mỗi nhóm khách): Hãy chọn
GROUP BY. - Cần giữ nguyên chi tiết từng dòng nhưng bổ sung cột tính toán (xếp hạng đơn, lấy đơn liền trước, tính lũy kế): Chọn ngay
Window Functions.
Thực hành trực tiếp trên PostgreSQL
Chuẩn bị bảng test giả lập kịch bản đơn hàng (nếu lượng bản ghi tiếp tục tăng trưởng đột biến, bạn có thể cân nhắc thêm table partitioning trong PostgreSQL để tối ưu I/O):
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT,
amount NUMERIC(10, 2),
order_date DATE
);
INSERT INTO orders (customer_id, amount, order_date) VALUES
(101, 150.00, '2026-01-05'),
(101, 300.00, '2026-01-12'),
(101, 300.00, '2026-02-01'),
(102, 50.00, '2026-01-10'),
(102, 200.00, '2026-01-18'),
(103, 500.00, '2026-02-15');
Nếu bạn có sẵn file CSV dump từ hệ thống cũ và cần test nhanh, có thể ghé toolcraft.app/vi/tools/data/csv-to-json để chuyển sang JSON hoặc xem trước cấu trúc mà không sợ rò rỉ dữ liệu lên server.
1. Phân biệt ROW_NUMBER, RANK và DENSE_RANK
Ba hàm này rất hay bị nhầm lẫn trong các bài toán xếp hạng:
ROW_NUMBER(): Đánh số tăng dần 1, 2, 3… liên tục, không quan tâm giá trị bằng nhau.RANK(): Hai dòng bằng điểm sẽ chung hạng, nhưng thứ hạng kế tiếp sẽ bị nhảy cóc (1, 2, 2, 4).DENSE_RANK(): Hai dòng bằng điểm vẫn chung hạng, nhưng thứ hạng kế tiếp không bị nhảy số (1, 2, 2, 3).
Thử xếp hạng giá trị đơn của từng khách:
SELECT
order_id,
customer_id,
amount,
ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS row_num,
RANK() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS dense_rnk
FROM orders;
Ở đây, PARTITION BY customer_id chia đơn theo từng khách. Còn ORDER BY amount DESC xếp đơn từ cao xuống thấp trong từng nhóm đó.
2. Lấy top N bản ghi trong từng nhóm
Để lọc đơn hàng đắt nhất của mỗi khách hàng, chúng ta dùng CTE kết hợp ROW_NUMBER():
WITH ranked_orders AS (
SELECT
order_id,
customer_id,
amount,
ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked_orders
WHERE rn = 1;
3. Tính chênh lệch với dòng trước/sau bằng LAG và LEAD
Khi tính chỉ số MoM (tháng này so với tháng trước) hoặc khoảng cách giữa hai lần mua, LAG và LEAD giải quyết cực gọn (bạn có thể tham khảo thêm hướng dẫn chi tiết về ROW_NUMBER, RANK, LEAD và LAG để áp dụng cho các case phân tích phức tạp):
LAG(cột, offset, default): Lấy giá trị dòng đứng trước.LEAD(cột, offset, default): Lấy giá trị dòng đứng sau.
Tính mức tăng giảm doanh thu so với đơn liền kề của cùng một khách:
SELECT
customer_id,
order_date,
amount AS current_amount,
LAG(amount, 1, 0) OVER(PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
amount - LAG(amount, 1, amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS diff_amount
FROM orders;
4. Tính tổng dồn (Running Total)
Thay vì viết truy vấn đệ quy, bạn chỉ cần kết hợp SUM() với ORDER BY:
SELECT
customer_id,
order_date,
amount,
SUM(amount) OVER(
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;
Mỗi dòng sẽ tự cộng dồn từ đơn đầu tiên đến thời điểm hiện tại. Câu lệnh vừa ngắn, vừa tận dụng tốt index trên (customer_id, order_date) mà không khiến DB nghẽn I/O. Khi hoàn thiện các truy vấn báo cáo, việc kết nối với các công cụ BI như Apache Superset cho PostgreSQL sẽ giúp việc trực quan hóa biểu đồ doanh thu mượt mà hơn rất nhiều.
