Trino SQL Bài học 25: Các phép toán tập hợp (UNION, INTERSECT, EXCEPT)
Mục tiêu bài học:
-
Phân biệt sự khác nhau cơ bản giữa nối bảng theo chiều ngang (
JOIN) và ghép dữ liệu theo chiều dọc (Set Operations). -
Nắm vững bộ 3 phép toán tập hợp: Hợp (
UNION), Giao (INTERSECT), và Trừ (EXCEPT). -
Hiểu sâu sắc sự khác biệt về mặt hiệu năng giữa
UNIONvàUNION ALLtrong kiến trúc phân tán. -
Tuân thủ luật "tương thích cấu trúc" để tránh lỗi cú pháp.
1. Bản chất: Nối dọc (Set) vs Nối ngang (Join)
Ở các bài trước, chúng ta đã dùng JOIN để ghép các cột từ nhiều bảng lại với nhau (Nối ngang). Ví dụ: ghép cột name của khách hàng với cột totalprice của đơn hàng.
Nhưng giả sử bạn có một bảng khach_hang_mien_bac và một bảng khach_hang_mien_nam. Cả hai bảng đều có cấu trúc giống hệt nhau (ID, Tên, Số điện thoại). Bây giờ bạn muốn chồng hai bảng này lên nhau để tạo thành một danh sách tổng, bạn sẽ dùng Các phép toán tập hợp (Set Operations). Đây gọi là Nối dọc.
Quy tắc thép của Set Operations: Để chồng hai câu lệnh SELECT lên nhau, chúng phải thỏa mãn 2 điều kiện tiên quyết:
-
Số lượng cột trả về từ cả hai câu lệnh phải bằng nhau hoàn toàn.
-
Kiểu dữ liệu của các cột tương ứng (cột 1 với cột 1, cột 2 với cột 2) phải tương thích với nhau (cùng là chuỗi, hoặc cùng là số nguyên). Trino với bản chất "Kiểu tĩnh" sẽ báo lỗi ngay lập tức nếu bạn cố ghép một cột
VARCHARđè lên cộtINTEGER.
2. Phép Hợp: UNION và UNION ALL
Đây là phép toán được sử dụng nhiều nhất, giúp gộp kết quả của nhiều truy vấn thành một tập hợp duy nhất.
A. UNION ALL (Ghép nối siêu tốc)
UNION ALL đơn giản là mang toàn bộ dữ liệu của câu truy vấn dưới, dán tiếp vào đuôi của câu truy vấn trên. Nếu có dữ liệu trùng lặp (một người xuất hiện ở cả hai bảng), nó sẽ giữ nguyên toàn bộ.
Ví dụ: Giả sử bạn muốn tạo một danh sách "Đối tác" bao gồm cả Khách hàng (Customer) và Nhà cung cấp (Supplier).
SQL
-- Lấy danh sách Khách hàng
SELECT custkey AS partner_id, name, 'Customer' AS partner_type
FROM tpch.tiny.customer
UNION ALL
-- Ghép nối với danh sách Nhà cung cấp
SELECT suppkey AS partner_id, name, 'Supplier' AS partner_type
FROM tpch.tiny.supplier;
Lưu ý: Cột trả về sẽ lấy tên bí danh của câu SELECT đầu tiên (ví dụ partner_id).
B. UNION (Ghép nối và Lọc trùng lặp)
UNION (hay UNION DISTINCT) cũng ghép dữ liệu, nhưng sau khi ghép xong, nó sẽ quét qua toàn bộ tập kết quả và loại bỏ các dòng trùng lặp y hệt nhau, chỉ giữ lại một đại diện duy nhất.
Góc nhìn Kỹ sư (Cảnh báo hiệu năng): Trong môi trường Big Data của Trino, việc loại bỏ trùng lặp đòi hỏi hệ thống phải gom toàn bộ dữ liệu (từ tất cả các Worker) về một chỗ để so sánh. Quá trình này tiêu tốn lượng RAM rất lớn và làm chậm truy vấn nghiêm trọng. Best Practice: Luôn luôn sử dụng UNION ALL làm mặc định. Chỉ dùng UNION khi bạn thực sự, chắc chắn cần loại bỏ dữ liệu trùng lặp về mặt logic nghiệp vụ.
3. Phép Giao: INTERSECT
INTERSECT trả về những dòng xuất hiện ở cả hai câu truy vấn. Về mặt logic, nó khá giống với INNER JOIN, nhưng INTERSECT so sánh toàn bộ các cột được SELECT thay vì chỉ so sánh qua mệnh đề ON.
Ví dụ: Tìm những phân khúc thị trường (mktsegment) có cả khách hàng ở quốc gia 'VIETNAM' và quốc gia 'JAPAN'.
SQL
-- Truy vấn 1: Phân khúc có tại Việt Nam
SELECT c.mktsegment
FROM tpch.tiny.customer AS c
JOIN tpch.tiny.nation AS n ON c.nationkey = n.nationkey
WHERE n.name = 'VIETNAM'
INTERSECT
-- Truy vấn 2: Phân khúc có tại Nhật Bản
SELECT c.mktsegment
FROM tpch.tiny.customer AS c
JOIN tpch.tiny.nation AS n ON c.nationkey = n.nationkey
WHERE n.name = 'JAPAN';
(Kết quả sẽ chỉ trả về những phân khúc xuất hiện ở cả hai tập, các bản ghi trùng lặp tự động bị loại bỏ).
4. Phép Trừ: EXCEPT
EXCEPT (trong một số RDBMS khác gọi là MINUS) trả về những dòng có trong câu truy vấn đầu tiên, nhưng KHÔNG CÓ trong câu truy vấn thứ hai.
Đây là cách tuyệt vời để tìm kiếm sự thiếu hụt (Gap Analysis) hoặc tìm khách hàng rời bỏ (Churn).
Ví dụ: Tìm những khách hàng đã từng mua hàng trong năm 1994, nhưng KHÔNG hề quay lại mua hàng trong năm 1995.
SQL
-- Tập hợp A: Khách hàng mua năm 1994
SELECT custkey
FROM tpch.tiny.orders
WHERE orderdate BETWEEN DATE '1994-01-01' AND DATE '1994-12-31'
EXCEPT
-- Tập hợp B: Khách hàng mua năm 1995
SELECT custkey
FROM tpch.tiny.orders
WHERE orderdate BETWEEN DATE '1995-01-01' AND DATE '1995-12-31';
Trino sẽ lấy tập A, vứt bỏ đi bất kỳ khách hàng nào vô tình lọt vào tập B, và trả về danh sách những người hoàn toàn biến mất trong năm 1995.
5. Quy tắc sắp xếp (ORDER BY) trong Set Operations
Nếu bạn muốn sắp xếp kết quả cuối cùng sau khi đã dùng UNION, INTERSECT hay EXCEPT, bạn chỉ được phép viết một mệnh đề ORDER BY duy nhất ở tận cùng của toàn bộ khối lệnh.
Không được đặt ORDER BY ở từng câu lệnh SELECT lẻ tẻ bên trong.
SQL
SELECT custkey, name FROM tpch.tiny.customer WHERE mktsegment = 'BUILDING'
UNION ALL
SELECT custkey, name FROM tpch.tiny.customer WHERE mktsegment = 'FURNITURE'
ORDER BY name ASC; -- Áp dụng cho tập kết quả đã được gộp lại
All rights reserved
