ウィンドウ関数
ウィンドウ関数を使用すると、現在の行に関連する行のセットに対して計算を行うことができます。できる計算のいくつかは集約関数で実行できるものに似ていますが、ウィンドウ関数は行を単一の出力にグループ化することはありません - 個々の行は依然として返されます。
標準ウィンドウ関数
ClickHouseは、ウィンドウおよびウィンドウ関数を定義するための標準構文をサポートしています。以下の表は、機能が現在サポートされているかどうかを示しています。
| 機能 | サポートされていますか？ |
|---|---|
アドホックウィンドウ指定（count(*) over (partition by id order by time desc)） | ✅ |
ウィンドウ関数を含む式（例：(count(*) over ()) / 2） | ✅ |
WINDOW句（select ... from table window w as (partition by id)） | ✅ |
ROWSフレーム | ✅ |
RANGEフレーム | ✅（デフォルト） |
DateTimeのRANGE OFFSETフレームに対するINTERVAL構文 | ❌（代わりに秒数を指定してください（RANGEは任意の数値型で動作します）。） |
GROUPSフレーム | ❌ |
フレーム内で集約関数を計算する（sum(value) over (order by time)） | ✅（すべての集約関数がサポートされています） |
rank()、dense_rank()、row_number() | ✅ エイリアス: denseRank() |
percent_rank() | ✅ データセット内のパーティション内での値の相対的な地位を効率的に計算します。この関数は、ifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) / nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0) と記述されるより冗長で計算リソースの多くを必要とする手動SQL計算に効果的に置き換わります。エイリアス: percentRank() |
lag/lead(value, offset) | ❌ 次の回避策のいずれかを使用できます： 1) any(value) over (.... rows between <offset> preceding and <offset> preceding)、またはleadにはfollowingを使います。 2) lagInFrame/leadInFrameは類似していますが、ウィンドウフレームを考慮します。lag/leadと同じ動作を得るには、rows between unbounded preceding and unbounded followingを使用します。 |
| ntile(buckets) | ✅ ウィンドウを次のように指定します（partition by x order by y rows between unbounded preceding and unbounded following）。 |
ClickHouse特有のウィンドウ関数
次のClickHouse特有のウィンドウ関数もあります。
nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])
指定されたmetric_columnの非負微分をtimestamp_columnによって計算します。 INTERVALは省略可能で、デフォルトはINTERVAL 1 SECONDです。計算された値は各行について次のようになります：
- 1行目の場合は
0、 - for 行。
構文
PARTITION BY- 結果セットをグループに分割する方法を定義します。ORDER BY- 集約関数の計算中にグループ内の行を整理する方法を定義します。ROWS or RANGE- フレームの境界を定義します。集約関数はフレーム内で計算されます。WINDOW- 複数の式が同じウィンドウ定義を使用できるようにします。
関数
これらの関数はウィンドウ関数としてのみ使用できます。
row_number()- 現在の行に1から始まる番号を付けます。first_value(x)- 順序付けされたフレーム内で評価された最初の値を返します。last_value(x)- 順序付けされたフレーム内で評価された最後の値を返します。nth_value(x, offset)- 順序付けされたフレーム内でのn行目（オフセット）に対して評価された最初の非NULL値を返します。rank()- 隙間をあけてパーティション内の現在の行にランクを付けます。dense_rank()- 隙間をあけずにパーティション内の現在の行にランクを付けます。lagInFrame(x)- 順序付けられたフレーム内で、現在の行の前の指定された物理オフセット行で評価された値を返します。leadInFrame(x)- 順序付けられたフレーム内で、現在の行の後のオフセット行で評価された値を返します。
例
ウィンドウ関数がどのように使用されるかのいくつかの例を見てみましょう。
行に番号を付ける
集約関数
各プレイヤーの給与をチームの平均と比較します。
各プレイヤーの給与をチームの最大給与と比較します。
列によるパーティショニング
フレーム境界
実世界の例
以下の例は、一般的な実世界の問題を解決します。
部門ごとの最大/合計給与
累積和
移動/スライディング平均（3行ごと）
移動/スライディング平均（10秒ごと）
移動/スライディング平均（10日ごと）
温度は秒単位の精度で保存されますが、RangeおよびORDER BY toDate(ts)を使用することで、10ユニットのサイズのフレームを形成し、toDate(ts)のために単位は日となります。
参考文献
GitHub Issues
ウィンドウ関数の初期サポートのロードマップはこちらをご覧ください。
ウィンドウ関数に関連するすべてのGitHubの問題は、comp-window-functionsタグを持っています。
テスト
これらのテストには、現在サポートされている文法の例が含まれています：
https://github.com/ClickHouse/ClickHouse/blob/master/tests/performance/window_functions.xml
Postgres Docs
https://www.postgresql.org/docs/current/sql-select.html#SQL-WINDOW
https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
https://www.postgresql.org/docs/devel/functions-window.html
https://www.postgresql.org/docs/devel/tutorial-window.html
MySQL Docs
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html
https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html