Saya memiliki dua tabel dalam database PostgreSQL: salesItems dan packageItems. Tabel…
- User
Saya memiliki dua tabel dalam database PostgreSQL: salesItems dan packageItems. Tabel salesItems
Berisi informasi mengenai setiap item penjualan dan memiliki kolom berikut:
id: ID unik dari tabel ini. code: Kode unik dari setiap sales item. name: Nama sales item. name_en: Nama sales item dalam bahasa Inggris. sales_item_type: Tipe sales item, yang dapat berupa MCU, LAB, RAD, atau Homecare. type: Menentukan apakah item bersifat Package atau Individual.Tabel packageItems
Digunakan untuk menyimpan informasi terkait paket penjualan. Memiliki kolom berikut:
id: ID unik dari tabel ini. packageId: ID dari salesItems yang bertipe Package. salesItemId: ID dari sales item yang termasuk dalam paket tersebut.Permintaan Query
Saya membutuhkan sebuah query untuk mengambil semua sales item dengan sales_item_type = 'lab'. Namun, jika item tersebut bertipe Package, maka semua sales item di dalam paket tersebut juga harus ditampilkan dalam baris yang sama.
Contoh hasil yang diharapkan: Jika sebuah paket berisi tiga sales item, maka hasil query akan menghasilkan tiga baris data yang memuat informasi paket serta sales item yang ada di dalamnya.
Assistant
WITH Recursive PackageExpansion AS ( -- Langkah 1: Pilih sales item dengan sales_item_type = 'lab' SELECT si.id AS package_item_id, si.id AS sales_item_id, si.code AS sales_item_code, si.name AS sales_item_name, si.name_en AS sales_item_name_en, si.sales_item_type AS sales_item_type, si.type AS sales_item_type_detail FROM salesItems si WHERE si.sales_item_type = 'lab' UNION ALL -- Langkah 2: Rekursif kembangkan paket jika sales item adalah tipe 'Package' SELECT pe.package_item_id, si.id AS sales_item_id, si.code AS sales_item_code, si.name AS sales_item_name, si.name_en AS sales_item_name_en, si.sales_item_type AS sales_item_type, si.type AS sales_item_type_detail FROM PackageExpansion pe JOIN packageItems pi ON pe.sales_item_id = pi.packageId JOIN salesItems si ON pi.salesItemId = si.id ) -- Langkah 3: Pilih semua kolom yang diinginkan dari hasil rekursif SELECT package_item_id, sales_item_id, sales_item_code, sales_item_name, sales_item_name_en, sales_item_type, sales_item_type_detail FROM PackageExpansion;Penjelasan Query:
-
WITH Recursive PackageExpansion AS (...): Ini adalah Common Table Expression (CTE) rekursif bernamaPackageExpansion. CTE rekursif memungkinkan kita untuk melakukan query yang merujuk pada dirinya sendiri. Ini sangat berguna untuk masalah hierarkis seperti ekspansi paket. -
SELECT ... FROM salesItems si WHERE si.sales_item_type = 'lab'(Bagian Dasar): Ini adalah bagian dasar dari rekursi. Ini memilih semua sales item yang memilikisales_item_type = 'lab'. Ini adalah titik awal untuk ekspansi paket. Perhatikan bahwa kita menyimpaniditem awal sebagaipackage_item_id, yang akan kita gunakan nanti untuk melacak paket asal. Kita juga memilih kolom lain yang relevan seperticode,name,name_en, dantype.sales_item_type_detailmenyimpan kolom 'type' dari tabelsalesItems, untuk mengidentifikasi apakah item tersebut merupakanPackageatauIndividual. -
UNION ALL: Menggabungkan hasil bagian dasar dengan hasil rekursif.UNION ALLmempertahankan semua baris, termasuk duplikat (yang mungkin kita inginkan dalam kasus paket). -
SELECT ... FROM PackageExpansion pe JOIN packageItems pi ON pe.sales_item_id = pi.packageId JOIN salesItems si ON pi.salesItemId = si.id(Bagian Rekursif): Ini adalah bagian rekursif.PackageExpansion pe: Merujuk pada CTE itu sendiri. Pada setiap iterasi, ini berisi hasil sebelumnya.JOIN packageItems pi ON pe.sales_item_id = pi.packageId: Bergabung dengan tabelpackageItemsuntuk menemukan item yang merupakan bagian dari paket. Kondisipe.sales_item_id = pi.packageIdmencocokkansales_item_iddari hasil sebelumnya denganpackageIddi tabelpackageItems. Ini menemukan item paket.JOIN salesItems si ON pi.salesItemId = si.id: Bergabung dengan tabelsalesItemslagi untuk mendapatkan detail dari item paket yang ditemukan di atas.- Secara efektif, bagian rekursif ini mencari semua
salesItemyang termasuk dalam paket di hasil sebelumnya.
-
SELECT ... FROM PackageExpansion(Query Terakhir): Query terakhir memilih semua kolom yang diinginkan dari CTEPackageExpansion. Ini memberikan hasil akhir.
Cara Kerja:
- Query dimulai dengan memilih semua sales item dengan
sales_item_type = 'lab'. - Kemudian, bagian rekursif memeriksa apakah salah satu dari item-item ini bertipe 'Package'.
- Jika ya, ia bergabung dengan tabel
packageItemsuntuk menemukan semuasalesItemyang merupakan bagian dari paket tersebut. - Proses ini berulang sampai tidak ada lagi paket yang dapat diperluas (yaitu, sampai tidak ada lagi item paket yang ditemukan).
- Akhirnya, query memilih semua baris yang dikumpulkan oleh proses rekursif.
Contoh:
Katakanlah Anda memiliki data berikut:
salesItems:
id code name name_en sales_item_type type 1 P001 Paket Lab A Lab Package A LAB Package 2 L001 Tes Darah Blood Test LAB Individual 3 L002 Urinalisis Urinalysis LAB Individual 4 L003 Tes Fungsi Hati Liver Function LAB Individual packageItems:
id packageId salesItemId 1 1 2 2 1 3 3 1 4 Query akan menghasilkan:
package_item_id sales_item_id sales_item_code sales_item_name sales_item_name_en sales_item_type sales_item_type_detail 1 1 P001 Paket Lab A Lab Package A LAB Package 1 2 L001 Tes Darah Blood Test LAB Individual 1 3 L002 Urinalisis Urinalysis LAB Individual 1 4 L003 Tes Fungsi Hati Liver Function LAB Individual 2 2 L001 Tes Darah Blood Test LAB Individual 3 3 L002 Urinalisis Urinalysis LAB Individual 4 4 L003 Tes Fungsi Hati Liver Function LAB Individual Pertimbangan:
- Performa: Untuk dataset yang sangat besar, query rekursif mungkin lambat. Pertimbangkan untuk menambahkan indeks pada kolom yang sering digunakan dalam
JOIN(sepertipackageIddansalesItemId). - Kedalaman Rekursi: PostgreSQL memiliki batas kedalaman rekursi. Jika paket Anda sangat kompleks dan memiliki banyak level, Anda mungkin perlu meningkatkan batas ini dengan
SET session max_recursive_depth = <nilai>. Tetapi secara umum arsitektur seperti ini akan sangat jarang ditemui. - Duplikat: Jika struktur paket Anda memiliki loop (misalnya, paket A berisi paket B, dan paket B berisi paket A), query ini akan menghasilkan loop tak terbatas. Pastikan data Anda tidak memiliki loop. Anda dapat menambahkan logika untuk mendeteksi dan mencegah loop jika diperlukan (walaupun ini jarang terjadi dalam skenario nyata).
Query ini memenuhi persyaratan untuk mengambil semua sales item dengan
sales_item_type = 'lab', dan untuk menampilkan semua item di dalam paket, jika item tersebut bertipe 'Package'. Semoga ini membantu!-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.