Tích Descartes trong SQL: Hướng dẫn toàn diện

Tích Descartes là một khái niệm nền tảng trong SQL vừa mạnh mẽ vừa phức tạp. Hiểu và sử dụng hiệu quả có thể nâng cao đáng kể khả năng thao tác dữ liệu của bạn. Trong hướng dẫn này, chúng ta sẽ khám phá mọi khía cạnh của tích Descartes, kèm theo những góc nhìn thực tiễn và ví dụ minh họa. 

Với những bạn mới học SQL, hãy bắt đầu với khóa Intermediate SQL để xây dựng nền tảng vững chắc. Ngoài ra, SQL Basics Cheat Sheet mà bạn có thể tải về cũng là tài liệu tham khảo hữu ích vì tổng hợp các hàm SQL phổ biến nhất.

Tích Descartes là gì?

Trong SQL, tích Descartes là kết quả của việc kết hợp mọi hàng từ một bảng với mọi hàng từ một bảng khác. Thao tác này thường được tạo ra thông qua CROSS JOIN, hình thành một tập kết quả chứa tất cả cặp hàng có thể có từ hai bảng. Dù rất mạnh, nó có thể tạo ra tập kết quả rất lớn, đặc biệt khi làm việc với các bảng có nhiều hàng, vì vậy cần sử dụng thận trọng. Thực tế, nhiều nhà phát triển có lẽ “khám phá” tích Descartes theo cách khó khăn khi vô tình bỏ sót điều kiện join nào đó và nhận về một tập kết quả khổng lồ.

Nền tảng toán học

Trong toán học, tích Descartes (ký hiệu A × B) là tập hợp mọi cặp có thứ tự được tạo bằng cách ghép từng phần tử của tập A với từng phần tử của tập B. Trong SQL, điều này tương ứng với việc ghép từng hàng của một bảng với mọi hàng ở bảng khác.

Ví dụ về tích Descartes. Hình do Tác giả thực hiện.

SQL cross join

Thao tác cross join trong SQL tạo ra tích Descartes giữa hai bảng bằng cách ghép mọi hàng từ bảng thứ nhất với mọi hàng từ bảng thứ hai. Kết quả là cross join tạo ra tất cả tổ hợp hàng có thể có cho dữ liệu. Hãy cùng xem một ví dụ thực hiện cross join trong SQL.

Giả sử bạn có hai bảng, Students và Courses:

Bảng Students

Bảng Courses

Trong truy vấn sau, mỗi học sinh được ghép với mọi khóa học, hiển thị khối lớp và độ khó.

-- Perform the cross join to list each student with every course
SELECT Students.name, Students.grade, Courses.course, Courses.level
FROM Students
CROSS JOIN Courses;Được hỗ trợ bởi

Ví dụ về tích Descartes trong SQL do cross join tạo ra. Hình từ Tác giả.

Khi nào nên và không nên dùng tích Descartes trong SQL

Tích Descartes trong SQL có thể rất hữu ích khi hiểu rõ hành vi và dùng một cách chủ đích. Hãy xem các trường hợp sử dụng (và những cạm bẫy tiềm ẩn) khi dùng tích Descartes.

Trường hợp sử dụng

Các kịch bản sau đây là nơi tích Descartes có thể hữu ích:

• Tạo tổ hợp: Tích Descartes lý tưởng để tạo mọi tổ hợp của hai tập giá trị. Ví dụ, bạn có thể ghép sản phẩm với mức giảm giá, ghép nhân viên với ca làm, hoặc tạo mọi cặp có thể giữa các danh mục khác nhau.
• Mô phỏng kịch bản kiểm thử: Khi chạy test, tích Descartes hữu ích để mô phỏng mọi tình huống có thể bằng cách ghép các bộ tham số hoặc cấu hình khác nhau.
• Bù khuyết dữ liệu thiếu: Tích Descartes cũng giúp xác định khoảng trống bằng cách tạo mọi tổ hợp có thể rồi left-join kết quả để tìm các mục thiếu, chẳng hạn như thiếu ngày trong phân tích chuỗi thời gian.

Cạm bẫy tiềm ẩn

Mặc dù hữu ích trong một số tình huống, tích Descartes đi kèm một số rủi ro sau:

• Vấn đề hiệu năng: Vì tích Descartes nhân số hàng của một bảng với bảng khác, kết quả có thể tăng theo cấp số nhân. Nếu dữ liệu quá lớn, điều này có thể dẫn đến truy vấn chạy chậm, ngốn bộ nhớ và thậm chí làm sập cơ sở dữ liệu.
• Nhân bản dữ liệu ngoài ý muốn: Nếu không lọc cẩn thận, tích Descartes có thể tạo ra các hàng trông như bị trùng lặp hoặc không liên quan cho phân tích, dẫn đến diễn giải lệch lạc hoặc kết quả sai nếu không dự liệu hoặc xử lý đúng cách.
• Khó debug: Những tích Descartes không chủ đích, như quên chỉ định điều kiện join, có thể dẫn đến cross join thay vì inner hoặc outer join. Điều này tạo ra kết quả bất ngờ, khó lần theo và sửa chữa.

Ứng dụng thực tế của tích Descartes

Dù nên dùng có chừng mực do rủi ro tiềm ẩn, tích Descartes hữu ích khi bạn cần kết hợp mọi cặp phần tử dữ liệu có thể.

Kịch bản kết hợp dữ liệu

Tích Descartes hữu ích để tính các chỉ số hiệu năng tương đối, phân tích mức độ tương thích hoặc tạo hệ thống xếp hạng. Khi cần so sánh mọi phần tử ở hai tập dữ liệu, bạn có thể dùng tích Descartes để ghép từng mục với mọi mục còn lại. 

Ví dụ, để so sánh hiệu suất của mỗi nhân viên với mọi nhân viên khác, tích Descartes sẽ tạo tất cả cặp nhân viên có thể:

-- Select two columns with employee names from the Employees table
SELECT 
    A.employee_name AS Employee1,
    B.employee_name AS Employee2
FROM Employees AS A
-- Cross join to create all possible pairs of employees
CROSS JOIN Employees AS B;Được hỗ trợ bởi

Tích Descartes cũng quan trọng trong phân tích thương mại điện tử để khám phá toàn bộ dải ghép cặp sản phẩm - khuyến mãi. Giả sử bạn có các bảng Products và Discounts, một cross join có thể tạo danh sách mọi tổ hợp sản phẩm - loại giảm giá:

-- Select product names and discount types
SELECT 
    Products.product_name,
    Discounts.discount_type
FROM Products
-- Generate all product and discount combinations
CROSS JOIN Discounts;Được hỗ trợ bởi

Kiểm thử và xác nhận

Tích Descartes cho phép tạo mọi tổ hợp tham số tiềm năng trong môi trường kiểm thử, điều cốt yếu cho thử nghiệm bao phủ kịch bản. Ví dụ, khi kiểm thử hệ thống với các cấu hình hệ điều hành, thiết bị và trình duyệt khác nhau, dùng cross join đảm bảo mọi cấu hình có thể đều được kiểm thử.

-- Select combinations of operating systems, devices, and browsers
SELECT 
    OS.name AS OperatingSystem,
    Device.name AS Device,
    Browser.name AS Browser
FROM OperatingSystems AS OS
-- All combinations with devices
CROSS JOIN Devices AS Device
-- All combinations with browsers           
CROSS JOIN Browsers AS Browser;Được hỗ trợ bởi

Tích Descartes có thể giúp xác định bản ghi thiếu, hữu ích để đảm bảo tính đầy đủ dữ liệu. Ví dụ, khi phân tích dữ liệu bán hàng theo ngày và xác nhận rằng mỗi sản phẩm đều có dữ liệu mỗi ngày, bạn có thể tạo tích Descartes của bảng Products và Dates. Sau đó, left-join dữ liệu bán hàng thực tế để nhận diện các cặp ngày - sản phẩm bị thiếu.

-- Select dates and products with no sales
SELECT 
    Dates.date,
    Products.product_name,
    Sales.sales_amount
FROM Dates
-- Create all date and product combinations
CROSS JOIN Products                          
-- Match sales by date and product
LEFT JOIN 
    Sales ON Dates.date = Sales.date 
           AND Products.product_name = Sales.product_name  
-- Filter for combinations with no sales
WHERE Sales.sales_amount IS NULL;Được hỗ trợ bởi

Một vài gợi ý về hiệu năng

Vì tích Descartes tạo ra các tổ hợp dữ liệu giữa hai bảng, có thể phát sinh vấn đề hiệu năng đáng kể khi liên quan đến các bảng lớn. Tôi đề xuất các kỹ thuật sau để tối ưu truy vấn và quản lý tập dữ liệu lớn nhằm cải thiện hiệu năng cơ sở dữ liệu.

Tối ưu truy vấn

Cân nhắc các phương pháp sau để viết truy vấn hiệu quả khi có tích Descartes.

• Giới hạn số hàng trước khi join: Để giảm số hàng tham gia tích Descartes, hãy áp dụng bộ lọc cho từng bảng trước khi thực hiện cross join. Ví dụ, dùng WHERE để lọc mỗi bảng theo tiêu chí phù hợp, tối thiểu hóa tập kết quả.

• Chỉ SELECT các cột cần thiết: Khi thực hiện tích Descartes, chỉ định các cột cần trong mệnh đề SELECT để giảm sử dụng bộ nhớ và cải thiện hiệu năng. Tránh dùng SELECT * vì trả về mọi cột, dễ dẫn đến kết quả lớn và khó xử lý.

• Thêm điều kiện để chuyển Cross Join thành Inner Join: Nếu chỉ một số tổ hợp hàng là liên quan, hãy cân nhắc thêm điều kiện bổ sung cho cross join hoạt động tương tự INNER JOIN. Điều này giúp thu hẹp đầu ra bằng cách giới hạn hàng từ mỗi bảng nên được ghép.

• Xử lý theo lô: Với tập dữ liệu lớn, chia nhỏ truy vấn thành các phần vừa phải để xử lý theo lô. Điều này giúp tránh tràn bộ nhớ và cải thiện thời gian phản hồi, đặc biệt khi truy vấn trên các bảng lớn.

Quản lý tập dữ liệu lớn

Dùng các kỹ thuật sau để quản lý tập dữ liệu lớn phát sinh từ tích Descartes.

• Đánh chỉ mục (Indexing): Thêm index trên các cột thường xuyên join để cải thiện hiệu năng khi lọc hoặc ghép bảng lớn. Dù đánh chỉ mục không trực tiếp tăng tốc cross join, nó hữu ích khi sau đó bạn lọc hoặc join tích Descartes với các bảng khác, giúp truy xuất hàng nhanh hơn.

• Phân vùng bảng lớn: Phân vùng bảng có thể cải thiện hiệu năng truy vấn có tích Descartes. Phân vùng chia bảng thành các phần nhỏ, dễ quản lý theo các cột xác định, cho phép cơ sở dữ liệu chỉ đọc các phân vùng liên quan khi thực thi truy vấn.

• Giới hạn số hàng kết quả: Dùng mệnh đề LIMIT để giới hạn số hàng trả về trong trường hợp bạn chỉ cần mẫu từ tích Descartes cho mục đích thử nghiệm hoặc xác minh.

• Dùng bảng tạm cho kết quả trung gian: Lưu kết quả trung gian vào bảng tạm nếu bạn cần tái sử dụng tích Descartes nhiều lần để giảm tính toán lặp và tăng tốc các thao tác tiếp theo.

Những thao tác SQL khác có thể giúp

Trong những tình huống mà việc tạo đầy đủ tích Descartes không hiệu quả, có các phương pháp thay thế. Các phương pháp dưới đây giúp bạn đạt mục tiêu mà không làm quá tải cơ sở dữ liệu.

Inner và outer join

INNER JOIN chỉ trả về các hàng có khớp giữa các cột được chỉ định ở cả hai bảng. Kỹ thuật này giới hạn kết quả ở các tổ hợp liên quan và tránh số hàng khổng lồ đi kèm với tích Descartes. Inner join phù hợp khi bạn chỉ cần các hàng thỏa điều kiện cụ thể ở cả hai bảng. 

Trong ví dụ dưới đây, INNER JOIN chỉ trả về các sản phẩm và giảm giá cùng danh mục.

-- Select products and corresponding discounts by category
SELECT 
    A.product_name,
    B.discount_type
FROM Products AS A
-- Match products and discounts by category
INNER JOIN Discounts AS B ON A.category_id = B.category_id;Được hỗ trợ bởi

Outer join (LEFT JOIN, RIGHT JOIN và FULL JOIN) trả về cả hàng khớp và không khớp từ một hoặc cả hai bảng. Kỹ thuật này hữu ích khi truy xuất toàn bộ hàng từ một bảng và các hàng khớp từ bảng kia, nhưng không tạo mọi tổ hợp có thể. Outer join phù hợp khi bạn muốn bao gồm dữ liệu không khớp để phân tích nhưng không cần ghép đủ kiểu tích Descartes.

Trong truy vấn dưới đây, LEFT JOIN trả về toàn bộ sản phẩm, bao gồm cả những sản phẩm không có giảm giá tương ứng, với giá trị NULL cho các giảm giá không khớp.

-- Select products with corresponding discounts, (NULL if no matching discount)
SELECT 
    A.product_name,
    B.discount_type
FROM Products AS A
-- Match products with discounts by category
LEFT JOIN Discounts AS B ON A.category_id = B.category_id;  Được hỗ trợ bởi

Tôi khuyến nghị học khóa Introduction to SQL Server của DataCamp để tìm hiểu thêm cách join bảng và truy xuất dữ liệu từ nhiều bảng.

Subquery và CTE

Subquery cho phép bạn chọn dữ liệu cụ thể từ một bảng và dùng nó để lọc kết quả trong truy vấn chính. Điều này giúp tránh các tổ hợp hàng không cần thiết bằng cách lọc dữ liệu chỉ còn các mục liên quan. Subquery hữu ích khi bạn cần tiền lọc dữ liệu hoặc tính toán giá trị cụ thể để dùng trong truy vấn chính mà không cần một tích Descartes đầy đủ.

Subquery này lọc các giảm giá có giá trị trên 10 trước khi join, giảm dữ liệu được xử lý và tránh cross join không cần thiết.

-- Select products with discounts greater than 10 by category
SELECT 
    A.product_name,
    B.discount
FROM Products AS A
-- Subquery to filter discounts over 10
INNER JOIN 
    (SELECT discount, category_id   
     FROM Discounts 
     WHERE discount > 10) AS B
-- Match products with filtered discounts by category
ON A.category_id = B.category_id;   Được hỗ trợ bởi

CTE, hay mệnh đề WITH, cho phép chia nhỏ truy vấn phức tạp thành từng bước. Phương pháp này giúp tránh tích Descartes bằng cách lọc hoặc tổng hợp dữ liệu trước khi join. CTE phù hợp khi đơn giản hóa truy vấn nhiều bước, đặc biệt khi cần tiền tổng hợp dữ liệu hoặc thực hiện nhiều join nhưng muốn tránh kết quả kiểu tích Descartes đầy đủ.

Truy vấn dưới đây dùng Common Table Expression (CTE) để lọc các giảm giá lớn hơn 10 trước khi join với sản phẩm theo danh mục.

-- Filter discounts and join with products by category
WITH FilteredDiscounts AS (
    SELECT discount, category_id
    FROM Discounts
    WHERE discount > 10
)
SELECT 
    A.product_name,
    FD.discount
FROM Products AS A
-- Join with filtered discounts by category
INNER JOIN 
    FilteredDiscounts AS FD ON A.category_id = FD.category_id;Được hỗ trợ bởi

Ứng dụng sáng tạo của tích Descartes

Mặc dù tích Descartes được dùng rộng rãi trong các thao tác SQL cơ bản, nó còn có ứng dụng vượt ra ngoài các trường hợp sử dụng cơ sở dữ liệu truyền thống. Hãy xem cách tích Descartes có thể được dùng trong chuẩn bị dữ liệu máy học và tạo dữ liệu tổng hợp cho mô phỏng.

Chuẩn bị dữ liệu máy học

Tập dữ liệu đa dạng và toàn diện rất quan trọng khi chuẩn bị dữ liệu cho máy học để huấn luyện mô hình vững chắc. Tích Descartes có thể tạo một tập dữ liệu đầy đủ bao phủ mọi tổ hợp giá trị đặc trưng, giúp mô hình học từ nhiều kịch bản phong phú hơn. 

Ví dụ, khi xây dựng mô hình phát hiện gian lận, bạn có thể kết hợp loại giao dịch, khu vực và loại thiết bị để mô phỏng các điều kiện khác nhau, giúp mô hình khái quát tốt hơn trên nhiều trường hợp.

Tạo dữ liệu tổng hợp

Dữ liệu tổng hợp thường được dùng để kiểm thử hệ thống dưới nhiều điều kiện hoặc bảo vệ quyền riêng tư bằng cách tránh dùng dữ liệu thật. Tích Descartes có thể giúp tạo các tập dữ liệu tổng hợp đa dạng, hữu ích cho các ứng dụng từ mô phỏng đến tăng cường dữ liệu cho mô hình máy học.

Ví dụ, bạn có thể kết hợp các điều kiện thị trường khác nhau, lãi suất và hồ sơ khách hàng trong các mô phỏng tài chính để kiểm thử mô hình hoặc thuật toán hoạt động ra sao trên các biến này.

Dưới đây là một ví dụ đơn giản về cách bạn có thể tạo dữ liệu tổng hợp “từ không” trong SQL rồi dùng CROSS JOIN để mở rộng thành tập dữ liệu lớn hơn. Ý tưởng là mỗi bảng nhỏ đại diện cho một chiều của dữ liệu thử nghiệm, rồi khi cross join, ta nhận mọi tổ hợp có thể.

Ví dụ 1: Kết hợp nhiều chiều

Giả sử bạn muốn mô phỏng các kịch bản tài chính khác nhau dựa trên Điều kiện thị trường, Lãi suất và Hồ sơ khách hàng. Ta có thể định nghĩa mỗi chiều qua một Common Table Expression (CTE) hoặc bảng tạm bằng giá trị literal. Sau đó, CROSS JOIN sẽ ghép chúng thành mọi tổ hợp có thể:

WITH MarketConditions AS (
    SELECT 'Bull' AS condition
    UNION ALL
    SELECT 'Bear'
    UNION ALL
    SELECT 'Sideways'
),
InterestRates AS (
    SELECT 1.5 AS rate
    UNION ALL
    SELECT 2.0
    UNION ALL
    SELECT 3.25
),
CustomerProfiles AS (
    SELECT 'Younger' AS profile
    UNION ALL
    SELECT 'Mid-career'
    UNION ALL
    SELECT 'Retired'
)
SELECT 
    mc.condition,
    ir.rate,
    cp.profile
FROM 
    MarketConditions mc
CROSS JOIN 
    InterestRates ir
CROSS JOIN 
    CustomerProfiles cp;Được hỗ trợ bởi

Tập kết quả sẽ có 3×3×3 = 27 hàng. Mỗi hàng là một tổ hợp duy nhất của điều kiện thị trường, lãi suất và hồ sơ khách hàng.

Ví dụ tạo dữ liệu tổng hợp bằng tích Descartes. Hình do Tác giả thực hiện.

Ví dụ 2: Sinh hàng bằng dãy số hoặc VALUES đơn giản

Một kỹ thuật khác là sinh dải số hoặc khoảng thời gian bằng cách cross join các bảng nhỏ với chính chúng. Cách này có thể tạo số lượng lớn hàng tổng hợp cho thử nghiệm nhanh. Ví dụ, ta có thể dùng truy vấn sau để sinh các số từ 1 đến 10.

WITH
Nums AS (
    SELECT 1 AS n
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
)
SELECT n
FROM Nums;Được hỗ trợ bởi

Dùng tích Descartes để sinh hàng bằng dãy số. Hình do Tác giả thực hiện.

Sau đó ta có thể cross join cùng bộ 10 hàng này với chính nó để tạo 10×10=100:

WITH
Nums AS (
    SELECT 1 AS n
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
)
SELECT A.n AS n1, B.n AS n2
FROM Nums A
CROSS JOIN Nums B
ORDER BY A.n, B.n;Được hỗ trợ bởi

Kết quả bạn sẽ nhận 100 hàng, mỗi hàng có một cặp giá trị (n1,n2). Bạn có thể mở rộng logic này lên hàng trăm, hàng nghìn, thậm chí hàng triệu hàng — nhưng lưu ý về hiệu năng và lưu trữ.

Bằng cách dùng các cách tiếp cận này — CTE (hoặc mệnh đề VALUES) để định nghĩa chiều và CROSS JOIN để kết hợp — bạn có thể nhanh chóng tạo tập dữ liệu tổng hợp trong SQL mà không cần dựa vào các bảng sẵn có.

Kết luận

Hiểu về tích Descartes trong SQL mở ra nhiều khả năng thao tác và phân tích dữ liệu. Thành thạo khái niệm này sẽ giúp bạn làm việc tốt hơn với các tập dữ liệu phức tạp và tối ưu hóa truy vấn cơ sở dữ liệu. Để có thêm kiến thức nâng cao, hãy tham khảo lộ trình kỹ năng SQL Server Fundamentals để đào sâu chuyên môn. 

Tùy theo định hướng nghề nghiệp, tôi khuyến nghị bạn đăng ký lộ trình nghề nghiệp Associate Data Analyst in SQL của DataCamp để học các kỹ năng ở cấp độ ngành nhằm trở thành nhà phân tích dữ liệu hoặc thử lộ trình Associate Date Engineer in SQL để trở thành kỹ sư dữ liệu.