Simple Log Service:ウィンドウ関数

概要

集計関数は行を 1 つの結果に集約しますが、ウィンドウ関数は関連する行のセットに基づき、各行に対して結果を計算します。ウィンドウ関数には、パーティション、順序、フレームという 3 つのコア要素があります (ウィンドウ関数の概念と構文)。

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

• パーティション： partition by 句は、行をパーティションに分割します。この句を省略した場合、結果セット全体が 1 つのパーティションとして扱われます。

• 順序： order by 句は、各パーティション内の行をソートします。

  説明

  重複する値を含む列に対して order by 句を使用すると、行の順序が非決定的になります。一貫したソート順序を確保するには、複数の列を指定してください。例： order by request_time, request_method。

• フレーム： パーティション内の行を制限します。ランキング関数では使用できません。構文： { rows | range} { frame_start | frame_between }。例： range between unbounded preceding and unbounded following。ウィンドウ関数のフレーム指定。

関数

集計関数

すべての集計関数は、ウィンドウ関数として使用できます。次の例では、 sum() を使用します。

構文

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

パラメーター

戻り値のデータ型

double

例

各従業員の給与を、所属部署の給与総額に対する割合として計算します。

• クエリ文

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

• クエリ結果： dev 部署の 4 名の従業員と、 Marketing 部署の 3 名の従業員のデータが表示されます。 salary_percentage 列には、各従業員の給与が所属部署の給与総額に占める割合が示されます。

Cume_dist 関数

パーティション内における値の累積分布を計算します。これは、現在の行の値以下の行数を総行数で割った比率です。範囲 (0, 1] の値を返します。

構文

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

パラメーター

戻り値のデータ型

double

例

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

• クエリ文

  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 関数

パーティション内の値のランクを返します。タイは同じランクになり、ランクはギャップなしで連続します。たとえば、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

• クエリと分析の結果：マーケティング部門では、Blan と Smith は同じランク 1 (給与 9000) で、Achilles はランク 2 (8000) です。開発部門では、Rob はランク 1 (9000)、Blan はランク 2 (8500)、Sansa はランク 3 (8000) です。

Ntile 関数

パーティション内で順序付けされた行を、指定された数のグループに分割します。

構文

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

• クエリと分析結果： クエリは 9 行を返します。 object_size の値は、昇順で 3396、3701、3750、3757、3914、3918、7440、7490、7521 です。 ntile 列の対応する値は、1、1、1、2、2、2、3、3、3 です。 結果として、ntile(3) は 9 行を、3 行ずつ 3 つのグループに均等に分割します。

PERCENT_RANK 関数

パーティション内の各行のパーセントランクを計算します。 数式： (rank - 1) / (total_rows - 1)。ここで、rank は現在の行のランク、total_rows はパーティションサイズです。

構文

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

パラメーター

戻り値のデータ型

double

例

OSS オブジェクトのパーセントランクをサイズ別に計算します。

• クエリ文

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

• クエリと分析結果： object_size でソートされた 6 行が表示され、pr 列には、0.0 から 1.0 まで均等に分布したパーセントランク値が表示されます。

RANK 関数

パーティション内の各行のランクを返します。同順位の場合は同じランクになり、シーケンスにギャップが生じます。たとえば、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

• クエリと分析の結果：Marketing では、2 人の従業員がランク 1 (給与 9000) です。次のランクは 3 となり、同順位によってシーケンスにギャップが生じることを示しています。

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

• 6 行の結果 (一例)： Marketing 部署では、Blan Stark (9000) が 1 行目、Smith (9000) が 2 行目、Achilles (8000) が 3 行目になります。給与が同額のため、Blan Stark と Smith の順序は非決定的です。dev 部署では、Rob (9000) が 1 行目、Blan (8500) が 2 行目、Sansa (8000) が 3 行目です。

FIRST_VALUE 関数

各パーティション内の最初の行の値を返します。

構文

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

パラメーター

戻り値のデータ型

x と同じデータ型を返します。

例

この例は、FIRST_VALUE 関数を使用して、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

• クエリと分析の結果： object によって 3 つのグループに分割された 7 行。各グループ内で、first_value は最小の object_size (昇順での最初の行) と等しくなります。

Last_value 関数

パーティション内の最後の行の値を返します。

構文

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

パラメーター

戻り値のデータ型

x と同じ型です。

例

order by 句と組み合わせて last_value 関数を使用し、指定された 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

• クエリは、object、object_size、last_value の 3 つの列を返します。 245-da918c.model の場合、7 つの行で object_size は 2383 から 6936 まで表示され、すべての行で last_value は 6936 です。 dashboard%2F2020%2F05%2F20%2F16%2F47.csv の場合、2 つの行で object_size は 2435 と 2603 で、last_value は 2603 です。

LAG 関数

パーティション内で、現在の行より offset 行前の行の値を返します。

構文

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

パラメーター

戻り値のデータ型

x と同じ型です。

例

日次 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
    )

• クエリと分析結果：2021 年 8 月 2 日〜 9 日の 8 件のレコードです。1 行目の diff_percentage は、lag 関数がデフォルト値の 0 を返すため、ゼロ除算が発生し、結果は Infinity となります。2 行目以降には、当日の UV と前日の UV の比率 (例：2.098、0.976) が表示されます。

LEAD 関数

パーティション内で、現在の行から指定された offset 行後にある行の値を返します。

構文

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

パラメーター

戻り値のデータ型

x と同じデータ型の値を返します。

例

2021 年 8 月 26 日の時間別ユニークビジター比率を計算します。

• クエリ文

  * |
  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
    )

• クエリと分析の結果：クエリは 8 行を返し、2021 年 8 月 26 日の 00:00 から 07:00 までの時間別ユニークビジター (UV) と、次の時間のユニークビジターに対する現在の時間のユニークビジターの比率 (diff_percentage) を示します。

nth_value 関数

パーティション内の offset 番目の行の値を返します。

構文

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

• クエリ結果： second_highest_salary 列には、dev 部門の各行に Blan が、Marketing 部門の各行に San が表示されます。