Trino SQL Bài học 23: Tích đề các (CROSS JOIN) và tự kết nối (Self Join)
Mục tiêu bài học:
-
Hiểu bản chất toán học của phép nhân tập hợp (Cartesian Product) sinh ra từ
CROSS JOIN. -
Nhận diện cạm bẫy bùng nổ dữ liệu có thể làm sập hệ thống phân tán khi lạm dụng kết nối chéo.
-
Nắm vững kỹ thuật
Self Join(bảng tự kết nối với chính nó) để xử lý dữ liệu phân cấp hoặc tìm kiếm các cặp dữ liệu tương quan.
1. CROSS JOIN (Tích đề các): Sự kết hợp mọi khả năng
Nếu INNER JOIN hay OUTER JOIN cần một điều kiện (ON) để so khớp và liên kết dữ liệu, thì CROSS JOIN hoàn toàn không cần điều kiện nào cả.
Logic hoạt động: Nó lấy mỗi dòng của bảng thứ nhất, ghép cặp với tất cả các dòng của bảng thứ hai.
Về mặt toán học, đây gọi là Tích đề các (Cartesian Product). Nếu bảng A có dòng và bảng B có dòng, kết quả của CROSS JOIN sẽ là một bảng mới có dòng.
Cú pháp:
SQL
SELECT
c.mktsegment,
n.name AS nation_name
FROM tpch.tiny.customer AS c
CROSS JOIN tpch.tiny.nation AS n;
(Lưu ý: Không hề có mệnh đề ON)
Ứng dụng thực tế hợp lý:
CROSS JOIN thường được dùng để tạo khung dữ liệu nền (Skeleton/Grid) trước khi ráp số liệu thực tế.
Ví dụ: Bạn muốn tạo một báo cáo doanh thu yêu cầu phải liệt kê tất cả các ngày trong tháng nhân với tất cả các cửa hàng, kể cả khi cửa hàng đó trong ngày đó không bán được sản phẩm nào (doanh thu = 0). Bạn sẽ CROSS JOIN bảng dim_date với bảng dim_store để tạo ra lưới báo cáo này.
2. Góc nhìn Kỹ sư: Thảm họa "Bùng nổ dữ liệu"
Trong hệ thống Big Data như Trino, CROSS JOIN là một "vũ khí hủy diệt" nếu bạn sử dụng sai cách.
Giả sử bạn có bảng users (1 triệu dòng) và bảng logs (10 triệu dòng). Nếu bạn vô tình CROSS JOIN hai bảng này, Trino engine sẽ cố gắng tạo ra tỷ dòng dữ liệu trên RAM. Cụm cluster của bạn sẽ lập tức cạn kiệt bộ nhớ (OOM - Out of Memory) và sập toàn bộ các node.
Lỗi vô ý do cú pháp cũ:
Trong SQL chuẩn cũ, thay vì viết chữ JOIN, người ta dùng dấu phẩy , giữa hai bảng và dùng WHERE để nối. Nếu bạn quên viết WHERE, Trino sẽ tự động biến nó thành một CROSS JOIN.
SQL
-- CÚ PHÁP NGUY HIỂM: Quên WHERE -> Biến thành CROSS JOIN khổng lồ
SELECT *
FROM tpch.tiny.orders, tpch.tiny.customer;
-- LUÔN KHUYẾN KHÍCH DÙNG CÚ PHÁP CHUẨN:
SELECT *
FROM tpch.tiny.orders AS o
JOIN tpch.tiny.customer AS c ON o.custkey = c.custkey;
3. Self Join: Bảng tự "soi gương"
Self Join không phải là một cú pháp SQL mới. Bản chất của nó chỉ là việc bạn dùng INNER JOIN, LEFT JOIN hoặc CROSS JOIN để nối một bảng với chính nó.
Quy tắc bắt buộc: Vì bạn đang nối cùng một bảng, tên cột sẽ bị trùng lặp hoàn toàn. Do đó, bạn bắt buộc phải sử dụng Bí danh bảng (Table Alias) khác nhau để Trino phân biệt được bạn đang nhắc đến "Bảng gốc" hay "Bảng bản sao".
4. Ứng dụng thực tế của Self Join
Bài toán 1: Dữ liệu phân cấp (Hierarchical Data)
Giả sử bạn có một bảng employees chứa thông tin nhân viên. Mỗi nhân viên có một cột manager_id (trỏ đến employee_id của người quản lý, và người quản lý này cũng nằm trong chính bảng đó).
SQL
-- Tìm tên nhân viên và tên của người quản lý trực tiếp
SELECT
e.employee_name AS nhan_vien,
m.employee_name AS quan_ly
FROM employees AS e
LEFT JOIN employees AS m -- Nối bảng employees với chính nó
ON e.manager_id = m.employee_id; -- Điều kiện: ID quản lý của người này = ID nhân viên của người kia
(Dùng LEFT JOIN để đảm bảo Giám đốc - người không có quản lý - vẫn hiện ra trong báo cáo).
Bài toán 2: Tìm kiếm các cặp tương quan (Finding Pairs)
Bạn muốn tìm những khách hàng đã từng đặt ít nhất 2 đơn hàng khác nhau vào cùng một ngày.
SQL
SELECT
o1.custkey,
o1.orderdate,
o1.orderkey AS don_hang_1,
o2.orderkey AS don_hang_2
FROM tpch.tiny.orders AS o1
JOIN tpch.tiny.orders AS o2
ON o1.custkey = o2.custkey -- Cùng một khách hàng
AND o1.orderdate = o2.orderdate -- Cùng một ngày
AND o1.orderkey < o2.orderkey; -- Đảm bảo không ghép 1 đơn hàng với chính nó (1=1), và tránh kết quả bị đảo ngược (A-B và B-A)
Điều kiện o1.orderkey < o2.orderkey là một kỹ thuật kinh điển trong Self Join để loại bỏ các cặp bị trùng lặp đối xứng.
All rights reserved
