Beberapa waktu lalu, saya ditugasi menulis prosedur yang melakukan penipisan kutipan pasar Forex (lebih tepatnya, data kerangka waktu).
Pernyataan masalah: data dimasukkan pada selang waktu 1 detik dalam format ini:
- Nama instrumen (kode pasangan USDEUR, dll.),
- Tanggal dan waktu dalam format waktu unix,
- Nilai terbuka (harga transaksi pertama dalam interval),
- Nilai tinggi (harga maksimum),
- Nilai rendah
- Nilai tutup (harga kesepakatan terakhir),
- Volume (volume, atau volume transaksi).
Hal ini diperlukan untuk memastikan perhitungan ulang dan sinkronisasi data dalam tabel: 5 detik, 15 detik, 1 menit, 5 menit, 15 menit, dll.
Format penyimpanan data yang dijelaskan disebut OHLC, atau OHLCV (Terbuka, Tinggi, Rendah, Tutup, Volume). Sering digunakan, Anda dapat langsung membangun grafik "lilin Jepang" di atasnya.
Di bawah potongan, saya menggambarkan semua opsi yang bisa saya buat, bagaimana cara menipiskan (memperbesar) data yang diterima, untuk analisis, misalnya, lompatan musim dingin dalam harga bitcoin, dan menurut data yang diterima, Anda akan segera membangun bagan "Lilin Jepang" (dalam MS Excel ada juga bagan seperti itu) ) Pada gambar di atas, bagan ini dibuat untuk kerangka waktu “1 bulan”, untuk alat “bitstampUSD”. Tubuh putih lilin menunjukkan kenaikan harga dalam interval, hitam - penurunan harga, sumbu atas dan bawah menunjukkan harga maksimum dan minimum yang dicapai dalam interval. Latar belakang - volume transaksi. Jelas terlihat bahwa pada bulan Desember 2017 harga mendekati tanda 20K.
Solusi akan diberikan untuk dua mesin basis data, untuk Oracle dan MS SQL, yang, dalam beberapa cara, akan memungkinkan untuk membandingkannya untuk tugas khusus ini (kami tidak akan menggeneralisasi perbandingan dengan tugas lain).
Kemudian saya memecahkan masalah dengan cara sepele: menghitung penipisan yang benar dalam tabel sementara dan menyinkronkan dengan tabel target - menghapus baris yang ada di tabel target tetapi tidak ada di tabel sementara dan menambahkan baris yang ada di tabel sementara tetapi tidak ada di target. Saat itu, pelanggan puas solusinya, dan saya menutup tugas.
Tetapi sekarang saya memutuskan untuk mempertimbangkan semua opsi, karena solusi di atas mengandung satu fitur - sulit untuk mengoptimalkan dua kasus sekaligus:
- ketika tabel target kosong dan Anda perlu menambahkan banyak data,
- dan ketika tabel target besar, dan Anda perlu menambahkan data dalam potongan kecil.
Ini disebabkan oleh fakta bahwa dalam prosedur Anda harus menghubungkan tabel target dan tabel sementara, dan Anda harus melampirkan tabel yang lebih besar, dan bukan sebaliknya. Dalam dua kasus di atas, yang lebih besar / lebih kecil dipertukarkan. Pengoptimal akan memutuskan urutan koneksi berdasarkan statistik, dan statistik mungkin kedaluwarsa, dan keputusan mungkin dibuat secara tidak benar, yang akan menyebabkan penurunan kinerja yang signifikan.
Pada artikel ini, saya akan menjelaskan metode penipisan satu kali yang mungkin berguna bagi pembaca untuk analisis, misalnya, lompatan musim dingin dalam harga bitcoin.
Prosedur penipisan daring dapat diunduh dari github di tautan di bagian bawah artikel.
Pada intinya ... Tugas saya menipis dari kerangka waktu "1 detik" ke yang berikutnya, tetapi di sini saya sedang mempertimbangkan penipisan dari tingkat transaksi (dalam tabel sumber, bidang STOCK_NAME, UT, ID, APRICE, AVOLUME). Karena data tersebut dikeluarkan oleh bitcoincharts.com.
Sebenarnya, penipisan dari tingkat transaksi ke tingkat "1 detik" dilakukan oleh perintah seperti itu (operator mudah diterjemahkan ke dalam penipisan dari tingkat "1 detik" ke tingkat atas):
Di Oracle:select 1 as STRIPE_ID , STOCK_NAME , TRUNC_UT (UT, 1) as UT , avg (APRICE) keep (dense_rank first order by UT, ID) as AOPEN , max (APRICE) as AHIGH , min (APRICE) as ALOW , avg (APRICE) keep (dense_rank last order by UT, ID) as ACLOSE , sum (AVOLUME) as AVOLUME , sum (APRICE * AVOLUME) as AAMOUNT , count (*) as ACOUNT from TRANSACTIONS_RAW group by STOCK_NAME, TRUNC_UT (UT, 1);
Fungsi
avg () keep (dense_rank urutan pertama oleh UT, ID) berfungsi seperti ini: karena permintaannya adalah GROUP BY, setiap grup dihitung secara independen dari yang lain. Di dalam setiap grup, string diurutkan berdasarkan UT dan ID, diberi nomor oleh
dense_rank . Karena fungsi pertama mengikuti, baris dipilih di mana
dense_rank mengembalikan 1 (dengan kata lain, minimum dipilih) - transaksi pertama dalam interval dipilih. Untuk UT minimum ini, ID, jika ada beberapa baris, rata-rata akan dipertimbangkan. Tetapi dalam kasus kami akan dijamin satu baris (karena keunikan ID), sehingga nilai yang dihasilkan segera dikembalikan sebagai AOPEN. Sangat mudah untuk memperhatikan bahwa fungsi
pertama menggantikan dua yang agregat.
Pada MS SQLTidak ada fungsi
pertama / terakhir (ada
first_value / last_value , tetapi bukan itu). Karena itu, Anda harus menghubungkan tabel itu sendiri.
Saya tidak akan memberikan permintaan secara terpisah, tetapi Anda dapat melihatnya di bawah di
dbo.THINNING_HABR_CALC prosedur. Tentu saja, tanpa yang
pertama / terakhir itu tidak begitu elegan, tetapi itu akan berhasil.
Bagaimana masalah ini dapat diselesaikan oleh satu operator? (Di sini, istilah "satu operator" tidak berarti bahwa operator akan menjadi satu, tetapi bahwa tidak akan ada siklus yang "menarik" data pada satu baris.)
Saya akan mencantumkan semua opsi yang saya tahu untuk menyelesaikan masalah ini:
- SIMP (sederhana, sederhana, produk Cartesian),
- CALC (menghitung, penipisan berulang dari tingkat atas),
- CHIN (cara china, permintaan besar untuk semua level sekaligus),
- UDAF (fungsi agregat yang ditentukan pengguna),
- PPTF (fungsi tabel pipelined dan paralel, solusi prosedural, tetapi dengan hanya dua kursor, pada kenyataannya, dua pernyataan SQL),
- MODE (model, frase MODEL),
- dan IDEA (ideal, solusi ideal yang mungkin tidak berfungsi sekarang).
Ke depan, saya akan mengatakan bahwa ini adalah kasus yang jarang terjadi ketika solusi PPTF prosedural adalah yang paling efektif di Oracle.
Unduh file transaksi dari
http://api.bitcoincharts.com/v1/csvSaya sarankan memilih file kraken *. File-file localbtc * sangat berisik - mengandung baris-baris yang mengganggu dengan harga yang tidak realistis. Semua kraken * berisi sekitar 31 juta transaksi, saya sarankan tidak termasuk krakenEUR dari sana, maka transaksi menjadi 11 juta. Ini adalah volume paling nyaman untuk pengujian.
Jalankan skrip di Powershell untuk menghasilkan file kontrol untuk SQLLDR untuk Oracle dan untuk menghasilkan permintaan impor untuk MSSQL.
# MODIFY PARAMETERS THERE $OracleConnectString = "THINNING/aaa@P-ORA11/ORCL" # For Oracle $PathToCSV = "Z:\10" # without trailing slash $filenames = Get-ChildItem -name *.csv Remove-Item *.ctl -ErrorAction SilentlyContinue Remove-Item *.log -ErrorAction SilentlyContinue Remove-Item *.bad -ErrorAction SilentlyContinue Remove-Item *.dsc -ErrorAction SilentlyContinue Remove-Item LoadData-Oracle.bat -ErrorAction SilentlyContinue Remove-Item LoadData-MSSQL.sql -ErrorAction SilentlyContinue ForEach ($FilenameExt in $Filenames) { Write-Host "Processing file: "$FilenameExt $StockName = $FilenameExt.substring(1, $FilenameExt.Length-5) $FilenameCtl = '.'+$Stockname+'.ctl' Add-Content -Path $FilenameCtl -Value "OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, ROWS=1000000, SKIP_INDEX_MAINTENANCE=Y)" Add-Content -Path $FilenameCtl -Value "UNRECOVERABLE" Add-Content -Path $FilenameCtl -Value "LOAD DATA" Add-Content -Path $FilenameCtl -Value "INFILE '.$StockName.csv'" Add-Content -Path $FilenameCtl -Value "BADFILE '.$StockName.bad'" Add-Content -Path $FilenameCtl -Value "DISCARDFILE '.$StockName.dsc'" Add-Content -Path $FilenameCtl -Value "INTO TABLE TRANSACTIONS_RAW" Add-Content -Path $FilenameCtl -Value "APPEND" Add-Content -Path $FilenameCtl -Value "FIELDS TERMINATED BY ','" Add-Content -Path $FilenameCtl -Value "(ID SEQUENCE (0), STOCK_NAME constant '$StockName', UT, APRICE, AVOLUME)" Add-Content -Path LoadData-Oracle.bat -Value "sqlldr $OracleConnectString control=$FilenameCtl" Add-Content -Path LoadData-MSSQL.sql -Value "insert into TRANSACTIONS_RAW (STOCK_NAME, UT, APRICE, AVOLUME)" Add-Content -Path LoadData-MSSQL.sql -Value "select '$StockName' as STOCK_NAME, UT, APRICE, AVOLUME" Add-Content -Path LoadData-MSSQL.sql -Value "from openrowset (bulk '$PathToCSV\$FilenameExt', formatfile = '$PathToCSV\format_mssql.bcp') as T1;" Add-Content -Path LoadData-MSSQL.sql -Value "" }
Mari kita buat tabel transaksi pada Oracle.
create table TRANSACTIONS_RAW ( ID number not null , STOCK_NAME varchar2 (32) , UT number not null , APRICE number not null , AVOLUME number not null) pctfree 0 parallel 4 nologging;
Di Oracle, jalankan file
LoadData-Oracle.bat , setelah sebelumnya
memperbaiki parameter koneksi di awal skrip Powershell.
Saya bekerja di mesin virtual. Mengunduh semua file transaksi 11M dalam file 8 kraken * (saya melewatkan file EUR) membutuhkan waktu sekitar 1 menit.
Dan membuat fungsi yang akan memotong tanggal ke batas interval:
create or replace function TRUNC_UT (p_UT number, p_StripeTypeId number) return number deterministic is begin return case p_StripeTypeId when 1 then trunc (p_UT / 1) * 1 when 2 then trunc (p_UT / 10) * 10 when 3 then trunc (p_UT / 60) * 60 when 4 then trunc (p_UT / 600) * 600 when 5 then trunc (p_UT / 3600) * 3600 when 6 then trunc (p_UT / ( 4 * 3600)) * ( 4 * 3600) when 7 then trunc (p_UT / (24 * 3600)) * (24 * 3600) when 8 then trunc ((trunc (date '1970-01-01' + p_UT / 86400, 'Month') - date '1970-01-01') * 86400) when 9 then trunc ((trunc (date '1970-01-01' + p_UT / 86400, 'year') - date '1970-01-01') * 86400) when 10 then 0 when 11 then 0 end; end; create or replace function UT2DATESTR (p_UT number) return varchar2 deterministic is begin return to_char (date '1970-01-01' + p_UT / 86400, 'YYYY.MM.DD HH24:MI:SS'); end;
Pertimbangkan opsinya. Pertama, kode semua opsi diberikan, lalu skrip untuk peluncuran dan pengujian. Pertama, tugas dijelaskan untuk Oracle, kemudian untuk MS SQL
Opsi 1 - SIMP (Sepele)
Seluruh rangkaian transaksi dikalikan dengan produk Cartesian dengan satu set 10 garis dengan angka dari 1 hingga 10. Ini diperlukan untuk mendapatkan 10 garis dari satu garis transaksi dengan tanggal yang dipotong pada batas 10 interval.
Setelah itu, garis dikelompokkan berdasarkan nomor interval dan tanggal terpotong, dan permintaan di atas dijalankan.
Buat LIHAT:
create or replace view THINNING_HABR_SIMP_V as select STRIPE_ID , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID) as UT , avg (APRICE) keep (dense_rank first order by UT, ID) as AOPEN , max (APRICE) as AHIGH , min (APRICE) as ALOW , avg (APRICE) keep (dense_rank last order by UT, ID) as ACLOSE , sum (AVOLUME) as AVOLUME , sum (APRICE * AVOLUME) as AAMOUNT , count (*) as ACOUNT from TRANSACTIONS_RAW , (select rownum as STRIPE_ID from dual connect by level <= 10) group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID);
Opsi 2 - CALC (dihitung secara iteratif)
Dalam opsi ini, kami melakukan iteratif dari transaksi ke level 1, dari level 1 ke level 2, dan seterusnya.
Buat tabel:
create table QUOTES_CALC ( STRIPE_ID number not null , STOCK_NAME varchar2 (128) not null , UT number not null , AOPEN number not null , AHIGH number not null , ALOW number not null , ACLOSE number not null , AVOLUME number not null , AAMOUNT number not null , ACOUNT number not null ) parallel 4 pctfree 0 nologging;
Anda dapat membuat indeks menggunakan bidang STRIPE_ID, tetapi telah ditetapkan secara eksperimental bahwa itu lebih menguntungkan untuk 11 juta transaksi tanpa indeks. Untuk jumlah yang lebih besar, situasinya dapat berubah. Atau Anda bisa mempartisi tabel dengan membatalkan komentar blok dalam kueri.
Buat prosedur:
create or replace procedure THINNING_HABR_CALC_T is begin rollback; execute immediate 'truncate table QUOTES_CALC'; insert
Untuk simetri, buat VIEW sederhana:
create view THINNING_HABR_CALC_V as select * from QUOTES_CALC;
Opsi 3 - CHIN (Kode Cina)
Metodenya berbeda langsung dari pendekatan brutal, dan merupakan penolakan terhadap prinsip "Jangan ulangi diri Anda sendiri." Dalam hal ini, penolakan siklus.
Opsi ini disediakan di sini hanya untuk kelengkapan.
Ke depan, saya akan mengatakan bahwa dalam hal kinerja pada tugas khusus ini dibutuhkan tempat kedua.
Permintaan besar create or replace view THINNING_HABR_CHIN_V as with T01 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select 1 , STOCK_NAME , UT , avg (APRICE) keep (dense_rank first order by ID) , max (APRICE) , min (APRICE) , avg (APRICE) keep (dense_rank last order by ID) , sum (AVOLUME) , sum (APRICE * AVOLUME) , count (*) from TRANSACTIONS_RAW group by STOCK_NAME, UT) , T02 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T01 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T03 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T02 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T04 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T03 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T05 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T04 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T06 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T05 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T07 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T06 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T08 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T07 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T09 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T08 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) , T10 (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) as (select STRIPE_ID + 1 , STOCK_NAME , TRUNC_UT (UT, STRIPE_ID + 1) , avg (AOPEN) keep (dense_rank first order by UT) , max (AHIGH) , min (ALOW) , avg (ACLOSE) keep (dense_rank last order by UT) , sum (AVOLUME) , sum (AAMOUNT) , sum (ACOUNT) from T09 group by STRIPE_ID, STOCK_NAME, TRUNC_UT (UT, STRIPE_ID + 1)) select * from T01 union all select * from T02 union all select * from T03 union all select * from T04 union all select * from T05 union all select * from T06 union all select * from T07 union all select * from T08 union all select * from T09 union all select * from T10;
Opsi 4 - UDAF
Opsi dengan Fungsi Gabungan yang Ditentukan Pengguna tidak akan diberikan di sini, tetapi dapat dilihat di github.
Opsi 5 - PPTF (Fungsi tabel dan Paralel)
Buat fungsi (dalam paket):
create or replace package THINNING_PPTF_P is type TRANSACTION_RECORD_T is record (STOCK_NAME varchar2(128), UT number, SEQ_NUM number, APRICE number, AVOLUME number); type CUR_RECORD_T is ref cursor return TRANSACTION_RECORD_T; type QUOTE_T is record (STRIPE_ID number, STOCK_NAME varchar2(128), UT number , AOPEN number, AHIGH number, ALOW number, ACLOSE number, AVOLUME number , AAMOUNT number, ACOUNT number); type QUOTE_LIST_T is table of QUOTE_T; function F (p_cursor CUR_RECORD_T) return QUOTE_LIST_T pipelined order p_cursor by (STOCK_NAME, UT, SEQ_NUM) parallel_enable (partition p_cursor by hash (STOCK_NAME)); end; / create or replace package body THINNING_PPTF_P is function F (p_cursor CUR_RECORD_T) return QUOTE_LIST_T pipelined order p_cursor by (STOCK_NAME, UT, SEQ_NUM) parallel_enable (partition p_cursor by hash (STOCK_NAME)) is QuoteTail QUOTE_LIST_T := QUOTE_LIST_T() ; rec TRANSACTION_RECORD_T; rec_prev TRANSACTION_RECORD_T; type ut_T is table of number index by pls_integer; ut number; begin QuoteTail.extend(10); loop fetch p_cursor into rec; exit when p_cursor%notfound; if rec_prev.STOCK_NAME = rec.STOCK_NAME then if (rec.STOCK_NAME = rec_prev.STOCK_NAME and rec.UT < rec_prev.UT) or (rec.STOCK_NAME = rec_prev.STOCK_NAME and rec.UT = rec_prev.UT and rec.SEQ_NUM < rec_prev.SEQ_NUM) then raise_application_error (-20010, 'Rowset must be ordered, ('||rec_prev.STOCK_NAME||','||rec_prev.UT||','||rec_prev.SEQ_NUM||') > ('||rec.STOCK_NAME||','||rec.UT||','||rec.SEQ_NUM||')'); end if; end if; if rec.STOCK_NAME <> rec_prev.STOCK_NAME or rec_prev.STOCK_NAME is null then for j in 1 .. 10 loop if QuoteTail(j).UT is not null then pipe row (QuoteTail(j)); QuoteTail(j) := null; end if; end loop; end if; for i in reverse 1..10 loop ut := TRUNC_UT (rec.UT, i); if QuoteTail(i).UT <> ut then for j in 1..i loop pipe row (QuoteTail(j)); QuoteTail(j) := null; end loop; end if; if QuoteTail(i).UT is null then QuoteTail(i).STRIPE_ID := i; QuoteTail(i).STOCK_NAME := rec.STOCK_NAME; QuoteTail(i).UT := ut; QuoteTail(i).AOPEN := rec.APRICE; end if; if rec.APRICE < QuoteTail(i).ALOW or QuoteTail(i).ALOW is null then QuoteTail(i).ALOW := rec.APRICE; end if; if rec.APRICE > QuoteTail(i).AHIGH or QuoteTail(i).AHIGH is null then QuoteTail(i).AHIGH := rec.APRICE; end if; QuoteTail(i).AVOLUME := nvl (QuoteTail(i).AVOLUME, 0) + rec.AVOLUME; QuoteTail(i).AAMOUNT := nvl (QuoteTail(i).AAMOUNT, 0) + rec.AVOLUME * rec.APRICE; QuoteTail(i).ACOUNT := nvl (QuoteTail(i).ACOUNT, 0) + 1; QuoteTail(i).ACLOSE := rec.APRICE; end loop; rec_prev := rec; end loop; for j in 1 .. 10 loop if QuoteTail(j).UT is not null then pipe row (QuoteTail(j)); end if; end loop; exception when no_data_needed then null; end; end; /
Buat LIHAT:
create or replace view THINNING_HABR_PPTF_V as select * from table (THINNING_PPTF_P.F (cursor (select STOCK_NAME, UT, ID, APRICE, AVOLUME from TRANSACTIONS_RAW)));
Opsi 6 - MODE (model clause)
Opsi iteratif menghitung penipisan untuk semua 10 level menggunakan frase klausa
MODEL dengan frasa
ITERATE .
Opsi ini juga tidak praktis karena lambat. Di lingkungan saya, 1000 transaksi untuk 8 instrumen dihitung dalam 1 menit. Sebagian besar waktu dihabiskan untuk menghitung frase
MODEL .
Di sini saya memberikan opsi ini hanya demi kelengkapan dan sebagai konfirmasi atas fakta bahwa pada Oracle hampir semua perhitungan kompleks yang sewenang-wenang dapat dilakukan dengan satu permintaan, tanpa menggunakan PL / SQL.
Salah satu alasan rendahnya kinerja frase
MODEL dalam permintaan ini adalah bahwa pencarian di sebelah kanan dilakukan untuk
setiap aturan yang kita miliki 6. Dua aturan pertama dihitung cukup cepat, karena ada pengalamatan eksplisit langsung, tanpa pelawak. Dalam empat aturan sisanya ada kata
any - ada perhitungan lebih lambat.
Kesulitan kedua adalah Anda harus menghitung model referensi. Ini diperlukan karena daftar dimensi harus diketahui
sebelum menghitung frase
MODEL , kami tidak dapat menghitung dimensi baru di dalam frasa ini. Mungkin ini dapat dielakkan dengan bantuan dua frase MODEL, tetapi saya tidak melakukan ini karena kinerja yang rendah dari sejumlah besar aturan.
Saya menambahkan bahwa dimungkinkan untuk tidak menghitung
UT_OPEN dan
UT_CLOSE dalam model referensi, tetapi untuk menggunakan fungsi yang sama
avg () tetap (dense_rank urutan pertama / terakhir oleh) langsung dalam frase
MODEL . Tetapi itu akan terjadi bahkan lebih lambat.
Karena keterbatasan kinerja, saya tidak akan memasukkan opsi ini dalam prosedur pengujian.
with
Opsi 6 - IDEA (ideal, ideal, tetapi tidak beroperasi)
Permintaan yang dijelaskan di bawah ini berpotensi menjadi yang paling efisien dan mengonsumsi jumlah sumber daya yang sama dengan minimum teoretis.
Tetapi baik Oracle maupun MS SQL tidak memungkinkan Anda untuk menulis kueri dalam formulir ini. Saya percaya ini ditentukan oleh standar.
with QUOTES_S1 as (select 1 as STRIPE_ID , STOCK_NAME , TRUNC_UT (UT, 1) as UT , avg (APRICE) keep (dense_rank first order by ID) as AOPEN , max (APRICE) as AHIGH , min (APRICE) as ALOW , avg (APRICE) keep (dense_rank last order by ID) as ACLOSE , sum (AVOLUME) as AVOLUME , sum (APRICE * AVOLUME) as AAMOUNT , count (*) as ACOUNT from TRANSACTIONS_RAW
Kueri ini terkait dengan bagian berikut dari dokumentasi Oracle:
Jika sebuah subquery_factoring_clause merujuk pada nama_pertanyaannya sendiri dalam subquery yang mendefinisikannya, maka subquery_factoring_clause dikatakan rekursif. Subquery_factoring_clause rekursif harus berisi dua blok permintaan: yang pertama adalah anggota anchor dan yang kedua adalah anggota rekursif. Anggota anchor harus muncul sebelum anggota rekursif, dan tidak dapat mereferensikan query_name. Anggota anchor dapat terdiri dari satu atau lebih blok permintaan yang digabungkan oleh operator yang ditetapkan: UNION ALL, UNION, INTERSECT atau MINUS. Anggota rekursif harus mengikuti anggota anchor dan harus mereferensikan query_name tepat sekali. Anda harus menggabungkan anggota rekursif dengan anggota jangkar menggunakan operator set UNION ALL.Tetapi itu bertentangan dengan paragraf dokumentasi berikut:
Anggota rekursif tidak dapat mengandung elemen-elemen berikut:
Kata kunci DISTINCT atau klausa GROUP BY
Fungsi agregat. Namun, fungsi analitik diizinkan dalam daftar pilih.Dengan demikian, dalam anggota rekursif, agregat dan pengelompokan tidak diperbolehkan.
Pengujian
Mari kita lakukan dulu untuk
Oracle .
Lakukan prosedur perhitungan untuk metode CALC dan catat waktu pelaksanaannya:
exec THINNING_HABR_CALC_T
Hasil perhitungan untuk keempat metode tersebut dalam empat tampilan:
- THINNING_HABR_SIMP_V (akan melakukan perhitungan, menyebabkan SELECT yang kompleks, sehingga akan memakan waktu lama),
- THINNING_HABR_CALC_V (akan menampilkan data dari tabel QUOTES_CALC, sehingga akan dieksekusi dengan cepat)
- THINNING_HABR_CHIN_V (juga akan melakukan perhitungan, menyebabkan SELECT yang kompleks, sehingga akan memakan waktu lama),
- THINNING_HABR_PPTF_V (akan menjalankan fungsi THINNING_HABR_PPTF).
Waktu tunggu untuk semua metode sudah diukur oleh saya dan diberikan dalam tabel di akhir artikel.
Untuk sisa LIHAT, kami mengeksekusi permintaan dan menulis waktu eksekusi:
select count (*) as CNT , sum (STRIPE_ID) as S_STRIPE_ID, sum (UT) as S_UT , sum (AOPEN) as S_AOPEN, sum (AHIGH) as S_AHIGH, sum (ALOW) as S_ALOW , sum (ACLOSE) as S_ACLOSE, sum (AVOLUME) as S_AVOLUME , sum (AAMOUNT) as S_AAMOUNT, sum (ACOUNT) as S_ACOUNT from THINNING_HABR_XXXX_V
di mana XXXX adalah SIMP, CHIN, PPTF.
PANDANGAN INI menghitung intisari perekrutan. Untuk menghitung intisari, Anda harus mengambil semua garis, dan menggunakan intisari Anda dapat membandingkan set dengan satu sama lain.
Anda juga dapat membandingkan set menggunakan paket dbms_sqlhash, tetapi ini jauh lebih lambat karena Anda perlu mengurutkan set asli, dan perhitungan hash tidak cepat.
Juga dalam 12c ada paket DBMS_COMPARISON.
Anda dapat memeriksa kebenaran semua algoritma pada saat yang sama. Kami menganggap intisari sebagai permintaan (dengan 11M entri di mesin virtual, ini akan relatif lama, sekitar 15 menit):
with T1 as (select 'SIMP' as ALG_NAME, a.* from THINNING_HABR_SIMP_V a union all select 'CALC', a.* from THINNING_HABR_CALC_V a union all select 'CHIN', a.* from THINNING_HABR_CHIN_V a union all select 'PPTF', a.* from THINNING_HABR_PPTF_V a) select ALG_NAME , count (*) as CNT , sum (STRIPE_ID) as S_STRIPE_ID, sum (UT) as S_UT , sum (AOPEN) as S_AOPEN, sum (AHIGH) as S_AHIGH, sum (ALOW) as S_ALOW , sum (ACLOSE) as S_ACLOSE, sum (AVOLUME) as S_AVOLUME , sum (AAMOUNT) as S_AAMOUNT, sum (ACOUNT) as S_ACOUNT from T1 group by ALG_NAME;
Kami melihat bahwa pencernaannya sama, jadi semua algoritma memberikan hasil yang sama.
Sekarang kita akan mereproduksi semua sama di
MS SQL . Saya menguji pada versi 2016.
Pertama buat database DBTEST, lalu buat tabel transaksi di dalamnya:
use DBTEST go create table TRANSACTIONS_RAW ( STOCK_NAME varchar (32) not null , UT int not null , APRICE numeric (22, 12) not null , AVOLUME numeric (22, 12) not null , ID bigint identity not null );
Unduh data yang diunduh.
Di MSSQL, buat file format_mssql.bcp:
12.0 3 1 SQLCHAR 0 0 "," 3 UT "" 2 SQLCHAR 0 0 "," 4 APRICE "" 3 SQLCHAR 0 0 "\n" 5 AVOLUME ""
Dan jalankan skrip LoadData-MSSQL.sql di SSMS (skrip ini dihasilkan oleh skrip powershell yang disediakan di bagian artikel ini untuk Oracle).
Mari kita membuat dua fungsi:
use DBTEST go create or alter function TRUNC_UT (@p_UT bigint, @p_StripeTypeId int) returns bigint as begin return case @p_StripeTypeId when 1 then @p_UT when 2 then @p_UT / 10 * 10 when 3 then @p_UT / 60 * 60 when 4 then @p_UT / 600 * 600 when 5 then @p_UT / 3600 * 3600 when 6 then @p_UT / 14400 * 14400 when 7 then @p_UT / 86400 * 86400 when 8 then datediff (second, cast ('1970-01-01 00:00:00' as datetime), dateadd(m, datediff (m, 0, dateadd (second, @p_UT, cast ('1970-01-01 00:00:00' as datetime))), 0)) when 9 then datediff (second, cast ('1970-01-01 00:00:00' as datetime), dateadd(yy, datediff (yy, 0, dateadd (second, @p_UT, cast ('1970-01-01 00:00:00' as datetime))), 0)) when 10 then 0 when 11 then 0 end; end; go create or alter function UT2DATESTR (@p_UT bigint) returns datetime as begin return dateadd(s, @p_UT, cast ('1970-01-01 00:00:00' as datetime)); end; go
Kami terus menerapkan opsi:Opsi 1 - SIMP
Jalankan: use DBTEST go create or alter view dbo.THINNING_HABR_SIMP_V as with T1 (STRIPE_ID) as (select 1 union all select STRIPE_ID + 1 from T1 where STRIPE_ID < 10) , T2 as (select STRIPE_ID , STOCK_NAME , dbo.TRUNC_UT (UT, STRIPE_ID) as UT , min (1000000 * cast (UT as bigint) + ID) as AOPEN_UT , max (APRICE) as AHIGH , min (APRICE) as ALOW , max (1000000 * cast (UT as bigint) + ID) as ACLOSE_UT , sum (AVOLUME) as AVOLUME , sum (APRICE * AVOLUME) as AAMOUNT , count (*) as ACOUNT from TRANSACTIONS_RAW, T1 group by STRIPE_ID, STOCK_NAME, dbo.TRUNC_UT (UT, STRIPE_ID)) select t.STRIPE_ID, t.STOCK_NAME, t.UT, t_op.APRICE as AOPEN, t.AHIGH , t.ALOW, t_cl.APRICE as ACLOSE, t.AVOLUME, t.AAMOUNT, t.ACOUNT from T2 t join TRANSACTIONS_RAW t_op on (t.STOCK_NAME = t_op.STOCK_NAME and t.AOPEN_UT / 1000000 = t_op.UT and t.AOPEN_UT % 1000000 = t_op.ID) join TRANSACTIONS_RAW t_cl on (t.STOCK_NAME = t_cl.STOCK_NAME and t.ACLOSE_UT / 1000000 = t_cl.UT and t.ACLOSE_UT % 1000000 = t_cl.ID);
Fungsi pertama / terakhir yang hilang diimplementasikan oleh penggandaan tabel ganda secara mandiri.Opsi 2 - CALC
Buat tabel, prosedur, dan tampilan: use DBTEST go create table dbo.QUOTES_CALC ( STRIPE_ID int not null , STOCK_NAME varchar(32) not null , UT bigint not null , AOPEN numeric (22, 12) not null , AHIGH numeric (22, 12) not null , ALOW numeric (22, 12) not null , ACLOSE numeric (22, 12) not null , AVOLUME numeric (38, 12) not null , AAMOUNT numeric (38, 12) not null , ACOUNT int not null ); go create or alter procedure dbo.THINNING_HABR_CALC as begin set nocount on; truncate table QUOTES_CALC; declare @StripeId int; with T1 as (select STOCK_NAME , UT , min (ID) as AOPEN_ID , max (APRICE) as AHIGH , min (APRICE) as ALOW , max (ID) as ACLOSE_ID , sum (AVOLUME) as AVOLUME , sum (APRICE * AVOLUME) as AAMOUNT , count (*) as ACOUNT from TRANSACTIONS_RAW group by STOCK_NAME, UT) insert into QUOTES_CALC (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) select 1, t.STOCK_NAME, t.UT, t_op.APRICE, t.AHIGH, t.ALOW, t_cl.APRICE, t.AVOLUME, t.AAMOUNT, t.ACOUNT from T1 t join TRANSACTIONS_RAW t_op on (t.STOCK_NAME = t_op.STOCK_NAME and t.UT = t_op.UT and t.AOPEN_ID = t_op.ID) join TRANSACTIONS_RAW t_cl on (t.STOCK_NAME = t_cl.STOCK_NAME and t.UT = t_cl.UT and t.ACLOSE_ID = t_cl.ID); set @StripeId = 1; while (@StripeId <= 9) begin with T1 as (select STOCK_NAME , dbo.TRUNC_UT (UT, @StripeId + 1) as UT , min (UT) as AOPEN_UT , max (AHIGH) as AHIGH , min (ALOW) as ALOW , max (UT) as ACLOSE_UT , sum (AVOLUME) as AVOLUME , sum (AAMOUNT) as AAMOUNT , sum (ACOUNT) as ACOUNT from QUOTES_CALC where STRIPE_ID = @StripeId group by STOCK_NAME, dbo.TRUNC_UT (UT, @StripeId + 1)) insert into QUOTES_CALC (STRIPE_ID, STOCK_NAME, UT, AOPEN, AHIGH, ALOW, ACLOSE, AVOLUME, AAMOUNT, ACOUNT) select @StripeId + 1, t.STOCK_NAME, t.UT, t_op.AOPEN, t.AHIGH, t.ALOW, t_cl.ACLOSE, t.AVOLUME, t.AAMOUNT, t.ACOUNT from T1 t join QUOTES_CALC t_op on (t.STOCK_NAME = t_op.STOCK_NAME and t.AOPEN_UT = t_op.UT) join QUOTES_CALC t_cl on (t.STOCK_NAME = t_cl.STOCK_NAME and t.ACLOSE_UT = t_cl.UT) where t_op.STRIPE_ID = @StripeId and t_cl.STRIPE_ID = @StripeId; set @StripeId = @StripeId + 1; end; end; go create or alter view dbo.THINNING_HABR_CALC_V as select * from dbo.QUOTES_CALC; go
Saya tidak menerapkan opsi 3 (CHIN) dan 4 (UDAF) pada MS SQL.Opsi 5 - PPTF
Buat fungsi dan tampilan tabel. Fungsi ini hanyalah fungsi tabel, bukan fungsi tabel pipelined paralel, hanya opsi yang mempertahankan nama historisnya dari Oracle: use DBTEST go create or alter function dbo.THINNING_HABR_PPTF () returns @rettab table ( STRIPE_ID bigint not null , STOCK_NAME varchar(32) not null , UT bigint not null , AOPEN numeric (22, 12) not null , AHIGH numeric (22, 12) not null , ALOW numeric (22, 12) not null , ACLOSE numeric (22, 12) not null , AVOLUME numeric (38, 12) not null , AAMOUNT numeric (38, 12) not null , ACOUNT bigint not null) as begin declare @i tinyint; declare @tut int; declare @trans_STOCK_NAME varchar(32); declare @trans_UT int; declare @trans_ID int; declare @trans_APRICE numeric (22,12); declare @trans_AVOLUME numeric (22,12); declare @trans_prev_STOCK_NAME varchar(32); declare @trans_prev_UT int; declare @trans_prev_ID int; declare @trans_prev_APRICE numeric (22,12); declare @trans_prev_AVOLUME numeric (22,12); declare @QuoteTail table ( STRIPE_ID bigint not null primary key clustered , STOCK_NAME varchar(32) not null , UT bigint not null , AOPEN numeric (22, 12) not null , AHIGH numeric (22, 12) , ALOW numeric (22, 12) , ACLOSE numeric (22, 12) , AVOLUME numeric (38, 12) not null , AAMOUNT numeric (38, 12) not null , ACOUNT bigint not null); declare c cursor fast_forward for select STOCK_NAME, UT, ID, APRICE, AVOLUME from TRANSACTIONS_RAW order by STOCK_NAME, UT, ID;
Mari kita hitung tabel QUOTES_CALC untuk metode CALC dan tulis waktu eksekusi: use DBTEST go exec dbo.THINNING_HABR_CALC
Hasil perhitungan untuk ketiga metode tersebut dalam tiga tampilan:- THINNING_HABR_SIMP_V (akan melakukan perhitungan, menyebabkan SELECT yang kompleks, sehingga akan memakan waktu lama),
- THINNING_HABR_CALC_V (akan menampilkan data dari tabel QUOTES_CALC, sehingga akan dieksekusi dengan cepat)
- THINNING_HABR_PPTF_V (akan menjalankan fungsi THINNING_HABR_PPTF).
Selama dua LIHAT, kami mengeksekusi permintaan dan menulis waktu eksekusi: select count (*) as CNT , sum (STRIPE_ID) as S_STRIPE_ID, sum (UT) as S_UT , sum (AOPEN) as S_AOPEN, sum (AHIGH) as S_AHIGH, sum (ALOW) as S_ALOW , sum (ACLOSE) as S_ACLOSE, sum (AVOLUME) as S_AVOLUME , sum (AAMOUNT) as S_AAMOUNT, sum (ACOUNT) as S_ACOUNT from THINNING_HABR_XXXX_V
di mana XXXX adalah SIMP, PPTF.Sekarang Anda dapat membandingkan hasil perhitungan untuk tiga metode untuk MS SQL. Ini dapat dilakukan dalam satu permintaan. Jalankan: use DBTEST go with T1 as (select 'SIMP' as ALG_NAME, a.* from THINNING_HABR_SIMP_V a union all select 'CALC', a.* from THINNING_HABR_CALC_V a union all select 'PPTF', a.* from THINNING_HABR_PPTF_V a) select ALG_NAME , count (*) as CNT, sum (cast (STRIPE_ID as bigint)) as STRIPE_ID , sum (cast (UT as bigint)) as UT, sum (AOPEN) as AOPEN , sum (AHIGH) as AHIGH, sum (ALOW) as ALOW, sum (ACLOSE) as ACLOSE, sum (AVOLUME) as AVOLUME , sum (AAMOUNT) as AAMOUNT, sum (cast (ACOUNT as bigint)) as ACOUNT from T1 group by ALG_NAME;
Jika tiga garis bertepatan di semua bidang, hasil perhitungan menggunakan tiga metode adalah identik.Saya sangat menyarankan Anda untuk menggunakan pilihan kecil pada tahap pengujian, karena kinerja tugas ini di MS SQL rendah.Jika Anda hanya memiliki mesin MS SQL dan ingin menghitung jumlah data yang lebih besar, maka Anda dapat mencoba metode pengoptimalan berikut: Anda dapat membuat indeks: create unique clustered index TRANSACTIONS_RAW_I1 on TRANSACTIONS_RAW (STOCK_NAME, UT, ID); create unique clustered index QUOTES_CALC_I1 on QUOTES_CALC (STRIPE_ID, STOCK_NAME, UT);
Hasil pengukuran kinerja pada mesin virtual saya adalah sebagai berikut:Skrip dapat diunduh dari github : Oracle, skema THINNING - skrip artikel ini, skema THINNING_LIVE - data unduhan online dari bitcoincharts.com dan penjarangan online (tetapi situs ini hanya mengirim data selama 5 hari terakhir dalam mode online), dan skrip untuk MS SQL juga ada di artikel ini.Kesimpulan:Tugas ini diselesaikan lebih cepat pada Oracle daripada pada MS SQL. Dengan meningkatnya jumlah transaksi, kesenjangan menjadi lebih signifikan.Di Oracle, PPTF adalah pilihan terbaik. Di sini pendekatan prosedural ternyata lebih menguntungkan, ini jarang terjadi. Metode lain juga menunjukkan hasil yang dapat diterima - Saya bahkan menguji volume transaksi 367M di mesin virtual (metode PPTF dihitung menipis dalam satu setengah jam).Pada MS SQL, metode perhitungan berulang (CALC) ternyata yang paling produktif.Mengapa metode PPTF pada Oracle berubah menjadi pemimpin? Karena konkurensi dan arsitektur, fungsi yang dibuat sebagai fungsi tabel pipelined paralel tertanam di tengah rencana kueri: