Cukup sering mereka bertanya apakah ada fungsi analitis (jendela) di MySQL.
Catatan Pada saat penulisan, tidak ada analog seperti itu, tetapi artikel tersebut masih menarik secara akademis dalam hal penguraian pendekatan asli untuk menggunakan variabel untuk MySQL.Untuk mengganti fungsi analitik, kueri yang terhubung sendiri, subkueri kompleks, dan banyak lagi sering digunakan. Sebagian besar solusi ini tidak efektif dalam hal kinerja.
Juga di MySQL tidak ada rekursi. Namun, beberapa tugas yang biasanya diselesaikan dengan fungsi analitik atau rekursi dapat ditangani oleh alat MySQL.
Salah satu alat ini adalah unik, tidak khas untuk mekanisme DBMS lain yang bekerja dengan variabel di dalam kueri SQL. Kami dapat mendeklarasikan variabel di dalam kueri, mengubah nilainya dan menggantinya dalam SELECT untuk output. Selain itu, urutan pemrosesan baris dalam permintaan dan, sebagai hasilnya, urutan pemberian nilai ke variabel dapat diatur dalam penyortiran khusus!
Peringatan Artikel ini mengasumsikan bahwa pemrosesan ekspresi dalam klausa SELECT dilakukan dari kiri ke kanan, namun, tidak ada konfirmasi resmi dari urutan pemrosesan ini dalam dokumentasi MySQL. Ini harus diingat ketika mengubah versi server. Untuk memastikan konsistensi, Anda dapat menggunakan pernyataan KASUS dummy atau IF.
Rekursi Analog
Pertimbangkan contoh sederhana yang menghasilkan urutan Fibonacci (dalam urutan Fibonacci, setiap istilah sama dengan jumlah dari dua sebelumnya, dan 2 pertama sama dengan satu):
SELECT IF(X=1, Fn_1, Fn_2) F FROM( SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2 FROM (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a, (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b, (SELECT @I := 1, @J := 1)IJ )T, (SELECT 1 X UNION ALL SELECT 2)X;
Kueri ini menghasilkan 18 angka Fibonacci, tidak termasuk dua angka pertama:
2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765
Sekarang mari kita lihat cara kerjanya.
Dalam baris 5) 6) 9 catatan dihasilkan. Tidak ada yang aneh di sini.
Pada baris 7) kita mendeklarasikan dua variabel @I, @J dan menetapkannya 1.
Pada baris 3), berikut ini terjadi: pertama, variabel @ saya diberi jumlah dari dua variabel. Kemudian kita menetapkan hal yang sama ke variabel @J, dengan mempertimbangkan fakta bahwa nilai @I sudah berubah.
Dengan kata lain, perhitungan dalam SELECT dilakukan dari kiri ke kanan - lihat juga komentar di awal artikel.
Selain itu, perubahan variabel dilakukan di masing-masing dari 9 catatan kami, yaitu saat memproses setiap baris baru, variabel @I dan @J akan berisi nilai yang dihitung dengan memproses baris sebelumnya.
Untuk mengatasi masalah yang sama dengan bantuan DBMS lainnya, kita harus menulis
kueri rekursif!Catatan:Variabel harus dideklarasikan dalam subquery yang terpisah (baris 7), jika kita mendeklarasikan variabel dalam klausa SELECT, kemungkinan besar akan dievaluasi hanya 1 kali (walaupun perilaku spesifik akan tergantung pada versi server). Jenis variabel ditentukan oleh nilai yang diinisialisasi. Jenis ini dapat berubah secara dinamis. Jika Anda mengatur variabel ke NULL, tipenya adalah BLOB.Urutan di mana baris diproses dalam SELECT, seperti yang disebutkan di atas, tergantung pada penyortiran khusus. Contoh sederhana penomoran baris dalam urutan tertentu:
SELECT val, @I:=@I+1 Num FROM (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a, (SELECT @I := 0)I ORDER BY val;
Val Num 10 1 20 2 30 3 50 4
Analog dari fungsi analitik
Variabel juga dapat digunakan untuk menggantikan fungsi analitik. Berikut ini adalah beberapa contoh. Untuk kesederhanaan, kami menganggap bahwa semua bidang BUKAN NULL, dan pengurutan serta pemartisian (PARTITION BY) terjadi pada satu bidang. Menggunakan nilai NULL dan pengurutan yang lebih kompleks akan membuat contoh lebih rumit, tetapi esensi tidak akan berubah.
Sebagai contoh, buat tabel TestTable:
CREATE TABLE TestTable( group_id INT NOT NULL, order_id INT UNIQUE NOT NULL, value INT NOT NULL );
dimana
group_id - pengidentifikasi grup (analog dari jendela fungsi analitik);
order_id - bidang unik untuk pengurutan;
nilai adalah beberapa nilai numerik.
Isi tabel kami dengan data uji:
INSERT TestTable(order_id, group_id, value) SELECT * FROM( SELECT 1 order_id, 1 group_id, 1 value UNION ALL SELECT 2, 1, 2 UNION ALL SELECT 3, 1, 2 UNION ALL SELECT 4, 2, 1 UNION ALL SELECT 5, 2, 2 UNION ALL SELECT 6, 2, 3 UNION ALL SELECT 7, 3, 1 UNION ALL SELECT 8, 3, 2 UNION ALL SELECT 9, 4, 1 UNION ALL SELECT 11, 3, 2 )T;
Contoh penggantian beberapa fungsi analitik.
1) ROW_NUMBER () LEBIH DARI (ORDER BY order_id)
SELECT T.*, @I:=@I+1 RowNum FROM TestTable T,(SELECT @I:=0)I ORDER BY order_id;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10
2) ROW_NUMBER () LEBIH (PARTITION BY group_id ORDER BY order_id)
SELECT group_id, order_id, value, RowNum FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNum, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1
3) SUM (nilai) LEBIH DARI (PARTISI DENGAN OLEH group_id ORDER OLEH order_id)
SELECT group_id, order_id, value, RunningTotal FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+value, @I:=value) RunningTotal, @last_group_id := group_id FROM TestTable T, (SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1
4) LAG (nilai) LEBIH DARI (PARTISI DENGAN OLEH group_id ORDER OLEH order_id)
SELECT group_id, order_id, value, LAG FROM( SELECT T.*, IF(@last_group_id = group_id, @last_value, NULL) LAG, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL
Untuk LEAD, semuanya sama, hanya Anda perlu mengubah pengurutan ke ORDER BY group_id, order_id DESC
Untuk fungsi COUNT, MIN, MAX, semuanya agak lebih rumit, karena sampai kita menganalisis semua baris dalam grup (jendela), kita tidak akan dapat mengetahui nilai fungsi. MS SQL, misalnya, "gulungan" jendela untuk keperluan ini (sementara menempatkan baris jendela dalam tabel buffer tersembunyi untuk mengaksesnya lagi), di MySQL tidak ada kemungkinan seperti itu. Tetapi kita dapat menghitung nilai fungsi pada setiap jendela pada baris terakhir untuk penyortiran yang diberikan (mis., Setelah menganalisis seluruh jendela), lalu, menyortir baris di jendela dalam urutan terbalik, meletakkan nilai yang dihitung di seluruh jendela.
Jadi kita perlu dua penyortiran. Sehingga pengurutan akhir tetap sama seperti pada contoh di atas, kita pertama-tama mengurutkan berdasarkan field group_id ASC, order_id DESC, kemudian oleh field group_id ASC, order_id ASC.
5) COUNT (*) LEBIH DARI (PARTISI DENGAN OLEH group_id)
Pada urutan pertama, kami cukup memberi nomor pada entri. Dalam yang kedua, kami menetapkan jumlah maksimum untuk semua baris jendela, yang akan sesuai dengan jumlah baris di jendela.
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxRowNum, @MaxRowNum := RowNumDesc) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNumDesc, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxRowNum:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1
Fungsi MAX dan MIN dihitung dengan analogi. Saya hanya akan memberikan contoh untuk MAX:
6) MAX (nilai) LEBIH DARI (PARTISI DENGAN OLEH group_id)
SELECT group_id, order_id, value, MaxVal FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxVal, @MaxVal := MaxVal) MaxVal, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, GREATEST(@MaxVal, value), @MaxVal:=value) MaxVal, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
7) COUNT (nilai DISTINCT) OVER (PARTITION BY group_id)
Suatu hal yang menarik yang tidak tersedia di MS SQL Server, tetapi dapat dihitung dengan subquery dengan mengambil MAX dari RANK. Kami akan melakukan hal yang sama di sini. Pada sort pertama, kami menghitung RANK () OVER (PARTITION BY group_id ORDER BY value DESC), lalu pada sort kedua kami menempatkan nilai maksimum untuk semua baris di setiap jendela:
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @Rank, @Rank := Rank) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, IF(@last_value = value, @Rank, @Rank:=@Rank+1) , @Rank:=1) Rank, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL, @Rank:=0)I ORDER BY group_id, value DESC, order_id DESC )T,(SELECT @last_group_id:=NULL, @Rank:=NULL)I ORDER BY group_id, value, order_id )T;
group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
Performa
Untuk mulai dengan, kami membandingkan kinerja penomoran baris dalam kueri menggunakan gabungan-sendiri dan variabel.
1) Cara klasik dengan self-connect
SELECT COUNT(*)N, T1.* FROM TestTable T1 JOIN TestTable T2 ON T1.order_id >= T2.order_id GROUP BY T1.order_id;
Apa yang dihasilkan oleh 10.000 rekaman dalam tabel yang TestTable:
Durasi / Ambil
16,084 dtk / 0,016 dtk
2) Menggunakan variabel:
SELECT @N:=@N+1 N, T1.* FROM TestTable T1, (SELECT @N := 0)M ORDER BY T1.order_id;
Ini menghasilkan:
Durasi / Ambil
0,016 dtk / 0,015 dtk
Hasilnya berbicara sendiri. Namun, harus dipahami bahwa nilai yang dihitung menggunakan variabel tidak secara optimal digunakan dalam kondisi penyaringan. Penyortiran dan perhitungan akan terjadi untuk SEMUA baris, walaupun pada kenyataannya kita hanya membutuhkan sebagian kecil saja.
Mari kita pertimbangkan secara lebih rinci dengan contoh tugas seperti itu:
Cetak 2 baris pertama dari tabel TestTable untuk setiap nilai group_id, diurutkan berdasarkan order_id.Inilah cara tugas ini akan diselesaikan dalam DBMS dengan dukungan untuk fungsi analitis:
SELECT group_id, order_id, value FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum FROM TestTable )T WHERE RowNum <= 2;
Namun, pengoptimal MySQL tidak tahu apa-apa tentang aturan yang digunakan untuk menghitung bidang RowNum. Dia harus memberi nomor SEMUA baris, dan hanya kemudian pilih yang diperlukan.
Sekarang bayangkan kita memiliki 1 juta catatan dan 20 nilai group_id unik. Yaitu untuk memilih 40 baris, MySQL akan menghitung nilai RowNum untuk satu juta baris! Tidak ada solusi bagus untuk masalah ini dengan satu permintaan di MySQL. Tapi Anda bisa mendapatkan daftar nilai group_id yang unik, misalnya, seperti ini:
SELECT DISTINCT group_id FROM TestTable;
Kemudian, menggunakan bahasa pemrograman lain apa pun, buat kueri formulir:
SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2 UNION ALL SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2 UNION ALL … SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2;
20 kueri mudah akan bekerja lebih cepat daripada menghitung RowNum untuk sejuta baris.