Subquery và CTE giúp biến một yêu cầu nhiều bước thành các phần có tên, có thể chạy và kiểm tra riêng. Bài viết xây báo cáo tăng trưởng doanh thu ngày theo cửa hàng, bắt đầu từ dữ liệu đơn hàng thô rồi tạo lớp hợp lệ, lớp tổng hợp và lớp so sánh kỳ trước.

Mục lục

1. Bài toán thực tế
2. Sản phẩm hoàn thành
3. Kiến thức cần dùng
4. Chuẩn bị môi trường
5. Dữ liệu thực hành
6. Xây dựng từng bước
7. Chương trình hoàn chỉnh
8. Kiểm tra kết quả
9. Lỗi thường gặp
10. Đưa vào thực tế
11. Bài tập thực hành
12. Project mở rộng
13. Tổng kết
14. Tài liệu tham khảo

1. Bài toán thực tế

Một truy vấn báo cáo đang lồng bốn SELECT trong nhau, dùng alias a, b, c và lặp điều kiện status ở nhiều nơi. Mỗi lần thay đổi định nghĩa doanh thu, nhóm phải sửa nhiều đoạn và dễ bỏ sót.

Yêu cầu mới cần tính doanh thu ngày, doanh thu ngày trước và tỷ lệ tăng trưởng. Nếu gom toàn bộ logic vào một SELECT, người mới khó xác định lỗi nằm ở lọc dữ liệu, tổng hợp hay so sánh.

2. Sản phẩm hoàn thành

Sản phẩm là truy vấn daily_store_growth gồm sale_date, store_id, order_count, revenue, previous_revenue và growth_rate.

Mỗi CTE tương ứng một bước nghiệp vụ và có thể được thay bằng SELECT riêng để kiểm tra.

• Phân biệt scalar subquery, correlated subquery và CTE.
• Chia truy vấn thành các lớp có grain rõ ràng.
• Tránh tính lặp cùng biểu thức.
• Hiểu CTE không mặc định là bảng tạm.
• Dùng EXPLAIN để đánh giá cách planner xử lý CTE.

3. Kiến thức cần dùng

Subquery là truy vấn nằm trong một biểu thức, FROM hoặc điều kiện. Scalar subquery phải trả tối đa một giá trị. Correlated subquery tham chiếu dòng của truy vấn ngoài và có thể được đánh giá nhiều lần.

CTE được khai báo bằng WITH và tồn tại trong phạm vi một câu lệnh. Giá trị lớn nhất của CTE là khả năng đặt tên cho ý định: valid_orders rõ hơn một khối SELECT lồng không tên.

Từ PostgreSQL 12, CTE không được tham chiếu nhiều lần có thể được inline. MATERIALIZED buộc tạo kết quả trung gian; NOT MATERIALIZED khuyến khích inline. Chỉ dùng sau khi đọc plan.

4. Chuẩn bị môi trường

Sử dụng orders ở grain một dòng mỗi đơn. Chốt status completed và khoảng ngày trước khi tổng hợp.

Tạo dữ liệu cho ít nhất ba ngày liên tiếp ở hai cửa hàng, trong đó một cửa hàng không có doanh thu một ngày để quan sát LAG và dữ liệu thiếu.

SELECT store_id, order_date::date sale_date, COUNT(*)
FROM orders
WHERE status='completed'
GROUP BY store_id, order_date::date
ORDER BY store_id, sale_date;

5. Dữ liệu thực hành

valid_orders loại đơn không hợp lệ nhưng vẫn giữ grain đơn. daily_sales chuyển grain sang cửa hàng-ngày. compared giữ grain đó và thêm previous_revenue bằng window function.

Không JOIN order_items nếu total_amount trong orders đã là số chốt đáng tin cậy; JOIN không cần thiết làm truy vấn phức tạp và có nguy cơ nhân dòng.

CREATE INDEX IF NOT EXISTS idx_orders_completed_store_date
ON orders (store_id, order_date)
WHERE status='completed';

6. Xây dựng từng bước

Bước 1: Dùng scalar subquery có kiểm soát

Subquery dưới đây trả một giá trị tổng doanh thu toàn hệ thống để so sánh từng cửa hàng. Nó không phụ thuộc dòng ngoài.

SELECT store_id, SUM(total_amount) store_revenue,
       (SELECT SUM(total_amount) FROM orders WHERE status='completed') total_revenue
FROM orders
WHERE status='completed'
GROUP BY store_id;

Kết quả mong đợi: Mỗi cửa hàng có doanh thu riêng và cùng một tổng toàn hệ thống.

Bước 2: Nhận diện correlated subquery

Truy vấn đếm đơn của từng khách dễ đọc nhưng có thể chạy subquery cho nhiều dòng. GROUP BY thường phù hợp hơn khi cần toàn bộ khách.

SELECT c.customer_id, c.customer_name,
       (SELECT COUNT(*) FROM orders o
        WHERE o.customer_id=c.customer_id AND o.status='completed') completed_orders
FROM customers c;

Kết quả mong đợi: Mỗi khách có số đơn completed, kể cả bằng 0.

Bước 3: Tạo CTE dữ liệu hợp lệ

Lớp đầu chỉ chọn cột cần thiết và áp dụng quy tắc nguồn. Không tổng hợp ở đây.

WITH valid_orders AS (
 SELECT order_id,store_id,order_date::date sale_date,total_amount
 FROM orders
 WHERE status='completed' AND total_amount>=0
)
SELECT * FROM valid_orders;

Kết quả mong đợi: Một dòng mỗi đơn hợp lệ.

Bước 4: Thêm lớp tổng hợp

CTE thứ hai nhận đầu vào từ valid_orders, vì vậy điều kiện status không bị lặp.

WITH valid_orders AS (
 SELECT order_id,store_id,order_date::date AS sale_date,total_amount
 FROM orders
 WHERE status='completed' AND total_amount>=0
), daily_sales AS (
 SELECT sale_date,store_id,COUNT(*) AS order_count,SUM(total_amount) AS revenue
 FROM valid_orders
 GROUP BY sale_date,store_id
)
SELECT * FROM daily_sales ORDER BY sale_date,store_id;

Kết quả mong đợi: Một dòng mỗi cửa hàng mỗi ngày.

Bước 5: So sánh với ngày trước

LAG hoạt động trên kết quả đã tổng hợp. NULL ở ngày đầu không phải lỗi mà thể hiện chưa có kỳ trước.

SELECT *, LAG(revenue) OVER (PARTITION BY store_id ORDER BY sale_date) previous_revenue
FROM daily_sales;

Kết quả mong đợi: Mỗi cửa hàng có doanh thu của bản ghi ngày trước hiện diện trong dữ liệu.

Bước 6: Tính tăng trưởng

NULLIF bảo vệ trường hợp previous_revenue bằng 0. Tỷ lệ được giữ dạng số; định dạng phần trăm thuộc lớp hiển thị.

(revenue-previous_revenue)/NULLIF(previous_revenue,0) AS growth_rate

Kết quả mong đợi: Tăng trưởng dạng thập phân hoặc NULL khi không có mẫu số hợp lệ.

Chọn giữa CTE và bảng trung gian

CTE phù hợp khi logic chỉ dùng trong một câu lệnh và dữ liệu trung gian không cần audit. Temporary table hữu ích khi cùng kết quả được dùng nhiều lần trong phiên hoặc cần index tạm. Bảng staging bền vững phù hợp khi cần truy vết qua nhiều job.

Quyết định dựa trên vòng đời, khả năng chạy lại và chi phí. Không biến mọi bước thành bảng vì sẽ tăng dọn dẹp; cũng không ép pipeline dài hàng trăm dòng vào một câu WITH nếu cần checkpoint.

Đọc truy vấn theo grain

Ghi chú bên cạnh mỗi CTE: one row per order, one row per store-day. Nếu một CTE không thể mô tả bằng một câu grain, nó có thể đang trộn nhiều cấp dữ liệu.

Khi daily_sales đã ở store-day, JOIN lại order_items sẽ đưa dữ liệu về grain chi tiết và làm revenue lặp. Grain không chỉ là khái niệm thiết kế; nó là công cụ review từng dòng SQL.

Tối ưu mà không phá khả năng hiểu

Planner có thể inline CTE, vì vậy tách bước để dễ đọc không nhất thiết làm chậm. Khi MATERIALIZED tạo điểm nghẽn, plan sẽ cho thấy scan và lượng dữ liệu trung gian.

Giữ một truy vấn chuẩn cho tính đúng và benchmark trước khi tối ưu. Phiên bản nhanh hơn phải vượt cùng test và reconciliation; giảm runtime không bù được KPI sai.

Viết cùng một bài toán bằng ba cách

Đếm đơn completed theo khách có thể viết bằng correlated subquery, LEFT JOIN + GROUP BY hoặc pre-aggregate CTE. Cả ba có thể đúng nhưng khác về khả năng đọc và plan. So sánh trên cùng dữ liệu giúp hiểu trade-off thay vì học quy tắc tuyệt đối.

Correlated subquery diễn đạt tự nhiên cho một lookup; pre-aggregate thường rõ khi cần nhiều metric. Planner PostgreSQL đôi khi tối ưu lại, vì vậy kết luận hiệu năng phải dựa trên EXPLAIN ANALYZE.

WITH order_counts AS (
 SELECT customer_id,COUNT(*) completed_orders
 FROM orders WHERE status='completed' GROUP BY customer_id
)
SELECT c.customer_id,c.customer_name,COALESCE(o.completed_orders,0) completed_orders
FROM customers c LEFT JOIN order_counts o USING(customer_id);

Recursive CTE qua ví dụ danh mục

Recursive CTE phù hợp dữ liệu phân cấp như category cha-con hoặc sơ đồ tổ chức. Phần anchor chọn nút gốc; phần recursive nối con với kết quả đã tìm. UNION ALL tiếp tục cho đến khi không còn dòng mới.

Phải có điều kiện hoặc cấu trúc dữ liệu ngăn vòng lặp. Trong hệ thống thật, kiểm tra cycle hoặc giới hạn depth để dữ liệu cha-con lỗi không làm truy vấn chạy vô hạn.

WITH RECURSIVE category_tree AS (
 SELECT category_id,parent_id,category_name,0 depth,category_name::text path
 FROM categories WHERE parent_id IS NULL
 UNION ALL
 SELECT c.category_id,c.parent_id,c.category_name,t.depth+1,t.path||' / '||c.category_name
 FROM categories c JOIN category_tree t ON c.parent_id=t.category_id
 WHERE t.depth<10
)
SELECT * FROM category_tree ORDER BY path;

Ngày thiếu và date spine

Báo cáo growth không thể phân biệt ngày không bán với ngày pipeline thiếu nếu chỉ nhìn daily_sales. generate_series tạo calendar; CROSS JOIN stores tạo mọi tổ hợp và LEFT JOIN doanh thu. Thêm cờ has_source_data nếu cần phân biệt freshness.

COALESCE revenue thành 0 chỉ khi nghiệp vụ xác nhận không có giao dịch nghĩa là 0. Nếu bảng nguồn chưa được nạp, 0 sẽ che sự cố.

WITH calendar AS (
 SELECT d::date sale_date FROM generate_series(DATE '2026-09-01',DATE '2026-09-30',INTERVAL '1 day') d
), grid AS (SELECT c.sale_date,s.store_id FROM calendar c CROSS JOIN stores s)
SELECT g.sale_date,g.store_id,COALESCE(d.revenue,0) revenue
FROM grid g LEFT JOIN daily_sales d USING(sale_date,store_id);

CTE dùng cho thao tác ghi

WITH không chỉ dành cho SELECT. Data-modifying CTE có thể INSERT và trả khóa bằng RETURNING cho bước kế tiếp. Tuy nhiên, câu lệnh nhiều thao tác khó debug hơn; transaction với nhiều statement đôi khi dễ vận hành hơn.

Ví dụ tạo customer mới rồi order tham chiếu khóa vừa sinh. Email unique bảo vệ trùng; ứng dụng thật cần xử lý conflict rõ.

WITH new_customer AS (
 INSERT INTO customers(email,customer_name) VALUES(:email,:customer_name)
 RETURNING customer_id
)
INSERT INTO orders(customer_id,order_date,status)
SELECT customer_id,now(),'pending' FROM new_customer
RETURNING order_id;

Checklist review CTE

Mỗi CTE phải có tên có nghĩa, grain mô tả được và không lặp logic. Cột cần thiết được chọn rõ; điều kiện lọc đặt ở lớp phù hợp; SELECT cuối chỉ trình bày metric thay vì chứa thêm nghiệp vụ lớn.

Nếu một CTE được dùng một lần nhưng chỉ đổi tên cột, cân nhắc gộp. Nếu một CTE dài hàng trăm dòng, cân nhắc tách view hoặc model có test riêng. Mục tiêu là ranh giới trách nhiệm, không phải số lượng WITH.

EXPLAIN (ANALYZE,BUFFERS,VERBOSE)
WITH valid_orders AS (
 SELECT order_id,store_id,order_date,total_amount FROM orders WHERE status='completed'
)
SELECT store_id,SUM(total_amount) FROM valid_orders GROUP BY store_id;

7. Chương trình hoàn chỉnh

Truy vấn hoàn chỉnh có ba lớp và một SELECT cuối. Tên CTE phản ánh grain hoặc hành động, tránh step1 và step2.

Nếu ngày thiếu phải được xem là doanh thu 0, cần tạo calendar và CROSS JOIN stores trước khi LAG; LAG chỉ nhìn dòng tồn tại, không tự tạo ngày.

WITH valid_orders AS (
 SELECT order_id,store_id,order_date::date sale_date,total_amount
 FROM orders WHERE status='completed' AND total_amount>=0
), daily_sales AS (
 SELECT sale_date,store_id,COUNT(*) order_count,SUM(total_amount) revenue
 FROM valid_orders GROUP BY sale_date,store_id
), compared AS (
 SELECT *,LAG(revenue) OVER(PARTITION BY store_id ORDER BY sale_date) previous_revenue
 FROM daily_sales
)
SELECT *,ROUND(100*(revenue-previous_revenue)/NULLIF(previous_revenue,0),2) growth_pct
FROM compared ORDER BY sale_date,store_id;

8. Kiểm tra kết quả

Chạy nội dung từng CTE bằng cách thay SELECT cuối. Xác nhận valid_orders không có total_amount âm, daily_sales duy nhất theo sale_date + store_id.

Tính tay một cửa hàng trong ba ngày. Kiểm tra ngày đầu growth NULL và ngày trước bằng 0 không gây lỗi.

WITH result AS (
 SELECT order_date::date AS sale_date,store_id,COUNT(*) AS order_count,SUM(total_amount) AS revenue
 FROM orders WHERE status='completed' AND total_amount>=0
 GROUP BY order_date::date,store_id
)
SELECT * FROM result WHERE revenue<0>

9. Lỗi thường gặp

• CTE quá nhiều bước nhỏ: Truy vấn dài hơn nhưng không rõ hơn.
• Tên a, b, temp: Không truyền đạt grain hoặc mục đích.
• Giả định CTE luôn nhanh: Planner và dữ liệu quyết định; phải đọc EXPLAIN.
• Correlated subquery trên bảng lớn: Có thể lặp công việc cho từng dòng.
• LAG trên dữ liệu thiếu ngày: So sánh với dòng trước, không nhất thiết ngày trước.

10. Đưa vào thực tế

View phù hợp khi logic cần dùng lại và chi phí tính chấp nhận được. Materialized view phù hợp khi kết quả tốn kém và độ trễ refresh được phép. Bảng mart thích hợp khi cần incremental load và audit.

Dùng EXPLAIN (ANALYZE, BUFFERS) trên bản sao dữ liệu phù hợp. Không chạy ANALYZE cho câu lệnh ghi trên production nếu chưa hiểu tác động.

11. Bài tập thực hành

1. Viết lại correlated subquery đếm đơn bằng LEFT JOIN.
2. Thêm calendar để ngày thiếu có revenue bằng 0.
3. So sánh CTE MATERIALIZED và NOT MATERIALIZED.
4. Tạo view daily_store_growth.
5. Thêm tăng trưởng so với cùng ngày tuần trước bằng LAG(...,7).

12. Project mở rộng

Xây báo cáo tăng trưởng tuần và tháng từ một date spine. Tách lớp metric khỏi lớp trình bày để dashboard dùng cùng định nghĩa.

13. Tổng kết

CTE tốt không chỉ làm đẹp SQL mà biến logic thành các điểm kiểm tra. Mỗi lớp nên có grain và trách nhiệm rõ.

Sau bài này, Window Function sẽ được học sâu hơn để xếp hạng, lũy kế và so sánh mà không làm mất chi tiết.

14. Tài liệu tham khảo

• WITH Queries
• Subquery Expressions
• EXPLAIN

TechData.AI - Leading the Future.
Tham khảo các khoá học theo link: https://techdata.ai/techdata-ai-course/
Hoàng Minh.