WITH menyediakan cara untuk menulis pernyataan tambahan untuk digunakan dalam permintaan yang besar. Pernyataan ini, juga dipanggil Ungkapan Jadual Biasa (CTE), boleh dianggap sebagai takrif jadual sementara yang wujud untuk hanya satu pertanyaan. Pernyataan tambahan dalam klausa WITH boleh menjadi SELECT, INSERT, UPDATE, atau DELETE, dan klausa WITH itu sendiri dilampirkan pada pernyataan utama, yang juga boleh menjadi SELECT, INSERT, UPDATE, atau DELETE.

7.8.1. PILIH dalam DENGAN

Tujuan utama SELECT dalam klausa WITH adalah untuk memecahkan pertanyaan kompleks kepada bahagian yang lebih mudah. Sebagai contoh, permintaan:

DENGAN regional_sales AS (SELECT region, SUM(amount) AS total_sales FROM order GROUP BY region), top_regions AS (SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)) PILIH wilayah, produk, SUM(kuantiti ) SEBAGAI unit_produk, JUMLAH(jumlah) AS produk_jualan DARI pesanan WHERE region IN (PILIH rantau DARI top_regions) KUMPULAN MENGIKUT rantau, produk;

memaparkan hasil jualan hanya untuk wilayah terkemuka. Klausa WITH mentakrifkan dua pengendali tambahan regional_sales dan top_regions supaya hasil regional_sales digunakan di top_regions dan hasil top_regions digunakan dalam utama PILIH pertanyaan. Contoh ini boleh ditulis semula tanpa WITH , tetapi kemudian kita memerlukan dua peringkat subqueries SELECT bersarang. Ini boleh dilakukan sedikit lebih mudah menggunakan kaedah yang ditunjukkan di atas.

Klausa RECURSIVE pilihan bertukar WITH daripada hanya binaan sintaksis yang mudah menjadi cara untuk melakukan sesuatu yang tidak mungkin dalam SQL standard. Dengan menggunakan RECURSIVE, pertanyaan WITH boleh mengakses keputusannya sendiri. Contoh yang sangat mudah menjumlahkan nombor dari 1 hingga 100:

DENGAN REKURSIF t(n) AS (NILAI (1) KESATUAN SEMUA PILIH n+1 DARI t MANA n< 100) SELECT sum(n) FROM t;

DALAM Pandangan umum Pertanyaan rekursif WITH sentiasa ditulis sebagai bukan bahagian rekursif, kemudian UNION (atau UNION ALL), dan kemudian bahagian rekursif, di mana hanya di bahagian rekursif anda boleh mengakses hasil pertanyaan. Permintaan sedemikian dilaksanakan seperti berikut:

Menilai Pertanyaan Rekursif

Bahagian bukan rekursif dikira. Untuk UNION (tetapi bukan UNION ALL), baris pendua dibuang. Semua baris yang tinggal dimasukkan dalam hasil pertanyaan rekursif dan juga diletakkan secara sementara lembaran kerja.

Selagi meja kerja tidak kosong, langkah berikut diulang:

1. Bahagian rekursif dinilai supaya rujukan rekursif kepada pertanyaan itu sendiri mengakses kandungan semasa jadual kerja. Untuk UNION (tetapi bukan UNION ALL), baris pendua dan baris yang menduplikasi baris yang diterima sebelum ini dibuang. Semua baris yang tinggal dimasukkan dalam hasil pertanyaan rekursif dan juga diletakkan secara sementara jadual perantaraan.

   Kandungan jadual kerja digantikan dengan kandungan jadual pementasan, dan kemudian jadual pementasan dibersihkan.

Dalam contoh yang ditunjukkan di atas, jadual kerja pada setiap peringkat mengandungi hanya satu baris dan nilai dari 1 hingga 100 terkumpul secara berurutan di dalamnya. Pada langkah keseratus, terima kasih kepada keadaan MANA, tiada apa yang dikembalikan, jadi penilaian pertanyaan tamat.

Pertanyaan rekursif biasanya digunakan untuk bekerja dengan struktur data hierarki atau pepohon. Sebagai contoh yang berguna anda boleh membuat pertanyaan yang mencari semua komponen langsung dan tidak langsung produk, menggunakan hanya jadual dengan sambungan langsung:

DENGAN RECURSIVE included_parts(sub_part, part, quantity) AS (SELECT sub_parts, part, quantity FROM parts WHERE part = "our_product" UNION SEMUA PILIH p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part) PILIH sub_bahagian, SUM(kuantiti) sebagai jumlah_kuantiti DARIPADA_bahagian_termasuk KUMPULAN OLEH sub_bahagian

Apabila bekerja dengan pertanyaan rekursif, adalah penting untuk memastikan bahawa bahagian rekursif pertanyaan tidak akan memulangkan sebarang tupel (baris), jika tidak gelung akan menjadi tidak terhingga. Kadangkala sudah cukup untuk menggunakan UNION dan bukannya UNION ALL untuk ini, kerana ini akan membuang baris yang sudah ada dalam hasilnya. Walau bagaimanapun, selalunya gelung menghasilkan baris yang tidak betul-betul sepadan dengan yang sebelumnya: dalam kes sedemikian, mungkin wajar untuk menyemak satu atau lebih medan untuk menentukan sama ada titik semasa telah dicapai lebih awal. Kaedah standard Penyelesaian kepada masalah tersebut adalah dengan mengira tatasusunan dengan nilai yang telah diproses. Sebagai contoh, pertimbangkan pertanyaan berikut, yang mencari jadual graf mengikut medan pautan:

DENGAN graf_carian REKURSIF(id, pautan, data, kedalaman) AS (PILIH g.id, g.link, g.data, 1 DARI graf g UNION SEMUA PILIH g.id, g.link, g.data, sg.depth + 1 DARI graf g, carian_graf sg DI MANA g.id = sg.link) PILIH * DARI carian_graf;

Permintaan ini akan gelung jika pautan pautan mengandungi gelung. Oleh kerana kita perlu mendapatkan hasilnya " kedalaman", hanya menukar UNION ALL kepada UNION tidak akan mengelakkan gelung. Sebaliknya, kita mesti menentukan apa yang telah kita capai talian semasa, setelah berjalan beberapa arah. Untuk melakukan ini, kami menambah dua laluan lajur dan kitaran dan mendapatkan pertanyaan yang dilindungi kitaran:

DENGAN graf_carian REKURSIF(id, pautan, data, kedalaman, laluan, kitaran) AS (PILIH g.id, g.link, g.data, 1, ARRAY, palsu DARI graf g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, laluan || g.id, g.id = MANA-MANA(laluan) DARI graf g, carian_graf sg DI MANA g.id = sg.pautan DAN BUKAN kitaran) PILIH * DARI graf carian;

Selain menghalang gelung, nilai tatasusunan selalunya berguna untuk mewakili " cara » membawa kepada garis tertentu.

Secara umum, apabila anda perlu menyemak berbilang medan untuk mengesan kitaran, anda harus menggunakan tatasusunan rentetan. Sebagai contoh, jika anda perlu membandingkan medan f1 dan f2:

DENGAN graf_carian REKURSIF(id, pautan, data, kedalaman, laluan, kitaran) AS (PILIH g.id, g.link, g.data, 1, ARRAY, palsu DARI graf g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, laluan || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = MANA-MANA(laluan) DARI graf g, carian_graf sg DI MANA g.id = sg.pautan DAN BUKAN kitaran) PILIH * DARI search_graph;

Petunjuk

Selalunya satu medan sudah cukup untuk mengenali kitaran, dan kemudian ROW() boleh ditinggalkan. Ini tidak akan menggunakan tatasusunan data jenis komposit, tetapi tatasusunan mudah, yang lebih cekap.

Petunjuk

Algoritma penilaian pertanyaan rekursif ini menghasilkan nod yang disusun sepanjang laluan rendaman. Untuk mendapatkan hasil yang diisih mengikut kedalaman, anda boleh menambah lajur ORDER BY pada pertanyaan luar. jalan", diperolehi seperti yang ditunjukkan di atas.

Helah yang baik untuk menguji pertanyaan yang mungkin gelung ialah menambahkan LIMIT pada pertanyaan induk. Sebagai contoh, pertanyaan berikut akan gelung jika anda tidak menambah klausa LIMIT:

DENGAN REKURSIF t(n) AS (PILIH 1 KESATUAN SEMUA PILIH n+1 DARI t) PILIH n DARI t HAD 100;

Tetapi dalam dalam kes ini ini tidak berlaku, kerana dalam PostgreSQL A WITH pertanyaan menghasilkan seberapa banyak baris yang sebenarnya diterima oleh pertanyaan induk. Teknik ini tidak disyorkan dalam persekitaran pengeluaran, kerana sistem lain mungkin berkelakuan berbeza. Selain itu, ini tidak akan berfungsi jika pertanyaan luar mengisih hasil pertanyaan rekursif atau menggabungkannya dengan jadual lain, kerana dalam kes sedemikian, pertanyaan luar biasanya akan memilih keseluruhan hasil pertanyaan WITH.

DENGAN pertanyaan telah harta yang berguna- mereka dinilai sekali sahaja untuk keseluruhan pertanyaan induk, walaupun pertanyaan itu atau pertanyaan WITH yang berdekatan mengaksesnya beberapa kali. Dengan cara ini, pengiraan kompleks yang keputusannya diperlukan di berbilang tempat boleh dimasukkan ke dalam WITH pertanyaan untuk tujuan pengoptimuman. Di samping itu, pertanyaan sedemikian mengelakkan penilaian fungsi yang tidak diingini dengan kesan sampingan. Namun begitu, ada juga bahagian belakang- Pengoptimum tidak boleh menyebarkan kekangan pertanyaan induk ke dalam pertanyaan WITH seperti yang boleh dilakukan untuk subquery biasa. Pertanyaan WITH biasanya dilaksanakan secara literal dan mengembalikan semua baris, termasuk baris yang kemudiannya boleh dibuang oleh pertanyaan induk. (Tetapi seperti yang dinyatakan di atas, pengiraan mungkin berhenti lebih awal jika rujukan kepada pertanyaan ini hanya memerlukan bilangan terhad baris.)

Contoh di atas hanya menunjukkan klausa WITH dengan SELECT , tetapi ia boleh digunakan dengan arahan INSERT , UPDATE dan DELETE dengan cara yang sama. Dalam setiap kes, ia pada asasnya mencipta jadual sementara yang boleh diakses dalam arahan utama.

7.8.2. Menukar Data dalam WITH

Anda juga boleh menggunakan pernyataan pengubahsuaian data (INSERT, UPDATE, atau DELETE) dalam klausa WITH. Ini membolehkan anda melakukan beberapa operasi berbeza sekaligus dalam satu permintaan. Sebagai contoh:

DENGAN moved_rows AS (PADAM DARI produk DI MANA "tarikh" >= "2010-10-01" DAN "tarikh"< "2010-11-01" RETURNING *) INSERT INTO products_log SELECT * FROM moved_rows;

Pertanyaan ini sebenarnya mengalihkan baris daripada produk ke products_log . PADAM kenyataan dalam WITH, mengalih keluar baris yang ditentukan daripada produk dan mengembalikan kandungannya dalam klausa RETURNING; dan kemudian pertanyaan utama membaca kandungan ini dan memasukkannya ke dalam jadual products_log.

Ambil perhatian bahawa klausa WITH dalam kes ini dilampirkan pada pernyataan INSERT, bukan pada SELECT yang bersarang dalam INSERT. Ini perlu kerana WITH hanya boleh mengandungi pernyataan yang menukar data dengan tingkat atas permintaan. Walau bagaimanapun, mereka memohon peraturan biasa DENGAN keterlihatan, supaya hasil WITH juga boleh diakses daripada pernyataan SELECT bersarang.

Pernyataan pengubahsuaian data dalam WITH biasanya disertakan dengan klausa RETURNING (lihat Bahagian 6.4), seperti yang ditunjukkan dalam contoh ini. Adalah penting untuk memahami bahawa jadual sementara yang boleh digunakan dalam pertanyaan selebihnya dicipta daripada hasil RETURNING , dan Tidak jadual sasaran pengendali. Jika pernyataan pengubahsuaian data dalam WITH tidak disertakan dengan klausa RETURNING, jadual sementara tidak dibuat dan tidak boleh diakses dalam pertanyaan yang lain. Walau bagaimanapun, permintaan sedemikian masih akan dilaksanakan. Sebagai contoh, katakan pertanyaan yang tidak begitu praktikal berikut:

DENGAN t AS (PADAM DARI foo) PADAM DARI bar;

Ia akan memadam semua baris daripada jadual foo dan bar . Dalam kes ini, bilangan baris yang terlibat yang akan diterima oleh pelanggan akan dikira hanya berdasarkan baris yang dialih keluar daripada bar .

DENGAN RECURSIVE included_parts(sub_part, part) AS (SELECT sub_part, part FROM parts WHERE part = "our_product" UNION ALL SELECT p.sub_part, p.part FROM included_parts pr, parts p WHERE p.part = pr.sub_part) PADAM DARI bahagian WHERE part IN (PILIH bahagian FROM included_parts);

Pertanyaan ini mengalih keluar semua komponen langsung dan tidak langsung produk.

Operator yang mengubah suai data dalam WITH dilaksanakan sekali sahaja dan sentiasa dalam keseluruhannya, tidak kira sama ada pertanyaan utama menerima keputusannya. Ambil perhatian bahawa ini berbeza daripada tingkah laku SELECT dalam WITH: seperti yang dibincangkan dalam bahagian sebelumnya, SELECT dilaksanakan hanya selagi keputusannya dituntut oleh pertanyaan utama.

Penyataan bersarang dalam WITH dilaksanakan secara serentak antara satu sama lain dan dengan pertanyaan utama. Oleh itu, susunan pernyataan dalam WITH sebenarnya akan mengubah data tidak dapat diramalkan. Semua pernyataan ini dilaksanakan dengan satu petikan data(lihat Bab 13) jadi mereka tidak boleh " lihat" bagaimana setiap daripada mereka menukar jadual sasaran. Ini mengurangkan ketidakpastian susunan perubahan baris sebenar dan bermakna RETURNING ialah satu-satunya pilihan untuk menghantar perubahan daripada pernyataan bersarang WITH kepada pertanyaan utama. Sebagai contoh, dalam kes ini:

DENGAN t AS (KEMASKINI produk SET harga = harga * 1.05 KEMBALI *) PILIH * DARI produk;

penyataan SELECT luar akan mengembalikan harga yang sebelum tindakan KEMASKINI, sedangkan dalam pertanyaan

DENGAN t AS (KEMASKINI produk SET harga = harga * 1.05 KEMBALI *) PILIH * DARI t;

SELECT luaran akan mengembalikan data yang diubah.

Menukar rentetan yang sama beberapa kali dalam pernyataan yang sama tidak disokong. Hanya satu daripada beberapa perubahan yang akan berlaku, dan dengan pasti menentukan yang mana satu selalunya agak sukar (dan kadangkala mustahil). Ini juga terpakai apabila baris dipadamkan dan diubah suai dalam pernyataan yang sama: hasilnya hanya boleh menjadi kemas kini. Oleh itu, secara amnya, operasi bertindih sedemikian harus dielakkan. Khususnya, elakkan WITH subqueries, yang boleh menjejaskan baris yang diubah suai oleh pernyataan utama atau pernyataan yang bersarang di dalamnya. Hasil daripada permintaan sedemikian tidak dapat diramalkan.

Pada masa ini, pernyataan yang mengubah suai data dalam WITH tidak boleh menyasarkan jadual yang mana peraturan bersyarat atau peraturan JUGA atau SEBALIKNYA jika ia terdiri daripada berbilang pernyataan.

standard SQL:1999 dan membuka kemungkinan menggunakan bahasa dalam aplikasi yang tidak sesuai sebelum ini. Ia mengenai tentang kemungkinan analisis dan pertanyaan rekursif . Topik-topik ini tidak berkaitan secara logik; mereka bersatu hanya oleh fakta bahawa alat yang sepadan sangat rumit dan tidak selalu mudah difahami. Dalam kuliah ringkas ini kami tidak berusaha untuk menyediakan Penerangan penuh keupayaan yang dinyatakan dalam standard SQL. Matlamat kami hanya untuk garis besar umum huraikan pendekatan SQL dalam arah ini.

Aplikasi analitik biasanya tidak memerlukan data terperinci yang disimpan secara langsung dalam pangkalan data, tetapi beberapa generalisasi dan agregatnya. Sebagai contoh, analitik tidak berminat upah orang tertentu pada masa tertentu, dan berubah upah kategori orang tertentu untuk tempoh masa tertentu. Menggunakan terminologi SQL, pertanyaan pangkalan data biasa daripada aplikasi analitik mengandungi klausa KUMPULAN OLEH dan panggilan fungsi agregat. Walaupun dalam kursus ini kami hampir tidak menyentuh isu pelaksanaan DBMS berorientasikan SQL, daripada pertimbangan umum Perlu jelas bahawa pertanyaan dengan klausa GROUP BY secara amnya "sukar" untuk DBMS, kerana pengumpulan jadual biasanya memerlukan jenis luaran.

Dalam sistem pangkalan data yang direka khusus untuk aplikasi analisis, masalah biasanya diselesaikan dengan penyimpanan data agregat yang berlebihan secara eksplisit (iaitu, hasil panggilan fungsi agregat). Sudah tentu, ini memerlukan pelarasan dinamik nilai agregat tersimpan apabila data terperinci berubah, tetapi untuk pangkalan data khusus sedemikian ini tidak terlalu membebankan, kerana pangkalan data analitik dikemas kini agak jarang.

Walau bagaimanapun, tidak setiap perusahaan mampu untuk mengekalkan pangkalan data operasi untuk kerja secara serentak aplikasi biasa pemprosesan operasi transaksi (OLTP), seperti perakaunan, HR dan aplikasi lain, dan pangkalan data analisis untuk aplikasi operasi pemprosesan analitikal (OLAP). Anda perlu menjalankan aplikasi analisis pada pangkalan data operasi terperinci, dan aplikasi ini mengakses DBMS dengan banyak pertanyaan yang memakan masa dengan klausa dan panggilan GROUP BY fungsi agregat.

Pembangun standard bahasa SQL cuba menyelesaikan dua masalah secara serentak: mengurangkan bilangan pertanyaan yang diperlukan dalam aplikasi analisis dan mengurangkan kos pertanyaan GROUP BY yang menyediakan data ringkasan yang diperlukan. Dalam kuliah ini kita akan membincangkan yang paling penting, dari sudut pandangan kita, pembinaan bahasa SQL, memudahkan penggubalan, pelaksanaan dan penggunaan keputusan pertanyaan analitikal: bahagian KUMPULAN MENGIKUT GILIRAN Dan KUMPULAN MENGIKUT KIUB dan baru fungsi agregat MENGUMPULKAN, membolehkan anda mentafsir keputusan dengan betul pertanyaan analitikal di hadapan nilai yang tidak ditentukan.

Secara tradisinya, SQL tidak pernah mempunyai keupayaan untuk merumus pertanyaan rekursif, di mana di bawah pertanyaan rekursif(Ringkasnya) kami memahami pertanyaan kepada jadual yang sendiri berubah dalam beberapa cara apabila pertanyaan ini dilaksanakan. Biar saya ingatkan anda bahawa ini tertanam dalam semantik asas pernyataan SQL: Sebelum klausa WHERE dilaksanakan, hasil klausa FROM mesti dinilai sepenuhnya.

Walau bagaimanapun, pembangun aplikasi selalunya perlu menyelesaikan masalah yang cara tradisional merumuskan pertanyaan dalam bahasa SQL tidak mencukupi: contohnya, mencari laluan antara dua titik geografi tertentu, menentukan set komponen biasa untuk memasang unit tertentu, dsb. Syarikat yang menghasilkan DBMS berorientasikan SQL cuba memenuhi keperluan tersebut melalui penyelesaian proprietari dengan sifat rekursif terhad, tetapi sehingga kemunculan standard SQL:1999, tiada alat piawai umum.

Perlu diingatkan bahawa terdapat beberapa tekanan antara jadual yang dijana sedang dimasukkan. Namun, hanya untuk rekursi linear disediakan ciri-ciri tambahan kawalan pesanan pengiraan ditakrifkan secara rekursif jadual yang dijana dan kawalan ketiadaan kitaran. Perlu diingatkan bahawa apabila membaca standard, kadang-kadang seseorang mendapat tanggapan bahawa pengarangnya sendiri belum menyedari sepenuhnya semua kemungkinan akibat, yang boleh terhasil daripada penggunaan struktur yang diperkenalkan. Saya berpendapat bahawa dalam versi piawaian akan datang kita harus mengharapkan penjelasan dan/atau sekatan ke atas penggunaan binaan ini. Sehubungan dengan itu, dalam kuliah ini kami hadkan definisi umum binaan rekursif bahasa SQL dan perbincangan kes mudah pertanyaan rekursif.

Menggabungkan pertanyaan daripada jenis yang sama

Paparkan bilangan penjual yang berlabel "penjual kuat" atau "lemah". Seorang penjual dianggap kuat jika dia kos purata dagangan lebih daripada 500, lemah – kurang daripada 500(permintaan ditunjukkan pada Helaian 39 , hasil - Jadual 51 ):

KUMPULAN OLEH N_Seller

MEMPUNYAI purata(Kos)<500

KUMPULAN OLEH N_Seller

MEMPUNYAI purata(Kos)>500;

Jadual 51. Keputusan pertanyaan dengan pengendali Kesatuan

Paparkan bilangan penjual yang berlabel "penjual kuat" atau "lemah". Penjual dengan purata nilai transaksi lebih daripada 500 dianggap kuat, penjual lemah kurang daripada 500, diisih mengikut aktiviti(permintaan ditunjukkan pada Helaian 40 , hasil - Jadual 52 ):

PILIH N_Seller, "Penjual Lemah" sebagai Aktiviti DARIPADA Transaksi

KUMPULAN OLEH N_Seller

MEMPUNYAI purata(Kos)<500

UNION SELECT N_Seller, "Strong Seller" sebagai Aktiviti DARIPADA Transaksi

KUMPULAN OLEH N_Seller

MEMPUNYAI purata(Kos)>500

PESANAN MENGIKUT Aktiviti;

Jadual 52. Keputusan permintaan dengan Kesatuan dan Perintah oleh

Pengendali persilangan dan kecuali berfungsi sama, sepadan dengan operasi persilangan dan perbezaan dalam algebra hubungan. Untuk bidang subjek Senarai "jualan" bandar pembeli dan penjual mungkin tidak sama, jadi kami boleh menyelesaikan pelbagai masalah dengan senarai ini, seperti yang ditunjukkan dalam Jadual. 53.

Jadual 53. Menggunakan operator gabungan untuk pertanyaan daripada jenis yang sama

Kepada Daun. 25 memberikan contoh permintaan di mana orang bawahan Ivanov telah dicari. Timbul persoalan bagaimana membina keseluruhan hierarki orang bawahan, dan bukan hanya orang bawahan langsungnya. Sebenarnya, kita perlu menggunakan pertanyaan Helaian yang sama ini. 25 kepada hasil melaksanakannya sendiri, kemudian lagi, dan lain-lain, sehingga orang bawahan semakin ramai tahap rendah hierarki.

Bina hierarki semua orang bawahan Ivanov dalam "panjang"(permintaan ditunjukkan pada Helaian 41 , hasil - Jadual 54 ).

Pertanyaan rekursif boleh dikenal pasti dengan kata kunci DENGAN REKURSIF. Untuk dipanggil secara rekursif, pertanyaan mesti mempunyai nama (dalam kes ini - Prod). Permintaan terdiri daripada dua bahagian, digabungkan menggunakan UNION. Di bahagian pertama - bos, di bahagian kedua - orang bawahan mereka. Pertanyaan mencari hamba pertama, kemudian hamba pertama hamba pertama, dan seterusnya. Pertanyaan ini dipanggil pertanyaan carian panjang.

Lembaran. 41. Pertanyaan rekursif "panjang"

(PILIH N, Nama

DARIPADA Penjual

WHERE Nama = 'Ivanov'

PILIH Penjual. N, Penjual.Nama

DARIPADA Penjual, Jualan

PILIH * DARI Samb;

Jadual 54. Hasil pertanyaan rekursif "panjang"

Jika kita perlu menyenaraikan semua orang bawahan Ivanov dahulu, kemudian orang bawahan Ivanov, dsb., maka kita perlu menggunakan pertanyaan rekursif pertama-luas menggunakan pengendali SEARCH BREADTH FIRST. Pertanyaan menetapkan nombor lelaran menggunakan operator SET.

Bina hierarki semua bawahan Ivanov "secara luas"(permintaan ditunjukkan pada Helaian 42 , hasil - Jadual 55 ).

Lembaran. 42. Pertanyaan luas-pertama rekursif

DENGAN RECURSIVE Cont (N, Nama, N_Head) sebagai

(PILIH N, Nama

DARIPADA Penjual

WHERE Nama = 'Ivanov'

DARIPADA Penjual, Jualan

WHERE Cont.N = Penjual.N_Ketua);

CARI KELUASAN DULU OLEH N_Chief, N

SET pesanan_lajur

PILIH * DARI Samb

PESANAN MENGIKUT order_column;

Jadual 55. Hasil pertanyaan "luas dahulu".

Dengan pertanyaan rekursif, situasi gelung adalah mungkin. Walaupun ini bukan dalam contoh kita, kita boleh mengandaikan bahawa terdapat situasi apabila satu pasukan bekerja pada dua projek, dan satu orang dalam projek pertama adalah bos, dan dalam yang kedua adalah bawahan, dan sebaliknya, orang kedua. dalam projek pertama adalah orang bawahan. Dan dalam kedua - bos. Dalam kes ini, pertanyaan rekursif sebelumnya akan bergelung.

Bina hierarki semua orang bawahan Ivanov, dengan mengambil kira kemungkinan gelung(Lembaran 43) .

Lembaran. 43. Pertanyaan rekursif dengan mengambil kira gelung

DENGAN RECURSIVE Cont (N, Nama) sebagai

(PILIH N, Nama

DARIPADA Penjual

WHERE Nama = 'Ivanov'

PILIH Penjual. N, Penjual. Nama

DARIPADA Penjual, Jualan

WHERE Cont.N = Penjual.N_Ketua);

TETAPkan tanda kitaran kepada "Y" lalai "N"

MENGGUNAKAN laluan berbasikal

PILIH * DARI Samb

D/Z 6. Untuk contoh daripada D/Z 4, buat pertanyaan berikut:

1. Pertanyaan jadual tunggal yang menilai fungsi agregat menggunakan di mana, mempunyai, susunan mengikut pernyataan.
2. Pertanyaan gabungan berbilang jadual menggunakan klausa where, satu jadual mesti digunakan berbilang kali dalam pertanyaan di bawah alias.
3. Pertanyaan mengikut kanan, kiri atau gabungan penuh.
4. Pertanyaan dengan dua subkueri di mana dan mempunyai klausa
5. Pertanyaan dengan subkueri menggunakan semua, mana-mana, atau wujud.
6. Pertanyaan bersarang dengan gabungan dua jadual (salah satu jenis cantuman), secara tidak langsung disambungkan oleh satu pertiga.
7. Menggabungkan pertanyaan daripada jenis yang sama.
8. Pertanyaan rekursif.

Soalan ujian kendiri:

1. Apakah perbezaan di antara menggunakan kumpulan operator mengikut, kumpulan dengan rollup, kumpulan dengan kiub rollup, kumpulan, penganjur mengikut, partition?

2. Apakah perbezaan antara menggunakan pernyataan di mana dan mempunyai?

3. Apakah pertanyaan yang disertai...pada perlawanan? di mana pengendali?

4. Apakah jenis operasi algebra hubungan sepadan dengan pernyataan di mana?

5. Apakah operasi algebra hubungan yang sepadan dengan operator cantum?

Keputusan ini kelihatan lebih berguna. Kini dalam jadual ReachableFrom, medan Destinasi mengandungi semua bandar yang boleh dicapai dari mana-mana bandar yang terletak dalam medan Sumber jadual yang sama, membuat tidak lebih daripada satu persinggahan. Kemudian, pada pas seterusnya, rekursi akan memproses laluan dengan dua perhentian perantaraan dan akan terus berbuat demikian sehingga semua bandar yang boleh dikunjungi telah ditemui.

Selepas rekursi selesai, pernyataan SELECT ketiga dan terakhir (yang tidak terlibat dalam rekursi) memilih daripada ReachableFrom hanya bandar-bandar yang boleh dicapai dari Portland. Dalam contoh ini, anda boleh pergi ke semua enam bandar lain, dan dengan bilangan perhentian perantaraan yang agak kecil. Jadi anda tidak perlu tergesa-gesa seperti anda menunggang tiang dengan spring.

Jika anda melihat dengan teliti kod pertanyaan rekursif, anda akan melihat bahawa ia tidak kelihatan lebih mudah daripada tujuh pertanyaan berasingan. Walau bagaimanapun, permintaan ini mempunyai dua kelebihan:

• selepas pelancarannya, campur tangan luar tidak lagi diperlukan;
• ia berfungsi dengan pantas.

Jika anda boleh, bayangkan syarikat penerbangan sebenar yang mempunyai lebih banyak bandar pada peta laluannya. Dan lebih banyak lagi tempat yang mungkin pelantikan, mereka lebih banyak faedah daripada kaedah rekursif.

Apakah yang menjadikan pertanyaan rekursif? Bahawa kita mentakrifkan jadual ReachableFrom berdasarkan dirinya sendiri. Bahagian rekursif definisi ialah pernyataan SELECT kedua, yang terletak sejurus selepas UNION. ReachableFrom ialah jadual sementara yang diisi dengan data semasa rekursi berlaku. Dan pengisian ini berterusan sehingga semua destinasi yang mungkin berada dalam ReachableFrom. Tiada baris pendua dalam jadual ini, kerana pengendali UNION tidak akan membenarkannya melalui. Apabila rekursi selesai, jadual ReachableFrom akan mengandungi semua bandar yang boleh dicapai dari mana-mana bandar permulaan. Pernyataan SELECT ketiga dan terakhir hanya mengembalikan bandar-bandar yang anda boleh pergi dari Portland. Jadi kami ucapkan selamat bersiar-siar.

Di mana lagi anda boleh menggunakan pertanyaan rekursif?

Sebarang masalah yang boleh diwakili sebagai struktur pokok boleh diselesaikan menggunakan pertanyaan rekursif. Contoh klasik tentang cara pertanyaan sedemikian digunakan dalam industri ialah pemprosesan bahan (proses menukar bahan mentah menjadi produk akhir). Katakan syarikat anda mengeluarkan kereta hibrid petrol-elektrik baharu. Mesin sedemikian dipasang dari komponen (enjin, bateri, dll.), Yang, pada gilirannya, terdiri daripada subassemblies yang lebih kecil (crankshaft, elektrod, dll.), Dan mereka - dari komponen yang lebih kecil.

Data tentang semua komponen komponen ini disimpan dalam pangkalan data hubungan sangat sukar - melainkan, sudah tentu, ia menggunakan rekursi. Rekursi membolehkan, bermula dengan keseluruhan mesin, untuk sampai ke bahagian terkecil dalam apa jua cara. Ingin mencari data tentang skru pelekap yang memegang terminal elektrod negatif bateri tambahan? Ini mungkin - dan tanpa banyak pelaburan masa. Berurusan dengan ini tugasan SQL boleh menggunakan struktur WITH RECURSIVE (pengendali rekursif).

Di samping itu, rekursi agak semula jadi dalam analisis "bagaimana jika". Sebagai contoh, apa yang berlaku jika Vannevar Airlines memutuskan untuk berhenti terbang dari Portland ke Charlotte? Bagaimanakah ini akan menjejaskan penerbangan ke bandar yang boleh diakses pada masa ini dari Portland? Pertanyaan rekursif akan segera menjawab soalan ini.