Kadang-kadang, ketika menganalisis data,
masalah muncul dari
membedakan "rantai" dalam sampel - yaitu
urutan urutan catatan, untuk masing-masing yang
memenuhi kondisi tertentu .
Ini bisa berupa kondisi pada data catatan itu sendiri, atau ekspresi kompleks sehubungan dengan satu atau lebih catatan sebelumnya - misalnya, panjang interval antara sampel waktu dekat.
Solusi tradisional memberikan opsi berbeda untuk "bergabung sendiri", ketika sampel terhubung dengan dirinya sendiri, atau penggunaan fakta tertentu "di luar data" - misalnya, bahwa catatan harus memiliki langkah yang ditentukan secara ketat (N + 1, "untuk setiap hari", ... )
Opsi pertama sering mengarah pada
kompleksitas kuadratik dari segi jumlah catatan, yang tidak
dapat diterima dalam sampel besar , dan yang kedua
dapat dengan mudah "berantakan" jika tiba-tiba tidak ada sampel dalam data sumber.
Tetapi tugas ini akan membantu kita untuk secara efektif menyelesaikan
fungsi-fungsi jendela di PostgreSQL.
Tugas: menghitung uang orang lain
Pertimbangkan kasus rantai paling sederhana - ketika kondisi kontinuitas ditentukan oleh data catatan itu sendiri.
Semua operasi lebih lanjut tidak harus dilakukan secara terpisah. Tetapi demi kejelasan algoritme, saya akan memecahnya menjadi langkah-langkah berturut-turut, dan
pada akhirnya saya akan menunjukkan apa dan bagaimana mengoptimalkan .
Mari kita bayangkan bahwa kita memiliki bank kecil yang mengelola saldo di rekening pelanggan dalam tabel. Segera setelah transaksi penerimaan dan pengeluaran terjadi, tanggal ini juga menetapkan jumlah total tagihan pada akhir hari.
Setelah liburan panjang Tahun Baru, bank memutuskan untuk memberi hadiah kepada pelanggannya - dan setiap orang yang membuka akun tahun ini juga bertambah + 1% dari saldo rata - rata harian untuk periode berkelanjutan terpanjang ketika akun tidak "disetel ulang" .
Ini dia kriteria kita untuk kesinambungan "rantai". Nah, pemesanan data akan ditentukan oleh tanggal saldo.
Mereka membawa kami CSV seperti itu, dan meminta untuk dengan cepat menghitung siapa dan berapa banyak kemurahan hati dari bank harus dapatkan:
date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5
Perhatikan beberapa fakta yang dapat dilihat pada data ini:
- 01.01 adalah hari libur, dan bank tidak bekerja. Oleh karena itu, tidak ada klien yang memiliki catatan perubahan saldo pada hari itu, tetapi mereka memiliki uang di akun mereka. Algoritma "brute force" yang diulang-ulang setiap hari tidak akan bekerja secara normal.
- 04.01 Alice tidak melakukan operasi apa pun, jadi tidak ada entri. Tetapi sebelum 05.01, jumlah dalam akunnya adalah nol - ini harus diperhitungkan dalam analisis.
- Kami melakukan analisis pada 01.01-12.01, tetapi saldo akun Alice pada akhir periode ini tidak nol. Kami juga memperhitungkan kebutuhan untuk membatasi periode.
CSV-to-table
Cara terbaik untuk mengimpor dari CSV adalah dengan
menggunakan operator COPY . Tetapi kami akan mencoba melakukan ini melalui ekspresi reguler untuk pemanasan:
CREATE TEMPORARY TABLE tbl AS SELECT to_date(prt[1], 'DD.MM.YYYY') dt , prt[2] client , prt[3]::numeric(32,2) balance FROM ( SELECT regexp_split_to_array(str, ';') prt FROM ( SELECT regexp_split_to_table( $$ date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5 $$ , E'\\n') str ) T WHERE str <> '' OFFSET 1 ) T;
Ini adalah metode “tidak jujur” dalam arti tidak akan dicerna dengan benar, misalnya, melindungi pemisah dalam tubuh bidang. Tetapi untuk sebagian besar aplikasi sederhana - cocok.
Langkah 1: Perbaiki kondisi aplikasi
Dalam kasus kami, kondisi kontinuitas rantai adalah keseimbangan yang tidak nol. Kami menampilkannya sebagai bidang terpisah, untuk kejelasan, secara kronologis dipesan oleh klien:
SELECT * , balance > 0 cond FROM tbl ORDER BY client, dt;
dt | client | balance | cond ------------------------------------ 2020-01-01 | | 150.00 | t 2020-01-02 | | 100.00 | t 2020-01-03 | | 200.00 | t 2020-01-05 | | 0.00 | f 2020-01-06 | | 50.00 | t 2020-01-08 | | 0.00 | f 2020-01-09 | | 0.00 | f 2020-01-10 | | 5.00 | t 2020-01-01 | | 100.00 | t 2020-01-02 | | 150.00 | t 2020-01-08 | | 200.00 | t 2020-01-09 | | 0.00 | f
Langkah 2: Hitung yang Hilang
Perhatikan bahwa jumlah Bob tidak berubah dari 02,01 menjadi 08,01. Dan sesuai dengan kondisi masalah, kita harus menghitung sisa
harian rata -
rata - yaitu, kita memerlukan informasi tentang hari-hari yang "terlewatkan" ini. Atau setidaknya jumlah hari ketika nilainya tetap sama:
coalesce(lead(dt) OVER(PARTITION BY client ORDER BY dt), '2020-01-12') - dt days
dt | client | balance | cond | days ------------------------------------------- 2020-01-01 | | 150.00 | t | 1 2020-01-02 | | 100.00 | t | 1 2020-01-03 | | 200.00 | t | 2 2020-01-05 | | 0.00 | f | 1 2020-01-06 | | 50.00 | t | 2 2020-01-08 | | 0.00 | f | 1 2020-01-09 | | 0.00 | f | 1 2020-01-10 | | 5.00 | t | 2 2020-01-01 | | 100.00 | t | 1 2020-01-02 | | 150.00 | t | 6 2020-01-08 | | 200.00 | t | 1 2020-01-09 | | 0.00 | f | 3
Menggunakan
fungsi jendela lead (), kami mempelajari tanggal dari rekaman
berikutnya secara berurutan, dan melalui
penggabungan kami membatasi interval untuk yang terakhir. Pada saat yang sama, mereka menggunakan properti yang berguna bahwa
perbedaan dua tanggal di PostgreSQL mengembalikan jumlah bilangan bulat hari di antara mereka.
Sebagai bonus hampir gratis, kami mendapat semua informasi yang sama untuk catatan dengan saldo nol. Tetapi jika ada banyak baris dengan kondisi tidak terpenuhi yang tidak menarik bagi kami, masuk akal
untuk mengarahkan perhitungan tersebut di bawah KASUS untuk menghemat sumber daya server.
Langkah 3: Temukan Break Points
Awal dari setiap rantai yang kami minati adalah titik di mana nilai kondisi yang dihitung sebelumnya berubah relatif terhadap catatan
sebelumnya . Kami akan menggunakan fungsi
lag () untuk menemukan titik-titik tersebut:
lag(cond) OVER(PARTITION BY client ORDER BY dt) IS DISTINCT FROM cond chain_start
dt | client | balance | cond | days | chain_start --------------------------------------------------------- 2020-01-01 | | 150.00 | t | 1 | t 2020-01-02 | | 100.00 | t | 1 | f 2020-01-03 | | 200.00 | t | 2 | f 2020-01-05 | | 0.00 | f | 1 | t 2020-01-06 | | 50.00 | t | 2 | t 2020-01-08 | | 0.00 | f | 1 | t 2020-01-09 | | 0.00 | f | 1 | f 2020-01-10 | | 5.00 | t | 2 | t 2020-01-01 | | 100.00 | t | 1 | t 2020-01-02 | | 150.00 | t | 6 | f 2020-01-08 | | 200.00 | t | 1 | f 2020-01-09 | | 0.00 | f | 3 | t
Dengan menggunakan
IS DISTINCT FROM operator alih-alih <>, kami menghindari masalah membandingkan dengan NULL untuk catatan pertama untuk setiap klien. Dengan demikian, semua baris di mana nilai TRUE adalah awal dari rantai baru, dan FALSE adalah kelanjutannya.
Langkah 4: merangkai tautan
Untuk mengelompokkan data dalam setiap rantai individual, akan lebih mudah untuk menetapkan
pengidentifikasi yang sama untuk semua catatannya. Nomor seri rantai itu sendiri sangat cocok untuk itu. Dan itu persis sama dengan
jumlah "permulaan" rantai yang ditemukan lebih tinggi dalam sampel.
Mereka dapat dihitung baik melalui "jendela" penjumlahan dari jumlah nilai bool ({boolean} :: integer), atau dengan menghitung jumlah catatan yang cocok dengan jumlah (*) FILTER (WHERE {boolean} kondisi. Kami akan menggunakan opsi kedua:
count(*) FILTER(WHERE chain_start) OVER(PARTITION BY client ORDER BY dt) grpid
dt | client | balance | cond | days | chain_start | grpid ----------------------------------------------------------------- 2020-01-01 | | 150.00 | t | 1 | t | 1 2020-01-02 | | 100.00 | t | 1 | f | 1 2020-01-03 | | 200.00 | t | 2 | f | 1 2020-01-06 | | 50.00 | t | 2 | t | 2 2020-01-10 | | 5.00 | t | 2 | t | 3 2020-01-01 | | 100.00 | t | 1 | t | 1 2020-01-02 | | 150.00 | t | 6 | f | 1 2020-01-08 | | 200.00 | t | 1 | f | 1
Pada langkah ini, kita sudah mengetahui panjang semua tautan di setiap rantai, kita tidak lagi membutuhkan catatan "tidak menarik", jadi saring saja.
Langkah 5: Menempatkan Rantai
Untuk menghitung rata-rata semua hari dalam suatu rantai, kita membutuhkan jumlah total hari dan keseimbangan “integral”:
SELECT client , min(dt) chain_dt , sum(days * balance) balance , sum(days) days FROM ... GROUP BY 1, grpid ORDER BY 1, grpid;
client | chain_dt | balance | days ------------------------------------- | 2020-01-01 | 650.00 | 4 | 2020-01-06 | 100.00 | 2 | 2020-01-10 | 10.00 | 2 | 2020-01-01 | 1200.00 | 8
Langkah 6: Mencari Nilai Terapan
Menggunakan
DISTINCT ON, kami akan meninggalkan satu catatan (dengan nilai hari maksimum) untuk setiap klien:
SELECT DISTINCT ON(client) * FROM ... ORDER BY client, days DESC;
client | chain_dt | balance | days ------------------------------------- | 2020-01-01 | 650.00 | 4 | 2020-01-01 | 1200.00 | 8
Sebenarnya, hanya itu, yang tersisa hanyalah ...
Kami menggabungkan dan mengoptimalkan
Permintaan ringkasan WITH step123 AS ( SELECT * , CASE WHEN cond THEN lag(cond) OVER(w) IS DISTINCT FROM cond END chain_start , CASE WHEN cond THEN coalesce(lead(dt) OVER(w), '2020-01-12') - dt END days FROM tbl , LATERAL(SELECT balance > 0 cond) T WINDOW w AS (PARTITION BY client ORDER BY dt) ) , step4 AS ( SELECT * , count(*) FILTER(WHERE chain_start) OVER(PARTITION BY client ORDER BY dt) grpid FROM step123 WHERE cond ) SELECT DISTINCT ON(client) client , sum(days) OVER(w) days , min(dt) OVER(w) chain_dt , sum(days * balance) OVER(w) balance FROM step4 WINDOW w AS (PARTITION BY client, grpid) ORDER BY 1, 2 DESC;
Di sini kami menggabungkan dan mengoptimalkan tiga langkah pertama:
- Subquery LATERAL memungkinkan kami untuk menghitung bidang tambahan tanpa harus melewati seleksi dan segera menggunakannya dalam fungsi
- penghapusan definisi umum di bawah WINDOW membantu PostgreSQL tidak melakukan penyortiran ganda untuk membentuk "jendela" dan menghitung kedua fungsi dalam satu simpul WindowAgg
- Perhitungan fungsi "malas" di bawah CASE mengurangi jumlah operasi yang dilakukan
Demikian pula, kami menggabungkan dua langkah berikut. Tetapi urutan "jendela" penghitungan agregat (klien, grpid) dan unikisasi (klien, jumlah (hari)) tidak sesuai, oleh karena itu masih ada dua Urutkan node di blok terakhir - sebelum WindowAgg dan sebelum Unik.
[lihat menjelaskan.tensor.ru]Saya perhatikan bahwa ketika rantai penomoran
, kondisi WHERE pertama kali dipenuhi , sehingga angka yang dihasilkan oleh
fungsi jendela berubah menjadi berurutan.