Truy vấn con ② — Bảng dẫn xuất, truy vấn con SELECT, CREATE / INSERT SELECT

Dữ liệu dùng trong bài viết này — employee / department / sales

Lần trước bạn đã dùng một truy vấn con trong WHERE.

Trong bài viết này bạn sẽ đi qua bốn ứng dụng lần lượt: đặt một truy vấn con trong mệnh đề `FROM` (một bảng dẫn xuất), viết một cái trong cột `SELECT`, kết hợp nó với `CASE`, và xây dựng một bảng mới từ kết quả của một truy vấn con.

Dữ liệu là ba bảng dữ liệu nhân viên.

Dùng employee (30 nhân viên), department (6 phòng ban), và sales (50 dòng doanh số), bạn sẽ xây dựng một bảng tổng hợp theo từng phòng ban và xếp tổng doanh số của mỗi nhân viên thành một cột.

Trước khi bắt đầu các bài tập, hãy xem định nghĩa cột và dữ liệu mẫu của ba bảng mà bài viết này dùng — employee / department / sales.

① Chạy PRAGMA table_info(...) để kiểm tra định nghĩa cột của cả ba bảng.

② Chạy SELECT * FROM table_name LIMIT 5; để xem trước 5 hàng đầu tiên của mỗi bảng.

Bảng dẫn xuất trong FROM — coi kết quả truy vấn con như một bảng đơn

Một bảng dẫn xuất là cách viết một truy vấn con trong mệnh đề FROM sao cho kết quả của nó được coi như một bảng ảo đơn.

Bạn có thể xây dựng "lương trung bình theo từng phòng ban" trước thành một bảng tổng hợp, rồi áp thêm JOIN hoặc WHERE lên bảng đó.

Nó tiện khi bạn muốn thu hẹp một kết quả đã tổng hợp thêm một bước nữa.

Quy tắc mấu chốt của bảng dẫn xuất là bạn phải đặt cho nó một bí danh.

Viết AS alias như FROM (SELECT ...) AS ds (SQLite cũng cho phép bỏ AS) — không có nó bạn sẽ gặp lỗi.

Qua bí danh đó bạn tham chiếu các cột của bảng dẫn xuất, như ds.avg_salary.

-- Bảng dẫn xuất trong FROM: tổng hợp số người và lương trung bình theo từng phòng ban, rồi join với department
SELECT d.dept_name, ds.headcount, ds.avg_salary
FROM (
  SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
) AS ds
JOIN department d ON d.dept_id = ds.dept_id
ORDER BY ds.avg_salary DESC;

Hãy hình dung yêu cầu này: "tổng hợp lương trung bình theo từng phòng ban, và chỉ liệt kê những phòng ban có trung bình cao hơn 5.900.000, kèm tên phòng ban của chúng." (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Trong một truy vấn con, tổng hợp employee theo dept_id và xây dựng một bảng dẫn xuất có dept_id và lương trung bình (bí danh avg_salary). Loại nhân viên có dept_id là NULL khỏi việc tổng hợp.

② Đặt cho bảng dẫn xuất một bí danh, join nó với bảng department trên dept_id, và lấy ra tên phòng ban.

③ Trong WHERE ngoài, thu hẹp về các phòng ban có avg_salary lớn hơn 5900000, và sắp xếp theo avg_salary giảm dần.

Truy vấn con mệnh đề SELECT — nhúng một giá trị đơn thành một cột

Khi bạn viết một truy vấn con scalar trong danh sách cột SELECT, bạn có thể thêm một cột mà giá trị của nó được tính một lần cho mỗi hàng.

Đặt (SELECT SUM(sale.amount) FROM sales sale WHERE sale.emp_id = emp.emp_id) vào một cột, và bạn có thể xếp "tổng doanh số của nhân viên đó" thành một cột cho mỗi nhân viên.

Đây là một truy vấn con tương quan tham chiếu emp.emp_id ở ngoài.

-- Truy vấn con mệnh đề SELECT: thêm tổng doanh số của mỗi nhân viên thành một cột
SELECT emp.name,
  COALESCE(
    (SELECT SUM(sale.amount) FROM sales sale WHERE sale.emp_id = emp.emp_id),
    0
  ) AS total_amount
FROM employee emp
ORDER BY emp.emp_id
LIMIT 6;

Một nhân viên không có doanh số khiến truy vấn con trả về NULL, nên thay nó bằng 0 dùng COALESCE(..., 0) làm bảng dễ đọc hơn.

Một truy vấn con mệnh đề SELECT cũng phải vừa trong một hàng và một cột.

Hãy hình dung yêu cầu này: "bên cạnh danh sách tất cả nhân viên, hiển thị tổng doanh số của nhân viên đó như một cột thêm, hiển thị 0 cho nhân viên không có doanh số."

① Đặt cho bảng employee bí danh emp và lấy ra name.

② Trong các cột SELECT, thêm một truy vấn con tính tổng sales của nhân viên đó, đặt bí danh là total_amount. Tương quan nó với sale.emp_id = emp.emp_id, và thay tổng bằng 0 cho những nhân viên có tổng ra NULL.

③ Sắp xếp theo total_amount giảm dần, phá hòa bằng emp_id tăng dần, và giới hạn về 8 hàng đầu tiên.

Kết hợp với CASE — gắn nhãn rẽ nhánh dựa trên giá trị của truy vấn con

Một truy vấn con mệnh đề SELECT cũng hoạt động tốt như một điều kiện trong CASE.

Bạn có thể xây dựng một cột xếp hạng như "nếu tổng doanh số từ 1.500.000 trở lên thì High, nếu lớn hơn 0 thì Mid, ngược lại None" bằng cách đánh giá giá trị của truy vấn con với CASE WHEN.

Lưu ý rằng bạn phải lặp lại cùng một truy vấn con trong mỗi WHEN của CASE (nếu khả năng đọc được ưu tiên, cũng có cách viết nó chỉ một lần với WITH (Biểu thức bảng chung), bạn sẽ học trong một chương sau).

Trong bài viết này bạn sẽ đề cập dạng cơ bản của việc kết hợp một truy vấn con và CASE trực tiếp.

-- Phân loại nhân viên theo số lượng doanh số của họ
SELECT emp.name,
  (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) AS sale_count,
  CASE
    WHEN (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) >= 4 THEN 'Frequent'
    WHEN (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) >= 1 THEN 'Occasional'
    ELSE 'None'
  END AS activity
FROM employee emp
ORDER BY sale_count DESC, emp.emp_id
LIMIT 8;

Hãy hình dung yêu cầu này: "với mỗi nhân viên, hiển thị tổng doanh số và một xếp hạng (High / Mid / None) cạnh nhau."

① Đặt cho employee bí danh emp và lấy ra name cùng tổng doanh số của nhân viên đó (NULL thay bằng 0, bí danh total_amount).

② Với CASE, thêm một cột xếp hạng đặt bí danh grade trong đó tổng doanh số từ 1500000 trở lên là 'High', lớn hơn 0 và nhỏ hơn 1500000 là 'Mid', và ngược lại (0) là 'None'. Dùng cùng truy vấn con tổng doanh số đó cho việc đánh giá.

③ Sắp xếp theo total_amount giảm dần, phá hòa bằng emp_id tăng dần, và giới hạn về 8 hàng đầu tiên.

CREATE / INSERT SELECT — biến kết quả truy vấn con thành một bảng

Viết CREATE TABLE new_table AS SELECT ... cho phép bạn lưu kết quả SELECT trực tiếp thành một bảng mới (CTAS: Create Table As Select).

Nó thường được dùng khi bạn muốn giữ một kết quả đã tổng hợp như một bản chụp.

Khi bạn muốn thêm hàng vào một bảng đã tồn tại, dùng INSERT INTO existing_table SELECT ... để thêm hàng loạt kết quả SELECT.

Đây là các thao tác ghi tạo một bảng hoặc thêm hàng.

Trong bài viết này bạn sẽ dùng một bảng làm việc dùng một lần top_seller để tổng hợp.

Để bạn nhận cùng một kết quả dù chạy bao nhiêu lần, bạn sẽ dọn bảng làm việc với DROP TABLE IF EXISTS trước khi tạo lại nó.

INSERT hàng loạt nhanh hơn

INSERT INTO table SELECT ... chèn các hàng đích tất cả cùng một lúc trong một câu lệnh duy nhất.

So với việc lặp lại INSERT INTO table VALUES (...) cho mỗi hàng, việc phân tích cú pháp SQL, cập nhật chỉ mục, và xử lý giao dịch chỉ diễn ra một lần, nên càng chèn nhiều hàng, nó càng nhanh.

Khi sao chép hoặc di chuyển một bảng khác hoặc một kết quả đã tổng hợp, hãy ưu tiên INSERT ... SELECT hàng loạt hơn là lặp lại INSERT từng hàng một.

-- Lưu một tổng hợp theo từng phòng ban vào một bảng chụp
DROP TABLE IF EXISTS dept_summary;
CREATE TABLE dept_summary AS
  SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id;

SELECT * FROM dept_summary ORDER BY avg_salary DESC;

Hãy hình dung yêu cầu này: "gom những nhân viên có tổng doanh số từ 1.500.000 trở lên vào một bảng tổng hợp với 3 cột — ID nhân viên, tên, và tổng doanh số." Vì đây là một thao tác ghi, hãy cấu trúc nó sao cho bảng làm việc được tạo lại và không hỏng khi chạy lại.

① Trước tiên, dọn bảng làm việc nếu nó tồn tại với DROP TABLE IF EXISTS top_seller;.

② Join employee và sales, tổng hợp tổng doanh số theo từng nhân viên, viết một SELECT chỉ giữ những nhân viên có tổng từ 1500000 trở lên, và từ kết quả đó xây dựng bảng top_seller với CREATE TABLE top_seller AS SELECT .... Đặt các cột là 3: emp_id, name, và total_amount (tổng doanh số).

③ Cuối cùng, kiểm tra nội dung với SELECT * FROM top_seller ORDER BY total_amount DESC;.

Hãy hình dung yêu cầu này: "vào bảng tổng hợp từ Thực hành 4, cũng thêm những nhân viên hạng trung có tổng doanh số từ 900.000 trở lên nhưng nhỏ hơn 1.500.000." Dùng INSERT INTO ... SELECT để thêm hàng loạt các hàng. Để nó không hỏng khi chạy lại, hãy cấu trúc nó tạo lại bảng trước khi thêm.

① Dọn bảng làm việc với DROP TABLE IF EXISTS top_seller;, rồi tạo lại nó với cùng CREATE TABLE top_seller AS SELECT ... (từ 1.5 triệu trở lên) như Thực hành 4.

② Với INSERT INTO top_seller SELECT ..., thêm những nhân viên có tổng doanh số từ 900000 trở lên và nhỏ hơn 1500000 (3 cột: emp_id, name, tổng doanh số). Khớp thứ tự cột với top_seller.

③ Kiểm tra tất cả các hàng với SELECT * FROM top_seller ORDER BY total_amount DESC;.