Fungsi Lambda dalam SQL ... mari kita pikirkan

Tentang apa artikel itu, dan demikian namanya.

Selain itu, penulis akan menjelaskan mengapa ini perlu dari sudut pandangnya, serta memberi tahu bahwa SUBJ bukan hanya teknologi yang modis, tetapi juga "bisnis yang perlu dua kali lipat - menyenangkan dan bermanfaat."

Itu selalu menarik untuk melihat bagaimana beberapa orang berbakat melakukan sesuatu (bahasa pemrograman, mengapa tidak), tahu persis masalah apa yang mereka pecahkan dan tugas apa yang mereka tetapkan untuk diri mereka sendiri. Dan juga menguji kreasi mereka pada diri mereka sendiri. Itu tidak bisa dibandingkan dengan kreasi monumental komite raksasa, yang menempatkan pemeliharaan harmoni alam semesta di garis depan, dan siapa yang memahaminya.

Bandingkan, misalnya, nasib FORTRAN dan PL / 1 . Siapa yang sekarang akan ingat tentang PL / 1 ini.

Dari sudut pandang ini, AWK , misalnya, sangat sukses. Patut dikatakan bahwa dalam namanya A adalah Alfred Aho , salah satu penulis Dragon Book , W adalah Peter Weinberger , yang memiliki andil di Fortran-77, K adalah Brian Kernigan , di mana ia akan tanpanya. Bahasa ini dimaksudkan untuk memproses aliran teks on-the-fly dalam pipa di antara proses.

Bahasa ini tidak bertuliskan ( ini tidak sepenuhnya benar ), sintaksisasinya sangat mirip dengan C, ia memiliki kemampuan penyaringan, susunan asosiatif, peristiwa mulai / akhir streaming, acara baris baru ...

Penulis selalu terkesan dengan bahasa ini juga oleh kenyataan bahwa penerjemahnya tidak perlu diinstal, di bawah sistem seperti UNIX selalu ada, dan di bawah Windows itu cukup hanya untuk menyalin file yang dapat dieksekusi dan semuanya berfungsi. Namun, ini bukan masalahnya.

Dalam prosesnya, penulis harus menggunakan bundel SQL + AWK cukup sering, dan itulah sebabnya. SQL masih merupakan bahasa yang awalnya bersifat deklaratif yang dirancang untuk mengontrol aliran data. Ini memberikan peluang yang sangat terbatas untuk bekerja dengan konteks eksekusi permintaan dalam bentuk fungsi agregat.

Bagaimana, misalnya, untuk membangun histogram dua dimensi menggunakan SQL?

--   100 x 100 SELECT count(), round(x, -2) AS cx, round(y, -2) AS cy FROM samples GROUP BY cx, xy 

Tapi katakanlah, menggunakan GROUP BY menyiratkan penyortiran, dan itu bukan kesenangan murah jika Anda memiliki ratusan juta (atau bahkan lebih) baris.
Ringkasan: Prosesor SQL resor untuk menyortir ketika tidak dapat memperkirakan jumlah grup setelah GROUP BY. Namun, pengelompokan berdasarkan nilai yang dihitung (seringkali) adalah kasus yang sebenarnya.

Karena itu, Anda harus melakukan sesuatu seperti:

 psql -t -q -c 'select x, y from samples' | gawk -f mk_hist2d.awk 

di mana mk_hist2d.awk mengakumulasi statistik dalam array asosiatif dan menampilkannya setelah selesai bekerja

 # mk_hist2d.awk { bucket[int($2*0.01), int($3*0.01)]+=$1; } END { for (i=0; i < 500; i++) for (j=0; j < 500; j++) { if ((i, j) in bucket) print i*100." "j*100." "bucket[i, j]; else print i*100." "j*100." 0"; } } 

Ada satu TETAPI - aliran data lengkap harus dikirim dari server ke mesin yang bekerja, dan ini tidak begitu murah.

Apakah mungkin untuk entah bagaimana menggabungkan yang menyenangkan dengan yang berguna - untuk mengumpulkan statistik selama pelaksanaan query SQL, tetapi tanpa menggunakan penyortiran? Ya, misalnya, menggunakan fungsi agregat khusus.

Fungsi Agregat Khusus

Subj hadir dalam sistem yang berbeda, di mana pun ia dilakukan sedikit dengan caranya sendiri.

1. PostgreSQL Dokumentasinya ada di sini . Lebih detail di sini .
   Di sinilah saldo akun maksimum dihitung.
   Dan ini adalah contoh yang menghitung apa yang lebih dalam kolom boolean - benar atau salah.
   
   Sepertinya ini -
   
   

    CREATE AGGREGATE mode(boolean) ( SFUNC = mode_bool_state, STYPE = INT[], FINALFUNC = mode_bool_final, INITCOND = '{0,0}' ); 

   
   Di sini SFUNC adalah fungsi yang dipanggil untuk setiap baris dalam aliran,
   argumen pertama di dalamnya adalah tipe STYPE .
   
   FINALFUNC digunakan untuk menyelesaikan perhitungan dan mengembalikan nilai agregat.
   INITCOND - inisialisasi nilai awal status internal ( STYPE ), disahkan sebagai argumen pertama.
   Mengingat bahwa fungsi dapat ditulis dalam C (yang berarti bahwa untuk keadaan internal Anda dapat menggunakan memori yang secara otomatis dibebaskan ketika Anda menutup permintaan), ini adalah alat yang sangat kuat. Di luar ruang lingkup penggunaannya, seseorang masih harus bisa pergi.
2. MS SQL
   Sebelumnya (2000), sebelum permintaan, perlu membuat objek ActiveX, untuk melakukan agregasi menggunakan objek ini.
   Sekarang (2016+) ini dilakukan di lingkungan CLR. Anda harus membuat fungsi kustom, membuat dan mendaftarkan sebuah majelis . Kemudian Anda bisa membuat agregat .
   Contoh menghitung rata-rata geometrik, serta menggabungkan string: dengan parameter tambahan dan tipe yang ditentukan pengguna untuk menyimpan keadaan perantara.
3. Oracle
   Di Oracle, ini dilakukan dengan menggunakan ODCIAggregate Data Cartridge (antarmuka).
   Untuk membuat agregat Anda sendiri, Anda perlu menulis jenis khusus yang mengimplementasikan 4 metode
   - inisialisasi (ODCIAggregateInitialize), statis, harus membuat turunan dari tipe yang diinginkan dan kembali melalui parameter
   - iterations (ODCIAggregateIterate), dipanggil pada setiap baris data
   - merge (ODCIAggregateMerge), digunakan untuk menggabungkan agregat yang dieksekusi secara paralel
   - selesai (ODCIAggregateTerminate) - hasil keluaran
   Contoh: 1 , 2 , 3 , 4 .
4. DB2
   Tidak ada cara eksplisit untuk menggunakan agregat kustom di DB2.
   Tapi Anda bisa memasukkan fungsi standar (meskipun MAX) ke tipe yang ditentukan pengguna (di Jawa) dan membuat sistem mengeksekusi kueri formulir
   
   

    CREATE TYPE Complex AS ( real DOUBLE, i DOUBLE ) … CREATE TABLE complexNumbers ( id INTEGER NOT NULL PRIMARY KEY, number Complex ) … SELECT sum..real, sum..i FROM ( SELECT GetAggrResult(MAX(BuildComplexSum(number))) FROM complexNumbers ) AS t(sum) 

Apa yang patut diperhatikan dalam semua sistem ini?

• Dengan satu atau lain cara, Anda perlu membuat beberapa objek dalam database. Baik itu AGREGAT atau JENIS. Minimal, diperlukan hak yang sesuai. Dan hanya ingin menambahkan beberapa angka pada lututnya.
  
• Anda mungkin harus menulis sesuatu dalam bahasa lain, apakah itu C, C # atau Java.
  Untuk mengintegrasikan apa yang ditulis ke dalam sistem, sekali lagi, diperlukan hak. Tapi yang saya inginkan ...
  
• Inisialisasi kesulitan. Misalkan Anda ingin membaca histogram dengan ukuran keranjang yang berbeda. Tampaknya lebih mudah - kami akan menunjukkan INITCOND yang diinginkan ketika mendeklarasikan agregat (PostgreSQL) dan seluruh bisnis. Tetapi kemudian untuk setiap ukuran keranjang Anda akan membutuhkan agregat Anda sendiri, dan untuk ini lagi diperlukan hak.
  
  Di sini Anda dapat menggunakan trik kotor dan menyelipkan prosesor gabungan dari garis inisialisasi (maju) dan data, membangun konteks bukan di konstruktor, tetapi ketika baris pertama diterima.
  
• Namun demikian, bahkan dengan batasan yang dijelaskan, agregat khusus memungkinkan Anda menghitung apa pun.
  
• Adalah penting bahwa agregat dapat diparalelkan , setidaknya PostgreSQL, dan Oracle (Enterprise Edition) dapat melakukan ini. Untuk ini, kebenaran harus belajar bagaimana membuat serial / deserialize negara perantara dan juga membekukan mereka yang diterima dari aliran yang berbeda.

Fungsi jendela

Fungsi jendela muncul dalam standar SQL: 2003 . Saat ini, mereka didukung oleh semua sistem di atas. Intinya, fungsi jendela adalah perpanjangan dari kerja dengan unit. Dan, tentu saja, fungsi agregat kustom juga berfungsi dalam konteks berjendela.

Perpanjangannya adalah ini. Dan sebelum SQL: 2003, fungsi agregat bekerja di jendela tertentu, yang merupakan seluruh resultset atau bagiannya, yang sesuai dengan kombinasi nilai bidang dari ekspresi GROUP BY. Pengguna sekarang memiliki kebebasan dalam memanipulasi jendela ini.

Perbedaannya adalah bahwa nilai-nilai yang dihitung menggunakan windows ditambahkan ke output dalam kolom terpisah, dan tidak memerlukan seluruh aliran untuk runtuh menggunakan fungsi agregat. Jadi dalam satu permintaan, Anda dapat menggunakan beberapa agregat jendela masing-masing dalam konteksnya sendiri (jendela). Mungkin ada beberapa fungsi agregat sebelumnya, tetapi mereka semua bekerja dalam satu jendela.

Stroke besar

• Over ()
  jendela adalah seluruh resultset. Katakanlah kueri ' pilih hitungan (1) dari Sampel ' mengembalikan 169. Dalam kasus ini, menjalankan ' pilih menghitung (1) lebih dari () dari Sampel ', kita mendapatkan kolom yang ditulis 169 kali 169 kali.
• LEBIH DARI (PARTISI DENGAN)
  ini adalah analog dari GROUP BY, untuk setiap kombinasi nilai, sebuah jendela dibuat di mana fungsi agregat dilakukan. Katakanlah dalam tabel Sampel satu kolom bilangan bulat adalah val, data adalah angka dari 1 hingga 169.
  Kemudian permintaan ' pilih hitungan (1) lebih (partisi dengan (12 + val) / 13) dari Sampel ' akan mengembalikan kolom di mana nilai 13 ditulis 169 kali.
  
• OVER (ORDER BY)
  dapat dikombinasikan dengan PARTITION BY, memungkinkan Anda untuk secara dinamis mengubah ukuran jendela selama kursor, dalam hal ini, jendela memanjang dari awal grup ke posisi kursor saat ini. Akibatnya, untuk grup, ternyata bukan nilai yang sama di kolom agregat, tetapi nilai sendiri. Nyaman untuk menghitung jumlah kumulatif. Hasil kueri
  'pilih penjumlahan (val) dari (urutan dengan val) dari Sampel ' akan menjadi kolom di mana elemen ke-n akan berisi jumlah bilangan asli dari 1 ke n.
• LEBIH (ROWS)
  memungkinkan Anda untuk menentukan bingkai jendela, mulai dari posisi kursor atau awal / akhir kisaran ORDER BY.
  
  Sebagai contoh, ' ... ROWS 1 PRECEDING ... ' berarti bahwa jendela terdiri dari baris saat ini dan 1 sebelumnya. A ' ... ROWS ANTARA 1 BERIKUT DAN 2 MENGIKUTI ... ' - jendela terdiri dari dua baris segera setelah kursor.
  
  CURRENT ROW dalam mode ini menunjukkan posisi kursor saat ini. Misalnya, ' BARIS ANTARA BARIS SAAT INI DAN BERIKUTNYA BERIKUT ' berarti dari garis saat ini ke akhir rentang.
• LEBIH (RANGE)
  berbeda dari ROWS dalam ROW SAAT ini di sini berarti sebagai awal jendela awal rentang dari ORDER BY, dan sebagai akhir jendela - baris terakhir dari rentang ORDER BY.

Sintaks untuk menggunakan fungsi jendela pada sistem yang berbeda sedikit berbeda.

Untuk meringkas hal di atas, masih ada perasaan yang agak menyakitkan bahwa para pengembang, setelah menganalisis pembangunan berbagai laporan dalam SQL, menyoroti kasus-kasus yang paling umum dan secara konkret mereka dalam sintaksis.

Fungsi Pengembalian Rekam

Dalam output fungsi agregat / jendela, setiap baris yang dihasilkan sesuai dengan rentang baris tertentu dari aliran data yang masuk. Dalam hidup, korespondensi semacam itu tidak selalu ada.

Sebagai contoh, diperlukan untuk membangun matriks kovarians 10X10 (untuk ini, dibutuhkan 672X672). Ini dapat dilakukan dalam satu pass, untuk ini kami menjalankan fungsi agregat yang ditulis oleh kami dengan 10 parameter numerik. Hasil karyanya adalah recordset 10 baris dari 10 nilai, setiap elemen matriks mengacu pada semua baris aliran input (tidak peduli berapa banyak ada).

Kita dapat mengatakan - jadi apa, di PostgreSQl, misalnya, Anda dapat mengembalikan array dua dimensi dari suatu fungsi (Contoh: 'ARRAY [[1,2], [3,4]')). Atau hanya membuat serialisasi matriks menjadi satu baris.

Itu bagus, tetapi tidak selalu memungkinkan untuk menjaga ukuran hasil dalam kerangka kerja yang dapat diterima untuk pendekatan semacam itu.


Matriks kovarians masih bukan contoh yang sangat baik dari sinkronisasi antara aliran input dan output, karena seluruh hasil diperoleh secara bersamaan di akhir. Misalkan Anda ingin memproses / mengompres aliran data sumber. Pada saat bersamaan

• ada banyak data, mereka berada di "tumpukan" tanpa indeks, sebenarnya mereka hanya 'cepat' ditulis ke disk
• Anda perlu mengurutkannya ke dalam kategori yang berbeda, yang relatif sedikit
• dalam kategori, rata-rata dari interval waktu, hanya menyimpan rata-rata, jumlah pengukuran dan varians
• semua ini perlu dilakukan dengan cepat

Apa saja pilihannya?

1. Dalam SQL, pengurutan berdasarkan interval waktu / kategori diperlukan, yang bertentangan dengan poin terakhir.
2. Jika data sudah diurutkan berdasarkan waktu (yang, pada kenyataannya, tidak dijamin), dan akan dimungkinkan untuk menyampaikan fakta ini ke prosesor SQL, Anda dapat melakukannya dengan fungsi jendela dan satu lintasan.
3. Tulis aplikasi terpisah yang akan melakukan semua ini. Dalam PL / SQL atau, lebih mungkin, mengingat bahwa ada banyak data, di C / C ++.
4. Fungsi yang mengembalikan catatan. Mungkin mereka bisa membantu kita.

Lebih detail tentang A.4. Ada dua mekanisme untuk ini - tabel sementara dan fungsi pipa.

1. Fungsi konveyor.
   Mekanisme ini muncul di Oracle (mulai dari 9i, 2001) dan memungkinkan fungsi yang mengembalikan recordset bukan untuk mengumpulkan data, tetapi menghitungnya sesuai kebutuhan (secara analogi dengan sinkronisasi stdout dan stdin dari dua proses yang terhubung melalui pipa).
   Yaitu Hasil fungsi-fungsi pipelined mungkin mulai diproses sebelum keluar dari fungsi ini. Untuk ini, cukup mengatakan dalam definisi fungsi
   
   

     FUNCTION f_trans(p refcur_t) RETURN outrecset PIPELINED IS … 

   
   dan daftarkan garis hasil di badan
   
   

    LOOP … out_rec.var_char1 := in_rec.email; out_rec.var_char2 := in_rec.phone_number; PIPE ROW(out_rec); … END LOOP; 

   
   Hasilnya, kita punya
   
   

    SELECT * FROM TABLE( refcur_pkg.f_trans( CURSOR(SELECT * FROM employees WHERE department_id = 60))); 

   
   Agregat khusus tidak diperlukan saat ada fungsi pipa.
   
   Bravo, Oracle!
   
   Belum lama ini (2014), fungsi pipa juga muncul di DB2 (IBM i 7.1 TR9, i 7.2 TR1).
2. Meja sementara.
   Untuk memulainya, tampaknya baik MS SQL maupun PostgreSQL tidak dapat mengembalikan kursor dari fungsi agregat.
   
   Baiklah, mari, dengan analogi dengan fungsi-fungsi pipa, dapatkan kursor sebagai parameter, proses itu, tambahkan ke tabel sementara dan kembalikan kursor ke sana.
   
   Namun, dalam MS SQL tidak mungkin untuk melewatkan kursor ke prosedur tersimpan oleh parameter, hanya mungkin untuk membuat kursor dalam prosedur dan mengembalikan parameter melalui output. Hal yang sama dapat dikatakan untuk PostgreSQL.
   
   Baiklah, buka saja kursor, kurangi, proses nilainya, hitung hasilnya, tambahkan ke tabel sementara dan render kursornya.
   
   Atau bahkan lebih sederhana, kami menambahkan hasil kueri ke satu tabel sementara, memprosesnya dan mengembalikan hasilnya melalui kursor ke tabel sementara lainnya.
   
   Apa yang bisa saya katakan. Pertama, dan yang paling penting, membaca data melalui kursor lebih lambat daripada memproses dalam aliran. Kedua, mengapa Anda memerlukan prosesor SQL sama sekali, mari kita baca tabel dengan kursor, buat tabel sementara dengan tangan kami, tulis logika join di loop ... Ini seperti insert assembler di C / C ++, kadang-kadang Anda bisa memperlakukan diri sendiri, tetapi lebih baik tidak menyalahgunakannya.

Jadi, setelah mempertimbangkan pertanyaan dengan fungsi mengembalikan recordset, kami sampai pada kesimpulan:

• Agregat khusus tidak akan sangat membantu kami di sini.
• Bagaimanapun, Anda perlu membuat beberapa objek dalam database. Baik itu fungsi atau tabel sementara. Minimal, diperlukan hak yang sesuai. Dan hanya ingin memproses beberapa angka.
  
• Namun demikian, bahkan dengan batasan yang dijelaskan, terkadang tidak terlalu elegan, tetapi dengan metode ini Anda dapat menyelesaikan masalah.

Apa lagi

Padahal, jika kita sudah memiliki kesempatan untuk menyelesaikan masalah, apa lagi yang dibutuhkan penulis?
Sebenarnya, mesin Turing juga dapat menghitung apa saja, hanya saja tidak terlalu cepat dan tidak terlalu nyaman.

Kami merumuskan persyaratan sebagai berikut:

1. itu harus operator relasional yang dapat digunakan setara dengan yang lainnya (seleksi, proyeksi, ...)
2. itu harus menjadi operator yang mengubah satu aliran data menjadi yang lain
3. tidak ada sinkronisasi antara aliran input dan output
4. deklarasi operator mendefinisikan struktur aliran output
5. operator memiliki kemampuan untuk menginisialisasi secara dinamis (dalam bentuk fungsi, lebih tepatnya tubuhnya, ditentukan langsung dalam definisi operator)
6. serta destruktor dalam bentuk fungsi (...)
7. serta fungsi (...) yang dipanggil setiap kali baris baru diterima dari aliran input
8. operator memiliki konteks eksekusi - seperangkat variabel dan / atau koleksi yang ditentukan pengguna yang diperlukan untuk pekerjaan
9. untuk menjalankan operator ini, Anda tidak perlu membuat objek database, Anda tidak perlu hak tambahan
10. segala sesuatu yang diperlukan untuk pekerjaan didefinisikan di satu tempat, dalam satu bahasa

Sekali waktu, penulis membuat operator yang memperluas prosesor buatan sendiri dari subset TTM / Tutorial D. Sekarang ide yang sama sedang diusulkan untuk SQL.

Perlu peringatan, di sini SQL berakhir dan improvisasi dimulai. Sintaksinya dibiarkan seperti aslinya, pada akhirnya, gula sintaksis bisa apa saja, tidak mengubah esensinya.

Jadi, operator mengunyah terdiri dari

1. Header yang berisi daftar bidang output dan tipenya.
   Setiap bidang output (dan input) adalah variabel lokal.
   Contoh: "chew {" var1 "float," var2 "integer}" berarti bahwa akan ada dua kolom dalam aliran output - titik mengambang dan bilangan bulat
2. Tubuh - daftar panggilan balik untuk acara, saat ini - awal aliran, akhir aliran, baris. Dengan sintaks, fungsinya dekat dengan PL / SQL. Fungsi yang telah ditetapkan __interrupt () adalah analog dari PIPE, ia mengambil nilai dari variabel yang sesuai dengan kolom output dan menempatkannya dalam aliran output. Jika buffer aliran output meluap, pekerjaan pawang akan berhenti dan pekerjaan sisi penerima aliran akan dimulai.
   Mis: “hook“ init ”{var1: = 0; var2: = -1; } "

Cara termudah untuk menunjukkan contoh.

• Analog dari fungsi agregat SUM.
  
  

   --  'select sum(val) from samples' -- select * from samples chew {“sum(val)” float} --    hook “init” { “sum(val)” := 0; --      } hook “row” { if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; end if; } hook “finit” { call __interrupt(); --  PIPE } 

  
  Terlihat tebal, tapi itu hanya contoh,
  tidak perlu menulis program C untuk menambahkan beberapa angka.
• SUM + AVG
  
  

   --  'select sum(val), avg(val) from samples' -- select * from samples chew { “sum(val)” float, “avg(val)” float --       } hook “init” { “sum(val)” := 0; “avg(val)” := 0; var num integer; num := 0; --    ,       } hook “row” { if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; num := num + 1; end if; } hook “finit” { if (num > 0) then “avg(val)” := “sum(val)” / num; end if; call __interrupt(); } 

  
  Di sini kita menarik perhatian pada fakta bahwa penjumlahan hanya terjadi satu kali.
• SUM + GROUP BY
  
  

   --  'select sum(val) from samples group by type' -- select * from --     ( samples val, type from samples order by type ) chew { “sum(val)” float } hook “init” { “sum(val)” := 0; var gtype integer; gtype := NULL; var num integer; --   num := 0; } hook “row” { if (gtype <> “type”) then __interrupt(); “gtype” := type; "sum(val)" := 0; num := 0; end if; if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; num := num + 1; end if; } hook “finit” { if (num > 0) then call __interrupt(); end if; } 

• ROW_NUMBER () OVER ()
  
  

   -- select row_number() over() as num, * from samples -- select * from samples chew { “num” integer, * --        --   '* except val1, ...valX',   TTM } hook “init” { num := 0; } hook “row” { num := num + 1; call __interrupt(); } 

Apakah mungkin untuk memberikan contoh di mana pendekatan ini memberikan hasil yang secara fundamental tidak dapat dicapai dengan cara yang biasa? Kami memilikinya.

Kadang-kadang terjadi bahwa data hampir diurutkan. Mereka bahkan mungkin sepenuhnya diurutkan, tetapi tidak diketahui pasti.

Misalkan dalam contoh di atas (kompresi aliran data) data berasal dari sumber yang berbeda dan karena berbagai alasan dapat sedikit dicampur. Yaitu T1 T2 T1 < T2.

, T1 T2 () , ( ) .

, , , , .

.

, .

.

, .
, .

SQL- .

lambda- SQL- , , .

Kesimpulan

.

PL/SQL.

.

, , GROUP BY.

, , SQL- .

, , .

PS: .