PostgreSQL 4 — SQL nâng cao: CTE, window, UPSERT, LATERAL

Có một ranh giới vô hình giữa người "biết viết SQL" và người "dùng SQL để giải quyết vấn đề". Ranh giới đó không nằm ở việc thuộc lòng thêm vài từ khoá, mà ở chỗ bạn có nhận ra được lúc nào một bài toán tưởng chừng phải viết vòng lặp trong tầng ứng dụng thực chất chỉ là một câu truy vấn. Bảng xếp hạng, running total, "giao dịch mới nhất mỗi tài khoản", "duyệt cây phòng ban", "báo cáo tổng hợp đa cấp" — tất cả đều làm được trong SQL thuần, và PostgreSQL cung cấp bộ công cụ đặc biệt mạnh cho chúng.

Bài này đi qua những tính năng SQL nâng cao mà PostgreSQL hỗ trợ tốt (nhiều trong số đó là đặc trưng riêng): CTE và recursive CTE, window functions, LATERAL join, GROUPING SETS/ROLLUP/CUBE, DISTINCT ON, UPSERT với ON CONFLICT, RETURNING, và generate_series. Toàn bộ ví dụ dùng schema ngân hàng nhỏ để bạn dễ hình dung và chạy thử ngay trong SQL Builder.

Schema mẫu dùng xuyên suốt: customers(id, full_name, city, created_at), accounts(id, customer_id, account_no, balance, currency), transactions(id, account_id, amount, kind, created_at), employees(id, name, department_id, salary, hired_at), departments(id, name).

CTE — WITH: đặt tên cho một bước trung gian

CTE (Common Table Expression) là mệnh đề WITH cho phép bạn đặt tên cho một truy vấn con và tham chiếu nó như một bảng tạm trong câu chính. Giá trị lớn nhất của CTE là tính đọc được: thay vì lồng subquery ba tầng khó dò, bạn tách thành các bước có tên rõ ràng, đọc từ trên xuống như một đoạn văn.

WITH large_tx AS (
    SELECT account_id, amount
    FROM transactions
    WHERE amount > 1000000
)
SELECT account_id, count(*) AS so_giao_dich_lon
FROM large_tx
GROUP BY account_id;

Inline hay materialize?

Đây là điểm mà hành vi của PostgreSQL đã thay đổi và bạn cần nắm rõ. Trước PostgreSQL 12, mọi CTE đều được materialize — tức là Postgres tính xong kết quả CTE, lưu vào một bảng tạm, rồi mới dùng. Điều này tạo ra một "hàng rào tối ưu" (optimization fence): planner không thể đẩy điều kiện WHERE từ câu ngoài vào trong CTE, nên đôi khi CTE trở thành nút thắt hiệu năng.

Từ PostgreSQL 12 trở đi, planner có thể inline CTE — nối nó vào câu chính và tối ưu chung — khi CTE thoả các điều kiện: không có tác dụng phụ (không phải INSERT/UPDATE/DELETE), không dùng đệ quy, và được tham chiếu đúng một lần. Khi đó predicate từ ngoài có thể được đẩy vào, index được dùng, tốc độ cải thiện đáng kể.

Bạn có thể ép hành vi bằng từ khoá:

-- Ép materialize (giữ hàng rào tối ưu, tính một lần dùng nhiều lần)
WITH stats AS MATERIALIZED (
    SELECT account_id, sum(amount) AS tong
    FROM transactions
    GROUP BY account_id
)
SELECT * FROM stats WHERE tong > 5000000;

-- Ép inline (cho planner tự do đẩy điều kiện vào)
WITH recent AS NOT MATERIALIZED (
    SELECT * FROM transactions
)
SELECT * FROM recent WHERE account_id = 42;

Quy tắc thực dụng: nếu CTE được tham chiếu nhiều lần và việc tính lại tốn kém, MATERIALIZED giúp tính một lần. Nếu CTE chỉ để cho dễ đọc và bạn muốn planner tối ưu như thể viết inline, để mặc định (Postgres 12+ tự inline) hoặc ép NOT MATERIALIZED. Chủ đề này liên quan chặt tới viết lại truy vấn để planner dùng được index — một CTE materialize vô tình có thể chặn mất index scan.

Recursive CTE — WITH RECURSIVE: duyệt cây và sinh dãy

WITH RECURSIVE là công cụ cho những bài toán có cấu trúc đệ quy: cây phòng ban cha–con, chuỗi giao dịch liên kết, cấu trúc thư mục, hoặc đơn giản là sinh một dãy số/ngày.

Một recursive CTE luôn có hai phần nối bằng UNION [ALL]:

1. Anchor (mỏ neo): truy vấn khởi tạo, chạy một lần.
2. Recursive term: truy vấn tham chiếu lại chính CTE, chạy lặp cho tới khi không còn dòng mới.

Ví dụ kinh điển — sinh dãy số:

▶ Chạy được trong SQL Builder

WITH RECURSIVE t(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n<5) SELECT * FROM t

Anchor cho n=1. Mỗi vòng lặp lấy các dòng vừa sinh, cộng 1, cho tới khi n<5 sai. Kết quả là 1,2,3,4,5.

Duyệt cây phòng ban

Giả sử departments có thêm cột parent_id (phòng ban cha). Để lấy toàn bộ cây con dưới một phòng gốc, kèm độ sâu:

WITH RECURSIVE dept_tree AS (
    -- Anchor: phòng gốc
    SELECT id, name, parent_id, 1 AS depth
    FROM departments
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive: nối con vào cha đã tìm được
    SELECT d.id, d.name, d.parent_id, dt.depth + 1
    FROM departments d
    JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT repeat('  ', depth - 1) || name AS cay, depth
FROM dept_tree
ORDER BY depth;

Sơ đồ dưới minh hoạ cách recursive CTE "loang" từ gốc xuống lá qua từng vòng lặp:

Cảnh báo an toàn: nếu dữ liệu có chu trình (A là con của B, B lại là con của A), recursive CTE sẽ lặp vô hạn. Từ PostgreSQL 14 có cú pháp ... CYCLE col SET is_cycle USING path để phát hiện và dừng; cách phổ thông hơn là tự mang theo một mảng path và kiểm tra NOT (d.id = ANY(path)) trước khi nối.

Window functions — tính toán "nhìn ngang" mà không gộp dòng

Đây có lẽ là nhóm tính năng đáng học nhất. GROUP BY gộp nhiều dòng thành một; window function tính giá trị tổng hợp nhưng giữ nguyên từng dòng. Bạn vừa thấy từng giao dịch, vừa thấy tổng luỹ kế của nó — điều GROUP BY không làm được.

Cú pháp cốt lõi là func() OVER (PARTITION BY ... ORDER BY ... frame):

• PARTITION BY chia dữ liệu thành các nhóm (như "mỗi tài khoản"); window function tính riêng trong từng nhóm.
• ORDER BY sắp thứ tự bên trong nhóm — quan trọng với các hàm phụ thuộc thứ tự (ranking, running total, LAG/LEAD).
• Frame (ROWS/RANGE BETWEEN ...) xác định "cửa sổ" dòng nào được tính cho mỗi dòng hiện tại.

Running total (tổng luỹ kế)

▶ Chạy được trong SQL Builder

SELECT account_id, amount, SUM(amount) OVER (PARTITION BY account_id ORDER BY id) FROM transactions

Với mỗi giao dịch, cột sum là tổng của nó và tất cả giao dịch trước đó trong cùng tài khoản (theo thứ tự id). Đây chính là running balance — cực kỳ hữu ích cho sao kê.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

Ba hàm này khác nhau ở cách xử lý giá trị bằng nhau (tie):

• ROW_NUMBER(): đánh số duy nhất 1,2,3,4 — không quan tâm tie.
• RANK(): giá trị bằng nhau nhận cùng hạng, rồi nhảy số (1,1,3,4).
• DENSE_RANK(): giá trị bằng nhau nhận cùng hạng, không nhảy (1,1,2,3).

SELECT name, department_id, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS drnk
FROM employees;

Để lấy top-N mỗi nhóm (ví dụ 3 nhân viên lương cao nhất mỗi phòng), bọc lại và lọc theo rn <= 3:

SELECT * FROM (
    SELECT name, department_id, salary,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees
) x
WHERE rn <= 3;

Lưu ý: không thể dùng window function trực tiếp trong WHERE (vì WHERE chạy trước khi window được tính), nên phải bọc subquery/CTE rồi mới lọc.

LAG / LEAD — nhìn dòng trước / sau

LAG(col, n) lấy giá trị cột ở dòng lùi n bước; LEAD lấy dòng tiến. Rất hợp để tính chênh lệch giữa các kỳ:

SELECT account_id, created_at, amount,
       LAG(amount) OVER (PARTITION BY account_id ORDER BY created_at) AS amount_truoc,
       amount - LAG(amount) OVER (PARTITION BY account_id ORDER BY created_at) AS chenh_lech
FROM transactions;

Frame: ROWS vs RANGE

Khi có ORDER BY mà không ghi frame, mặc định là RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Sự khác biệt tinh tế:

• ROWS đếm theo số dòng vật lý. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = trung bình trượt 3 dòng.
• RANGE gộp theo giá trị của cột ORDER BY — mọi dòng có cùng giá trị được coi là một "peer" và tính chung. Điều này gây bất ngờ khi có giá trị trùng.

-- Trung bình trượt 3 giao dịch gần nhất trong mỗi tài khoản
SELECT account_id, created_at, amount,
       AVG(amount) OVER (
           PARTITION BY account_id ORDER BY created_at
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS ma3
FROM transactions;

Vì RANGE có bẫy tie, khi làm running total hay moving average bạn nên ghi rõ ROWS để tránh kết quả khó hiểu. Các hàm khác đáng biết: NTILE(n) chia đều thành n nhóm phần trăm (dùng cho phân vị), FIRST_VALUE/LAST_VALUE lấy giá trị đầu/cuối frame (nhớ mở rộng frame tới UNBOUNDED FOLLOWING khi cần LAST_VALUE toàn nhóm).

WINDOW clause — tái dùng định nghĩa cửa sổ

Nếu nhiều hàm dùng chung một OVER (...), đặt tên một lần cho gọn:

SELECT account_id, amount,
       sum(amount) OVER w,
       avg(amount) OVER w
FROM transactions
WINDOW w AS (PARTITION BY account_id ORDER BY id);

DISTINCT ON — lấy một dòng đại diện mỗi nhóm

DISTINCT ON (cols) là đặc trưng riêng của PostgreSQL, không có trong SQL chuẩn. Nó giữ dòng đầu tiên cho mỗi tổ hợp cols, theo đúng thứ tự ORDER BY. Đây là cách gọn nhất để trả lời "lấy bản ghi mới nhất mỗi nhóm".

▶ Chạy được trong SQL Builder

SELECT DISTINCT ON (account_id) account_id, amount FROM transactions ORDER BY account_id, created_at DESC

Điểm mấu chốt: ORDER BY phải bắt đầu bằng chính các cột trong DISTINCT ON, sau đó tới cột quyết định dòng nào được chọn (created_at DESC = mới nhất). Đây là mẫu "latest per group" ngắn nhất trong Postgres, gọn hơn cả window function nếu bạn chỉ cần đúng một dòng mỗi nhóm.

LATERAL join — subquery được nhìn thấy cột bảng bên trái

Bình thường một subquery trong FROM không được tham chiếu cột của bảng đứng trước nó. LATERAL phá bỏ giới hạn đó: subquery bên phải được phép dùng cột của bảng bên trái, và nó được chạy lại cho từng dòng bên trái. Đây chính là công cụ chuẩn cho bài toán top-N mỗi nhóm khi N > 1 hoặc khi logic phức tạp.

Lấy giao dịch mới nhất của mỗi tài khoản:

▶ Chạy được trong SQL Builder

SELECT a.account_no, t.* FROM accounts a JOIN LATERAL (SELECT * FROM transactions x WHERE x.account_id=a.id ORDER BY x.created_at DESC LIMIT 1) t ON true

Với mỗi dòng accounts a, subquery bên phải tìm giao dịch mới nhất của đúng a.id. ON true vì điều kiện nối đã nằm trong subquery. Muốn top-3 thay vì top-1? Chỉ cần đổi LIMIT 1 thành LIMIT 3 — điều mà DISTINCT ON không làm được.

Vài lưu ý:

• Dùng JOIN LATERAL ... ON true (giữ mọi dòng trái, kể cả tài khoản không có giao dịch nếu là LEFT JOIN LATERAL) hoặc CROSS JOIN LATERAL (loại dòng trái nếu subquery rỗng).
• LATERAL phát huy mạnh nhất khi kết hợp với index trên (account_id, created_at DESC) — mỗi lần chạy subquery chỉ là một index scan nhỏ.

GROUPING SETS / ROLLUP / CUBE — tổng hợp đa cấp trong một truy vấn

Thay vì chạy nhiều query GROUP BY rồi UNION, ba từ khoá này cho bạn nhiều mức tổng hợp trong một câu.

ROLLUP(a, b) tạo các mức: (a,b), (a), và () (tổng chung) — phù hợp phân cấp (năm → quý → tháng, hoặc phòng → nhân viên).

▶ Chạy được trong SQL Builder

SELECT department_id, count(*) FROM employees GROUP BY ROLLUP(department_id)

Kết quả có thêm một dòng cuối với department_id là NULL — đó là tổng toàn bộ. Muốn phân biệt dòng tổng với dòng có department_id thực sự là NULL, dùng hàm GROUPING(department_id) (trả 1 nếu đây là dòng tổng hợp).

Phân biệt ba anh em:

• ROLLUP(a,b) → (a,b), (a), () — phân cấp một chiều.
• CUBE(a,b) → mọi tổ hợp: (a,b), (a), (b), () — phân tích chéo mọi chiều.
• GROUPING SETS ((a,b),(a),()) → bạn tự liệt kê chính xác các mức muốn.

SELECT department_id, city, count(*), avg(salary)
FROM employees e JOIN customers c ON false  -- minh hoạ cú pháp
GROUP BY CUBE(department_id, city);

Với báo cáo BI (doanh số theo vùng × sản phẩm × tháng, kèm subtotal và grand total), ROLLUP/CUBE gộp việc mà trước đây phải nhiều query hoặc xử lý ở tầng ứng dụng.

UPSERT — INSERT ... ON CONFLICT (DML, chỉ minh hoạ)

Lưu ý: Các ví dụ dưới đây là DML (thay đổi dữ liệu). SQL Builder chỉ chạy SELECT/WITH/EXPLAIN, nên chúng không chạy được trong sandbox — chỉ mang tính minh hoạ.

UPSERT nghĩa là "insert nếu chưa có, update nếu đã có". PostgreSQL làm điều này qua mệnh đề ON CONFLICT, dựa trên một ràng buộc unique/primary key.

-- Nếu account_no đã tồn tại thì cộng dồn balance, chưa có thì thêm mới
INSERT INTO accounts (customer_id, account_no, balance, currency)
VALUES (1, 'ACC-001', 500000, 'VND')
ON CONFLICT (account_no)
DO UPDATE SET balance = accounts.balance + EXCLUDED.balance;

Vài điểm cần hiểu:

• ON CONFLICT (account_no) chỉ tới cột (hoặc constraint) gây xung đột — cột đó phải có unique/primary key.
• EXCLUDED là "bảng ảo" chứa dòng đáng lẽ được insert — dùng để lấy giá trị mới trong nhánh DO UPDATE.
• DO NOTHING bỏ qua lặng lẽ nếu trùng (hữu ích cho insert idempotent, nạp dữ liệu lặp lại an toàn):

INSERT INTO customers (id, full_name, city)
VALUES (100, 'Nguyen Van A', 'Ha Noi')
ON CONFLICT (id) DO NOTHING;

• Có thể thêm WHERE vào DO UPDATE để chỉ cập nhật khi thoả điều kiện.

RETURNING — lấy lại dòng vừa ghi (DML, chỉ minh hoạ)

Cũng là DML, không chạy trong sandbox.

RETURNING cho phép INSERT/UPDATE/DELETE trả về ngay các cột của dòng vừa tác động — tiết kiệm một round-trip query. Cực hữu ích để lấy id tự sinh (serial/identity) ngay sau khi insert:

INSERT INTO transactions (account_id, amount, kind, created_at)
VALUES (1, 250000, 'deposit', now())
RETURNING id, created_at;

Kết hợp UPSERT + RETURNING là mẫu rất mạnh: ghi xong, biết ngay dòng kết quả cuối cùng:

INSERT INTO accounts (customer_id, account_no, balance, currency)
VALUES (2, 'ACC-002', 1000000, 'VND')
ON CONFLICT (account_no) DO UPDATE SET balance = EXCLUDED.balance
RETURNING id, account_no, balance;

Bạn còn có thể đưa RETURNING vào một CTE để dùng tiếp trong cùng câu — mẫu "data-modifying CTE":

WITH moved AS (
    DELETE FROM transactions WHERE created_at < '2020-01-01'
    RETURNING *
)
INSERT INTO transactions_archive SELECT * FROM moved;

generate_series, FILTER, VALUES — những mảnh ghép nhỏ mà đắt giá

generate_series — sinh dãy số và ngày

▶ Chạy được trong SQL Builder

SELECT * FROM generate_series(1,5)

generate_series sinh một tập dòng — số nguyên, số thực với bước tuỳ ý, hoặc chuỗi ngày/giờ. Ứng dụng kinh điển là tạo khung thời gian đầy đủ rồi LEFT JOIN dữ liệu thật vào, để những ngày không có giao dịch vẫn hiện ra với giá trị 0 (thay vì bị mất dòng):

SELECT d::date AS ngay, count(t.id) AS so_gd
FROM generate_series('2026-01-01'::date, '2026-01-07'::date, interval '1 day') AS d
LEFT JOIN transactions t ON t.created_at::date = d::date
GROUP BY d
ORDER BY d;

FILTER — aggregate có điều kiện, sạch hơn CASE

Mệnh đề FILTER (WHERE ...) gắn vào hàm tổng hợp, chỉ tính các dòng thoả điều kiện. Nó thay cho mẹo sum(CASE WHEN ... THEN 1 ELSE 0 END) cũ kỹ, đọc rõ hơn nhiều:

▶ Chạy được trong SQL Builder

SELECT account_id, count(*) AS tong, count(*) FILTER (WHERE amount > 1000000) AS gd_lon FROM transactions GROUP BY account_id

VALUES list — bảng hằng ngay trong truy vấn

VALUES không chỉ dùng cho INSERT; nó tạo được một bảng tạm nhỏ ngay trong FROM, tiện để nối bảng tra cứu hoặc map giá trị:

SELECT t.kind, m.nhan
FROM transactions t
JOIN (VALUES ('deposit','Nạp'), ('withdraw','Rút')) AS m(kind, nhan)
  ON t.kind = m.kind;

Use case thực tế

1. Báo cáo xếp hạng + running total (window functions). Bài toán "sao kê tài khoản có số dư luỹ kế và xếp hạng giao dịch lớn nhất tháng" gói gọn trong một query: SUM(amount) OVER (PARTITION BY account_id ORDER BY created_at ROWS UNBOUNDED PRECEDING) cho số dư chạy, kèm RANK() OVER (PARTITION BY account_id ORDER BY amount DESC) để đánh dấu giao dịch lớn nhất. Không cần vòng lặp ở tầng ứng dụng, không cần nhiều lượt query — planner đọc bảng một lần rồi tính tất cả.

2. Top-N mỗi nhóm bằng LATERAL. Màn hình "chi tiết tài khoản" thường cần 5 giao dịch gần nhất của mỗi tài khoản đang hiển thị. Nếu code ứng dụng lặp qua từng tài khoản rồi query riêng, bạn dính N+1 query (xem viết lại truy vấn). Thay vào đó, một LEFT JOIN LATERAL (... ORDER BY created_at DESC LIMIT 5) ON true lấy tất cả trong một lượt, và với index (account_id, created_at DESC) mỗi subquery chỉ là một index scan cực rẻ.

3. Nạp dữ liệu idempotent bằng UPSERT. Job đồng bộ chạy lại nhiều lần (ETL, webhook retry) cần "ghi mà không nhân đôi". INSERT ... ON CONFLICT (natural_key) DO UPDATE biến thao tác thành idempotent; thêm RETURNING để biết ngay dòng nào vừa được tạo/cập nhật mà không cần query lại.

4. Báo cáo tổng hợp đa cấp. Dashboard tài chính cần "doanh số theo phòng, kèm subtotal mỗi phòng và grand total toàn công ty". GROUP BY ROLLUP(department_id) trả cả chi tiết lẫn dòng tổng trong một truy vấn, tránh việc tính subtotal riêng ở BI tool.

5. Duyệt phân cấp bằng recursive CTE. Tính "tổng chi phí của một phòng ban và mọi phòng con của nó" đòi hỏi đi hết cây con — WITH RECURSIVE gom toàn bộ id hậu duệ rồi tổng hợp, thay cho nhiều lượt query đệ quy ở tầng ứng dụng.

Những kỹ thuật này thường dùng dữ liệu bán cấu trúc — nếu bạn lưu cấu hình/metadata trong cột JSONB, hãy xem JSONB trong PostgreSQL; còn khi logic phức tạp cần đóng gói phía server (trigger, hàm xử lý), PL/pgSQL là bước tiếp theo.

Ghi nhớ

• CTE giúp câu SQL dễ đọc; nhớ hành vi inline (Postgres 12+) vs materialize và biết ép MATERIALIZED/NOT MATERIALIZED khi cần — CTE materialize vô tình có thể chặn index.
• Recursive CTE = anchor + recursive term nối bằng UNION ALL; luôn nghĩ tới chống chu trình (mảng path hoặc CYCLE) khi duyệt đồ thị.
• Window functions tính tổng hợp mà giữ nguyên dòng; ghi rõ ROWS cho running total/moving average để tránh bẫy RANGE với giá trị trùng; không lọc window trực tiếp trong WHERE — phải bọc subquery.
• DISTINCT ON là cách ngắn nhất lấy "một dòng mới nhất mỗi nhóm"; ORDER BY phải mở đầu bằng đúng cột trong DISTINCT ON.
• LATERAL cho top-N mỗi nhóm (N tuỳ ý) và diệt N+1; kết hợp index phù hợp để mỗi lần chạy là index scan.
• ROLLUP/CUBE/GROUPING SETS gộp nhiều mức tổng hợp trong một query; dùng GROUPING() để nhận diện dòng tổng.
• UPSERT (ON CONFLICT DO UPDATE/DO NOTHING) cần một ràng buộc unique; EXCLUDED mang giá trị định insert. RETURNING trả dòng vừa ghi — cả hai là DML, không chạy trong SQL Builder.
• generate_series dựng khung số/ngày cho báo cáo không thủng dòng; FILTER làm aggregate có điều kiện sạch hơn CASE; VALUES tạo bảng hằng ngay trong FROM.