Clever Geek Handbook

Clickhouse-存在しないウィンドウ機能..。

BigQueryを使用して列データベースの操作を開始しました。Clickhouseに「移動」しなければならなかったとき、本格的なウィンドウ機能がないことに不愉快に驚きました。もちろん、配列を操作するための多くの関数、高次の関数、およびその他の関数があります（1つの関数runningDifferenceStartingWithFirstValueはそれだけの価値があります）。最長の単語Donaudampfschifffahrtsgesellschaftskapitänswitweのタイトルで1999年の勝者がすぐに思い浮かびます。これはドイツ語から「ドナウ川の船長の未亡人」と訳されています。



「Clickhouseのウィンドウ機能」を検索しても、意味のある結果は返されません。この記事は、インターネットからの散在するデータ、ClickHouseMeetupの例、および私自身の経験を要約する試みです。



ウィンドウ関数-構文



ウィンドウ関数の構文と、得られる結果の種類を思い出させてください。例では、Standart SQL GoogleBigQueryダイアレクトを使用します。ここにあるリンクウィンドウ関数についてのドキュメントには、（ -分析関数のような、より正確な翻訳音彼らはドキュメントの分析関数と呼ばれています）。そして、ここにある機能自体のリストが。



一般的な構文は次のようになります。 



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 }


ステップバイステップでそれを取りましょう：



  1. ウィンドウ関数は、over_clause式で定義されたレコードセットに適用されます。
  2. レコードセットは、PARTITIONBY句によって定義されます。ここでは、レコードセットを決定するための1つ以上のフィールドを一覧表示できます。GROUPBYと同様に機能します。

    セット内のレコードの並べ替えは、ORDERBYを使用して定義されます。
  3. さらに、事前定義されたレコードのセットをウィンドウとして制限できます。ウィンドウは静的に定義できます。たとえば、現在のレコードと現在のレコード自体の前後に2つずつ、ウィンドウとして5つのレコードを取得できます。次のようになります。2つの先行と2つの後続の間の行。

    動的に定義されたウィンドウを指定するための構成の例は、次のようになります-境界のない前の行と現在の行の間の範囲。この構成は、指定されたソート順に従って、最初のレコードから現在のレコードまでのウィンドウを定義します。


例として、累積合計の計算について考えてみます（ドキュメントの例）。 



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


結果： 



+-------------------------------------------------------+
| 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              |
+-------------------------------------------------------+


Clickhouseでできること



ClickHouseでこの例を繰り返してみましょう。もちろん、ClickHouseにはrunningAccumulate、arrayCumSum、およびgroupArrayMovingSum関数があります。ただし、最初のケースでは、サブクエリで状態を判別する必要があり（詳細）、2番目のケースでは、関数が配列を返し、それを展開する必要があります。



最も一般的なクエリを作成します。リクエスト自体は次のようになります。 



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


ステップバイステップでそれを取りましょう：



  1. まず、必要なデータの並べ替えが行われるサブクエリを作成します（ORDER BYカテゴリ、購入）。ソートは、ウィンドウ関数のPARTITIONBY式およびORDERBY式のフィールドと一致する必要があります。
  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. 最後のステップは、ARRAYJOINを使用して配列をテーブルに展開する必要があることです。また、-Array修飾子を指定した合計集計関数（結果として、集計関数はsumArrayのようになります）をArrayMap関数によって返される結果に適用する必要があります。


出力



ClickHouseでウィンドウ関数の操作をエミュレートすることができます。あまり速くなく、あまりきれいでもありません。簡単に言うと、パイプラインは3つのステップで構成されています。



  1. ソートされたクエリ。このステップでは、レコードセットを準備します。
  2. 配列にグループ化し、配列操作を実行します。このステップでは、ウィンドウ関数のウィンドウを定義します。
  3. 集計関数を使用してテーブルに展開し直します。


More articles:


All Articles