Persediaan Temu Duga Pengaturcaraan · Pelajaran

Menulis Semula Subpertanyaan Berkorelasi sebagai Cantuman

Ratakan logik berkorelasi menjadi cantuman atau fungsi tetingkap demi prestasi

Pelajaran 4 daripada 413 langkah

Menulis Semula Subpertanyaan Berkorelasi sebagai Cantuman ialah pelajaran Persediaan Temu Duga Pengaturcaraan percuma di CoddyKit. Ini ialah pelajaran 4 daripada 4. Anda boleh membaca keseluruhan pelajaran di bawah secara percuma — kemudian berlatih secara praktikal dalam pelayar menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran Persediaan Temu Duga Pengaturcaraan, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Persediaan Temu Duga Pengaturcaraan merangkumi sejumlah 4 pelajaran.

Mengapa Perlu Menulis Semula

Subkueri berkorelasi mudah dibaca tetapi boleh menjadi perlahan: kueri dalaman mungkin dijalankan sekali bagi setiap baris luar. Penemu duga sering meminta anda menulis semula salah satunya sebagai gabungan atau fungsi tetingkap untuk meningkatkan prestasi.

Matlamatnya ialah mendapatkan hasil yang sama dengan satu imbasan terhadap data, bukannya imbasan dalaman yang berulang.

Mengetahui dua atau tiga corak penulisan semula, serta masa setiap satunya mengekalkan ketepatan, ialah kemahiran teras peringkat pertengahan.

Corak 1: EXISTS kepada INNER JOIN

EXISTS yang berkorelasi dan menguji sekurang-kurangnya satu padanan selalunya boleh ditukar kepada INNER JOIN.

Namun, berhati-hati: gabungan boleh menghasilkan baris luar pendua jika berbilang baris dalaman sepadan. Tambahkan DISTINCT atau lakukan pengagregatan untuk mengembalikan satu baris bagi setiap kunci luar.

-- Correlated EXISTS
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
             WHERE o.customer_id = c.customer_id);

-- Join rewrite (DISTINCT avoids dupes from fan-out)
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

Perangkap Penggandaan

Kesilapan penulisan semula yang paling biasa ialah terlupa tentang penggandaan. EXISTS mengembalikan setiap pelanggan sekali sahaja tanpa mengira bilangan pesanan mereka. Gabungan naif mengembalikan satu baris bagi setiap pesanan, lalu menggelembungkan kiraan.

Jika langkah seterusnya melakukan COUNT(*) atau SUM(amount) terhadap hasil gabungan itu tanpa pengumpulan yang teliti, nombornya akan salah.

Sentiasa tanya: bolehkah gabungan menggandakan baris? Jika ya, gunakan DISTINCT atau GROUP BY untuk menggabungkannya semula.

Corak 2: NOT EXISTS kepada LEFT JOIN / IS NULL

Penulisan semula anti-gabungan ialah corak temu duga yang hampir pasti ditanya. NOT EXISTS yang berkorelasi menjadi LEFT JOIN apabila bahagian kanan ialah NULL.

Baris luar tanpa padanan mendapat nilai NULL di sebelah kanan; penapisan untuk nilai NULL itu mengekalkan tepat baris yang tiada padanan.

-- Correlated NOT EXISTS
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id);

-- LEFT JOIN / IS NULL rewrite
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

Pilih Lajur yang Bukan NULL untuk Diuji

Dalam penulisan semula LEFT JOIN / IS NULL, uji lajur di sebelah kanan yang tidak pernah bernilai NULL apabila terdapat padanan sebenar, sebaik-baiknya kunci gabungan atau kunci utama.

Jika anda menguji lajur yang boleh bernilai NULL, anda tidak dapat membezakan tiada padanan sebenar (tiada baris) daripada baris sepadan yang kebetulan mempunyai NULL di situ. Kesilapan itu mengembalikan baris yang salah.

Menggunakan kunci gabungan (di sini o.customer_id) atau o.order_id menjamin bahawa NULL bermaksud "tiada baris sepadan."

Corak 3: Agregat Skalar kepada JOIN + GROUP BY

Agregat berkorelasi dalam SELECT boleh ditukar kepada gabungan dengan subkueri terkumpul (jadual terbitan).

Kira agregat bagi setiap kumpulan sekali, kemudian gabungkannya semula dengan baris terperinci. Kueri dalaman dijalankan sekali sahaja, bukannya bagi setiap baris.

-- Correlated scalar aggregate
SELECT e1.name,
       (SELECT MAX(e2.salary) FROM employees e2
        WHERE e2.dept_id = e1.dept_id) AS dept_max
FROM employees e1;

-- Join + GROUP BY rewrite
SELECT e.name, m.dept_max
FROM employees e
JOIN (SELECT dept_id, MAX(salary) AS dept_max
      FROM employees GROUP BY dept_id) m
  ON m.dept_id = e.dept_id;

Corak 4: Penulisan Semula dengan Fungsi Tetingkap

Selalunya penulisan semula yang paling kemas ialah menggunakan fungsi tetingkap. MAX(salary) OVER (PARTITION BY dept_id) menggantikan agregat berkorelasi sepenuhnya tanpa memerlukan gabungan.

Ia mengira nilai kumpulan dalam satu laluan dan mengekalkan setiap baris terperinci. Biasanya inilah jawapan yang paling ingin dilihat oleh penemu duga bagi kueri analitis.

SELECT name,
       dept_id,
       salary,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max
FROM employees;

Penulisan Semula Nilai Terbesar N bagi Setiap Kumpulan

Subkueri berkorelasi yang memilih baris teratas bagi setiap kumpulan (salary = MAX per dept) boleh ditulis semula dengan kemas menggunakan ROW_NUMBER.

Bahagikan mengikut kumpulan, susun mengikut metrik, dan kekalkan kedudukan 1. Gunakan RANK sebagai ganti jika anda mahu semua baris teratas yang seri.

SELECT name, dept_id, salary
FROM (
    SELECT name, dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id
                              ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn = 1;

Bila Tidak Perlu Menulis Semula

Penulisan semula tidak semestinya lebih baik. Kekalkan subkueri berkorelasi apabila:

Set luar sangat kecil, maka kos bagi setiap baris boleh diabaikan.
Lajur berkorelasi diindeks dengan baik dan pengoptimum sudah menukarkannya kepada semi-gabungan yang cekap.
Kebolehbacaan lebih penting daripada pengoptimuman kecil dalam kod yang diselenggara.

Pengoptimum moden kerap menukar EXISTS kepada semi-gabungan secara automatik. Nyatakan bahawa anda akan mengukur dengan EXPLAIN sebelum menganggap penulisan semula itu membantu.

Mengesahkan Kesetaraan

Selepas sebarang penulisan semula, sahkan bahawa ia mengembalikan baris yang sama dan kardinaliti yang sama seperti versi asal.

Pastikan kiraan baris sepadan.
Pastikan tiada pendua diperkenalkan oleh penggandaan gabungan.
Pastikan kes khas NULL dan kumpulan kosong masih berkelakuan dengan betul.

Cara pantas ialah menjalankan kedua-dua versi dan menggunakan EXCEPT pada kedua-dua arah; hasil kosong bermakna kedua-duanya sepadan. Penemu duga menghargai bahawa anda mengesahkan hasil, bukannya membuat andaian.

SELECT customer_id FROM query_a
EXCEPT
SELECT customer_id FROM query_b;
-- and the reverse; both empty => equivalent

Menulis Semula IN kepada JOIN

Subkueri IN yang tidak berkorelasi juga sering boleh ditulis semula sebagai gabungan, tetapi amaran tentang penggandaan yang sama tetap terpakai. IN menghapuskan pendua dalam keahlian; gabungan tidak berbuat demikian.

Jika senarai dalaman mempunyai kunci pendua, gabungan akan mengulangi baris luar. Gunakan DISTINCT pada bahagian dalaman atau pada hasil akhir untuk menyamai semantik IN.

-- IN subquery
SELECT c.name FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o);

-- Join rewrite, de-duplicated to match IN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

Semakan Pantas

Pilih penulisan semula gabungan yang betul untuk anti-gabungan NOT EXISTS yang berkorelasi.

Jawab dalam pelajaran interaktif

Kesimpulan: Menulis Semula Subkueri Berkorelasi sebagai Gabungan

Kesimpulan utama:

EXISTS → INNER JOIN (tambahkan DISTINCT untuk mengelakkan pendua akibat penggandaan).
NOT EXISTS → LEFT JOIN ... WHERE key IS NULL (uji lajur yang tidak boleh bernilai NULL).
Agregat skalar berkorelasi → JOIN jadual terbitan yang dikumpulkan, atau lebih baik lagi, fungsi tetingkap.
Baris teratas bagi setiap kumpulan → ROW_NUMBER (atau RANK untuk baris yang seri).
Sahkan kesetaraan dan semak dengan EXPLAIN sebelum menganggap penulisan semula itu lebih pantas.

Mengetahui kedua-dua bentuk serta perangkap penggandaan ialah perkara yang tepat diuji dalam temu duga peringkat pertengahan.

Percuma untuk bermula

Pelajari Persediaan Temu Duga Pengaturcaraan dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 90
Pelajaran: 360

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “Menulis Semula Subpertanyaan Berkorelasi sebagai Cantuman” percuma?

Ya — teks penuh “Menulis Semula Subpertanyaan Berkorelasi sebagai Cantuman” boleh dibaca secara percuma di web ini. Untuk berlatih secara interaktif menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7, serta membuka kunci baki kursus Persediaan Temu Duga Pengaturcaraan, tingkat taraf kepada CoddyKit PRO. Kursus Persediaan Temu Duga Pengaturcaraan merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Menulis Semula Subpertanyaan Berkorelasi sebagai Cantuman”?

Ratakan logik berkorelasi menjadi cantuman atau fungsi tetingkap demi prestasi Anda berlatih Persediaan Temu Duga Pengaturcaraan menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan Persediaan Temu Duga Pengaturcaraan?

Tiada pengalaman terdahulu diperlukan. Pembelajaran Persediaan Temu Duga Pengaturcaraan di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 4 daripada 4.

Berapa lamakah pelajaran “Menulis Semula Subpertanyaan Berkorelasi sebagai Cantuman” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran Persediaan Temu Duga Pengaturcaraan ini?

Ya. Setiap pelajaran Persediaan Temu Duga Pengaturcaraan menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

Anatomi Subpertanyaan Berkorelasi
Agregat Setiap Kumpulan Tanpa GROUP BY
EXISTS dan NOT EXISTS Berkorelasi
Menulis Semula Subpertanyaan Berkorelasi sebagai Cantuman

← Kembali ke Persediaan Temu Duga Pengaturcaraan