Clickhouse - fungsi jendela yang tidak ada ...

Saya mulai bekerja dengan database kolumnar dengan BigQuery. Ketika saya harus "pindah" ke Clickhouse, saya terkejut dengan kurangnya fungsi jendela yang lengkap. Ada, tentu saja, banyak fungsi untuk bekerja dengan array, fungsi dengan urutan yang lebih tinggi, dan fungsi lainnya (satu fungsi runningDifferenceStartingWithFirstValue sepadan). Pemenang tahun 1999 untuk gelar kata terpanjang Donaudampfschifffahrtsgesellschaftskapitänswitwe segera terlintas dalam pikiran. Yang diterjemahkan dari bahasa Jerman sebagai "janda kapten perusahaan pelayaran di Danube."



Pencarian untuk "fungsi jendela di Clickhouse" tidak memberikan hasil yang berarti. Artikel ini merupakan upaya untuk meringkas data yang tersebar dari Internet, contoh dengan ClickHouseMeetup dan pengalaman saya sendiri.

Fungsi jendela - sintaks

Izinkan saya mengingatkan Anda tentang sintaks fungsi jendela dan jenis hasil yang kita dapatkan. Dalam contoh, kita akan menggunakan dialek Google BigQuery SQL Standart. Berikut ini tautan ke dokumentasi tentang fungsi jendela (mereka disebut fungsi analitik dalam dokumentasi - terjemahan yang lebih akurat terdengar seperti fungsi analitik). Dan inilah daftar fungsinya itu sendiri.



Sintaks umumnya terlihat seperti ini:

analytic_function_name ( [ argument_list ] ) OVER over_clause
over_clause:
  { named_window | ( [ window_specification ] ) }
window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]
window_frame_clause:
  { rows_range } { frame_start | frame_between }
rows_range:
  { ROWS | RANGE }

Mari kita selangkah demi selangkah:

1. Fungsi jendela diterapkan ke kumpulan data yang ditentukan dalam ekspresi over_clause,
2. Recordset ditentukan oleh klausa PARTITION BY. Di sini Anda dapat membuat daftar satu atau lebih bidang yang akan menentukan kumpulan data. Bekerja mirip dengan GROUP BY.
   
   Pengurutan rekaman dalam satu set ditentukan menggunakan ORDER BY.
3. Anda juga dapat membatasi kumpulan rekaman yang telah ditentukan sebelumnya sebagai jendela. Jendela dapat didefinisikan secara statis. Misalnya, Anda dapat mengambil 5 catatan sebagai jendela, 2 sebelum dan 2 setelah catatan saat ini dan catatan itu sendiri. Ini akan terlihat seperti ini: BARIS ANTARA 2 PRECEDING DAN 2 FOLLOWING.
   
   Contoh konstruksi untuk menentukan jendela yang didefinisikan secara dinamis terlihat seperti ini - BERBAGAI ANTARA BATAS YANG TEPAT TERBARU DAN BARIS SAAT INI. Konstruksi ini mendefinisikan jendela dari rekaman pertama hingga saat ini sesuai dengan urutan sortir yang ditentukan.

Sebagai contoh, perhatikan perhitungan jumlah kumulatif (contoh dari dokumentasi):

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_purchases
FROM Produce

Hasil:

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 2               |
| cabbage   | 9          | vegetable  | 11              |
| lettuce   | 10         | vegetable  | 21              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

Apa yang bisa dilakukan di Clickhouse

Mari coba ulangi contoh ini di ClickHouse. Tentu saja, ClickHouse memiliki fungsi runningAccumulate , arrayCumSum dan groupArrayMovingSum . Namun dalam kasus pertama, Anda perlu menentukan status dalam subkueri ( lebih detail ), dan dalam kasus kedua, fungsi mengembalikan larik, yang kemudian perlu diperluas.



Kami akan membuat kueri paling umum. Permintaan itu sendiri mungkin terlihat seperti ini:

SELECT
   items,
   summ as purchases,
   category,
   sumArray(cum_summ) as total_purchases
FROM (SELECT
         category,
         groupArray(item) AS items,
         groupArray(purchases) AS summ,
         arrayMap(x -> arraySlice(summ, 1, x), arrayEnumerate(summ)) AS cum_summ
     FROM (SELECT
               item,
               purchases,
               category
           FROM produce
           ORDER BY category, purchases)
     GROUP BY category)
   ARRAY JOIN items, summ, cum_summ
GROUP BY category, items, summ
ORDER BY category, purchases

Mari kita selangkah demi selangkah:

1. Pertama, kita membangun subkueri, di mana penyortiran data yang diperlukan dilakukan (ORDER BY kategori, pembelian). Pengurutan harus cocok dengan bidang dalam ekspresi PARTITION BY dan ORDER BY dari fungsi jendela.
2. , , PARTITION BY. item .
   
   purchases , summ .
3. — ArrayMap. , func arr.
   
   arr — [1, 2, …, length(summ)], arrayEnumerate.
   
   func — arraySlice(summ, 1, x), x — arr, . summ x. , cum_sum , , .
   
   
   
   ArrayMap arrayEnumerate , , . ( 3), ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING.
   
   
   
   

   arrayMap(x -> arraySlice(summ, if(x-1 > 0, x-1, 1), if(x-1 > 0, 3, 2)), arrayEnumerate(summ))

   
   
   , . 2 ClickHouse:
   
   
   
   
   • — [edited] — . [/edited]. , , arrayMap arrayFilter. . — ( — ) (alias) arrayMap, arrayFilter .
   • — . , , arrayReverse arraySlice.
   
   
   
4. Langkah terakhir adalah kita perlu memperluas array menjadi tabel menggunakan ARRAY JOIN. Kita juga perlu menerapkan fungsi agregat jumlah dengan pengubah -Array (sebagai hasilnya, fungsi agregat terlihat seperti sumArray) ke hasil yang dikembalikan oleh fungsi ArrayMap.

Keluaran

Dimungkinkan untuk meniru pengoperasian fungsi jendela di ClickHouse. Tidak terlalu cepat dan tidak terlalu cantik. Secara singkat, pipeline terdiri dari 3 langkah:

1. Kueri yang diurutkan. Langkah ini mempersiapkan kumpulan data.
2. Mengelompokkan ke dalam array dan melakukan operasi array. Langkah ini mendefinisikan jendela dari fungsi jendela kita.
3. Memperluas kembali ke tabel menggunakan fungsi agregat.