Produk Kartesius dalam SQL: Panduan Komprehensif

Produk Kartesius adalah konsep dasar dalam SQL yang bisa sangat kuat sekaligus kompleks. Memahami cara menggunakannya secara efektif dapat sangat meningkatkan kemampuan Anda dalam memanipulasi data. Dalam panduan ini, kita akan membahas seluk-beluk produk Kartesius, disertai wawasan praktis dan contoh. 

Bagi Anda yang baru mengenal SQL, pertimbangkan untuk memulai dengan kursus Intermediate SQL kami untuk membangun fondasi yang kuat. Selain itu, SQL Basics Cheat Sheet yang dapat Anda unduh adalah referensi yang membantu karena memuat semua fungsi SQL yang paling umum.

Apa itu Produk Kartesius?

Produk Kartesius dalam SQL adalah hasil penggabungan setiap baris dari satu tabel dengan setiap baris dari tabel lain. Operasi ini paling sering dihasilkan melalui CROSS JOIN, membentuk himpunan hasil yang berisi semua pasangan baris yang mungkin dari kedua tabel. Meskipun kuat, ini dapat menghasilkan himpunan hasil yang sangat besar, terutama saat bekerja dengan tabel yang berisi banyak baris, sehingga harus digunakan dengan hati-hati. Faktanya, saya yakin banyak pengembang pertama kali menemukan produk Kartesius dengan cara yang sulit, yaitu secara tidak sengaja mengabaikan kondisi join tertentu, yang berujung pada himpunan hasil yang masif.

Latar belakang matematis

Dalam matematika, produk Kartesius (dilambangkan sebagai A × B) adalah himpunan dari semua pasangan berurutan yang mungkin yang dibuat dengan memasangkan setiap elemen himpunan A dengan setiap elemen himpunan B. Dalam SQL, ini diterjemahkan menjadi memasangkan setiap baris dari satu tabel dengan setiap baris di tabel lain.

Contoh produk Kartesius. Gambar oleh Penulis.

SQL cross join

Operasi cross join dalam SQL membuat produk Kartesius antara dua tabel dengan menggabungkan setiap baris dari tabel pertama dengan setiap baris dari tabel kedua. Hasilnya, cross join menghasilkan semua kombinasi baris yang mungkin untuk data tersebut. Mari kita lihat contoh melakukan operasi cross join dalam SQL.

Misalkan Anda memiliki dua tabel, Students dan Courses:

Tabel Students

Tabel Courses

Dalam kueri berikut, setiap siswa dipasangkan dengan setiap mata kuliah, menampilkan kelas dan tingkat kesulitannya.

-- 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;Didukung oleh

Contoh produk Kartesius dalam SQL yang dihasilkan dari cross join. Gambar dari Penulis.

Kapan Menggunakan dan Tidak Menggunakan Produk Kartesius dalam SQL

Produk Kartesius dalam SQL bisa sangat berguna ketika perilakunya dipahami dan digunakan dengan sengaja. Mari kita lihat kasus penggunaan (dan potensi jebakan) penggunaan produk Kartesius.

Kasus penggunaan

Berikut adalah skenario di mana penggunaan produk Kartesius mungkin berguna:

• Menghasilkan Kombinasi: Produk Kartesius ideal untuk menghasilkan semua kombinasi dari dua himpunan nilai. Misalnya, Anda mungkin ingin memasangkan produk dengan diskon, mencocokkan karyawan dengan shift, atau membuat semua pasangan yang mungkin antar berbagai kategori.
• Skenario Pengujian: Saat menjalankan test case, produk Kartesius dapat berguna untuk mensimulasikan setiap kemungkinan skenario dengan mencocokkan berbagai himpunan parameter atau konfigurasi.
• Mengisi Data yang Hilang: Produk Kartesius juga dapat membantu mengidentifikasi celah dengan menghasilkan semua kombinasi yang mungkin dan kemudian melakukan left join pada hasilnya untuk menemukan entri yang hilang, seperti tanggal yang hilang dalam analisis deret waktu.

Potensi jebakan

Meskipun produk Kartesius berguna untuk beberapa skenario, ada sejumlah risiko, di antaranya:

• Masalah Kinerja: Karena produk Kartesius mengalikan baris dari satu tabel dengan baris dari tabel lain, hasilnya dapat tumbuh secara eksponensial. Jika set data terlalu besar, hal ini dapat menyebabkan waktu eksekusi kueri menjadi lambat, penggunaan memori berlebihan, bahkan kegagalan basis data.
• Duplikasi Data yang Tidak Disengaja: Tanpa penyaringan yang cermat, produk Kartesius dapat menghasilkan baris yang tampak duplikat atau tidak relevan untuk analisis, sehingga menimbulkan interpretasi data yang bias atau hasil yang salah jika duplikat tersebut tidak diantisipasi atau dikelola dengan benar.
• Sulit Dilacak: Produk Kartesius yang tidak disengaja, seperti lupa menentukan kondisi join, dapat menyebabkan cross join alih-alih inner atau outer join. Ini dapat menghasilkan keluaran yang tidak diharapkan dan sulit ditelusuri kembali serta diperbaiki.

Aplikasi Nyata dari Produk Kartesius

Meskipun produk Kartesius mungkin digunakan secara terbatas karena potensi risikonya, ini berguna dalam skenario di mana Anda perlu menggabungkan setiap pasangan elemen data yang mungkin.

Skenario penggabungan data

Produk Kartesius berguna untuk menghitung metrik kinerja relatif, menganalisis kompatibilitas, atau membuat sistem pemeringkatan. Saat Anda perlu membandingkan semua elemen dalam dua set data, Anda dapat menggunakan produk Kartesius untuk memasangkan setiap item dengan setiap item lainnya. 

Sebagai contoh, untuk membandingkan kinerja setiap karyawan dengan setiap karyawan lainnya, produk Kartesius akan membuat semua pasangan karyawan yang mungkin:

-- 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;Didukung oleh

Produk Kartesius juga penting dalam analitik e-commerce untuk mengeksplorasi seluruh rentang pasangan produk-diskon. Misalkan Anda memiliki tabel Products dan Discounts, cross join dapat membuat daftar semua kombinasi produk-diskon:

-- Select product names and discount types
SELECT 
    Products.product_name,
    Discounts.discount_type
FROM Products
-- Generate all product and discount combinations
CROSS JOIN Discounts;Didukung oleh

Pengujian dan validasi

Produk Kartesius memungkinkan pembuatan semua kombinasi parameter yang potensial dalam lingkungan pengujian, yang penting untuk pengujian skenario secara menyeluruh. Misalnya, jika menguji sistem dengan berbagai konfigurasi OS, perangkat, dan browser, menggunakan cross join memastikan setiap kemungkinan konfigurasi diuji.

-- 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;Didukung oleh

Produk Kartesius dapat membantu mengidentifikasi catatan yang hilang, sehingga berguna untuk memastikan kelengkapan data. Misalnya, saat menganalisis data penjualan harian dan memastikan bahwa data tersedia untuk setiap produk setiap hari, Anda dapat membuat produk Kartesius dari tabel Products dan Dates. Kemudian, Anda dapat melakukan left join pada data penjualan aktual untuk mengidentifikasi entri tanggal-produk yang hilang.

-- 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;Didukung oleh

Beberapa Gagasan tentang Kinerja

Karena produk Kartesius menghasilkan kombinasi data yang mungkin antara dua tabel, mungkin ada masalah kinerja yang signifikan saat tabel besar terlibat. Saya menyarankan teknik berikut untuk pengoptimalan kueri dan pengelolaan set data besar guna meningkatkan kinerja basis data.

Optimasi kueri

Pertimbangkan metode berikut untuk menulis kueri efisien yang melibatkan produk Kartesius.

• Batasi Jumlah Baris Sebelum Melakukan Join: Untuk mengurangi jumlah baris yang terlibat dalam produk Kartesius, terapkan filter pada setiap tabel sebelum melakukan cross join. Misalnya, gunakan klausa WHERE untuk memfilter setiap tabel berdasarkan kriteria yang relevan, sehingga meminimalkan himpunan data yang dihasilkan.

• Gunakan SELECT dengan Kolom Spesifik: Saat melakukan produk Kartesius, tentukan hanya kolom yang Anda butuhkan dalam klausa SELECT untuk mengurangi penggunaan memori dan meningkatkan kinerja. Hindari menggunakan SELECT * karena akan mengembalikan semua kolom, yang berpotensi menghasilkan hasil yang besar dan sulit dikelola.

• Terapkan Kondisi untuk Mengonversi Cross Join menjadi Inner Join: Jika hanya kombinasi baris tertentu yang relevan, pertimbangkan menambahkan kondisi tambahan pada cross join yang berfungsi seperti INNER JOIN. Ini membantu membatasi keluaran dengan mempersempit baris mana dari setiap tabel yang harus dipasangkan.

• Pemrosesan Batch: Untuk set data besar, pecah kueri menjadi potongan yang dapat dikelola untuk diproses per batch. Ini dapat mencegah kelebihan memori dan meningkatkan waktu respons, terutama saat melakukan kueri pada tabel besar.

Mengelola set data besar

Gunakan teknik berikut untuk mengelola set data besar yang dihasilkan dari penggunaan produk Kartesius.

• Pengindeksan: Tambahkan indeks pada kolom yang sering dijoin untuk meningkatkan kinerja saat memfilter atau memasangkan tabel besar. Meskipun pengindeksan tidak secara langsung mempercepat cross join, ini berguna saat Anda kemudian memfilter atau melakukan join produk Kartesius dengan tabel lain, sehingga basis data dapat mengakses baris lebih cepat.

• Partisi Tabel Besar: Pemartisian tabel dapat meningkatkan kinerja kueri yang melibatkan produk Kartesius. Pemartisian membagi tabel menjadi bagian-bagian yang lebih kecil dan mudah dikelola berdasarkan kolom tertentu, memungkinkan basis data membaca hanya partisi yang relevan selama eksekusi kueri.

• Batasi Baris Hasil: Gunakan klausa LIMIT untuk membatasi jumlah baris yang dikembalikan dalam kasus saat Anda hanya membutuhkan sampel dari produk Kartesius untuk tujuan pengujian atau verifikasi.

• Gunakan Tabel Sementara untuk Hasil Perantara: Simpan hasil perantara dalam tabel sementara jika Anda perlu menggunakan kembali produk Kartesius beberapa kali untuk mengurangi perhitungan ulang dan mempercepat operasi berikutnya.

Operasi SQL Lain yang Membantu

Dalam skenario di mana menghasilkan produk Kartesius penuh mungkin tidak efisien, terdapat metode alternatif. Metode di bawah ini memungkinkan Anda mencapai hasil yang diinginkan tanpa membebani basis data.

Inner dan outer join

INNER JOIN hanya mengembalikan baris yang cocok antara kolom yang ditentukan di kedua tabel. Teknik ini membatasi hasil pada kombinasi yang relevan dan menghindari banyaknya baris yang muncul pada produk Kartesius. Inner join tepat digunakan saat Anda hanya memerlukan baris yang memenuhi kriteria tertentu di kedua tabel. 

Pada contoh di bawah, INNER JOIN hanya mengembalikan produk dan diskon yang memiliki kategori yang sama.

-- 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;Didukung oleh

Outer join (LEFT JOIN, RIGHT JOIN, dan FULL JOIN) mengembalikan baris yang cocok maupun tidak cocok dari satu atau kedua tabel. Teknik ini bermanfaat saat mengambil semua baris dari satu tabel dan baris yang cocok dari tabel lain, tetapi tanpa membuat semua kombinasi yang mungkin. Outer join tepat digunakan saat Anda ingin menyertakan data yang tidak cocok untuk analisis tetapi tidak ingin melakukan pencocokan Kartesius penuh.

LEFT JOIN pada kueri di bawah mengembalikan semua produk, termasuk yang tidak memiliki diskon terkait, dengan nilai NULL untuk diskon yang tidak cocok.

-- 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;  Didukung oleh

Saya merekomendasikan mengikuti kursus Introduction to SQL Server dari DataCamp untuk mempelajari lebih lanjut cara melakukan join tabel dan mengambil data dari beberapa tabel.

Subkueri dan CTE

Subkueri memungkinkan Anda memilih data spesifik dari satu tabel dan menggunakannya untuk memfilter hasil pada kueri utama. Ini membantu menghindari kombinasi baris yang tidak perlu dengan memfilter data hanya pada entri yang relevan. Subkueri berguna saat Anda perlu memfilter data terlebih dahulu atau menghitung nilai tertentu untuk digunakan dalam kueri utama tanpa memerlukan join Kartesius penuh.

Subkueri memfilter diskon dengan nilai di atas 10 sebelum join, mengurangi data yang diproses dan menghindari cross join yang tidak perlu.

-- 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;   Didukung oleh

CTE, atau klausa WITH, memungkinkan kueri kompleks dipecah menjadi beberapa langkah. Metode ini membantu menghindari produk Kartesius dengan memfilter atau mengagregasi data sebelum melakukan join. CTE tepat digunakan saat menyederhanakan kueri multi-langkah, terutama ketika melakukan pra-agregasi data atau melakukan beberapa join, tetapi ingin menghindari hasil Kartesius penuh.

Kueri di bawah menggunakan Common Table Expression (CTE) untuk memfilter diskon lebih besar dari 10 sebelum melakukan join dengan produk berdasarkan kategori.

-- 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;Didukung oleh

Aplikasi Inovatif Produk Kartesius

Meskipun produk Kartesius banyak digunakan dalam operasi SQL dasar, konsep ini memiliki aplikasi di luar kasus penggunaan basis data tradisional. Mari kita lihat bagaimana produk Kartesius dapat digunakan dalam penyiapan data machine learning dan pembuatan dataset sintetis untuk simulasi.

Penyiapan data machine learning

Dataset yang beragam dan komprehensif penting saat menyiapkan data untuk machine learning guna melatih model yang andal. Produk Kartesius dapat membuat dataset lengkap yang mencakup semua kombinasi nilai fitur, memungkinkan model belajar dari beragam skenario. 

Sebagai contoh, saat membangun model deteksi penipuan, Anda dapat menggabungkan jenis transaksi, wilayah, dan jenis perangkat untuk mensimulasikan berbagai kondisi, membantu model melakukan generalisasi pada berbagai kasus.

Pembuatan data sintetis

Data sintetis sering digunakan untuk menguji sistem dalam berbagai kondisi atau untuk melindungi privasi dengan menghindari penggunaan data nyata. Produk Kartesius dapat membantu menghasilkan dataset sintetis yang beragam, yang berguna dalam berbagai aplikasi mulai dari simulasi hingga augmentasi data untuk model machine learning.

Sebagai contoh, Anda dapat menggabungkan berbagai kondisi pasar, suku bunga, dan profil pelanggan dalam simulasi keuangan untuk menguji bagaimana sebuah model atau algoritma bekerja pada variabel-variabel tersebut.

Di bawah ini adalah contoh sederhana tentang bagaimana Anda dapat membuat data sintetis “dari nol”, bisa dibilang, dalam SQL dan kemudian menggunakan CROSS JOIN untuk memperluasnya menjadi dataset yang lebih besar. Idenya adalah setiap tabel kecil mewakili satu dimensi data uji kita, lalu saat kita melakukan cross join, kita mendapatkan semua kombinasi yang mungkin.

Contoh 1: Menggabungkan beberapa dimensi

Misalkan Anda ingin mensimulasikan berbagai skenario keuangan berdasarkan kondisi Pasar, suku bunga, dan profil Pelanggan. Kita dapat mendefinisikan setiap dimensi melalui Common Table Expression (CTE) atau tabel sementara menggunakan nilai literal. Lalu, CROSS JOIN menggabungkannya menjadi setiap kombinasi yang mungkin:

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;Didukung oleh

Himpunan hasilnya akan memiliki 3×3×3 = 27 baris. Setiap baris adalah satu kombinasi unik dari kondisi pasar, suku bunga, dan profil pelanggan.

Contoh pembuatan data sintetis menggunakan produk Kartesius. Gambar oleh Penulis.

Contoh 2: Menghasilkan baris menggunakan urutan atau VALUES sederhana

Teknik lain adalah menghasilkan rentang angka atau interval waktu dengan melakukan cross join tabel kecil dengan dirinya sendiri. Ini bisa digunakan untuk menghasilkan volume baris sintetis yang besar untuk pengujian cepat. Misalnya, kita dapat menggunakan kueri berikut untuk menghasilkan angka 1 hingga 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;Didukung oleh

Menggunakan produk Kartesius untuk menghasilkan baris menggunakan urutan. Gambar oleh Penulis.

Kita kemudian dapat melakukan cross join pada set 10 baris yang sama dengan dirinya sendiri untuk menghasilkan 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;Didukung oleh

Untuk hasilnya, Anda akan mendapatkan 100 baris, masing-masing memiliki sepasang nilai (n1,n2). Anda dapat memperluas logika ini menjadi ratusan, ribuan, bahkan jutaan baris—meskipun tetap perhatikan kinerja dan penyimpanan.

Dengan menggunakan pendekatan ini—CTE (atau klausa VALUES) untuk definisi dimensi dan CROSS JOIN untuk kombinasi—Anda dapat dengan cepat membuat dataset sintetis di SQL tanpa bergantung pada tabel yang sudah ada.

Kesimpulan

Memahami produk Kartesius dalam SQL membuka beragam kemungkinan untuk manipulasi dan analisis data. Dengan menguasai konsep ini, Anda dapat meningkatkan kemampuan bekerja dengan set data yang kompleks dan mengoptimalkan kueri basis data Anda. Untuk wawasan yang lebih lanjut, pertimbangkan SQL Server Fundamentals skill track kami untuk memperdalam keahlian Anda. 

Bergantung pada arah karier Anda, saya merekomendasikan untuk mendaftar pada career track Associate Data Analyst in SQL dari DataCamp untuk mempelajari keterampilan tingkat industri yang diperlukan menjadi analis data atau mencoba career track Associate Date Engineer in SQL kami untuk menjadi data engineer.