Database Engineering for Backend & AI Engineers
Trang chủ / Module 2
🔍 Module 2 / 14 · 1-2 tuần

SQL Nâng Cao — Joins, Subqueries, CTEs, Window Functions

Vượt qua SELECT/WHERE cơ bản: các loại JOIN, subquery tương quan, Common Table Expression, và window function cho phân tích dữ liệu.

SQLWindow FunctionCTE
📌 Vì sao module này quan trọng

Phần lớn engineer viết SQL đủ dùng để CRUD nhưng "đứng hình" trước một câu hỏi nghiệp vụ như "top 3 đơn hàng gần nhất của mỗi khách hàng" hoặc "doanh thu tích luỹ theo tháng". Đây chính là ranh giới giữa dùng ORM cho mọi thứ (chậm, kéo hết dữ liệu về xử lý ở app) và để database làm đúng việc nó giỏi nhất. Kỹ năng này áp dụng trực tiếp vào phần Data Engineering của khoá AI Engineer Master Course.

🎯 Mục tiêu học tập

  • Phân biệt và sử dụng đúng INNER/LEFT/RIGHT/FULL JOIN và self-join
  • Viết subquery tương quan (correlated subquery) và biết khi nào nó chậm hơn JOIN
  • Sử dụng Common Table Expression (CTE), kể cả CTE đệ quy, để chia nhỏ truy vấn phức tạp
  • Áp dụng window function (ROW_NUMBER, RANK, LAG/LEAD, SUM OVER) cho các bài toán phân tích phổ biến

JOIN — nắm chắc 4 loại chính và self-join

Loại JOINKết quả
INNER JOINChỉ các dòng khớp ở cả 2 bảng
LEFT JOINToàn bộ bảng trái + dòng khớp bên phải (NULL nếu không khớp)
RIGHT JOINToàn bộ bảng phải + dòng khớp bên trái (hiếm dùng — thường viết lại thành LEFT JOIN)
FULL OUTER JOINToàn bộ dòng ở cả 2 bảng, NULL ở phía không khớp
-- Tìm khách hàng CHƯA từng đặt đơn hàng nào (LEFT JOIN + IS NULL — pattern rất phổ biến)
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Self-join: tìm nhân viên và tên quản lý trực tiếp của họ
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Subquery tương quan vs JOIN — khi nào chậm

Subquery tương quan (correlated subquery) tham chiếu đến cột của truy vấn ngoài, khiến nó phải chạy lại một lần cho mỗi dòng của bảng ngoài — về mặt lý thuyết là O(n×m). Trình tối ưu hiện đại (PostgreSQL, MySQL 8+) thường tự viết lại thành JOIN hoặc semi-join khi có thể, nhưng không phải lúc nào cũng tối ưu được — luôn kiểm tra bằng EXPLAIN (Module 3) thay vì đoán.

-- Subquery tương quan: đơn hàng có giá trị lớn hơn trung bình của CHÍNH khách hàng đó
SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(o2.total) FROM orders o2 WHERE o2.customer_id = o.customer_id
);

-- Thường viết lại hiệu quả hơn bằng window function (xem phần cuối module)
SELECT id, customer_id, total FROM (
  SELECT id, customer_id, total, AVG(total) OVER (PARTITION BY customer_id) AS avg_total
  FROM orders
) t WHERE total > avg_total;

Common Table Expression (CTE) — chia nhỏ truy vấn phức tạp

CTE (WITH ... AS (...)) đặt tên cho một truy vấn con, giúp câu lệnh dài dễ đọc hơn và có thể tái sử dụng nhiều lần trong cùng truy vấn — khác với subquery lồng nhau gây khó đọc khi lồng 3-4 tầng. CTE đệ quy (WITH RECURSIVE) là công cụ chuẩn để truy vấn dữ liệu phân cấp (cây thư mục, sơ đồ tổ chức, category cha-con nhiều tầng).

-- CTE đệ quy: lấy toàn bộ nhân viên dưới quyền (trực tiếp + gián tiếp) của 1 quản lý
WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Window Function — phân tích dữ liệu không cần GROUP BY gộp dòng

Window function tính toán trên một "cửa sổ" các dòng liên quan (PARTITION BY) nhưng vẫn giữ nguyên từng dòng kết quả — khác GROUP BY gộp nhiều dòng thành 1. Đây là công cụ chuẩn cho các câu hỏi "top N mỗi nhóm", "so với dòng trước đó", "tích luỹ theo thời gian".

-- Top 3 đơn hàng giá trị cao nhất của MỖI khách hàng
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
  FROM orders
) t WHERE rn <= 3;

-- Doanh thu tích luỹ theo tháng (running total)
SELECT month, revenue, SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue
FROM monthly_revenue;

-- So sánh với tháng trước (LAG)
SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue
FROM monthly_revenue;

ROW_NUMBER() luôn cho số thứ tự duy nhất; RANK() bỏ số khi có đồng hạng; DENSE_RANK() không bỏ số khi có đồng hạng — chọn đúng hàm theo yêu cầu nghiệp vụ, đây là lỗi rất hay gặp khi làm bài toán "top N".

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

5 câu hỏi phân tích trên dữ liệu đơn hàng

Với schema orders/order_items/customers từ Module 1, viết SQL cho: (1) top 5 khách hàng chi tiêu nhiều nhất, (2) khách hàng chưa từng mua hàng trong 90 ngày qua, (3) doanh thu tích luỹ theo tuần trong năm nay, (4) mỗi khách hàng và đơn hàng gần nhất + đơn hàng trước đó của họ (dùng LAG), (5) sản phẩm bán chạy nhất theo từng danh mục (dùng window function, không dùng subquery cho mỗi danh mục). So sánh thời gian chạy khi viết bằng subquery tương quan và bằng window function trên cùng 1 bộ dữ liệu lớn.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Viết đúng LEFT JOIN + IS NULL để tìm bản ghi không có quan hệ
  • Giải thích được khi nào subquery tương quan có thể chậm và cách viết lại bằng JOIN/window function
  • Viết được CTE đệ quy cho dữ liệu phân cấp
  • Dùng đúng ROW_NUMBER/RANK/DENSE_RANK/LAG/LEAD cho từng loại bài toán cụ thể