Moving Average Sql Oracle

Fungsi SQL Fungsi SQL dibangun ke dalam Oracle Database dan tersedia untuk digunakan dalam berbagai pernyataan SQL yang sesuai. Jangan membingungkan fungsi SQL dengan fungsi user-defined yang ditulis dalam PLSQL. Jika Anda memanggil fungsi SQL dengan argumen tipe datatype selain tipe data yang diharapkan oleh fungsi SQL, maka Oracle mencoba mengubah argumen ke tipe data yang diharapkan sebelum melakukan fungsi SQL. Jika Anda memanggil fungsi SQL dengan argumen null, maka fungsi SQL secara otomatis mengembalikan null. Satu-satunya fungsi SQL yang tidak harus mengikuti perilaku ini adalah CONCAT. NVL. MENGGANTIKAN. Dan REGEXPREPLACE. Nilai gabungan pengaturan NLSCOMP dan NLSSORT menentukan aturan yang dengannya karakter diurutkan dan dibandingkan. Jika NLSCOMP diatur ke LINGUISTIC untuk database Anda, maka semua entitas dalam bab ini akan ditafsirkan sesuai dengan aturan yang ditentukan oleh parameter NLSSORT. Jika NLSCOMP tidak disetel ke LINGUISTIC. Maka fungsinya ditafsirkan tanpa memperhatikan setting NLSSORT. NLSSORT dapat diatur secara eksplisit. Jika tidak diatur secara eksplisit, itu berasal dari bahasa NLSLANGUAGE. Silakan merujuk ke Panduan Dukungan Oracle Database Globalization untuk informasi lebih lanjut tentang pengaturan ini. Dalam diagram sintaks untuk fungsi SQL, argumen ditunjukkan oleh tipe data mereka. Ketika fungsi parameter muncul dalam sintaks SQL, ganti dengan salah satu fungsi yang dijelaskan di bagian ini. Fungsi dikelompokkan berdasarkan tipe datatype argumen dan nilai kembaliannya. Ketika Anda menerapkan fungsi SQL ke kolom LOB, Oracle Database menciptakan LOB sementara selama pemrosesan SQL dan PLSQL. Anda harus memastikan bahwa kuota tablespace sementara cukup untuk menyimpan LOB sementara ini untuk aplikasi Anda. Fungsi yang Ditetapkan Pengguna untuk informasi tentang fungsi pengguna dan Konversi Data untuk konversi jenis datatypes secara implisit Referensi Teks Oracle untuk informasi tentang fungsi yang digunakan dengan Oracle Text Oracle Data Mining Application Developers Panduan untuk informasi tentang fungsi frequent itemset yang sering digunakan dengan Oracle Data Mining Sintaks yang menunjukkan kategori Dari fungsi berikut: Bagian-bagian yang mengikuti daftar fungsi SQL built-in di masing-masing kelompok yang digambarkan dalam diagram sebelumnya kecuali fungsi yang ditentukan pengguna. Semua fungsi SQL built-in kemudian dijelaskan secara abjad. Fungsi Single-Row Fungsi single-row mengembalikan satu baris hasil untuk setiap baris tabel atau tampilan tanya. Fungsi ini bisa muncul dalam daftar pilih, klausa WHERE, START WITH dan CONNECT BY clauses, dan HAVING clause. Fungsi Numerik Fungsi numerik menerima input numerik dan mengembalikan nilai numerik. Sebagian besar fungsi numerik yang mengembalikan nilai NUMBER yang akurat ke 38 digit desimal. Fungsi transendental COS. COSH. EXP. LN. LOG. DOSA. SINH. SQRT. TAN. Dan TANH akurat sampai 36 digit desimal. Fungsi transendental ACOS. SEPERTI DALAM. ATAN. Dan ATAN2 akurat sampai 30 digit desimal. Fungsi numeriknya adalah: Fungsi Karakter Mengembalikan Nilai Karakter Fungsi karakter yang mengembalikan nilai karakter mengembalikan nilai dari tipe data berikut kecuali jika didokumentasikan secara lain: Jika argumen inputnya CHAR atau VARCHAR2. Maka nilai yang dikembalikan adalah VARCHAR2. Jika argumen inputnya adalah NCHAR atau NVARCHAR2. Maka nilai yang dikembalikan adalah NVARCHAR2. Panjang nilai yang dikembalikan oleh fungsi dibatasi oleh panjang maksimum datatype yang dikembalikan. Untuk fungsi yang mengembalikan CHAR atau VARCHAR2. Jika panjang nilai pengembalian melebihi batas, maka Oracle Database memotongnya dan mengembalikan hasilnya tanpa pesan kesalahan. Untuk fungsi yang mengembalikan nilai CLOB, jika panjang nilai pengembalian melebihi batas, maka Oracle menimbulkan kesalahan dan mengembalikan data. Fungsi karakter yang mengembalikan nilai karakter adalah: Fungsi Karakter NLS Fungsi karakter NLS mengembalikan informasi tentang himpunan karakter. Fungsi karakter NLS adalah: Fungsi Karakter Mengembalikan Nilai Jumlah Fungsi karakter yang mengembalikan nilai nilai dapat dianggap sebagai argumen masing-masing karakter tipe data. Fungsi karakter yang mengembalikan nilai angka adalah: Fungsi Waktu Fungsi datetime beroperasi pada tanggal (DATE), timestamp (TIMESTAMP, TIMESTAMP DENGAN ZONA TIME, TIMESTAMP DENGAN ZONA WAKTU TIMUR), dan interval (HARI INTERVAL KE KEDUA TAHUN INTERVAL KE BULAN) Nilai. Beberapa fungsi datetime dirancang untuk datatype Oracle DATE (ADDMONTHS. CURRENTDATE. LASTDAY. NEWTIME dan NEXTDAY). Jika Anda memberikan nilai timestamp sebagai argumen mereka, Oracle Database mengubah tipe masukan secara internal menjadi nilai DATE dan mengembalikan nilai DATE. Pengecualiannya adalah fungsi MONTHSBETWENEN, yang mengembalikan angka, dan fungsi ROUND dan TRUNC, yang tidak menerima nilai cap waktu atau nilai interval sama sekali. Fungsi datetime yang tersisa dirancang untuk menerima salah satu dari tiga jenis data (tanggal, cap waktu, dan interval) dan mengembalikan nilai salah satu dari jenis ini. Fungsi datetime adalah: Fungsi Perbandingan Umum Fungsi perbandingan umum menentukan nilai terbesar dan atau paling sedikit dari sekumpulan nilai. Fungsi perbandingan umum adalah: Fungsi Konversi Fungsi konversi mengubah nilai dari satu datatype yang lain. Umumnya, bentuk nama fungsi mengikuti konvensi datatype TO datatype. Tipe datatype pertama adalah datatype input. Datatype kedua adalah output datatype. Fungsi konversi SQL adalah: Fungsi Objek Besar Fungsi objek besar beroperasi pada LOB. Fungsi objek besar adalah: Fungsi Koleksi Fungsi pengumpulan beroperasi pada tabel bersarang dan varrays. Fungsi pengumpulan SQL adalah: Fungsi hirarkis Fungsi hierarkis menerapkan informasi jalur hierarkis ke kumpulan hasil. Fungsi Data Mining Fungsi data mining beroperasi pada model yang telah dibangun menggunakan paket DBMSDATAMINING atau Oracle Data Mining Java API. Fungsi pengumpulan data SQL adalah: Fungsi XML Fungsi XML berfungsi pada atau mengembalikan dokumen XML atau fragmen. Untuk informasi lebih lanjut tentang memilih dan query data XML menggunakan fungsi ini, termasuk informasi mengenai pemformatan output, lihat Panduan Pengembang XML XML XML. Fungsi SQL XML adalah: Encoding and Decoding Functions Fungsi pengkodean dan penguraian memungkinkan Anda memeriksa dan memecahkan kode data dalam database. NULL-Related Functions Fungsi yang berhubungan dengan NULL memudahkan penanganan null. Fungsi yang berhubungan dengan NULL adalah: Fungsi Lingkungan dan Identifier Fungsi lingkungan dan pengenal memberikan informasi tentang instance dan sesi. Fungsi-fungsi ini adalah: Fungsi Agregat Fungsi agregat mengembalikan satu baris hasil berdasarkan kelompok baris, bukan pada baris tunggal. Fungsi agregat dapat muncul dalam daftar pilih dan dalam klausa ORDER BY dan HAVING. Mereka biasanya digunakan dengan klausa GROUP BY dalam sebuah pernyataan SELECT, di mana Oracle Database membagi baris tabel tanya atau melihat ke dalam kelompok. Dalam kueri yang berisi klausa GROUP BY, elemen dari daftar pilih dapat berupa fungsi agregat, ekspresi, konstanta, atau ekspresi GROUP BY yang melibatkan salah satunya. Oracle menerapkan fungsi agregat ke setiap kelompok baris dan mengembalikan satu baris hasil untuk setiap grup. Jika Anda menghilangkan klausa GROUP BY, maka Oracle menerapkan fungsi agregat dalam daftar pilih ke semua baris di tabel tanya atau tampilan. Anda menggunakan fungsi agregat dalam klausa HAVING untuk menghilangkan kelompok dari keluaran berdasarkan hasil fungsi agregat, dan bukan pada nilai masing-masing baris tabel atau tampilan kueri. Menggunakan klausa GROUP BY: Contoh dan Klausa HAVING untuk informasi lebih lanjut tentang klausa GROUP BY dan HAVING clauses in queries and subqueries Banyak (tapi tidak semua) fungsi agregat yang mengambil satu argumen menerima klausul ini: DISTINCT menyebabkan fungsi agregat dipertimbangkan Hanya nilai ekspresi argumen yang berbeda. SEMUA menyebabkan fungsi agregat untuk mempertimbangkan semua nilai, termasuk semua duplikat. Misalnya, rata-rata DISTINCT 1, 1, 1, dan 3 adalah 2. Rata-rata ALL adalah 1,5. Jika Anda menentukan tidak, maka defaultnya adalah ALL. Semua fungsi agregat kecuali COUNT () dan GROUPING ignore nulls. Anda dapat menggunakan fungsi NVL dalam argumen ke fungsi agregat untuk mengganti nilai null. COUNT tidak pernah mengembalikan null, tapi mengembalikan angka atau nol. Untuk semua fungsi agregat yang tersisa, jika kumpulan data tidak berisi baris, atau hanya berisi baris dengan null sebagai argumen ke fungsi agregat, maka fungsi mengembalikan null. Fungsi agregat MIN. MAX. JUMLAH. AVG. MENGHITUNG. PERBEDAAN. Dan STDDEV. Ketika diikuti oleh kata kunci KEEP, dapat digunakan bersamaan dengan fungsi PERTAMA atau LAST untuk beroperasi pada seperangkat nilai dari sekumpulan baris yang diberi peringkat sebagai FIRST atau TERAKHIR berkenaan dengan spesifikasi penyortiran tertentu. Silakan merujuk ke PERTAMA untuk informasi lebih lanjut. Anda bisa menyarangkan fungsi agregat. Misalnya, contoh berikut menghitung rata-rata dari gaji maksimum semua departemen dalam skema sampel: Perhitungan ini mengevaluasi agregat dalam (MAX (gaji)) untuk setiap kelompok yang didefinisikan oleh klausa GROUP BY (departemen), dan agregat Hasilnya lagi Fungsi agregat adalah: Fungsi Analitik Fungsi analitik menghitung nilai agregat berdasarkan sekelompok baris. Mereka berbeda dari fungsi agregat karena mereka mengembalikan beberapa baris untuk setiap grup. Kelompok baris disebut jendela dan didefinisikan oleh analitik. Untuk setiap baris, sebuah jendela geser dari baris ditentukan. Jendela menentukan kisaran baris yang digunakan untuk melakukan perhitungan untuk baris saat ini. Ukuran jendela dapat didasarkan pada sejumlah fisik baris atau interval logis seperti waktu. Fungsi analitik adalah rangkaian operasi terakhir yang dilakukan dalam kueri kecuali klausa ORDER BY akhir. Semua bergabung dan semua WHERE. GROUP BY. Dan klausa HAVING selesai sebelum fungsi analitik diproses. Oleh karena itu, fungsi analitik hanya bisa muncul dalam daftar pilih atau klausa ORDER BY. Fungsi analitik biasanya digunakan untuk menghitung agregat kumulatif, bergerak, terpusat, dan pelaporan. Saya melakukan beberapa Googling dan tidak dapat menemukan jawaban atas pertanyaan ini lebih baru dari beberapa tahun yang lalu, jadi saya pikir Id bertanya. Fitur RAC Oracles menawarkan penyeimbang beban untuk transaksi baca dan tulis, serta peningkatan skala dan ketersediaan tinggi tanpa downtime (setidaknya, seperti yang saya mengerti - akan menyebarkan database pertama kami yang menggunakan RAC, lihatlah dengan baik bagaimana Itu pergi). Apakah ada set fitur SQL Server (atau komponen pihak ketiga yang dapat Anda instal di atas) yang memberikan fungsionalitas setara Weve selalu menggunakan pengelompokan Windows, di mana peristiwa failover menyebabkan sekitar 20-30 detik downtime SQL - selalu dapat ditolerir, namun tidak ideal. Sekarang, dengan AlwaysOn di SQL 2012, SQL Server menyusut menjadi sekitar 15 detik dan menambahkan konsep basis data hanya-baca-sekunder, namun mereka tetap mewajibkan penulisan transaksi tersedak melalui satu titik koneksi (lebih meningkat, karena banyak transaksi Baca saja, tapi tetap saja tidak benar-benar load balancing), dan dalam kasus kegagalan node atau kebutuhan untuk patch, masih ada downtime. Saya rasa hanya rasa ingin tahu yang lebih - saya merasa seperti ini adalah satu-satunya area yang SQL Server tertinggal Oracle (setidaknya di antara fitur yang saya lihat secara pribadi digunakan). Saya ingin melihat apakah ada pilihan di luar sana untuk menutup celah itu dan mungkin memperbaiki penerapan SQL Server kami sendiri sambil menunggu fitur setara Microsoft ditambahkan - mungkin di SQL 20142015 bertanya 13 Juli 12 di 22:04 rwmnau lagi, saya Sarankan agar Anda memuji pujian RAC Anda sampai Anda berhasil menerapkannya dan menggunakannya selama beberapa bulan. -) Anda mungkin benar itu semua yang dijanjikan dan bekerja sempurna untuk Anda. Tapi Anda mungkin dibanjiri oleh glitter mengkilap di kotaknya. -) ndash Aaron Bertrand 9830 14 Jul 12 12 di 2: 5122 SQL untuk Analisis dan Pelaporan Oracle telah meningkatkan kemampuan pemrosesan analisis SQL dengan memperkenalkan keluarga baru fungsi SQL analitik. Fungsi analitik ini memungkinkan Anda untuk menghitung: Tingkatan dan persentil Perhitungan jendela bergerak Regresi linier Statistik Fungsi peringkat mencakup distribusi kumulatif, peringkat persen, dan ubin N. Perhitungan jendela bergerak memungkinkan Anda menemukan agregasi bergerak dan kumulatif, seperti jumlah dan rata-rata. Analisis Laglead memungkinkan referensi antar baris langsung sehingga Anda dapat menghitung perubahan periode-ke-periode. Analisis firstlast memungkinkan Anda menemukan nilai pertama atau terakhir dalam grup yang dipesan. Perangkat tambahan lainnya ke SQL mencakup ekspresi CASE dan partisi outer join. Pernyataan CASE menyediakan jika-maka logika berguna dalam banyak situasi. Partisi outer join adalah perpanjangan sintaks join ANSI outer yang memungkinkan pengguna untuk secara selektif mengelompokkan dimensi tertentu sambil menjaga agar orang lain jarang. Ini memungkinkan alat pelaporan untuk memodifikasikan dimensi secara selektif, misalnya yang muncul dalam laporan lintas-tabular sambil membuat orang lain jarang. Untuk meningkatkan kinerja, fungsi analitik dapat diparalelkan: beberapa proses secara bersamaan dapat mengeksekusi semua pernyataan ini. Kemampuan ini membuat perhitungan menjadi lebih mudah dan efisien, sehingga meningkatkan kinerja database, skalabilitas, dan kesederhanaan. Fungsi analitik diklasifikasikan seperti yang dijelaskan pada Tabel 22-1. Tabel 22-1 Fungsi Analitik dan Kegunaannya Untuk melakukan operasi ini, fungsi analitik menambahkan beberapa elemen baru ke pemrosesan SQL. Unsur-unsur ini dibangun di atas SQL yang ada untuk memungkinkan ekspresi perhitungan yang fleksibel dan kuat. Dengan hanya beberapa pengecualian, fungsi analitik memiliki elemen baru ini. Aliran pemrosesan ditunjukkan pada Gambar 22-1. Gambar 22-1 Pesanan Pengolahan Konsep penting yang digunakan dalam fungsi analitik adalah: Pengolahan kueri menggunakan fungsi analitik berlangsung dalam tiga tahap. Pertama, semua bergabung, WHERE. Klausa GROUP BY dan HAVING dilakukan. Kedua, himpunan hasil dibuat tersedia untuk fungsi analitik, dan semua perhitungannya terjadi. Ketiga, jika kueri memiliki klausa ORDER BY pada akhirnya, ORDER BY diproses untuk memungkinkan pesanan pesanan yang tepat. Urutan pemrosesan ditunjukkan pada Gambar 22-1. Hasil mengatur partisi Fungsi analitik memungkinkan pengguna untuk membagi hasil query set ke dalam kelompok baris yang disebut partisi. Perhatikan bahwa istilah partisi yang digunakan dengan fungsi analitik tidak terkait dengan fitur partisi tabel. Sepanjang bab ini, istilah partisi hanya mengacu pada makna yang terkait dengan fungsi analitik. Partisi dibuat setelah kelompok didefinisikan dengan klausa GROUP BY, sehingga tersedia untuk hasil agregat seperti jumlah dan rata-rata. Pembagian partisi mungkin didasarkan pada kolom atau ungkapan yang diinginkan. Hasil query set dapat dipartisi menjadi satu partisi yang memegang semua baris, beberapa partisi besar, atau banyak partisi kecil yang masing-masing hanya memiliki beberapa baris. Untuk setiap baris di partisi, Anda dapat menentukan jendela geser data. Jendela ini menentukan rentang baris yang digunakan untuk melakukan perhitungan untuk baris saat ini. Ukuran jendela dapat didasarkan pada sejumlah fisik baris atau interval logis seperti waktu. Jendela memiliki baris awal dan baris akhir. Bergantung pada definisinya, jendela bisa bergerak pada satu atau kedua ujungnya. Misalnya, jendela yang didefinisikan untuk fungsi jumlah kumulatif akan memiliki baris mulai yang ditetapkan pada baris pertama partisi, dan baris akhir akan meluncur dari titik awal sampai ke baris terakhir partisi. Sebaliknya, jendela yang didefinisikan untuk rata-rata bergerak akan memiliki titik awal dan akhir slide sehingga mempertahankan rentang fisik atau logis konstan. Sebuah jendela dapat diatur sebesar semua baris dalam sebuah partisi atau hanya sebuah jendela geser satu baris dalam sebuah partisi. Ketika sebuah jendela dekat perbatasan, fungsi mengembalikan hasil hanya untuk baris yang tersedia, daripada memberi peringatan bahwa hasilnya bukan yang Anda inginkan. Saat menggunakan fungsi jendela, baris saat ini disertakan selama penghitungan, jadi sebaiknya tentukan (n -1) saat Anda menangani n item. Setiap perhitungan yang dilakukan dengan fungsi analitik didasarkan pada baris saat ini dalam sebuah partisi. Baris saat ini berfungsi sebagai titik acuan menentukan awal dan akhir jendela. Misalnya, perhitungan rata-rata bergerak terpusat dapat didefinisikan dengan jendela yang menampung baris saat ini, enam baris sebelumnya, dan enam baris berikut. Ini akan membuat sebuah jendela geser dari 13 baris, seperti yang ditunjukkan pada Gambar 22-2. Gambar 22-2 Contoh Jendela Sliding Ranking, Windowing, dan Fungsi Pelaporan Bagian ini mengilustrasikan fungsi analitik dasar untuk rangking, windowing, dan pelaporan. Perhitungan Regresi Linier Linier Dalam contoh ini, kita menghitung garis regresi kuadrat biasa-kuadrat terkecil yang mengekspresikan kuantitas yang terjual suatu produk sebagai fungsi linear dari daftar harga produk. Perhitungannya dikelompokkan menurut saluran penjualan. Nilai SLOPE. INTCPT. RSQR adalah kemiringan, intersep, dan koefisien determinasi garis regresi. Nilai (integer) COUNT adalah jumlah produk di setiap saluran yang tersedia untuk kuantitas dan harga jual. Agregat Statistik Oracle menyediakan satu set fungsi statistik SQL dan paket statistik, DBMSSTATFUNCS. Bagian ini mencantumkan beberapa fungsi baru beserta sintaks dasar. Statistik Deskriptif Anda dapat menghitung statistik deskriptif berikut ini: Median dari Mode Kumpulan Data Kumpulan Data Anda dapat menghitung statistik parametrik berikut ini: Spearmans rho Koefisien Kendalls tau-b Koefisien Selain fungsi, rilis ini memiliki paket PLSQL, DBMSSTATFUNCS . Ini berisi fungsi statistik deskriptif RINGKASAN beserta fungsinya untuk mendukung pemasangan distribusi. Fungsi RINGKASAN merangkum kolom numerik tabel dengan berbagai statistik deskriptif. Fungsi distribusi lima distribusi mendukung distribusi normal, seragam, Weibull, Poisson, dan eksponensial. Agregat yang Ditetapkan Pengguna Oracle menawarkan fasilitas untuk membuat fungsi Anda sendiri, yang disebut fungsi agregat yang ditentukan pengguna. Fungsi-fungsi ini ditulis dalam bahasa pemrograman seperti PLSQL, Java, dan C, dan dapat digunakan sebagai fungsi analitik atau agregat dalam tampilan terwujud. Lihat Panduan Pengembang Cartridge Data Database Oracle untuk informasi lebih lanjut mengenai sintaks dan batasan. Kelebihan dari fungsi ini adalah: Fungsi yang sangat kompleks dapat diprogram menggunakan bahasa prosedural secara lengkap. Skalabilitas yang lebih tinggi daripada teknik lainnya saat fungsi yang ditentukan pengguna diprogram untuk pemrosesan paralel. Tipe data objek bisa diolah. Sebagai contoh sederhana dari fungsi agregat yang ditentukan pengguna, perhatikan statistik miring. Perhitungan ini mengukur jika kumpulan data memiliki distribusi miring tentang mean-nya. Ini akan memberitahu Anda jika satu ekor distribusi secara signifikan lebih besar dari yang lain. Jika Anda membuat agregat yang ditentukan pengguna yang disebut udskew dan menerapkannya pada data batas kredit pada contoh sebelumnya, pernyataan dan hasil SQL mungkin terlihat seperti ini: Sebelum membangun fungsi agregat yang ditentukan pengguna, Anda harus mempertimbangkan apakah kebutuhan Anda dapat dipenuhi. Di SQL biasa Banyak perhitungan yang kompleks dapat dilakukan secara langsung di SQL, terutama dengan menggunakan ekspresi CASE. Tinggal dengan SQL biasa akan memungkinkan pengembangan yang lebih sederhana, dan banyak operasi query sudah paralel dengan baik di SQL. Bahkan contoh sebelumnya, statistik miring, dapat dibuat dengan menggunakan standar, meskipun panjang, SQL. Operasi Pivoting D ata yang dikembalikan oleh query intelijen bisnis seringkali paling dapat digunakan jika disajikan dalam format crosstabular. The pivotclause dari pernyataan SELECT memungkinkan Anda menulis query crosstabulation yang memutar baris ke kolom, menggabungkan data dalam proses rotasi. Pivoting adalah teknik kunci dalam gudang data. Di dalamnya, Anda mengubah beberapa baris input ke baris yang lebih sedikit dan umumnya lebih lebar di gudang data. Saat berputar, operator agregasi diterapkan untuk setiap item dalam daftar nilai kolom pivot. Kolom pivot tidak boleh mengandung ekspresi yang sewenang-wenang. Jika Anda perlu berporos pada ekspresi, maka Anda harus alias berekspresi dalam pandangan sebelum operasi PIVOT. Sintaks dasarnya adalah sebagai berikut: Untuk menggambarkan penggunaan pivoting, buatlah tampilan berikut sebagai dasar untuk contoh selanjutnya: Contoh: Pivoting Pernyataan berikut ini menggambarkan pivot khas pada kolom saluran: Perhatikan bahwa output telah menciptakan empat kolom alias baru. , PENJUALAN LANGSUNG. INTERNETSALES. KATALOGSAL Dan TELESALES. Satu untuk masing-masing nilai pivot. Outputnya adalah jumlah. Jika tidak ada alias yang diberikan, judul kolomnya adalah nilai IN - list. Pivoting on Multiple Columns Anda dapat berporos pada lebih dari satu kolom. Pernyataan berikut menggambarkan pivot kolom beberapa tipikal: Perhatikan bahwa contoh ini menentukan IN - list multi kolom dengan judul kolom yang dirancang agar sesuai dengan anggota IN - list. Pivoting: Multiple Agregat Anda dapat berporos dengan beberapa agregat, seperti ditunjukkan pada contoh berikut: Perhatikan bahwa kueri membuat judul kolom dengan menggabungkan nilai pivot (atau alias) dengan alias fungsi agregat, ditambah garis bawah. Membedakan Nulls Generasi PIVOT dari Nulls di Sumber Data Anda dapat membedakan antara nilai null yang dihasilkan dari penggunaan PIVOT dan yang ada di data sumber. Contoh berikut menggambarkan null yang dihasilkan oleh PIVOT. Query berikut mengembalikan baris dengan 5 kolom, kolom prodid. Dan pivot menghasilkan kolom Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. Untuk setiap nilai unik prodid. Q1COUNTTOTAL mengembalikan jumlah baris yang nilai qtrnya adalah Q1. Yaitu, dan Q2COUNTTOTAL mengembalikan jumlah baris yang nilai qtrnya adalah Q2. Asumsikan kita memiliki tabel penjualan2 dari struktur berikut: Dari hasilnya, kita tahu bahwa untuk prodid 100, ada 2 baris penjualan untuk kuartal Q1. Dan 1 baris penjualan untuk kuartal Q2 untuk produk 200, ada 1 baris penjualan untuk kuartal Q1. Dan tidak ada baris penjualan untuk kuartal Q2. Jadi, di Q2COUNTTOTAL. Anda dapat mengidentifikasi bahwa NULLlt1gt berasal dari sebuah baris di tabel asli yang ukurannya bernilai null, sementara NULLlt2gt adalah karena tidak ada baris yang hadir dalam tabel asli untuk produk 200 pada kuartal Q2. Operasi yang Tidak Menghasilkan Sebuah unpivot tidak membalikkan operasi PIVOT. Sebagai gantinya, ia memutar data dari kolom menjadi beberapa baris. Jika Anda bekerja dengan data berporos, operasi UNPIVOT tidak dapat membalikkan agregasi yang telah dilakukan oleh PIVOT atau cara lainnya. Untuk mengilustrasikan unpivoting, pertama buat tabel yang diputar yang mencakup empat kolom, untuk perempat tahun: Isi tabel menyerupai berikut ini: Operasi UNPIVOT berikut memutar kolom kuartal ke dalam baris. Untuk setiap produk, akan ada empat baris, satu untuk setiap kuartal. Perhatikan penggunaan INCLUDE NULLS dalam contoh ini. Anda juga bisa menggunakan EXCLUDE NULLS. Yang merupakan setting default. Selain itu, Anda juga dapat melakukan unpivot dengan menggunakan dua kolom, seperti berikut ini: Wildcard dan Subquery Pivoting with XML Operations Jika Anda ingin menggunakan wildcard argument atau subquery di kolom pivoting Anda, Anda dapat melakukannya dengan sintaks XML PIVOT. Dengan PIVOT XML, output dari operasi tersebut diformat dengan benar XML. Contoh berikut menggambarkan penggunaan kata kunci wildcard, APAPUN. Ini menghasilkan XML yang mencakup semua nilai saluran di salesview: Perhatikan bahwa kata kunci ANY tersedia dalam operasi PIVOT hanya sebagai bagian dari operasi XML. Output ini mencakup data untuk kasus dimana saluran berada di kumpulan data. Perhatikan juga bahwa fungsi agregasi harus menentukan klausa GROUP BY untuk mengembalikan beberapa nilai, namun pivotclause tidak mengandung klausa GROUP BY eksplisit. Sebagai gantinya, pivotclause melakukan GROUP BY implisit. Contoh berikut menggambarkan menggunakan subkueri. Ini menghasilkan XML yang mencakup semua nilai saluran dan data penjualan yang sesuai dengan masing-masing saluran: Output densifies data untuk memasukkan semua saluran yang mungkin untuk setiap produk. Data Densification untuk Pelaporan Data biasanya disimpan dalam bentuk yang jarang. Artinya, jika tidak ada nilai untuk kombinasi nilai dimensi yang diberikan, tidak ada baris dalam tabel fakta. Namun, Anda mungkin ingin melihat data dalam bentuk padat, dengan baris untuk semua kombinasi nilai dimensi ditampilkan meskipun tidak ada data fakta untuk mereka. Misalnya, jika produk tidak terjual selama jangka waktu tertentu, Anda mungkin masih ingin melihat produk untuk jangka waktu tersebut dengan nilai penjualan nol di sampingnya. Selain itu, perhitungan deret waktu dapat dilakukan dengan sangat mudah bila data padat sepanjang dimensi waktu. Ini karena data padat akan mengisi jumlah baris yang konsisten untuk setiap periode, yang pada gilirannya mempermudah penggunaan fungsi windowing analitik dengan offset fisik. Keragaman data adalah proses pengubahan data yang jarang menjadi bentuk yang padat. Untuk mengatasi masalah sparsity, Anda bisa menggunakan outer yang dipartisi untuk mengisi celah dalam deret waktu atau dimensi lainnya. Gabung semacam itu memperluas sintaks join luar konvensional dengan menerapkan outer join ke setiap partisi logical yang didefinisikan dalam query. Oracle secara logis mempartisi baris dalam query Anda berdasarkan ekspresi yang Anda tentukan di dalam PARTITION BY clause. Hasil dari outer join yang dipartisi adalah UNION dari outer join dari masing-masing partisi di tabel yang dipartisi secara logis dengan tabel di sisi lain join. Perhatikan bahwa Anda dapat menggunakan jenis ini untuk mengisi celah dalam dimensi apa pun, bukan hanya dimensi waktu saja. Sebagian besar contoh di sini berfokus pada dimensi waktu karena dimensi inilah yang paling sering digunakan sebagai dasar perbandingan. Partition Join Syntax Sintaks untuk partisi outer join memperluas klausa ANSI SQL JOIN dengan frasa PARTITION BY diikuti oleh daftar ekspresi. Ekspresi dalam daftar menentukan kelompok yang mana outer join diterapkan. Berikut ini adalah dua bentuk sintaks yang biasanya digunakan untuk partisi outer join: Perhatikan bahwa FULL OUTER JOIN tidak didukung dengan outer join yang dipartisi. Sampel Data Langka Situasi tipikal dengan dimensi yang jarang ditunjukkan pada contoh berikut, yang menghitung penjualan mingguan dan penjualan terkini untuk produk Bounce selama minggu 20-30 pada tahun 2000 dan 2001: Dalam contoh ini, kami Akan mengharapkan 22 deret data (11 minggu masing-masing dari 2 tahun) jika datanya padat. Namun, kami hanya mendapatkan 18 baris karena minggu 25 dan 26 hilang pada tahun 2000, dan minggu ke 26 dan 28 pada tahun 2001. Mengisi Kesenjangan dalam Data Kami dapat mengambil data yang jarang dari kueri sebelumnya dan melakukan pembagian di luar bergabung dengan sekumpulan padat Data waktu Dalam query berikut, kita alias query asli kita sebagai v dan kita pilih data dari tabel kali, yang kita sebut sebagai t. Disini kita mengambil 22 baris karena tidak ada celah dalam seri. Keempat baris yang ditambahkan masing-masing memiliki 0 karena nilai Penjualan mereka diset ke 0 dengan menggunakan fungsi NVL. Perhatikan bahwa dalam query ini, kondisi WHERE ditempatkan selama berminggu-minggu antara 20 dan 30 pada tampilan inline untuk dimensi waktu. Ini diperkenalkan untuk menjaga agar hasilnya tetap kecil. Mengisi Kesenjangan dalam Dua Dimensi Data dimensi-n biasanya ditampilkan sebagai tab silang 2 dimensi padat dimensi (n - 2) halaman. Ini mengharuskan semua nilai dimensi untuk dua dimensi yang muncul di tab silang terisi. Berikut adalah contoh lain di mana kemampuan bergabung di luar partisi dapat digunakan untuk mengisi kekosongan pada dua dimensi: Dalam kueri ini, klausa anjak subquery WITH V1 merangkum data penjualan di tingkat produk, negara, dan tahun. Hasil ini jarang terjadi, namun pengguna mungkin ingin melihat kombinasi setiap tahun di tiap negara. Untuk mencapai hal ini, kita mengambil setiap partisi v1 berdasarkan nilai produk dan outer join pada dimensi negara terlebih dahulu. Ini akan memberi kita semua nilai negara untuk setiap produk. Kami kemudian mengambil hasil itu dan mempartisinya pada nilai produk dan negara dan kemudian di luar bergabung pada dimensi waktu. Ini akan memberi kita semua nilai waktu untuk setiap kombinasi produk dan negara. Mengisi Kesenjangan dalam Tabel Inventaris Tabel inventaris biasanya melacak jumlah unit yang tersedia untuk berbagai produk. Tabel ini jarang: hanya menyimpan deretan produk saat ada acara. Untuk tabel penjualan, acara tersebut adalah penjualan, dan untuk tabel persediaan, acara tersebut merupakan perubahan jumlah barang yang tersedia untuk suatu produk. Misalnya, perhatikan tabel inventaris berikut: Tabel persediaan sekarang memiliki baris berikut: Untuk tujuan pelaporan, pengguna mungkin ingin melihat data inventaris ini secara berbeda. Misalnya, mereka mungkin ingin melihat semua nilai waktu untuk setiap produk. Hal ini bisa dilakukan dengan menggunakan parted outer join. Selain itu, untuk rentang waktu yang baru dimasukkan dalam periode waktu yang hilang, pengguna mungkin ingin melihat nilai untuk jumlah kolom satuan yang akan dibawa dari periode waktu yang ada. Yang terakhir dapat dilakukan dengan menggunakan fungsi analisis nilai LASTVALUE. Berikut adalah query dan output yang diinginkan: Query batin menghitung outer part yang dipartisi tepat waktu dalam setiap produk. Kueri dalam dumatikan data pada dimensi waktu (artinya dimensi waktu sekarang akan memiliki baris untuk setiap hari dalam seminggu). Namun, jumlah kolom ukuran akan memiliki null untuk baris yang baru ditambahkan (lihat output dalam jumlah kolom pada hasil berikut. Permintaan luar menggunakan fungsi analitik LASTVALUE. Menerapkan fungsi ini mempartisi data berdasarkan produk dan memerintahkan data pada Kolom dimensi waktu (timeid).Untuk setiap baris, fungsi menemukan nilai non-null terakhir di jendela karena opsi MENGABAIKAN NULLS yang dapat Anda gunakan dengan LASTVALUE dan FIRSTVALUE. Kita melihat output yang diinginkan dalam kolom yang berulang-ulang dalam Output berikut: Nilai Data Komputasi untuk Mengisi Sampul Contoh pada bagian sebelumnya menggambarkan bagaimana menggunakan partisi outer join untuk mengisi gap dalam satu atau beberapa dimensi. Namun, set hasil yang dihasilkan oleh outer join yang dipartisi memiliki nilai null untuk kolom yang tidak termasuk dalam Daftar PARTITION BY Biasanya, ini adalah kolom ukuran. Pengguna dapat menggunakan fungsi SQL analitik untuk menggantikan nilai null tersebut dengan nilai non-null. Misalnya, q berikut Uery menghitung total bulanan untuk produk kartu memori 64MB dan disk DVD-R (nomor produk 122 dan 136) untuk tahun 2000. Menggunakan partisi luar bergabung untuk memenuhi data selama berbulan-bulan. Untuk bulan-bulan yang hilang, kemudian menggunakan fungsi SQL analitik AVG untuk menghitung penjualan dan unit menjadi rata-rata bulan ketika produk terjual. Jika bekerja di SQLPlus, dua perintah berikut membungkus judul kolom untuk keterbacaan hasil yang lebih besar: Perhitungan Time Series pada Densified Data Densificatio n tidak hanya untuk tujuan pelaporan. Ini juga memungkinkan beberapa jenis perhitungan, terutama, perhitungan deret waktu. Perhitungan deret waktu lebih mudah bila data padat sepanjang dimensi waktu. Data padat memiliki jumlah baris yang konsisten untuk setiap periode waktu yang pada gilirannya mempermudah penggunaan fungsi jendela analitik dengan offset fisik. To illustrate, let us first take the example on Filling Gaps in Data. and lets add an analytic function to that query. In the following enhanced version, we calculate weekly year-to-date sales alongside the weekly sales. The NULL values that the partitioned outer join inserts in making the time series dense are handled in the usual way: the SUM function treats them as 0s. Period-to-Period Comparison for One Time Level: Example How do we use this feature to compare values across time periods Specifically, how do we calculate a year-over-year sales comparison at the week level The following query returns on the same row, for each product, the year-to-date sales for each week of 2001 with that of 2000. Note that in this example we start with a WITH clause. This improves readability of the query and lets us focus on the partitioned outer join. If working in SQLPlus, the following command wraps the column headings for greater readability of results: In the FROM clause of the inline view densesales. we use a partitioned outer join of aggregate view v and time view t to fill gaps in the sales data along the time dimension. The output of the partitioned outer join is then processed by the analytic function SUM. OVER to compute the weekly year-to-date sales (the weeklyytdsales column). Thus, the view densesales computes the year-to-date sales data for each week, including those missing in the aggregate view s. The inline view yearoveryearsales then computes the year ago weekly year-to-date sales using the LAG function. The LAG function labeled weeklyytdsalesprioryear specifies a PARTITION BY clause that pairs rows for the same week of years 2000 and 2001 into a single partition. We then pass an offset of 1 to the LAG function to get the weekly year to date sales for the prior year. The outermost query block selects data from yearoveryearsales with the condition yr 2001. and thus the query returns, for each product, its weekly year-to-date sales in the specified weeks of years 2001 and 2000. Period-to-Period Comparison for Multiple Time Levels: Example While the prior example shows us a way to create comparisons for a single time level, it would be even more useful to handle multiple time levels in a single query. For example, we could compare sales versus the prior period at the year, quarter, month and day levels. How can we create a query which performs a year-over-year comparison of year-to-date sales for all levels of our time hierarchy We will take several steps to perform this task. The goal is a single query with comparisons at the day, week, month, quarter, and year level. The steps are as follows: We will create a view called cubeprodtime. which holds a hierarchical cube of sales aggregated across times and products . Then we will create a view of the time dimension to use as an edge of the cube. The time edge, which holds a complete set of dates, will be partitioned outer joined to the sparse data in the view cubeprodtime . Finally, for maximum performance, we will create a materialized view, mvprodtime. built using the same definition as cubeprodtime . For more information regarding hierarchical cubes, see Chapter 21, SQL for Aggregation in Data Warehouses. The materialized view is defined in Step 1 in the following section. Step 1 Create the hierarchical cube view The materialized view shown in the following may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short: Because this view is limited to two products, it returns just over 2200 rows. Note that the column HierarchicalTime contains string representations of time from all levels of the time hierarchy. The CASE expression used for the HierarchicalTime column appends a marker (0, 1. ) to each date string to denote the time level of the value. A 0 represents the year level, 1 is quarters, 2 is months, and 3 is day. Note that the GROUP BY clause is a concatenated ROLLUP which specifies the rollup hierarchy for the time and product dimensions. The GROUP BY clause is what determines the hierarchical cube contents. Step 2 Create the view edgetime, which is a complete set of date values edgetime is the source for filling time gaps in the hierarchical cube using a partitioned outer join. The column HierarchicalTime in edgetime will be used in a partitioned join with the HierarchicalTime column in the view cubeprodtime. The following statement defines edgetime : Step 3 Create the materialized view mvprodtime to support faster performance The materialized view definition is a duplicate of the view cubeprodtime defined earlier. Because it is a duplicate query, references to cubeprodtime will be rewritten to use the mvprodtime materialized view. The following materialized may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short. Step 4 Create the comparison query We have now set the stage for our comparison query. We can obtain period-to-period comparison calculations at all time levels. It requires applying analytic functions to a hierarchical cube with dense data along the time dimension. Some of the calculations we can achieve for each time level are: Sum of sales for prior period at all levels of time. Variance in sales over prior period. Sum of sales in the same period a year ago at all levels of time. Variance in sales over the same period last year. The following example performs all four of these calculations. It uses a partitioned outer join of the views cubeprodtime and edgetime to create an inline view of dense data called densecubeprodtime. The query then uses the LAG function in the same way as the prior single-level example. The outer WHERE clause specifies time at three levels: the days of August 2001, the entire month, and the entire third quarter of 2001. Note that the last two rows of the results contain the month level and quarter level aggregations. Note that to make the results easier to read if you are using SQLPlus, the column headings should be adjusted with the following commands. The commands will fold the column headings to reduce line length: Here is the query comparing current sales to prior and year ago sales: The first LAG function ( salespriorperiod ) partitions the data on gidp. cat. subcat. prod. gidt and orders the rows on all the time dimension columns. It gets the sales value of the prior period by passing an offset of 1. The second LAG function ( salessameperiodprioryear ) partitions the data on additional columns qtrnum. monnum. and daynum and orders it on yr so that, with an offset of 1, it can compute the year ago sales for the same period. The outermost SELECT clause computes the variances. Creating a Custom Member in a Dimension: Example In many analytical SQL tasks, it is helpful to define custom members in a dimension. For instance, you might define a specialized time period for analyses. You can use a partitioned outer join to temporarily add a member to a dimension. Note that the new SQL MODEL clause is suitable for creating more complex scenarios involving new members in dimensions. See Chapter 23, SQL for Modeling for more information on this topic. As an example of a task, what if we want to define a new member for our time dimension We want to create a 13th member of the Month level in our time dimension. This 13th month is defined as the summation of the sales for each product in the first month of each quarter of year 2001. The solution has two steps. Note that we will build this solution using the views and tables created in the prior example. Two steps are required. First, create a view with the new member added to the appropriate dimension. The view uses a UNION ALL operation to add the new member. To query using the custom member, use a CASE expression and a partitioned outer join. Our new member for the time dimension is created with the following view: In this statement, the view timec is defined by performing a UNION ALL of the edgetime view (defined in the prior example) and the user-defined 13th month. The gidt value of 8 was chosen to differentiate the custom member from the standard members. The UNION ALL specifies the attributes for a 13th month member by doing a SELECT from the DUAL table. Note that the grouping id, column gidt. is set to 8, and the quarter number is set to 5. Then, the second step is to use an inline view of the query to perform a partitioned outer join of cubeprodtime with timec. This step creates sales data for the 13th month at each level of product aggregation. In the main query, the analytic function SUM is used with a CASE expression to compute the 13th month, which is defined as the summation of the first months sales of each quarter. The SUM function uses a CASE to limit the data to months 1, 4, 7, and 10 within each year. Due to the tiny data set, with just 2 products, the rollup values of the results are necessarily repetitions of lower level aggregations. For more realistic set of rollup values, you can include more products from the Game Console and Y Box Games subcategories in the underlying materialized view. Miscellaneous Analysis and Reporting Capabilities This section illustrates the following additional analytic capabilities: WIDTHBUCKET Function For a given expression, the WIDTHBUCKET function returns the bucket number that the result of this expression will be assigned after it is evaluated. You can generate equiwidth histograms with this function. Equiwidth histograms divide data sets into buckets whose interval size (highest value to lowest value) is equal. The number of rows held by each bucket will vary. A related function, NTILE. creates equiheight buckets. Equiwidth histograms can be generated only for numeric, date or datetime types. So the first three parameters should be all numeric expressions or all date expressions. Other types of expressions are not allowed. If the first parameter is NULL. the result is NULL. If the second or the third parameter is NULL. an error message is returned, as a NULL value cannot denote any end point (or any point) for a range in a date or numeric value dimension. The last parameter (number of buckets) should be a numeric expression that evaluates to a positive integer value 0, NULL. or a negative value will result in an error. Buckets are numbered from 0 to ( n 1). Bucket 0 holds the count of values less than the minimum. Bucket( n 1) holds the count of values greater than or equal to the maximum specified value. WIDTHBUCKET Syntax The WIDTHBUCKET takes four expressions as parameters. The first parameter is the expression that the equiwidth histogram is for. The second and third parameters are expressions that denote the end points of the acceptable range for the first parameter. The fourth parameter denotes the number of buckets. Consider the following data from table customers. that shows the credit limits of 17 customers. This data is gathered in the query shown in Example 22-24 . In the table customers. the column custcreditlimit contains values between 1500 and 15000, and we can assign the values to four equiwidth buckets, numbered from 1 to 4, by using WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Ideally each bucket is a closed-open interval of the real number line, for example, bucket number 2 is assigned to scores between 5000.0000 and 9999.9999. sometimes denoted 5000, 10000) to indicate that 5,000 is included in the interval and 10,000 is excluded. To accommodate values outside the range 0, 20,000), values less than 0 are assigned to a designated underflow bucket which is numbered 0, and values greater than or equal to 20,000 are assigned to a designated overflow bucket which is numbered 5 (num buckets 1 in general). See Figure 22-3 for a graphical illustration of how the buckets are assigned. Figure 22-3 Bucket Assignments You can specify the bounds in the reverse order, for example, WIDTHBUCKET ( custcreditlimit. 20000. 0. 4 ). When the bounds are reversed, the buckets will be open-closed intervals. In this example, bucket number 1 is ( 15000,20000 , bucket number 2 is ( 10000,15000 , and bucket number 4, is ( 0 ,5000 . The overflow bucket will be numbered 0 ( 20000. infinity ), and the underflow bucket will be numbered 5 (- infinity. 0 . It is an error if the bucket count parameter is 0 or negative. Example 22-24 WIDTHBUCKET The followin g query shows the bucket numbers for the credit limits in the customers table for both cases where the boundaries are specified in regular or reverse order. We use a range of 0 to 20,000. Linear Algebra Linear algebra is a branch of mathematics with a wide range of practical applications. Many areas have tasks that can be expressed using linear algebra, and here are some examples from several fields: statistics (multiple linear regression and principle components analysis), data mining (clustering and classification), bioinformatics (analysis of microarray data), operations research (supply chain and other optimization problems), econometrics (a nalysis of consumer demand data), and finance (asset allocation problems). Various libraries for linear algebra are freely available for anyone to use. Oracles UTLNLA package exposes matrix PLSQL data types and wrapper PLSQL subprograms for two of the most popular and robust of these libraries, BLAS and LAPACK. Linear algebra depends on matrix manipulation. Performing matrix manipulation in PLSQL in the past required inventing a matrix representation based on PLSQLs native data types and then writing matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. If developers chose to send data to external packages for processing rather than create their own routines, data transfer back and forth could be time consuming. Using the UTLNLA package lets data stay within Oracle, removes the programming effort, and delivers a fast implementation. Example 22-25 Linear Algebra Here is an example of how Oracles linear algebra support could be used for business analysis. It invokes a multiple linear regression application built using the UTLNLA package. The multiple regression application is implemented in an object called OLSRegression. Note that sample files for the OLS Regression object can be found in ORACLEHOMEplsqldemo . Consider the scenario of a retailer analyzing the effectiveness of its marketing program. Each of its stores allocates its marketing budget over the following possible programs: media advertisements ( media ), promotions ( promo ), discount coupons ( disct ), and direct mailers ( dmail ). The regression analysis builds a linear relationship between the amount of sales that an average store has in a given year ( sales ) and the spending on the four components of the marketing program. Suppose that the marketing data is stored in the following table: Then you can build the following sales-marketing linear model using coefficients: This model can be implemented as the following view, which refers to the OLS regression object: Using this view, a marketing program manager can perform an analysis such as Is this sales-marketing model reasonable for year 2004 data That is, is the multiple-correlation greater than some acceptable value, say, 0.9 The SQL for such a query might be as follows: You could also solve questions such as What is the expected base-line sales revenue of a store without any marketing programs in 2003 or Which component of the marketing program was the most effective in 2004 That is, a dollar increase in which program produced the greatest expected increase in sales See Oracle Database PLSQL Packages and Types Reference for further information regarding the use of the UTLNLA package and linear algebra. CASE Expressions Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on). The syntax for simple CASE statements is: Simple CASE expressions test if the expr value equals the comparisonexpr . The syntax for searched CASE statements is: You can use any kind of condition in a searched CASE expression, not just an equality test. You can specify only 65,535 arguments and each WHEN. THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the returnexpr itself is a CASE expression. Example 22-26 CASE Suppose you wanted to find the average salary of all employees in the company. If an employees salary is less than 2000, you want the query to use 2000 instead. Without a CASE statement, you might choose to write this query as follows: Note that this runs against the hr sample schema. In this, foo is a function that returns its input if the input is greater than 2000, and returns 2000 otherwise. The query has performance implications because it needs to invoke a function for each row. Writing custom functions can also add to the development load. Using CASE expressions in the database without PLSQL, this query can be rewritten as: Using a CASE expression lets you avoid developing custom functions and can also perform faster. Example 22-27 CASE for Aggregating Independent Subsets Using CASE inside aggregate functions is a convenient way to perform aggregates on multiple subsets of data when a plain GROUP BY will not suffice. For instance, the preceding example could have included multiple AVG columns in its SELECT list, each with its own CASE expression. We might have had a query find the average salary for all employees in the salary ranges 0-2000 and 2000-5000. It would look like: Although this query places the aggregates of independent subsets data into separate columns, by adding a CASE expression to the GROUP BY clause we can display the aggregates as the rows of a single column. The next section shows the flexibility of this approach with two approaches to creating histograms with CASE . Creating Histograms You can use the CASE statement when you want to obtain histograms with user-defined buckets (both in number of buckets and width of each bucket). The following are two examples of histograms created with CASE statements. In the first example, the histogram totals are shown in multiple columns and a single row is returned. In the second example, the histogram is shown with a label column and a single column for totals, and multiple rows are returned. Example 22-28 Histogram Example 1 Example 22-29 Histogram Example 2 Frequent Itemsets Instead of counting how often a given event occurs (for example, how often someone has purchased milk at the grocery), you may find it useful to count how often multiple events occur together (for example, how often someone has purchased both milk and cereal together at the grocery store). You can count these multiple events using what is called a frequent itemset, which is, as the name implies, a set of items. Some examples of itemsets could be all of the products that a given customer purchased in a single trip to the grocery store (commonly called a market basket), the web pages that a user accessed in a single session, or the financial services that a given customer utilizes. The practical motivation for using a frequent itemset is to find those itemsets that occur most often. If you analyze a grocery stores point-of-sale data, you might, for example, discover that milk and bananas are the most commonly bought pair of items. Frequent itemsets have thus been used in business intelligence environments for many years, with the most common one being for market basket analysis in the retail industry. Frequent itemset calculations are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides the following key benefits: Applications that previously relied on frequent itemset operations now benefit from significantly improved performance as well as simpler implementation. SQL-based applications that did not previously use frequent itemsets can now be easily extended to take advantage of this functionality. Frequent itemsets analysis is performed with the PLSQL package DBMSFREQUENTITEMSETS. See Oracle Database PLSQL Packages and Types Reference for more information. In addition, there is an example of frequent itemset usage in Frequent itemsets . Scripting on this page enhances content navigation, but does not change the content in any way.