このトピックでは、ウィンドウ関数の構文について説明し、ウィンドウ関数の使用方法の例を示します。

概要

集計関数は行のグループの単一の結果を計算し、ウィンドウ関数はグループ内の各行の結果を計算します。 ウィンドウ関数には、パーティション、オーダー、フレームの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( xoffsetdefaut_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 
  • クエリ結果sum

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から
  • クエリ結果cume_dist

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
  • クエリ結果dense_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から
  • クエリ結果ntile

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から
  • クエリ結果percent_rank

ランク関数

パーティション内の各行のランクを計算します。 同じフィールド値を有する行には、同じランクが割り当てられる。 ランクは連続していません。 例えば、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
  • クエリ結果row_number

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から
  • クエリ結果first_value

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から
  • クエリ結果last_value

ラグ関数

各パーティションの現在の行の前の指定されたオフセットにある行の指定されたフィールドの値を返します。 オフセットは、offsetパラメーターで指定します。

構文

(xoffsetdefaut_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
      )
  • クエリ結果Window 関数

リード機能

各パーティションの現在の行の後の指定されたオフセットにある行の指定されたフィールドの値を返します。 オフセットは、offsetパラメーターで指定します。

構文

lead( xoffsetdefaut_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として
  • クエリ結果nth_value