Sejak awal, saya ingin menjelaskan bahwa dalam hal ini Oracle disajikan sebagai bahasa SQL kolektif. Pengelompokan dan penerapannya berlaku untuk seluruh keluarga SQL (yang dipahami di sini sebagai bahasa kueri terstruktur) dan berlaku untuk semua kueri, dengan koreksi untuk sintaks setiap bahasa.
Saya akan mencoba menjelaskan secara singkat dan mudah semua informasi yang diperlukan dalam dua bagian. Posting kemungkinan besar akan berguna untuk pengembang pemula. Siapa peduli - selamat datang di kucing.
Bagian 1: Penawaran Diurutkan berdasarkan, Kelompokkan menurut, Memiliki
Di sini kita akan berbicara tentang pengurutan - Urutkan menurut, pengelompokan - Kelompokkan menurut, pemfilteran - Memiliki, dan rencana kueri. Tapi hal pertama yang pertama.
Dipesan oleh
Order by operator mengurutkan nilai output, mis. mengurutkan nilai yang diambil dengan kolom tertentu. Pengurutan juga dapat diterapkan dengan alias kolom yang ditentukan menggunakan operator.
Keuntungan dari Order by adalah dapat diterapkan ke kolom numerik dan string. Kolom string biasanya diurutkan menurut abjad.
Urutan menaik diterapkan secara default. Jika Anda ingin mengurutkan kolom dalam urutan menurun, gunakan operator DESC tambahan.
Sintaks:
PILIH kolom1, kolom2, … (menunjukkan nama)
FROM nama_tabel
ORDER BY kolom1, kolom2 … ASC | DESC ;
Mari kita lihat semuanya dengan contoh:
Pada tabel pertama, kita mendapatkan semua data dan mengurutkannya dalam urutan menaik menurut kolom ID.
Yang kedua, kami juga mendapatkan semua data. Sortir menurut kolom ID secara berurutan menggunakan kata kunci DESC .
Tabel ketiga menggunakan beberapa bidang pengurutan. Pertama, sortir menurut departemen. Jika operator pertama sama untuk bidang dengan departemen yang sama, kondisi pengurutan kedua diterapkan; dalam kasus kami, ini adalah gaji.
Sangat sederhana. Kita dapat menetapkan lebih dari satu kondisi pengurutan, yang memungkinkan kita mengurutkan daftar keluaran dengan lebih cerdas.
Kelompokkan menurut
Di SQL, Grup menurut klausa mengumpulkan data yang diambil dari database dalam grup tertentu. Pengelompokan membagi semua data ke dalam kumpulan logika sehingga penghitungan statistik dapat dilakukan secara terpisah di setiap grup.
Operator ini digunakan untuk menggabungkan hasil seleksi dengan satu atau lebih kolom. Setelah pengelompokan, hanya akan ada satu entri untuk setiap nilai yang digunakan di kolom.
Terkait erat dengan penggunaan SQL Group dengan pernyataan adalah penggunaan fungsi agregat dan pernyataan SQL Having. Fungsi agregat di SQL adalah fungsi yang mengembalikan satu nilai atas sekumpulan nilai kolom. Sebagai contoh: COUNT (), MIN (), MAX (), AVG (), SUM ()
Sintaks:
SELECT nama_kolom
FROM nama_tabel
DI MANA kondisi
KELOMPOKKAN OLEH nama_kolom
ORDER OLEH nama_kolom;
Kelompokkan menurut muncul setelah klausa WHERE dalam kueri SELECT . Secara opsional, Anda dapat menggunakan ORDER BY untuk mengurutkan nilai output.
Jadi, berdasarkan tabel dari contoh sebelumnya, kita perlu mencari gaji maksimal untuk karyawan masing-masing departemen. Sampel akhir harus menyertakan nama departemen dan gaji maksimum.
Solusi 1 (tanpa menggunakan pengelompokan):
SELECT DISTINCT
ie.department
ie.slary
FROM itx_employee ie
WHERE ie.salary = (
SELECT
max(ie1.salary)
FROM itx_employee ie1
WHERE ie.department = ie1.department
)
Solusi 2 (menggunakan pengelompokan):
SELECT
department,
max(salary)
FROM itx_employee
GROUP BY department
Dalam contoh pertama, kami menyelesaikan masalah tanpa menggunakan pengelompokan, tetapi menggunakan sub-pilih, yaitu. letakkan yang kedua dalam satu pilihan. Dalam solusi kedua, kami menggunakan pengelompokan.
Contoh kedua lebih pendek dan lebih mudah dibaca, meskipun fungsinya sama seperti yang pertama.
Bagaimana Kelompok menurut bekerja untuk kita: pertama membagi dua departemen menjadi kelompok qa dan dev. Kemudian dia mencari gaji maksimal untuk masing-masing.
Memiliki
Memiliki adalah alat penyaringan. Ini menunjukkan hasil dari melakukan fungsi agregat. Memiliki klausa digunakan dalam SQL di mana WHERE tidak dapat digunakan.
Jika klausa WHERE mendefinisikan predikat untuk menyaring baris, maka Having digunakan setelah pengelompokan untuk menentukan predikat logis yang memfilter grup berdasarkan nilai-nilai fungsi agregat. Klausa diperlukan untuk menguji nilai yang diperoleh menggunakan fungsi agregat dari grup baris.
Sintaks:
PILIH nama_kolom
FROM nama_tabel
DI MANA kondisi
GROUP BY nama_kolom Kondisi
HAVING Pertama, kami menampilkan departemen dengan gaji rata-rata lebih dari 4000. Kemudian kami menampilkan gaji maksimum menggunakan filtering.
Solusi 1 (tanpa menggunakan GROUP BY dan HAVING):
SELECT DISTINCT
ie.department AS "DEPARTMENT",
(
(SELECT
AVG(ie1.salary)
FROM itx_employee ie1
WHERE ie1.department = ie.department)
) AS "AVG SALARY"
FROM itx_employee ie
where (SELECT
AVG(ie1.salary)
FROM itx_employee ie1
WHERE ie1.department = ie.department) > 4000
Solusi 2 (menggunakan GROUP BY dan HAVING):
SELECT
department,
AVG(salary)
FROM itx_employee
GROUP BY department
HAVING AVG(salary) > 4000
Contoh pertama menggunakan dua sub-pilihan, satu untuk menemukan gaji maksimum, dan yang lainnya untuk memfilter gaji rata-rata. Contoh kedua, sekali lagi, keluar jauh lebih sederhana dan lebih ringkas.
Minta rencana
Cukup sering ada situasi ketika permintaan berjalan dalam waktu lama, menghabiskan sumber daya memori dan disk yang signifikan. Untuk memahami mengapa kueri berjalan lama dan tidak efisien, kita bisa melihat rencana kueri.
Rencana kueri adalah rencana eksekusi yang dimaksudkan untuk kueri, mis. bagaimana DBMS akan menjalankannya. DBMS akan menuliskan semua operasi yang akan dilakukan di dalam subquery. Setelah menganalisis semuanya, kami akan dapat memahami di mana kelemahan berada dalam permintaan dan menggunakan rencana kueri kami dapat mengoptimalkannya.
Eksekusi pernyataan SQL apa pun di Oracle mengambil apa yang disebut "rencana eksekusi". Rencana eksekusi kueri ini adalah deskripsi tentang bagaimana Oracle akan mengambil data sesuai dengan pernyataan SQL yang dijalankan. Rencana adalah pohon yang berisi urutan langkah-langkah dan hubungan di antara mereka.
Alat yang memungkinkan Anda untuk mendapatkan perkiraan rencana eksekusi suatu kueri termasuk Toad, SQL Navigator, PL / SQL Developer , dll. Alat tersebut memberikan sejumlah indikator konsumsi sumber daya kueri, di antaranya yang utama adalah: biaya - biaya eksekusi dan kardinalitas (atau baris ) - kardinalitas (atau kuantitas garis).
Semakin tinggi nilai indikator ini, semakin kurang efisien kueri.
Di bawah ini Anda dapat melihat analisis dari query plan. Solusi pertama menggunakan sub-pilih, yang kedua menggunakan pengelompokan. Perhatikan bahwa solusi pertama memproses 22 baris, yang kedua diproses 15.
Analisis
rencana kueri: Analisis rencana kueri lain yang menggunakan dua subpilihan:
Contoh ini disajikan sebagai varian penggunaan alat SQL yang tidak efisien dan saya tidak menyarankan Anda menggunakannya dalam kueri Anda.
Semua fitur di atas akan membuat hidup Anda lebih mudah saat menulis kueri dan meningkatkan kualitas serta keterbacaan kode Anda.
Bagian 2: Fungsi Jendela
Fungsi jendela tanggal kembali ke Microsoft SQL Server 2005. Mereka melakukan perhitungan pada rentang baris tertentu dalam klausa Select. Singkatnya, "jendela" adalah sekumpulan garis di mana penghitungan dilakukan. "Jendela" memungkinkan Anda mengurangi data dan memprosesnya dengan lebih baik. Fitur ini memungkinkan Anda untuk membagi seluruh dataset menjadi beberapa jendela.
Jendela memiliki keuntungan besar. Tidak perlu membentuk kumpulan data untuk penghitungan, yang memungkinkan Anda menyimpan semua baris kumpulan dengan ID uniknya. Hasil fungsi jendela ditambahkan ke pilihan yang dihasilkan di satu bidang lagi.
Sintaks:
SELECT column_name (s)
Aggregate function (kolom untuk perhitungan)
OVER ([ PARTITION BYkolom ke grup]
FROM nama_tabel
[ ORDER BY kolom untuk mengurutkan]
[ ekspresi ROWS atau RANGE untuk membatasi baris dalam grup])
OVER PARTITION BY adalah properti untuk menyetel ukuran jendela. Di sini Anda dapat menentukan informasi tambahan, memberikan perintah layanan, misalnya, menambahkan nomor baris. Sintaks fungsi jendela cocok dengan pemilihan kolom.
Mari kita lihat semuanya dengan contoh: departemen lain telah ditambahkan ke tabel kita, sekarang ada 15 baris di tabel. Kami akan mencoba menarik karyawan, gaji mereka, serta gaji maksimum organisasi.
Di bidang pertama kami mengambil namanya, di bidang kedua - gaji. Selanjutnya, kami menggunakan fungsi jendela di atas ()... Kami menggunakannya untuk mendapatkan gaji maksimum di seluruh organisasi, karena ukuran "jendela" tidak ditunjukkan. Over () dengan tanda kurung kosong berlaku untuk seluruh pilihan. Oleh karena itu, di mana-mana gaji maksimum adalah 10.000 Hasil dari fungsi jendela ditambahkan ke setiap baris.
Jika kami menghapus penyebutan fungsi jendela dari baris keempat kueri, mis. hanya max (gaji) yang tersisa , permintaan tidak akan berhasil. Gaji maksimum tidak bisa dihitung. Karena data akan diproses baris demi baris, dan pada saat menelepon max (gaji) hanya akan ada satu nomor di baris saat ini, yaitu. pekerja sekarang. Di sinilah Anda dapat melihat keuntungan dari fungsi jendela. Pada saat panggilan berlangsung, ini bekerja dengan seluruh jendela dan dengan semua data yang tersedia.
Mari kita lihat contoh lain di mana Anda perlu menampilkan gaji maksimum setiap departemen:
Faktanya, kami menetapkan kerangka untuk "jendela", membaginya menjadi beberapa departemen. Kami menggunakan departemen sebagai contoh peringkat. Kami memiliki tiga departemen: dev, qa, dan penjualan.
"Window" menemukan gaji maksimum untuk setiap departemen. Sebagai hasil dari pemilihan, kami melihat bahwa gaji maksimum pertama ditemukan untuk dev, lalu untuk qa, lalu untuk penjualan. Seperti disebutkan di atas, hasil dari fungsi jendela dituliskan ke hasil pengambilan setiap baris.
Dalam contoh sebelumnya, tanda kurung setelah over tidak ditentukan. Di sini kami menggunakan PARTITION BY yang memungkinkan kami untuk mengatur ukuran jendela kami. Di sini Anda dapat menentukan beberapa informasi tambahan, mengirim perintah layanan, misalnya nomor baris.
Kesimpulan
SQL tidak sesederhana yang terlihat pada pandangan pertama. Semua yang dijelaskan di atas adalah fungsionalitas dasar fungsi jendela. Dengan bantuan mereka, Anda dapat "menyederhanakan" permintaan kami. Tetapi ada lebih banyak potensi yang tersembunyi di dalamnya: ada operator utilitas (misalnya ROWS atau RANGE) yang dapat digabungkan untuk menambahkan lebih banyak fungsionalitas ke kueri.
Semoga postingan ini bermanfaat bagi semua orang yang tertarik dengan topik ini.