このトピックでは、ウィンドウ関数の構文について説明し、ウィンドウ関数の使用方法の例を示します。
概要
集計関数は行のグループの単一の結果を計算し、ウィンドウ関数はグループ内の各行の結果を計算します。 ウィンドウ関数には、パーティション、オーダー、フレームの3つの要素があります。 詳細については、「ウィンドウ関数の概念と構文」をご参照ください。
function over (
[partition_expressionによるパーティション]
[order_expressionによる注文]
[フレーム]
)
- Partition: partition要素は、PARTITION by句によって定義されます。 PARTITION BY句は、行をパーティションに分離します。 PARTITION BY句を指定しない場合、すべての行は単一のパーティションとして扱われます。
- Order: order要素は、ORDER by句によって定義されます。 ORDER BY句は、すべてのパーティションの行をソートします。 説明 ORDER BY句を使用して同じ値を持つフィールドの行を並べ替える場合、これらの行の順序は非決定的です。 ORDER BY句に追加のフィールドを含めて、これらの行の予想される順序を取得できます。 例:
order by request_time, request_method
- Frame: frame要素はFRAME句で定義されます。 FRAME句は、各パーティションのサブセットを指定します。 フレームはさらに、各パーティション内の行を洗練する。 ランキング関数にFRAME句を指定することはできません。 FRAME句の構文:
{ rows | range} { frame_start | frame_between }
例:unbounded preceding and unbounded following
詳細については、「ウィンドウ関数フレームの仕様」をご参照ください。
Window 関数
カテゴリ | 機能 | 構文 | 説明 |
---|---|---|---|
集計関数 | 集計関数 | なし | すべての集計関数をウィンドウ関数として使用できます。 集計関数の詳細については、「集約関数」をご参照ください。 |
ランキング関数 | cume_dist関数 | cume_dist() | パーティション内の各値の累積分布を計算します。 結果は除算を使用して得られます。 分子は、フィールド値が指定された行のフィールド値以下である行の数です。 指定された行もカウントされます。 分母は、パーティション内の行の総数です。 計算は、パーティション内の行の順序に基づいています。 値の範囲: (0,1) 。 |
dense_rank関数 | dense_rank() | パーティション内の各値のランクを計算します。 同じフィールド値を有する行には、同じランクが割り当てられる。 ランクは連続しています。 例えば、2つの行が同じランク1を有する場合、次の行のランクは2である。 | |
ntile関数 | ntile( n) | 各パーティションの行を、Nパラメーターで指定されたグループ数に分割します。 | |
percent_rank関数 | percent_rank() | パーティション内の各行のパーセンテージランキングを計算します。 | |
ランク関数 | rank() | パーティション内の各行のランクを計算します。 同じフィールド値を有する行には、同じランクが割り当てられる。 ランクは連続していません。 例えば、2つの行が同じランク1を有する場合、次の行のランクは3である。 | |
row_number関数 | row_number() | パーティション内の各行のランクを計算します。 ランクは一意であり、1から始まります。 同じ値を有する行には、連続するランクが割り当てられる。 たとえば、同じフィールド値を持つ3つの行には、ランク1、2、および3が割り当てられます。 | |
オフセット関数 | first_value関数 | first_value(x) | 各パーティションの最初の行にある指定されたフィールドの値を返します。 |
last_value関数 | last_value(x) | 各パーティションの最後の行にある指定されたフィールドの値を返します。 | |
ラグ関数 | ラグ (x、オフセット、defaut_値) | Returns値の指定されたフィールド行に指定されたオフセット電流各パーティションの行の前に。 オフセットは、offsetパラメーターで指定します。 現在の行の前の指定されたオフセットに行が存在しない場合、defaut_valueパラメーターで指定された値がパーティションに返されます。 | |
リード機能 | lead( x 、offset、defaut_value) | 各パーティションの現在の行の後の指定されたオフセットにある行の指定されたフィールドの値を返します。 オフセットは、offsetパラメーターで指定します。 現在の行の後の指定されたオフセットに行が存在しない場合、defaut_valueで指定された値がパーティションに返されます。 | |
nth_value関数 | nth_value( x 、オフセット) | 各パーティションの先頭から指定されたオフセットにある行の指定されたフィールドの値を返します。 オフセットは、offsetパラメーターで指定します。 |
集計関数
すべての集計関数をウィンドウ関数として使用できます。 集計関数の詳細については、「集約関数」をご参照ください。 次の例は、ウィンドウ関数として合計関数を使用する方法を示しています。
構文
sum() over ()
[partition_expressionによるパーティション]
[order_expressionによる注文]
[フレーム]
)
パラメーター
項目 | 説明 |
---|---|
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
order_expressionによる注文 | order_expressionパラメーターの値に基づいて、各パーティションの行の順序を指定します。 |
フレーム | 各パーティションのサブセットを指定します。 例: unbounded preceding and unbounded following |
戻り値のデータ型
ダブルデータ型。
例
各部門の各従業員給与の割合を計算します。
- クエリ文
* | 選択 部門、 staff_name, 給料、 ラウンド (サラリー * 1.0 /合計 (サラリー) オーバー (部門別のパーティション) 、3) AS salary_percentage
- クエリ結果
cume_dist関数
パーティション内の各値の累積分布を計算します。 結果は除算を使用して得られます。 分子は、フィールド値が指定された行のフィールド値以下である行の数です。 指定された行もカウントされます。 分母は、パーティション内の行の総数です。 計算は、パーティション内の行の順序に基づいています。 値の範囲: (0,1) 。
構文
cume_dist() over ()
[partition_expressionによるパーティション]
[order_expressionによる注文]
)
パラメーター
項目 | 説明 |
---|---|
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
order_expressionによる注文 | 各パーティションの行の順序を指定します。 行は、order_expressionパラメーターの値に基づいて順序付けられます。 |
戻り値のデータ型
ダブルデータ型。
例
bucket00788という名前のOSSバケット内の各オブジェクトのサイズの累積分布を計算します。
- クエリ文
bucket=bucket00788 | 選択 オブジェクト、 object_size, cume_dist() over () オブジェクトごとのパーティション 注文する object_size ) cume_distとして oss-log-storeから
- クエリ結果
dense_rank関数
パーティション内の各値のランクを計算します。 同じフィールド値を有する行には、同じランクが割り当てられる。 ランクは連続しています。 例えば、2つの行が同じランク1を有する場合、次の行のランクは2である。
構文
dense_rank() over ()
[partition_expressionによるパーティション]
[order_expressionによる注文]
)
パラメーター
項目 | 説明 |
---|---|
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
order_expressionによる注文 | order_expressionパラメーターの値に基づいて、各パーティションの行の順序を指定します。 |
戻り値のデータ型
bigintデータ型。
例
各部門の各従業員給与のランクを計算します。
- クエリ文
* | 選択 部門、 staff_name, 給料、 dense_rank() over () 部門別のパーティション 注文する 給与desc ) salary_rankとして による注文 部門、 salary_rank
- クエリ結果
ntile関数
各パーティションの行をいくつかのグループに分割します。 グループの数は、Nパラメータによって指定される。
構文
ntile( n) over (
[partition_expressionによるパーティション]
[order_expressionによる注文]
)
パラメーター
項目 | 説明 |
---|---|
n | グループの数を指定します。 |
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
order_expressionによる注文 | order_expressionパラメーターの値に基づいて、各パーティションの行の順序を指定します。 |
戻り値のデータ型
bigintデータ型。
例
各パーティションの行を3つのグループに分割します。
- クエリ文
オブジェクト=245-da918c.model | 選択 オブジェクト、 object_size, ntile(3) オーバー ( オブジェクトごとのパーティション 注文する object_size ) ntileとして oss-log-storeから
- クエリ結果
percent_rank関数
パーティション内の各行のパーセンテージランキングを計算します。 計算式は、(rank − 1)/(total_rows − 1)
である。 数式では、rankは現在の行のランクを表し、total_rowsはパーティション内の行の総数を表します。
構文
percent_rank() オーバー ()
[partition_expressionによるパーティション]
[order_expressionによる注文]
)
パラメーター
項目 | 説明 |
---|---|
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
order_expressionによる注文 | order_expressionパラメーターの値に基づいて、各パーティションの行の順序を指定します。 |
戻り値のデータ型
ダブルデータ型。
例
各OSSオブジェクトのサイズのパーセンテージランキングを計算します。
- クエリ文
object=245-da918c3e2dd9dc9cb4d9283b % 2F555e2441b6a4c7f094099a6dba8e7a5f.mo del | 選択 オブジェクト、 object_size, percent_rank() オーバー () オブジェクトごとのパーティション 注文する object_size ) ntileとして oss-log-storeから
- クエリ結果
ランク関数
パーティション内の各行のランクを計算します。 同じフィールド値を有する行には、同じランクが割り当てられる。 ランクは連続していません。 例えば、2つの行が同じランク1を有する場合、次の行のランクは3である。
構文
rank() オーバー ()
[partition_expressionによるパーティション]
[order_expressionによる注文]
)
パラメーター
項目 | 説明 |
---|---|
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
order_expressionによる注文 | order_expressionパラメーターの値に基づいて、各パーティションの行の順序を指定します。 |
戻り値のデータ型
bigintデータ型。
例
各部門の各従業員給与のランクを計算します。
- クエリ文
* | 選択 部門、 staff_name, 給料、 rank() オーバー () 部門別のパーティション 注文する 給与desc ) salary_rankとして による注文 部門、 salary_rank
- クエリ結果
row_number関数
パーティション内の各行のランクを計算します。 ランクは一意であり、1から始まります。
構文
row_number() オーバー ()
[partition_expressionによるパーティション]
[order_expressionによる注文]
)
パラメーター
項目 | 説明 |
---|---|
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
order_expressionによる注文 | 各パーティションの行の順序を指定します。 行は、order_expressionパラメーターの値に基づいて順序付けられます。 |
戻り値のデータ型
bigintデータ型。
例
各部門の給与で各従業員のランクを計算します。
- クエリ文
* | 選択 部門、 staff_name, 給料、 row_number() オーバー () 部門別のパーティション 注文する 給与desc ) salary_rankとして による注文 部門、 salary_rank
- クエリ結果
first_value関数
各パーティションの最初の行にある指定されたフィールドの値を返します。
構文
first_value(x) over (
[partition_expressionによるパーティション]
[order_expressionによる注文]
[フレーム]
)
パラメーター
項目 | 説明 |
---|---|
x | フィールド名。 フィールドは任意のデータ型にすることができます。 |
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
による注文注文_表現 | order_expressionパラメーターの値に基づいて、各パーティションの行の順序を指定します。 |
フレーム | 各パーティションのサブセットを指定します。 例: unbounded preceding and unbounded following |
戻り値のデータ型
データ型は、xパラメーターのデータ型と同じです。
例
指定されたOSSバケット内の各オブジェクトの最小サイズを返します。
- クエリ文
バケット: bucket90 | 選択 オブジェクト、 object_size, first_value(object_size) を超える ( オブジェクトごとのパーティション 注文する object_size 無制限の先行と無制限の後続の間の範囲 ) としてfirst_value oss-log-storeから
- クエリ結果
last_value関数
各パーティションの最後の行にある指定されたフィールドの値を返します。
構文
last_value(x) over (
[partition_expressionによるパーティション]
[order_expressionによる注文]
[フレーム]
)
パラメーター
項目 | 説明 |
---|---|
x | フィールド名。 フィールドは任意のデータ型にすることができます。 |
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
order_expressionによる注文 | order_expressionパラメーターの値に基づいて、各パーティションの行の順序を指定します。 |
フレーム | 各パーティションのサブセットを指定します。 例: unbounded preceding and unbounded following |
戻り値のデータ型
データ型は、xパラメーターのデータ型と同じです。
例
指定されたOSSバケット内の各オブジェクトの最大サイズを返します。
- クエリ文
バケット: bucket90 | 選択 オブジェクト、 object_size, last_value(object_size) を超える ( オブジェクトごとのパーティション 注文する object_size 無制限の先行と無制限の後続の間の範囲 ) last_valueとして oss-log-storeから
- クエリ結果
ラグ関数
各パーティションの現在の行の前の指定されたオフセットにある行の指定されたフィールドの値を返します。 オフセットは、offsetパラメーターで指定します。
構文
(x、offset、defaut_value) over (
[partition_expressionによるパーティション]
[order_expressionによる注文]
[フレーム]
)
パラメーター
項目 | 説明 |
---|---|
x | フィールド名。 フィールドは任意のデータ型にすることができます。 |
オフセット | パーティション内の現在の行の前のオフセット。 offsetパラメーターの値が0の場合、現在の行の指定されたフィールドの値が返されます。 |
defaut_value | 現在の行の前の指定されたオフセットに行が存在しない場合、defaut_valueパラメーターの値が返されます。 |
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
order_expressionによる注文 | order_expressionパラメーターの値に基づいて、各パーティションの行の順序を指定します。 |
フレーム | 各パーティションのサブセットを指定します。 例: unbounded preceding and unbounded following |
戻り値のデータ型
データ型は、xパラメーターのデータ型と同じです。
例
ウェブサイトへの毎日のユニークビジター (UV) を数え、前日のUVの増加率を計算します。
- クエリ文
* | 選択 日, UV, UV * 1.0 /(ラグ (UV, 1,0) over()) as diff_percentage から ( 選択 approx_distinct(client_ip) をUVとして、 date_trunc('day', __time__) as day ログから グループによって day 注文する 日asc )
- クエリ結果
リード機能
各パーティションの現在の行の後の指定されたオフセットにある行の指定されたフィールドの値を返します。 オフセットは、offsetパラメーターで指定します。
構文
lead( x 、offset、defaut_value) over (
[partition_expressionによるパーティション]
[order_expressionによる注文]
[フレーム]
)
パラメーター
項目 | 説明 |
---|---|
x | フィールド名。 フィールドは任意のデータ型にすることができます。 |
オフセット | パーティション内の現在の行の後のオフセット。 offsetパラメーターの値が0の場合、現在の行の指定されたフィールドの値が返されます。 |
defaut_value | 現在の行の後の指定されたオフセットに行が存在しない場合、defaut_valueパラメーターの値が返されます。 |
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
order_expressionによる注文 | order_expressionパラメーターの値に基づいて、各パーティションの行の順序を指定します。 |
フレーム | 各パーティションのサブセットを指定します。 例: unbounded preceding and unbounded following |
戻り値のデータ型
データ型は、xパラメーターのデータ型と同じです。
例
2021-08-26にウェブサイトへの1時間ごとのユニークビジター (UV) を数え、2時間連続のUV間のパーセンテージの差を計算します。
- クエリ文
* | 選択 時間, UV, UV * 1.0 /(リード (UV, 1,0) over()) as diff_percentage から ( 選択 approx_distinct(client_ip) としてuv、 date_trunc('hour', __time__) as time ログから グループによって time 注文する asc時間 )
- クエリ結果
nth_value関数
各パーティションの先頭から指定されたオフセットにある行の指定されたフィールドの値を返します。 オフセットは、offsetパラメーターで指定します。
構文
nth_value( x 、オフセット) over (
[partition_expressionによるパーティション]
[order_expressionによる注文]
[フレーム]
)
パラメーター
項目 | 説明 |
---|---|
x | フィールド名。 フィールドは任意のデータ型にすることができます。 |
オフセット | パーティションの先頭からのオフセット。 |
partition_expressionによるパーティション | partition_expressionパラメーターの値に基づいて行をパーティション分割する方法を指定します。 |
order_expressionによる注文 | order_expressionパラメーターの値に基づいて、各パーティションの行の順序を指定します。 |
フレーム | 各パーティションのサブセットを指定します。 例: unbounded preceding and unbounded following |
戻り値のデータ型
データ型は、xパラメーターのデータ型と同じです。
例
各部門で2番目に給与が高い従業員を返します。
- クエリ文
* | 選択 部門、 staff_name, 給料、 nth_value(staff_name、2) ( 部門別のパーティション 注文する 給与desc 無制限の先行と無制限の後続の間の範囲 ) ログからsecond_highest_salaryとして
- クエリ結果