Simple Log Service:Window 関数

概要

集計関数は行のグループの単一の結果を計算し、ウィンドウ関数はグループ内の各行の結果を計算します。 ウィンドウ関数には、パーティション、オーダー、フレームの3つの要素があります。 詳細については、「ウィンドウ関数の概念と構文」をご参照ください。

function over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

• 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 詳細については、「ウィンドウ関数フレームの仕様」をご参照ください。

関数

集計関数

すべての集計関数をウィンドウ関数として使用できます。 集計関数の詳細については、「集計関数」をご参照ください。 次の例は、ウィンドウ関数として合計関数を使用する方法を示しています。

構文

sum() over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

パラメーター

戻り値のデータ型

ダブルタイプ。

例

各部門の各従業員給与の割合を計算します。

• クエリ文

  * |
  SELECT
    department,
    staff_name,
    salary,
    round ( salary * 1.0 / sum(salary) over(partition by department), 3) AS salary_percentage 

• クエリと分析結果

cume_dist関数

cume_dist関数は、パーティション内の各値の累積分布を計算します。 結果は除算を使用して得られます。 分子は、フィールド値が指定された行のフィールド値以下である行の数です。 指定された行もカウントされます。 分母は、パーティション内の行の総数です。 計算は、パーティション内の行の順序に基づいています。 戻り値の範囲は (0,1) です。

構文

cume_dist() over (
    [partition by partition_expression]
    [order by order_expression]
)

パラメーター

戻り値のデータ型

ダブルタイプ。

例

bucket00788という名前のOSSバケット内の各オブジェクトのサイズの累積分布を計算します。

• クエリ文

  bucket=bucket00788 |
  select
    object,
    object_size,
    cume_dist() over (
      partition by object
      order by
        object_size
    ) as cume_dist
  from  oss-log-store

  から

• クエリと分析結果

dense_rank関数

dense_rank関数は、パーティション内の指定されたフィールドの各値のランクを計算します。 2つの値が同じ場合、同じランクが割り当てられます。 ランクは連続しています。 例えば、2つの値に同じランク1が割り当てられる場合、次のランクは2である。

構文

dense_rank() over (
    [partition by partition_expression]
    [order by order_expression]
)

パラメーター

戻り値のデータ型

bigint型。

例

各部門の各従業員給与のランクを計算します。

• クエリ文

  * |
  select
    department,
    staff_name,
    salary,
    dense_rank() over(
      partition by department
      order by
        salary desc
    ) as salary_rank
  order by
    department,
    salary_rank

• クエリと分析結果

ntile関数

ntile関数は、各パーティションの行をNパラメーターで指定されたグループ数に分割します。

構文

ntile(n) over (
    [partition by partition_expression]
    [order by order_expression]
)

パラメーター

戻り値のデータ型

bigint型。

例

各パーティションの行を3つのグループに分割します。

• クエリ文

  object=245-da918c.model |
  select
    object,
    object_size,
    ntile(3) over (
      partition by object
      order by
        object_size
    ) as ntile
  from  oss-log-store

  から

• クエリと分析結果

percent_rank関数

percent_rank関数は、パーティション内の各行のパーセンテージランキングを計算します。 計算式は、(rank − 1) / (total_rows − 1) である。 数式では、rankは現在の行のランクを表し、total_rowsはパーティション内の行の総数を表します。

構文

percent_rank() over (
    [partition by partition_expression]
    [order by order_expression]
)

パラメーター

戻り値のデータ型

ダブルタイプ。

例

各OSSオブジェクトのサイズのパーセンテージランキングを計算します。

• クエリ文

  object=245-da918c3e2dd9dc9cb4d9283b%2F555e2441b6a4c7f094099a6dba8e7a5f.model|
  select
    object,
    object_size,
   percent_rank() over (
      partition by object
      order by
        object_size
    ) as ntile
  FROM  oss-log-store

  から

• クエリと分析結果

ランク関数

rank関数は、パーティション内の指定されたフィールドの各値のランクを計算します。 2つの値が同じ場合、同じランクが割り当てられます。 ランクは連続していません。 例えば、2つの値に同じランク1が割り当てられる場合、次のランクは3である。

構文

rank() over (
    [partition by partition_expression]
    [order by order_expression]
)

パラメーター

戻り値のデータ型

bigint型。

例

各部門の各従業員給与のランクを計算します。

• クエリ文

  * |
  select
    department,
    staff_name,
    salary,
    rank() over(
      partition by department
      order by
        salary desc
    ) as salary_rank
  order by
    department,
    salary_rank

• クエリと分析結果

row_number関数

row_number関数は、パーティション内の指定されたフィールドの各値のランクを計算します。 各値には一意のランクが割り当てられます。 ランクは1から始まります。

構文

row_number() over (
    [partition by partition_expression]
    [order by order_expression]
)

パラメーター

戻り値のデータ型

bigint型。

例

各部門の各従業員給与のランクを計算します。

• クエリ文

  * |
  select
    department,
    staff_name,
    salary,
    row_number() over(
      partition by department
      order by
        salary desc
    ) as salary_rank
  order by
    department,
    salary_rank

• クエリと分析結果

first_value関数

first_value関数は、各パーティションの最初の行の指定されたフィールドの値を返します。

構文

first_value(x) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

パラメーター

戻り値のデータ型

データ型は、xパラメーターのデータ型と同じです。

例

指定されたOSSバケット内の各オブジェクトの最小サイズを返します。

• クエリ文

  bucket :bucket90 |
  select
    object,
    object_size,
    first_value(object_size) over (
      partition by object
      order by
        object_size 
       range between unbounded preceding and unbounded following
    ) as first_value
  from  oss-log-store

  から

• クエリと分析結果

last_value関数

last_value関数は、各パーティションの最後の行の指定されたフィールドの値を返します。

構文

last_value(x) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

パラメーター

戻り値のデータ型

データ型は、xパラメーターのデータ型と同じです。

例

指定されたOSSバケット内の各オブジェクトの最大サイズを返します。

• クエリ文

  bucket :bucket90 |
  select
    object,
    object_size,
    last_value(object_size) over (
      partition by object
      order by
        object_size 
       range between unbounded preceding and unbounded following
    ) as last_value
  from  oss-log-store

  から

• クエリと分析結果

ラグ関数

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

構文

lag(x, offset, default_value) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

パラメーター

戻り値のデータ型

データ型は、xパラメーターのデータ型と同じです。

例

ウェブサイトへの毎日のユニークビジター (UV) を数え、前日のUVの増加率を計算します。

• クエリ文

  * |
  select
    day,
    UV,
    UV * 1.0 /(lag(UV, 1, 0) over()) as diff_percentage
  from  (
      select
        approx_distinct(client_ip) as UV,
        date_trunc('day', __time__) as day
      from  log
      group by
        day
      order by
        day asc
    )

• クエリと分析結果

リード関数

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

構文

lead(x, offset, default_value) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

パラメーター

戻り値のデータ型

データ型は、xパラメーターのデータ型と同じです。

例

2021-08-26にウェブサイトへの1時間ごとのユニークビジター (UV) を数え、2時間連続のUV間のパーセンテージの差を計算します。

• クエリ文

  * |
  select
    time,
    UV,
    UV * 1.0 /(lead(UV, 1, 0) over()) as diff_percentage
  from  (
      select
        approx_distinct(client_ip) as uv,
        date_trunc('hour', __time__) as time
      from    log
      group by
        time
      order by
        time asc
    )

• クエリと分析結果

nth_value関数

nth_value関数は、パーティションの先頭から指定されたオフセットにある行の指定されたフィールドの値を返します。

構文

nth_value(x, offset) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

パラメーター

戻り値のデータ型

データ型は、xパラメーターのデータ型と同じです。

例

各部門で2番目に給与が高い従業員を返します。

• クエリ文

  * |
  select
    department,
    staff_name,
    salary,
    nth_value(staff_name, 2) over(
      partition by department
      order by
        salary desc
        range between unbounded preceding and unbounded following
    ) as second_highest_salary from log

• クエリと分析結果