SQL 4 — Subquery, CTE & Window Functions

13 thg 7, 2026 3 lượt xem
#sql
#window-function
#cte
#subquery

Nếu ba bài trước đã giúp bạn lấy dữ liệu, lọc, nhóm và nối bảng, thì bài này mở ra "tầng cao" của SQL: cách viết truy vấn lồng nhau, cách dùng CTE để tổ chức logic phức tạp cho dễ đọc, và đặc biệt là window functions — công cụ mà rất nhiều người làm dữ liệu mơ ước được biết sớm hơn. Đây là phần phân tách giữa người "biết SQL" và người "dùng SQL để phân tích".

Tất cả ví dụ đều chạy được với bộ bảng sandbox:

  • customers(id, full_name, city, created_at)
  • accounts(id, customer_id, account_no, balance, currency)
  • transactions(id, account_id, amount, kind['credit'|'debit'], created_at)
  • employees(id, name, department_id, salary, hired_at)
  • departments(id, name)

Subquery — truy vấn lồng trong truy vấn

Subquery (truy vấn con) là một câu SELECT đặt bên trong một câu lệnh khác. Bạn có thể đặt nó ở nhiều vị trí, và mỗi vị trí có một "vai" khác nhau.

Scalar subquery — trả về đúng một giá trị

Khi subquery trả về một dòng, một cột, nó được coi như một giá trị đơn (scalar) và có thể đứng bất cứ đâu một biểu thức được phép xuất hiện.

▶ Chạy được trong SQL Builder

-- So sánh lương từng nhân viên với mức lương trung bình toàn công ty
SELECT
  name,
  salary,
  (SELECT ROUND(AVG(salary), 0) FROM employees) AS avg_salary,
  salary - (SELECT AVG(salary) FROM employees) AS diff_vs_avg
FROM employees
ORDER BY diff_vs_avg DESC;

Subquery (SELECT AVG(salary) FROM employees) được tính một lần và "cắm" vào mỗi dòng. Lưu ý: nếu scalar subquery trả về nhiều hơn một dòng, database sẽ báo lỗi.

Subquery trong WHERE với IN

Subquery có thể trả về một danh sách giá trị để lọc bằng IN.

▶ Chạy được trong SQL Builder

-- Các tài khoản thuộc về khách hàng ở Hà Nội
SELECT account_no, balance, currency
FROM accounts
WHERE customer_id IN (
  SELECT id FROM customers WHERE city = 'Hà Nội'
);

Câu này đọc tự nhiên: "lấy tài khoản mà customer_id nằm trong tập id của khách hàng Hà Nội". Bạn cũng có thể viết bằng JOIN, nhưng IN đôi khi diễn đạt ý định rõ hơn khi bạn chỉ cần lọc chứ không cần lấy thêm cột.

Subquery trong FROM — bảng dẫn xuất

Một subquery đặt trong FROM trở thành một "bảng tạm" (derived table). Bạn bắt buộc phải đặt cho nó một alias.

▶ Chạy được trong SQL Builder

-- Tổng số dư mỗi khách hàng, rồi lọc những người có tổng > 100 triệu
SELECT t.customer_id, t.total_balance
FROM (
  SELECT customer_id, SUM(balance) AS total_balance
  FROM accounts
  GROUP BY customer_id
) AS t
WHERE t.total_balance > 100000000
ORDER BY t.total_balance DESC;

Ở đây ta nhóm trước trong subquery, rồi lọc kết quả nhóm ở câu ngoài. Đây là cách thay thế cho HAVING, và đôi khi dễ đọc hơn khi logic nhiều tầng.

EXISTS và correlated subquery

EXISTS kiểm tra xem subquery có trả về ít nhất một dòng hay không — nó trả về true/false chứ không quan tâm giá trị cụ thể. EXISTS thường đi cùng correlated subquery (subquery tương quan): subquery tham chiếu tới cột của câu ngoài, nên nó được chạy lại cho từng dòng của câu ngoài.

▶ Chạy được trong SQL Builder

-- Khách hàng đã từng có ít nhất một giao dịch ghi nợ (debit)
SELECT c.id, c.full_name, c.city
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM accounts a
  JOIN transactions t ON t.account_id = a.id
  WHERE a.customer_id = c.id
    AND t.kind = 'debit'
);

Dòng WHERE a.customer_id = c.id chính là phần "tương quan" — nó liên kết subquery với từng c đang xét ở câu ngoài. Ta viết SELECT 1 vì EXISTS không quan tâm chọn cột gì, chỉ quan tâm có dòng hay không.

Ngược lại có NOT EXISTS để tìm "những khách hàng chưa từng giao dịch debit":

▶ Chạy được trong SQL Builder

SELECT c.id, c.full_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM accounts a
  JOIN transactions t ON t.account_id = a.id
  WHERE a.customer_id = c.id
    AND t.kind = 'debit'
);

NOT EXISTS là cách an toàn để diễn đạt "không tồn tại", đặc biệt khi cột có thể chứa NULL (nơi mà NOT IN dễ cho kết quả sai bất ngờ).

CTE — WITH để tổ chức truy vấn

Khi truy vấn lồng nhiều tầng, subquery trong FROM trở nên khó đọc — dấu ngoặc chồng chất, không biết tầng nào tính gì. CTE (Common Table Expression) dùng từ khoá WITH để đặt tên cho từng bước trung gian, rồi bạn dùng tên đó như một bảng.

▶ Chạy được trong SQL Builder

-- Cùng bài toán "tổng số dư > 100 triệu", nhưng dùng CTE cho dễ đọc
WITH balance_per_customer AS (
  SELECT customer_id, SUM(balance) AS total_balance
  FROM accounts
  GROUP BY customer_id
)
SELECT bpc.customer_id, c.full_name, bpc.total_balance
FROM balance_per_customer bpc
JOIN customers c ON c.id = bpc.customer_id
WHERE bpc.total_balance > 100000000
ORDER BY bpc.total_balance DESC;

So với subquery trong FROM, CTE đọc tự nhiên hơn: "đầu tiên tính balance_per_customer, sau đó dùng nó". Bạn có thể định nghĩa nhiều CTE liên tiếp, ngăn cách bằng dấu phẩy, và CTE sau được dùng CTE trước.

▶ Chạy được trong SQL Builder

-- Hai CTE nối tiếp: tính tổng giao dịch, rồi xếp hạng tài khoản
WITH tx_summary AS (
  SELECT
    account_id,
    SUM(CASE WHEN kind = 'credit' THEN amount ELSE 0 END) AS total_credit,
    SUM(CASE WHEN kind = 'debit'  THEN amount ELSE 0 END) AS total_debit
  FROM transactions
  GROUP BY account_id
),
net_flow AS (
  SELECT account_id, total_credit - total_debit AS net
  FROM tx_summary
)
SELECT a.account_no, a.currency, n.net
FROM net_flow n
JOIN accounts a ON a.id = n.account_id
ORDER BY n.net DESC;

CTE không tự động nhanh hơn subquery — về cơ bản nó là cách viết. Lợi ích lớn nhất là khả năng đọc và bảo trì: mỗi bước có tên, dễ kiểm tra từng phần.

CTE đệ quy — đi qua cấu trúc phân cấp

CTE còn có dạng đệ quy (recursive), dùng WITH RECURSIVE. Nó gồm hai phần nối bằng UNION ALL:

  1. Anchor (mỏ neo): truy vấn khởi đầu, chạy một lần.
  2. Recursive member (phần đệ quy): tham chiếu chính CTE đó, chạy lặp lại cho đến khi không sinh thêm dòng mới.

Ví dụ kinh điển là sinh dãy số (không cần bảng):

▶ Chạy được trong SQL Builder

-- Sinh dãy 1..10 bằng CTE đệ quy
WITH RECURSIVE counter(n) AS (
  SELECT 1                       -- anchor: dòng khởi đầu
  UNION ALL
  SELECT n + 1 FROM counter      -- recursive: lấy dòng trước + 1
  WHERE n < 10                   -- điều kiện dừng
)
SELECT n FROM counter;

Cơ chế: anchor cho n = 1. Phần đệ quy lấy dòng vừa có, cộng 1, chừng nào n < 10. Khi n = 10, điều kiện sai, không sinh dòng mới, đệ quy dừng. Luôn phải có điều kiện dừng — nếu không, truy vấn chạy vô hạn.

Ứng dụng thực tế của CTE đệ quy là duyệt cây/phân cấp: sơ đồ tổ chức (nhân viên — quản lý), danh mục sản phẩm nhiều cấp, đường đi trong đồ thị. Bất cứ khi nào dữ liệu "tự tham chiếu chính nó", CTE đệ quy là công cụ đúng.

Window Functions — phân tích theo "cửa sổ"

Đây là phần quan trọng nhất của bài. Window function (hàm cửa sổ) cho phép bạn tính toán trên một nhóm dòng liên quan mà không gộp các dòng đó lại — khác hẳn GROUP BY.

Cú pháp OVER

Một window function luôn đi kèm mệnh đề OVER (...):

<hàm>() OVER (
  PARTITION BY <cột chia nhóm>     -- tùy chọn
  ORDER BY <cột sắp xếp>           -- tùy chọn
)
  • PARTITION BY chia dữ liệu thành các "ngăn" (giống GROUP BY nhưng không gộp dòng). Hàm được tính riêng trong mỗi ngăn.
  • ORDER BY (bên trong OVER) quyết định thứ tự xử lý trong ngăn — cần cho xếp hạng, running total, LAG/LEAD.

Cơ chế xử lý có thể hình dung như sau:

Điểm cốt lõi: số dòng ra bằng số dòng vào. Window function thêm một cột tính toán bên cạnh, chứ không làm biến mất dòng nào.

ROW_NUMBER, RANK, DENSE_RANK

Ba hàm xếp hạng này hay bị nhầm. Khác biệt nằm ở cách xử lý giá trị bằng nhau (tie):

  • ROW_NUMBER(): đánh số 1, 2, 3... liên tục, không bao giờ trùng — kể cả khi giá trị bằng nhau.
  • RANK(): giá trị bằng nhau nhận cùng hạng, nhưng bỏ qua các số tiếp theo (1, 1, 3).
  • DENSE_RANK(): giá trị bằng nhau nhận cùng hạng, nhưng không bỏ số (1, 1, 2).

▶ Chạy được trong SQL Builder

-- Xếp hạng tài khoản theo số dư, RIÊNG trong từng loại tiền tệ
SELECT
  account_no,
  currency,
  balance,
  RANK()       OVER (PARTITION BY currency ORDER BY balance DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY currency ORDER BY balance DESC) AS dense_rnk,
  ROW_NUMBER() OVER (PARTITION BY currency ORDER BY balance DESC) AS row_num
FROM accounts
ORDER BY currency, balance DESC;

PARTITION BY currency nghĩa là việc xếp hạng được "reset" cho mỗi loại tiền: tài khoản VND xếp riêng, USD xếp riêng. Trong mỗi nhóm, ORDER BY balance DESC quyết định ai hạng 1.

Một ứng dụng cực phổ biến của ROW_NUMBER là lấy top-N mỗi nhóm:

▶ Chạy được trong SQL Builder

-- 2 tài khoản số dư cao nhất mỗi loại tiền tệ
WITH ranked AS (
  SELECT
    account_no,
    currency,
    balance,
    ROW_NUMBER() OVER (PARTITION BY currency ORDER BY balance DESC) AS rn
  FROM accounts
)
SELECT account_no, currency, balance
FROM ranked
WHERE rn <= 2
ORDER BY currency, balance DESC;

Lưu ý: bạn không thể đặt window function trực tiếp trong WHERE (vì nó được tính sau khi lọc). Cách chuẩn là bọc trong CTE rồi lọc ở câu ngoài — đây là lý do CTE và window function thường đi đôi.

Running total với SUM() OVER

Hàm tổng hợp như SUM, AVG, COUNT cũng dùng được làm window function. Khi có ORDER BY trong OVER, chúng tính lũy kế (running total) — cộng dồn từ dòng đầu đến dòng hiện tại.

▶ Chạy được trong SQL Builder

-- Số dư lũy kế theo thời gian cho từng tài khoản
SELECT
  account_id,
  created_at,
  amount,
  kind,
  SUM(CASE WHEN kind = 'credit' THEN amount ELSE -amount END)
    OVER (PARTITION BY account_id ORDER BY created_at) AS running_balance
FROM transactions
ORDER BY account_id, created_at;

Với mỗi tài khoản, các giao dịch được sắp theo created_at, và running_balance cộng dồn dần: credit cộng, debit trừ. Đây là cách tái tạo sổ phụ ngân hàng chỉ bằng một câu SQL.

Khi SUM() OVER không có ORDER BY, nó tính tổng toàn bộ ngăn cho mọi dòng — hữu ích để tính tỉ trọng:

▶ Chạy được trong SQL Builder

-- Tỉ trọng số dư mỗi tài khoản so với tổng số dư cùng loại tiền
SELECT
  account_no,
  currency,
  balance,
  SUM(balance) OVER (PARTITION BY currency) AS total_in_currency,
  ROUND(
    balance * 100.0 / SUM(balance) OVER (PARTITION BY currency),
    2
  ) AS pct
FROM accounts
ORDER BY currency, pct DESC;

LAG và LEAD — nhìn dòng trước, dòng sau

LAG(col, n) lấy giá trị của dòng trước (n dòng), LEAD(col, n) lấy dòng sau. Mặc định n = 1. Chúng tuyệt vời để so sánh kỳ này với kỳ trước.

▶ Chạy được trong SQL Builder

-- 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
SELECT
  account_id,
  created_at,
  amount,
  LAG(amount) OVER (PARTITION BY account_id ORDER BY created_at) AS prev_amount,
  amount - LAG(amount) OVER (PARTITION BY account_id ORDER BY created_at) AS delta
FROM transactions
ORDER BY account_id, created_at;

Dòng đầu tiên của mỗi tài khoản có prev_amount là NULL (không có dòng trước). Bạn có thể đặt giá trị mặc định: LAG(amount, 1, 0) sẽ trả về 0 thay vì NULL.

Một ví dụ khác kết hợp employees:

▶ Chạy được trong SQL Builder

-- Trong mỗi phòng ban, mức lương cách người được thuê liền trước bao nhiêu
SELECT
  e.name,
  d.name AS department,
  e.hired_at,
  e.salary,
  e.salary - LAG(e.salary) OVER (
    PARTITION BY e.department_id ORDER BY e.hired_at
  ) AS salary_gap_vs_prev_hire
FROM employees e
JOIN departments d ON d.id = e.department_id
ORDER BY d.name, e.hired_at;

Window vs GROUP BY — khác biệt cốt lõi

Đây là điểm hay gây bối rối nhất, nên cần nói thẳng:

  • GROUP BY gộp nhiều dòng thành một dòng tóm tắt mỗi nhóm. Bạn mất dữ liệu chi tiết.
  • Window function giữ nguyên mọi dòng chi tiết và thêm cột tính theo nhóm bên cạnh.

So sánh trực tiếp cùng một câu hỏi — "lương từng người so với trung bình phòng ban":

▶ Chạy được trong SQL Builder

-- Cách GROUP BY: chỉ ra trung bình mỗi phòng, MẤT chi tiết từng người
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

▶ Chạy được trong SQL Builder

-- Cách window: GIỮ từng người, vẫn có trung bình phòng để so sánh
SELECT
  e.name,
  d.name AS department,
  e.salary,
  ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id), 0) AS dept_avg,
  e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id) AS diff
FROM employees e
JOIN departments d ON d.id = e.department_id
ORDER BY d.name, e.salary DESC;

Với GROUP BY, để biết một người cao hơn trung bình bao nhiêu, bạn phải tự join lại bảng tóm tắt. Với window function, mọi thứ nằm trên cùng một dòng. Quy tắc đơn giản: cần dòng tổng hợp → GROUP BY; cần giữ chi tiết kèm số liệu nhóm → window function.

Tóm tắt

  • Subquery là SELECT lồng trong câu khác: scalar (một giá trị), IN (danh sách), derived table (trong FROM), và EXISTS/NOT EXISTS cho correlated subquery — kiểm tra sự tồn tại theo từng dòng.
  • CTE (WITH) đặt tên cho các bước trung gian, giúp truy vấn nhiều tầng dễ đọc và bảo trì; nhiều CTE nối tiếp được, CTE sau dùng CTE trước.
  • WITH RECURSIVE xử lý dữ liệu phân cấp/tự tham chiếu: anchor + recursive member + điều kiện dừng.
  • Window functions tính trên nhóm dòng nhưng giữ nguyên số dòng: OVER với PARTITION BY (chia ngăn) và ORDER BY (thứ tự trong ngăn).
  • ROW_NUMBER/RANK/DENSE_RANK khác nhau ở cách xử lý giá trị bằng nhau; SUM() OVER (ORDER BY ...) cho running total; LAG/LEAD so sánh dòng trước/sau.
  • Khác biệt window vs GROUP BY: gộp dòng (GROUP BY) hay giữ chi tiết kèm số liệu nhóm (window).

Tự kiểm tra

  1. Khi nào nên dùng NOT EXISTS thay vì NOT IN, và tại sao NOT IN dễ cho kết quả sai khi có NULL?
  2. Một CTE đệ quy bắt buộc phải có những thành phần nào? Điều gì xảy ra nếu thiếu điều kiện dừng?
  3. Cho cùng dữ liệu có giá trị trùng, hãy mô tả khác biệt kết quả giữa RANK() và DENSE_RANK().
  4. Tại sao không thể lọc trực tiếp WHERE row_number = 1, và cách chuẩn để lấy top-N mỗi nhóm là gì?
  5. SUM(x) OVER (PARTITION BY g) và SUM(x) OVER (PARTITION BY g ORDER BY t) cho kết quả khác nhau thế nào?
  6. Với câu hỏi "lương từng người so với trung bình phòng ban", vì sao window function tiện hơn GROUP BY?

Đọc tiếp

SQL 5 — Thiết kế CSDL & chuẩn hoá.

Bài viết liên quan

SQL
Nổi bật
SQL 6 — Index & Tối ưu truy vấn

Cách index hoạt động (B-Tree), đọc EXPLAIN, seq scan vs index scan và mẫu tối ưu truy vấn.

13 thg 7, 2026 4
SQL
SQL 5 — Thiết kế CSDL & Chuẩn hoá

Khoá, ràng buộc, quan hệ và chuẩn hoá 1NF/2NF/3NF — thiết kế lược đồ đúng từ đầu.

13 thg 7, 2026 4
SQL
SQL 3 — JOIN toàn tập: INNER, LEFT, RIGHT, FULL, SELF, CROSS

Kết nhiều bảng đúng cách: các loại JOIN, bẫy thường gặp và phép hợp tập.

13 thg 7, 2026 3
SQL
SQL 2 — Truy vấn cốt lõi: WHERE, ORDER BY, GROUP BY, HAVING

Lọc, sắp xếp, gộp nhóm và hàm tổng hợp — nền tảng mọi truy vấn phân tích.

13 thg 7, 2026 3