Checkpoint
Create a new dataset
/ 25
Identify a key field in your ecommerce dataset
/ 25
Pitfall: non-unique key
/ 25
Join pitfall solution
/ 25
Pemecahan Masalah dan Menyelesaikan Kendala Penggabungan Data
- GSP412
- Ringkasan
- Penyiapan dan persyaratan
- Tugas 1. Membuat set data baru untuk menyimpan tabel Anda
- Tugas 2. Menyematkan project lab di BigQuery
- Tugas 3. Memeriksa kolom
- Tugas 4. Mengidentifikasi kolom kunci dalam set data e-commerce Anda
- Tugas 5. Kendala: kunci tidak unik
- Tugas 6. Solusi kendala gabungan: gunakan SKU yang berbeda sebelum penggabungan
- Selamat!
GSP412
Ringkasan
BigQuery adalah database analisis berbiaya rendah dan tanpa pengoperasian (NoOps) yang dikelola sepenuhnya oleh Google. Dengan BigQuery, Anda dapat mengkueri data berskala terabyte tanpa perlu mengelola infrastruktur atau memiliki administrator database. BigQuery menggunakan SQL dan dapat memanfaatkan model bayar sesuai penggunaan. Dengan BigQuery, Anda dapat berfokus untuk menganalisis data guna menemukan insight yang bermakna.
Menggabungkan tabel data dapat memberikan insight yang bermakna terkait set data Anda. Namun, saat menggabungkan data, ada kendala umum yang dapat merusak hasil Anda. Lab ini berfokus untuk menghindari kendala tersebut. Jenis-jenis gabungan (join):
- Cross join: menggabungkan tiap baris dari set data pertama dengan tiap baris dari set data kedua, di mana tiap kombinasi direpresentasikan dalam outputnya.
- Inner join: mengharuskan adanya nilai kunci di kedua tabel agar kumpulan data muncul di tabel hasil. Kumpulan data muncul dalam gabungan hanya jika ada kecocokan di kedua tabel untuk nilai kuncinya.
- Left join: Tiap baris di tabel kiri muncul di hasil, terlepas dari apakah ada kecocokan di tabel kanan atau tidak.
- Right join: kebalikan dari left join. Tiap baris di tabel kanan akan muncul di hasil, terlepas dari apakah ada kecocokan di tabel kiri atau tidak.
Untuk mengetahui informasi lebih lanjut tentang penggabungan, lihat Halaman Gabungan.
Set data yang digunakan dalam lab ini adalah set data e-commerce yang memiliki jutaan kumpulan data Google Analytics untuk Google Merchandise Store yang dimuat ke BigQuery. Anda memiliki salinan set data tersebut untuk lab ini dan akan menjelajahi kolom dan baris yang tersedia untuk mendapatkan insight.
Untuk mengetahui informasi sintaksis guna membantu Anda mengikuti dan memperbarui kueri, lihat Sintaksis Kueri SQL Standar.
Yang akan Anda lakukan
Di lab ini, Anda akan mempelajari cara:
- Menggunakan BigQuery untuk menjelajahi dan memecahkan masalah baris duplikat dalam set data.
- Membuat gabungan antara tabel data.
- Memilih di antara jenis-jenis gabungan yang berbeda.
Penyiapan dan persyaratan
Sebelum mengklik tombol Mulai Lab
Baca petunjuk ini. Lab memiliki timer dan Anda tidak dapat menjedanya. Timer, yang dimulai saat Anda mengklik Start Lab, akan menampilkan durasi ketersediaan resource Google Cloud untuk Anda.
Lab praktik ini dapat Anda gunakan untuk melakukan sendiri aktivitas lab di lingkungan cloud sungguhan, bukan di lingkungan demo atau simulasi. Untuk mengakses lab ini, Anda akan diberi kredensial baru yang bersifat sementara dan dapat digunakan untuk login serta mengakses Google Cloud selama durasi lab.
Untuk menyelesaikan lab ini, Anda memerlukan:
- Akses ke browser internet standar (disarankan browser Chrome).
- Waktu untuk menyelesaikan lab. Ingat, setelah dimulai, lab tidak dapat dijeda.
Cara memulai lab dan login ke Google Cloud Console
-
Klik tombol Start Lab. Jika Anda perlu membayar lab, jendela pop-up akan terbuka untuk memilih metode pembayaran. Di sebelah kiri adalah panel Lab Details dengan berikut ini:
- Tombol Open Google Console
- Waktu tersisa
- Kredensial sementara yang harus Anda gunakan untuk lab ini
- Informasi lain, jika diperlukan, untuk menyelesaikan lab ini
-
Klik Open Google Console. Lab akan menjalankan resource, lalu membuka tab lain yang menampilkan halaman Login.
Tips: Atur tab di jendela terpisah secara berdampingan.
Catatan: Jika Anda melihat dialog Choose an account, klik Use Another Account. -
Jika perlu, salin Username dari panel Lab Details dan tempel ke dialog Sign in. Klik Next.
-
Salin Password dari panel Lab Details dan tempel ke dialog Welcome. Klik Next.
Penting: Anda harus menggunakan kredensial dari panel sebelah kiri. Jangan menggunakan kredensial Google Cloud Skills Boost. Catatan: Menggunakan akun Google Cloud sendiri untuk lab ini dapat dikenai biaya tambahan. -
Klik halaman berikutnya:
- Setujui persyaratan dan ketentuan.
- Jangan tambahkan opsi pemulihan atau autentikasi 2 langkah (karena ini akun sementara).
- Jangan daftar uji coba gratis.
Setelah beberapa saat, Cloud Console akan terbuka di tab ini.
Membuka konsol BigQuery
- Di Google Cloud Console, pilih Navigation menu > BigQuery.
Kotak pesan Welcome to BigQuery in the Cloud Console akan terbuka. Kotak pesan ini menyediakan link ke panduan memulai dan catatan rilis.
- Klik Done.
Konsol BigQuery terbuka.
Tugas 1. Membuat set data baru untuk menyimpan tabel Anda
Di project BigQuery Anda, buat set data baru yang bernama ecommerce
.
- Klik tiga titik di samping Project ID Anda, lalu pilih Create dataset.
Dialog Create dataset terbuka.
-
Tetapkan dataset ID ke
ecommerce
. -
Biarkan opsi lain diisi dengan nilai defaultnya, lalu klik Create dataset.
Di panel kiri, terlihat sebuah tabel ecommerce
yang tercantum dalam project Anda.
Klik Check my progress untuk memverifikasi tujuan.
Tugas 2. Menyematkan project lab di BigQuery
Skenario: Tim Anda memberikan set data baru terkait tingkat stok inventaris untuk tiap produk yang dijual di situs e-commerce Anda. Anda ingin memahami produk di situs dan kolom yang dapat Anda gunakan untuk digabungkan ke set data lain.
Project dengan set data baru ini adalah data-to-insights.
- Klik Navigation menu > BigQuery.
Kotak pesan Welcome to BigQuery pada Konsol Cloud akan terbuka.
-
Klik Done.
-
Set data publik BigQuery tidak ditampilkan secara default. Untuk membuka project set data publik, salin data-to-insights.
-
Klik + Add > Star a project by name lalu tempel nama data-to-insights.
-
Klik Star.
Project data-to-insights
dicantumkan pada bagian Explorer.
Tugas 3. Memeriksa kolom
Selanjutnya, kenali produk dan kolom di situs yang dapat Anda gunakan untuk membuat kueri guna menganalisis set data.
-
Di panel sebelah kiri pada bagian Resources, arahkan kursor ke
data-to-insights
>ecommerce
>all_sessions_raw
. -
Di sebelah kanan, di bawah Query editor, klik tab Schema untuk melihat Fields dan informasi terkait tiap kolom.
Tugas 4. Mengidentifikasi kolom kunci dalam set data e-commerce Anda
Periksa produk dan kolom lebih lanjut. Anda ingin memahami produk di situs dan kolom yang dapat Anda gunakan untuk digabungkan ke set data lain.
Memeriksa kumpulan data
Di bagian ini, Anda akan mengetahui berapa banyak nama produk dan SKU produk yang ada di situs Anda dan apakah salah satu kolom tersebut unik atau tidak.
- Temukan berapa nama produk dan SKU produk yang ada di situs. Salin dan Tempel kueri berikut di EDITOR BigQuery:
- Klik Run.
Lihat hasil penomoran halaman di konsol untuk mengetahui jumlah total kumpulan data yang ditampilkan.
Tapi apakah hasil ini berarti SKU produk yang unik ada sebanyak itu? Salah satu kueri pertama yang akan Anda jalankan sebagai analis data adalah melihat keunikan nilai data Anda.
- Hapus kueri sebelumnya dan jalankan kueri di bawah ini untuk mencantumkan jumlah SKU unik yang dicantumkan menggunakan
DISTINCT
:
Memeriksa hubungan antara SKU & Nama
Sekarang tentukan produk mana yang memiliki lebih dari satu SKU dan SKU mana yang memiliki lebih dari satu Nama Produk.
- Hapus kueri sebelumnya dan jalankan kueri di bawah ini untuk menentukan apakah beberapa nama produk memiliki lebih dari satu SKU. Penggunaan fungsi STRING_AGG() adalah untuk menggabungkan semua SKU produk yang dikaitkan dengan satu nama produk ke dalam comma separated values.
- Klik Run.
Hasil:
ecommerce website catalog menunjukkan bahwa tiap nama produk mungkin memiliki beberapa pilihan (ukuran, warna) yang dijual sebagai SKU terpisah.
Jadi Anda sudah melihat bahwa 1 Produk bisa memiliki 12 SKU. Bagaimana dengan 1 SKU? Apakah boleh dimiliki lebih dari 1 produk?
- Hapus kueri sebelumnya dan jalankan kueri di bawah ini untuk mengetahuinya:
Anda akan melihat mengapa hubungan data many-to-many ini menjadi masalah di bagian berikutnya.
Klik Check my progress untuk memverifikasi tujuan.
Tugas 5. Kendala: kunci tidak unik
Dalam pelacakan inventaris, SKU dirancang untuk mengidentifikasi satu dan hanya satu produk secara unik. Namun, hal ini akan menjadi dasar kondisi JOIN Anda ketika Anda mencari informasi dari tabel lain. Memiliki kunci yang tidak unik dapat menyebabkan masalah data yang serius seperti yang akan Anda lihat.
-
Tulis kueri untuk mengidentifikasi semua nama produk untuk SKU
'GGOEGPJC019099'
.
Solusi yang dapat dilakukan:
- Klik Run.
v2ProductName |
productSKU |
7" Dog Frisbee |
GGOEGPJC019099 |
7" Dog Frisbee |
GGOEGPJC019099 |
Google 7-inch Dog Flying Disc Blue |
GGOEGPJC019099 |
Dari hasil kueri, sepertinya ada tiga nama berbeda untuk produk yang sama. Dalam contoh ini, terdapat karakter khusus pada satu nama dan nama yang sedikit berbeda untuk yang lain:
Menggabungkan data situs terhadap daftar inventaris produk Anda
Sekarang lihat dampak penggabungan pada set data yang memiliki beberapa produk untuk satu SKU. Pertama-tama, jelajahi set data inventaris produk (tabel products
) untuk melihat apakah SKU ini unik atau tidak di tabel tersebut.
- Hapus kueri sebelumnya, lalu jalankan kueri di bawah ini:
Kendala Gabungan: Hubungan SKU many-to-one yang tidak disengaja
Anda sekarang memiliki dua set data: satu untuk tingkat stok inventaris dan yang lainnya untuk analisis situs kita. Dengan fungsi JOIN, gabungkan set data inventaris dengan nama produk dan SKU situs Anda, sehingga Anda dapat mengetahui tingkat stok inventaris yang terkait dengan tiap produk yang dijual di situs.
- Hapus kueri sebelumnya, lalu jalankan kueri di bawah ini:
Selanjutnya, perluas kueri sebelumnya untuk menjumlahkan inventaris yang tersedia berdasarkan produk menggunakan fungsi SUM.
- Hapus kueri sebelumnya, lalu jalankan kueri di bawah ini:
Astaga! Jadinya 154x3=462 atau inventaris dihitung tiga kali! Ini disebut cross join yang tidak disengaja (topik yang akan dibahas nanti).
Klik Check my progress untuk memverifikasi tujuan.
Tugas 6. Solusi kendala gabungan: gunakan SKU yang berbeda sebelum penggabungan
Apa saja pilihan untuk mengatasi dilema penghitungan rangkap tiga yang Anda alami? Pertama, Anda perlu memilih hanya SKU yang berbeda dari situs sebelum menggabungkan dengan set data lainnya.
Anda tahu bahwa mungkin ada lebih dari satu nama produk (misalnya 7" Dog Frisbee) yang dapat menggunakan SKU yang sama.
- Kumpulkan semua nama yang mungkin ke dalam array:
Sekarang, alih-alih memiliki baris untuk tiap Nama Produk, Anda hanya memiliki satu baris untuk tiap SKU unik.
- Jika Anda ingin menghapus duplikat nama produk, Anda bahkan dapat membatasi array menggunakan fungsi LIMIT, seperti:
Kendala gabungan: kehilangan kumpulan data setelah penggabungan
Sekarang Anda siap untuk menggabungkan dengan set data inventaris produk Anda lagi.
- Hapus kueri sebelumnya, lalu jalankan kueri di bawah ini:
Tampaknya 819 SKU hilang setelah menggabungkan set data tersebut. Selidiki hal ini dengan menambahkan lebih banyak kekhususan di kolom Anda (satu kolom SKU dari tiap set data):
- Hapus kueri sebelumnya, lalu jalankan kueri di bawah ini:
Tampaknya SKU tersebut ada di kedua set data setelah penggabungan 1.090 kumpulan data ini. Bagaimana cara menemukan kumpulan data yang hilang?
Solusi kendala gabungan: memilih jenis gabungan yang benar dan memfilter NULL
Jenis JOIN default adalah INNER JOIN yang menampilkan kumpulan data hanya jika ada kecocokan SKU di tabel kiri dan kanan yang digabungkan.
- Tulis ulang kueri sebelumnya untuk menggunakan jenis penggabungan yang berbeda guna menyertakan semua kumpulan data dari tabel situs, terlepas dari apakah ada kecocokan pada kumpulan data SKU inventaris produk atau tidak. Pilihan jenis gabungan: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN.
Solusi yang dapat dilakukan:
- Klik Run.
Anda telah berhasil menggunakan LEFT JOIN untuk menampilkan ke-1.909 SKU asli situs dalam hasil Anda.
Berapa banyak SKU yang hilang dari set inventaris produk Anda?
- Tulis kueri untuk memfilter nilai NULL dari tabel inventaris.
Solusi yang dapat dilakukan:
- Klik Run.
Pertanyaan: Berapa banyak produk yang hilang?
Jawaban: 819 produk hilang (SKU IS NULL) dari set data inventaris produk Anda.
- Hapus kueri sebelumnya dan jalankan kueri di bawah ini untuk mengonfirmasi penggunaan salah satu SKU spesifik dari set data situs:
Sekarang, bagaimana dengan situasi sebaliknya? Apakah ada produk dalam set data inventaris produk tetapi hilang dari situs?
- Tulis kueri menggunakan jenis gabungan yang berbeda untuk menyelidikinya.
Solusi yang dapat dilakukan:
- Klik Run.
Jawaban: Ya. Ada dua SKU produk yang hilang dari set data situs
Selanjutnya, tambahkan lebih banyak kolom dari set data inventaris produk untuk mendapatkan detail selengkapnya.
- Hapus kueri sebelumnya, lalu jalankan kueri di bawah ini:
Mengapa produk di bawah ini hilang dari set data situs e-commerce?
website_SKU |
SKU |
name |
orderedQuantity |
stockLevel |
restockingLeadTime |
sentimentScore |
sentimentMagnitude |
null |
GGOBJGOWUSG69402 |
USB wired soundbar - in store only |
10 |
15 |
2 |
1.0 |
1.0 |
null |
GGADFBSBKS42347 |
PC gaming speakers |
0 |
100 |
1 |
null |
null |
Kemungkinan jawaban:
- Satu produk baru (tidak ada pesanan, tidak ada sentimentScore) dan satu produk yang berstatus "in store only"
- Kemungkinan lainnya adalah produk baru dengan 0 pesanan
Mengapa produk baru tidak muncul di set data situs Anda?
- Set data situs adalah transaksi pesanan sebelumnya oleh pelanggan. Produk baru yang belum pernah terjual tidak akan muncul di analisis web sampai produk tersebut dilihat atau dibeli.
Bagaimana jika Anda menginginkan satu kueri yang mencantumkan semua produk yang hilang dari situs atau inventaris?
- Tulis kueri menggunakan tipe penggabungan yang berbeda.
Solusi yang dapat dilakukan:
- Klik Run.
Anda memiliki 819+2=821 SKU produk.
LEFT JOIN + RIGHT JOIN = FULL JOIN yang menampilkan semua kumpulan data dari kedua tabel, terlepas dari apakah ada kunci gabungan yang cocok atau tidak. Anda kemudian memfilter dan mengeluarkan ketidakcocokan pada salah satu sisi
Kendala gabungan: cross join yang tidak disengaja
Tidak mengetahui hubungan antara kunci tabel data (1:1, 1:N, N:N) dapat memberikan hasil yang tidak diharapkan dan juga mengurangi performa kueri secara signifikan.
Jenis gabungan yang terakhir adalah CROSS JOIN.
Buat tabel baru dengan persentase diskon seluruh situs yang ingin Anda terapkan di seluruh produk dalam kategori Clearance.
- Hapus kueri sebelumnya, lalu jalankan kueri di bawah ini:
Di panel kiri, site_wide_promotion
kini tercantum di bagian Resource di bawah project dan set data Anda.
- Hapus kueri sebelumnya dan jalankan kueri di bawah ini untuk mengetahui berapa banyak produk yang ada dalam clearance:
Lihat dampak penambahan lebih dari satu kumpulan data secara tidak disengaja di tabel diskon.
- Hapus kueri sebelumnya dan jalankan kueri di bawah ini untuk memasukkan dua kumpulan data lagi ke dalam tabel promosi:
Selanjutnya, lihat nilai data di tabel promosi.
- Hapus kueri sebelumnya, lalu jalankan kueri di bawah ini:
Berapa banyak kumpulan data yang ditampilkan?
Jawaban: 3
Apa yang terjadi jika Anda menerapkan diskon lagi di semua 82 produk clearance?
- Hapus kueri sebelumnya, lalu jalankan kueri di bawah ini:
Berapa banyak produk yang ditampilkan?
Jawaban: Alih-alih 82, Anda sekarang memiliki 246 kumpulan data yang ditampilkan dan jumlah ini lebih banyak daripada tabel awal Anda.
Sekarang selidiki penyebab utamanya dengan memeriksa satu SKU produk.
- Hapus kueri sebelumnya, lalu jalankan kueri di bawah ini:
Apa dampak dari CROSS JOIN?
Jawaban: Karena ada 3 kode diskon untuk digabungkan dengan cross join, Anda menyebabkan set data asli dikalikan 3.
Solusinya adalah mengetahui hubungan data Anda sebelum melakukan penggabungan dan jangan menganggap kunci-kunci adalah unik.
Klik Check my progress untuk memverifikasi tujuan.
Selamat!
Anda telah menyelesaikan lab ini dan mengatasi beberapa kendala serius dalam penggabungan SQL dengan mengidentifikasi kumpulan data duplikat dan mengetahui kapan harus menggunakan tiap jenis JOIN. Bagus!
Langkah berikutnya/Pelajari lebih lanjut
- Punya akun Google Analytics dan ingin melakukan kueri set data Anda sendiri di BigQuery? Ikuti panduan ekspor ini.
- Pelajari lebih lanjut praktik terbaik yang memberikan panduan tentang cara Mengoptimalkan Komputasi Kueri.
- Jika Anda ingin berlatih dengan lebih banyak sintaksis SQL untuk JOIN, baca dokumentasi BigQuery JOIN.
- Baca lab ini:
Sertifikasi dan pelatihan Google Cloud
...membantu Anda mengoptimalkan teknologi Google Cloud. Kelas kami mencakup keterampilan teknis dan praktik terbaik untuk membantu Anda memahami dengan cepat dan melanjutkan proses pembelajaran. Kami menawarkan pelatihan tingkat dasar hingga lanjutan dengan opsi on demand, live, dan virtual untuk menyesuaikan dengan jadwal Anda yang sibuk. Sertifikasi membantu Anda memvalidasi dan membuktikan keterampilan serta keahlian Anda dalam teknologi Google Cloud.
Manual Terakhir Diperbarui pada 03 Februari 2024
Lab Terakhir Diuji pada 20 September 2023
Hak cipta 2024 Google LLC Semua hak dilindungi undang-undang. Google dan logo Google adalah merek dagang dari Google LLC. Semua nama perusahaan dan produk lain mungkin adalah merek dagang masing-masing perusahaan yang bersangkutan.