Subquery trong FROM

Bạn sẽ học được

• Dùng subquery trong FROM như một bảng tạm có tên.
• Vì sao derived table bắt buộc phải có bí danh.
• Tổng hợp hai tầng: tính chỉ số theo nhóm, rồi tổng hợp tiếp trên kết quả đó.
• Nối derived table trở lại bảng gốc để lấy thêm cột.
• Biết khi nào nên đổi derived table thành CTE.

Khái niệm cốt lõi

FROM không chỉ nhận tên bảng — nó nhận bất kỳ thứ gì trả về một tập dòng. Đặt một câu SELECT vào FROM, bạn có bảng tạm (derived table) chỉ tồn tại trong phạm vi câu lệnh:

SELECT ROUND(AVG(items), 4) AS trung_binh_mon
FROM (
  SELECT order_id, SUM(quantity) AS items
  FROM order_items
  GROUP BY order_id
) t;

Kết quả: 3.3750. Đọc từ trong ra ngoài: câu con gom 29 dòng hàng thành 16 dòng — mỗi đơn một dòng với tổng số lượng. Câu ngoài lấy trung bình 16 con số đó: 54 món chia 16 đơn = 3,375.

Vì sao phải có hai tầng? Vì AVG(SUM(quantity)) là lồng hai hàm tổng hợp trong cùng một lớp — SQL không cho phép. Bảng tạm "hạ cấp" kết quả SUM thành dữ liệu thường, để lớp ngoài tính AVG trên đó. Mọi câu hỏi dạng "trung bình của tổng", "tổng của đếm", "lớn nhất của trung bình" đều cần cấu trúc này.

Bí danh t ở cuối là bắt buộc. PostgreSQL báo subquery in FROM must have an alias nếu thiếu. Bí danh không chỉ để thoả cú pháp — nó là tên bạn dùng để tham chiếu cột: t.items, t.order_id.

Cột của bảng tạm do chính bạn đặt tên bằng AS. Hãy đặt tên có nghĩa: items, tong, spend — vì lớp ngoài sẽ đọc chúng như cột thật.

Bảng tạm thường được dùng theo ba cách:

Một điểm dễ nhầm: bảng tạm trong FROM không tương quan với câu ngoài. Nó không thấy bí danh của bảng ngoài:

-- LỖI: câu con không thấy o.id
SELECT o.id
FROM orders o
JOIN (SELECT SUM(quantity) FROM order_items WHERE order_id = o.id) t ON true;

Muốn câu con thấy cột của bảng đứng trước trong FROM, phải dùng LATERAL:

SELECT o.id, t.so_mon
FROM orders o
JOIN LATERAL (
  SELECT SUM(quantity) AS so_mon FROM order_items oi WHERE oi.order_id = o.id
) t ON true;

LATERAL tương đương vòng lặp: với mỗi đơn, chạy câu con một lần. Nó hữu ích cho "top N mỗi nhóm", nhưng với bài toán đơn giản thì JOIN thường thường hoặc CTE dễ đọc hơn.

Cuối cùng, đừng lạm dụng bảng tạm lồng nhau. Ba tầng ngoặc là dấu hiệu nên đặt tên bằng CTE:

-- Khó đọc
SELECT * FROM (SELECT * FROM (SELECT * FROM orders) a) b;

CTE cho cùng kết quả với cấu trúc phẳng, đọc từ trên xuống — nội dung của bài 4.

Ví dụ chạy được

Đếm số dòng hàng và tổng số lượng mỗi đơn, rồi lấy năm đơn nhiều món nhất:

SELECT o.id, o.order_date, t.lines, t.qty
FROM orders o
JOIN (
  SELECT order_id, COUNT(*) AS lines, SUM(quantity) AS qty
  FROM order_items
  GROUP BY order_id
) t ON t.order_id = o.id
ORDER BY t.qty DESC, o.id
LIMIT 5;

Năm dòng: đơn 13 (3 dòng hàng, 7 món); đơn 9 (2 dòng, 6 món); đơn 4, 7, 8 (mỗi đơn 4 món). Đơn 13 là đơn duy nhất có ba dòng hàng — 11 đơn có hai dòng, 4 đơn có một dòng.

Tách hai khái niệm: COUNT(*) đếm dòng hàng (mỗi sản phẩm trong đơn), SUM(quantity) đếm số món. Đơn 13 có 3 dòng nhưng 7 món vì có dòng mua số lượng lớn. Đây chính là lý do phải gom nhóm trước khi tổng hợp tiếp.

Lọc trên kết quả tổng hợp:

SELECT COUNT(*) AS so_don_lon
FROM (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
) t
WHERE t.total > 1000000;

Kết quả 13. Ba đơn dưới ngưỡng là đơn 5 (640.000), đơn 10 (960.000) và đơn 14 (580.000). Điều kiện total > 1000000 không thể đặt trong câu con vì total chỉ tồn tại sau khi gom nhóm — nó thuộc lớp ngoài.

Tính trung bình giá trị đơn, rồi đếm số đơn trên mức đó — hai tầng tổng hợp thật sự:

SELECT COUNT(*) AS so_don_tren_trung_binh
FROM (
  SELECT o.id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id
) t
WHERE t.total > (
  SELECT AVG(total) FROM (
    SELECT SUM(quantity * unit_price) AS total
    FROM order_items GROUP BY order_id
  ) a
);

Kết quả 7, với trung bình 1.951.875. Truy vấn con trong WHERE lại chứa một bảng tạm khác — cấu trúc ba tầng nhưng mỗi tầng một nhiệm vụ rõ ràng. Khi viết xong, nếu thấy khó đọc, hãy chuyển sang CTE ở bài sau.

Gom nhóm theo khách từ dữ liệu đã gom theo đơn:

SELECT c.name, COUNT(*) AS so_don, ROUND(AVG(t.total), 0) AS trung_binh_don
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items GROUP BY order_id
) t ON t.order_id = o.id
GROUP BY c.id, c.name
ORDER BY trung_binh_don DESC
LIMIT 3;

Ba dòng đầu: Minh Dương (1 đơn, trung bình 4.030.000); Giang Đặng (2 đơn, 2.745.000); Bình Trần (3 đơn, 2.250.000). Lưu ý một khách có trung bình cao chưa chắc tổng cao — Minh Dương chỉ mua một lần nhưng đơn rất lớn, còn Bình Trần mua ba lần nên tổng cao nhất dù đơn nhỏ hơn.

Thực hành

1. Tính số món trung bình mỗi đơn. Đáp số 3,375.
2. Đếm số đơn có tổng trên 1.000.000. Đáp số 13.
3. Tìm khách có tổng chi tiêu cao nhất, dùng bảng tạm gom theo khách rồi ORDER BY ... LIMIT 1. Đáp số Bình Trần, 6.750.000.
4. Viết lại câu ở mục "Ví dụ chạy được" cuối cùng bằng LATERAL và so sánh kết quả. Hai cách phải cho cùng con số.

Lỗi thường gặp

Lỗi cuối cùng đáng bàn: đặt bộ lọc bên trong câu con khi có thể sẽ giảm số dòng trước khi gom nhóm, nhanh hơn nhiều so với lọc ở ngoài. Ví dụ WHERE o.status = 'completed' nên nằm trong câu con nếu mọi phép tính sau đó chỉ quan tâm đơn hoàn tất.

Tóm tắt

• Subquery trong FROM tạo bảng tạm; bí danh là bắt buộc.
• Dùng để tổng hợp hai tầng, lọc sau gom nhóm, hoặc nối lại bảng gốc.
• Đặt tên cột trong câu con bằng AS — lớp ngoài đọc chúng như cột thật.
• Bảng tạm trong FROM không thấy bảng ngoài; cần tương quan thì dùng LATERAL.
• Ba tầng lồng nhau là lúc nên chuyển sang CTE.