SQL Deep Dive: Menguasai Fungsi Tetingkap, Ekspresi Jadual Biasa (CTE), dan prosedur yang disimpan

SQL menyediakan tiga fungsi yang kuat: Fungsi Tetingkap, Ekspresi Jadual Biasa (CTE), dan prosedur yang disimpan. 1. Fungsi tetingkap membenarkan pengumpulan dan pengendalian operasi tanpa mengubah set data. 2.Ctes menyediakan set hasil sementara untuk memudahkan pertanyaan kompleks. 3. Prosedur yang disimpan adalah blok kod SQL yang boleh dikompilasi yang boleh dilaksanakan berulang kali untuk meningkatkan kecekapan dan konsistensi.

Pengenalan

Dalam dunia yang didorong data, SQL bukan sekadar bahasa pertanyaan, tetapi juga seni. Hari ini, kami akan menyelam ke dalam tiga ciri yang berkuasa dalam SQL: Fungsi Tetingkap, Ekspresi Jadual Biasa (CTE), dan prosedur yang disimpan. Melalui artikel ini, anda akan belajar cara menggunakan alat ini untuk menangani masalah data yang kompleks, meningkatkan kemahiran SQL anda, dan selesa dalam analisis data dan pengurusan.

Semak pengetahuan asas

Pesona SQL terletak pada kesederhanaan dan fungsi yang kuat. Fungsi tetingkap membolehkan anda mengumpulkan dan menyusun data tanpa menukar set data. CTE menyediakan cara yang ditetapkan sementara untuk membuat pertanyaan kompleks lebih mudah dibaca dan diuruskan. Prosedur yang disimpan adalah blok kod SQL yang dikompilasi yang boleh dilaksanakan berulang kali untuk meningkatkan kecekapan dan konsistensi.

Konsep teras atau analisis fungsi

Fungsi tingkap

Fungsi tetingkap adalah alat ajaib dalam SQL yang membolehkan anda mengumpulkan dan menyusun data tanpa mengubah set data. Mereka sangat berguna dalam analisis data kerana mereka dapat membantu anda mengira purata bergerak, kedudukan, jumlah kumulatif, dan banyak lagi.

 Pilih 
    Pekerja_id,
    Gaji,
    Avg (gaji) over (partition by jabatan) sebagai avg_department_salary,
    Pangkat () lebih (pesanan mengikut gaji desc) sebagai gaji_rank
Dari 
    pekerja;

Dalam contoh ini, kami mengira gaji purata bagi setiap jabatan pekerja dan pangkat pekerja berdasarkan gaji mereka. Kuasa fungsi tetingkap adalah bahawa mereka membolehkan anda melakukan pelbagai pengiraan dalam pertanyaan yang sama tanpa menggunakan subqueries atau join diri.

Ekspresi Jadual Biasa (CTE)

CTE adalah set hasil sementara dalam SQL yang memudahkan struktur pertanyaan kompleks dan menjadikan kod lebih mudah dibaca dan dikekalkan. CTEs amat berguna dalam pertanyaan rekursif kerana mereka boleh merujuk kepada diri mereka sendiri.

 Dengan rekursif pekerja_hierarki sebagai (
    Pilih Pekerja_ID, Manager_ID, 0 sebagai tahap
    Dari pekerja
    Di mana pengurus_id adalah batal
    Kesatuan Semua
    Pilih E.Eployee_ID, E.Manager_ID, EH.LEVEL 1
    Dari pekerja e
    Sertailah Pekerja_Hierarchy eh di e.manager_id = eh.employee_id
)
Pilih * dari Pekerja_Hierarchy;

Dalam contoh ini, kami menggunakan CTE untuk membina hierarki pekerja. Sifat rekursif CTE membolehkan kita dengan mudah melintasi seluruh pokok pekerja tanpa menulis pertanyaan sambungan diri yang kompleks.

Prosedur tersimpan

Prosedur yang disimpan adalah blok kod SQL yang boleh dikompilasi yang boleh dilaksanakan berulang kali. Mereka berguna apabila anda perlu melakukan logik yang kompleks atau meningkatkan prestasi kerana mereka mengurangkan trafik rangkaian dan masa penyusunan.

 Buat prosedur get_employee_details (dalam emp_id int)
Mulakan
    Pilih 
        e.employee_id,
        e.first_name,
        e.last_name,
        D.Department_name
    Dari 
        Pekerja e
    Menyertai 
        jabatan d di e.department_id = d.department_id
    Di mana 
        e.employee_id = emp_id;
Akhir;

Dalam contoh ini, kami membuat prosedur tersimpan untuk mendapatkan butiran pekerja. Kelebihan prosedur yang disimpan ialah mereka dapat merangkum logik kompleks dan boleh dipanggil beberapa kali, meningkatkan kebolehgunaan semula kod dan konsistensi.

Contoh penggunaan

Penggunaan asas

Penggunaan asas fungsi tingkap sangat mudah. Anda boleh menggunakan klausa OVER untuk menentukan tetingkap dan menggunakan pelbagai fungsi agregat untuk mengira hasilnya.

 Pilih 
    produk_id,
    Sale_date,
    Sale_amount,
    SUM (SALE_AMOUNT) OVER (partition by product_id order by Sale_date) sebagai running_total
Dari 
    jualan;

Dalam contoh ini, kami mengira jualan terkumpul untuk setiap produk. PARTITION BY data kumpulan klausa, dan ORDER BY klausa mentakrifkan urutan tingkap.

Penggunaan asas CTES juga sangat mudah. Anda boleh menggunakan WITH kunci untuk menentukan CTE dan kemudian merujuknya dalam pertanyaan berikutnya.

 Dengan top_sellers sebagai (
    Pilih Product_ID, SUM (SALE_AMOUNT) sebagai total_sales
    Dari jualan
    Kumpulan dengan produk_id
    Pesanan oleh total_sales desc
    Had 10
)
Pilih * dari top_sellers;

Dalam contoh ini, kami menggunakan CTE untuk mencari 10 produk dengan jualan tertinggi. CTE menjadikan struktur pertanyaan lebih jelas dan lebih mudah untuk dikendalikan.

Penggunaan asas prosedur yang disimpan juga sangat mudah. Anda boleh menggunakan pernyataan CREATE PROCEDURE untuk menentukan prosedur yang disimpan, dan kemudian gunakan pernyataan CALL untuk memanggilnya.

 Hubungi get_employee_details (1);

Dalam contoh ini, kami memanggil prosedur yang disimpan sebelum ini untuk mendapatkan butiran pekerja dengan ID pekerja 1.

Penggunaan lanjutan

Penggunaan fungsi tetingkap lanjutan termasuk menggunakan ROWS atau klausa RANGE untuk menentukan skop tetingkap, dan menggunakan fungsi LAG dan LEAD untuk mengakses data baris depan dan belakang.

 Pilih 
    produk_id,
    Sale_date,
    Sale_amount,
    Lag (sale_amount) over (partition by product_id order by Sale_date) sebagai sebelumnya_sale,
    Lead (Sale_amount) over (partition by product_id order by Sale_date) sebagai next_sale
Dari 
    jualan;

Dalam contoh ini, kami menggunakan fungsi LAG dan LEAD untuk mendapatkan jualan sebelumnya dan seterusnya setiap produk. Penggunaan lanjutan sedemikian dapat membantu anda melakukan analisis data yang lebih kompleks.

Penggunaan CTE lanjutan termasuk menggunakan CTE rekursif untuk memproses data hierarki, dan menggunakan pelbagai CTE untuk memudahkan pertanyaan kompleks.

 Dengan kategori rekursif_hierarki sebagai (
    Pilih kategori_id, parent_category_id, 0 sebagai tahap
    Dari kategori
    Di mana parent_category_id adalah batal
    Kesatuan Semua
    Pilih c.category_id, c.parent_category_id, ch.level 1
    Dari kategori c
    Sertailah kategori_hierarchy ch pada c.parent_category_id = ch.category_id
))
Product_categories sebagai (
    Pilih p.product_id, ch.category_id, ch.level
    Dari produk ms
    Sertailah kategori_hierarchy ch di p.category_id = ch.category_id
)
Pilih * dari Product_categories;

Dalam contoh ini, kami menggunakan CTE rekursif untuk membina hierarki kategori produk, dan kemudian menggunakan CTE lain untuk mengaitkan produk dengan kategorinya. Penggunaan lanjutan sedemikian dapat membantu anda menangani data hierarki yang kompleks.

Penggunaan prosedur yang tersimpan termasuk penggunaan kursor, pengendalian pengecualian, dan pengurusan transaksi untuk melaksanakan logik perniagaan yang kompleks.

 Buat Prosedur Update_Employee_Salary (dalam emp_id int, dalam perpuluhan baru (10, 2))
Mulakan
    Mengisytiharkan keluar pengendali untuk sqlexception
    Mulakan
        Rollback;
        Meletakkan jawatan;
    Akhir;

    Memulakan transaksi;
    Mengemas kini pekerja
    Tetapkan gaji = new_salary
    Di mana pekerja_id = emp_id;
    Komit;
Akhir;

Dalam contoh ini, kami membuat prosedur tersimpan untuk mengemas kini gaji pekerja. Prosedur yang disimpan menggunakan pengurusan transaksi dan pengendalian pengecualian untuk memastikan konsistensi dan integriti data.

Kesilapan biasa dan tip debugging

Kesilapan biasa apabila menggunakan fungsi tetingkap adalah lupa untuk menggunakan klausa OVER . Ini menyebabkan enjin SQL gagal menghuraikan fungsi tetingkap dengan betul.

 - Contoh Ralat Pilih 
    Pekerja_id,
    Gaji,
    AVG (Gaji) - hilang atas klausa dari 
    pekerja;

Untuk mengelakkan kesilapan ini, pastikan bahawa klausa OVER selalu dimasukkan apabila menggunakan fungsi tetingkap.

Apabila menggunakan CTE, kesilapan biasa adalah lupa untuk menentukan semua lajur yang diperlukan dalam CTE. Ini akan menyebabkan pertanyaan berikutnya gagal untuk merujuk data dengan betul dalam CTE.

 - Contoh ralat dengan top_sellers sebagai (
    Pilih Product_ID - Total_Sales Column Hilang dari Jualan
    Kumpulan dengan produk_id
    Pesanan oleh total_sales desc
    Had 10
)
Pilih * dari top_sellers;

Untuk mengelakkan kesilapan ini, pastikan anda memasukkan semua lajur yang diperlukan apabila menentukan CTE.

Kesilapan biasa apabila menggunakan prosedur tersimpan adalah lupa untuk mengendalikan pengecualian. Ini boleh menyebabkan prosedur tersimpan gagal melancarkan urus niaga dengan betul apabila mereka menghadapi ralat.

 - Ralat Contoh Buat Prosedur Update_Employee_Salary (dalam emp_id int, dalam perpuluhan baru (10, 2))
Mulakan
    Mengemas kini pekerja
    Tetapkan gaji = new_salary
    Di mana pekerja_id = emp_id;
Akhir;

Untuk mengelakkan kesilapan ini, pastikan untuk memasukkan pengendalian pengecualian dan pengurusan transaksi dalam prosedur yang disimpan.

Pengoptimuman prestasi dan amalan terbaik

Apabila menggunakan fungsi tetingkap, titik utama dalam pengoptimuman prestasi adalah untuk memilih bingkai tetingkap yang sesuai. Menggunakan ROWS atau klausa RANGE dapat meningkatkan prestasi pertanyaan dengan ketara kerana mereka dapat mengurangkan jumlah fungsi pengiraan tingkap.

 - Contoh Pengoptimuman Pilih 
    produk_id,
    Sale_date,
    Sale_amount,
    SUM (SALE_AMOUNT) OVER (partition by product_id order oleh baris SALE_DATE antara baris sebelumnya yang tidak terkawal dan semasa) sebagai runner_total
Dari 
    jualan;

Dalam contoh ini, kami menggunakan klausa ROWS untuk menentukan rangka kerja tetingkap, yang dapat meningkatkan prestasi pertanyaan.

Apabila menggunakan CTE, titik utama dalam pengoptimuman prestasi adalah untuk mengelakkan menggunakan pengiraan kompleks dalam CTE. CTE adalah set hasil sementara yang boleh menjejaskan prestasi pertanyaan jika ia mengandungi pengiraan kompleks.

 - Contoh pengoptimuman dengan sales_summary sebagai (
    Pilih Product_ID, SUM (SALE_AMOUNT) sebagai total_sales
    Dari jualan
    Kumpulan dengan produk_id
)
Pilih * dari sales_summary;

Dalam contoh ini, kami meletakkan pengiraan kompleks di luar CTE untuk meningkatkan prestasi pertanyaan.

Apabila menggunakan prosedur yang disimpan, titik utama dalam pengoptimuman prestasi adalah untuk mengelakkan menggunakan kursor dalam prosedur yang disimpan. Kursor boleh menyebabkan kemerosotan prestasi kerana mereka perlu memproses garis data mengikut baris.

 - Contoh Pengoptimuman Buat Prosedur Update_Employee_Salaries ()
Mulakan
    Mengemas kini pekerja
    Tetapkan gaji = gaji * 1.1;
Akhir;

Dalam contoh ini, kami mengelakkan menggunakan kursor dan menggunakan operasi kemas kini batch untuk meningkatkan prestasi.

Apabila menulis kod SQL, amalan terbaik termasuk menggunakan alias yang bermakna, kod anotasi, dan menyimpan kod yang boleh dibaca dan dikekalkan.

 - Contoh Amalan Terbaik Pilih 
    E.Lempl EMP_ID, - Gunakan alias bermakna E.First_name, - Kod Komen E.LAST_NAME,
    D.Department_name - Pastikan kebolehbacaan dan kebolehkerjaan kod dari 
    Pekerja e
Menyertai 
    jabatan d pada e.department_id = d.department_id;

Dengan mengikuti amalan terbaik ini, anda boleh menulis kod SQL yang lebih cekap dan lebih mudah dipelihara.

Dalam proses meneroka SQL, kita bukan sahaja menguasai penggunaan fungsi, CTE dan prosedur yang disimpan asas dan maju, tetapi juga belajar bagaimana untuk mengelakkan kesilapan biasa dan mengoptimumkan prestasi. Saya harap artikel ini dapat membantu anda memahami dan menggunakan ciri -ciri SQL yang kuat ini dan mencapai kejayaan yang lebih besar dalam analisis data dan pengurusan.

Atas ialah kandungan terperinci SQL Deep Dive: Menguasai Fungsi Tetingkap, Ekspresi Jadual Biasa (CTE), dan prosedur yang disimpan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!