Simple Log Service：視窗函數

簡介

普通的彙總函式只能用來計算一行內的結果或把所有行彙總成一行結果，而視窗函數支援為每一行產生一個結果。視窗函數包含分區、排序和架構這3個核心元素。更多資訊，請參見Window Function Concepts and Syntax。

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

• 分區：分區元素由partition by子句定義。partition by子句用於劃分視窗分區，如果沒有指定partition by子句，則整個查詢與分析結果集作為一個視窗分區。

• 排序：排序元素由order by子句定義。order by子句用於對視窗分區內的行進行排序。

  說明

  使用order by子句對重複的數值進行排序時，排序結果不穩定。如果您希望每次排序結果相同，可指定多個列進行排序。例如order by request_time, request_method。

• 架構：架構元素在視窗分區內對行進一步限制。架構元素不適用於次序函數。架構子句的文法為{ rows | range} { frame_start | frame_between }，例如range between unbounded preceding and unbounded following。更多資訊，請參見Window Function Frame Specification。

函數列表

彙總函式

所有彙總函式都支援在視窗函數中使用。彙總函式列表請參見彙總函式。此處以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 

• 查詢和分析結果：查詢結果返回7條記錄，包含 department、staff_name、salary、salary_percentage 四列。其中 dev 部門4人（Rob/9000/0.277、Blan/8500/0.262、Sansa/8000/0.246、Snow/7000/0.215），Marketing 部門3人（Achilles/8500/0.362、San/8000/0.340、Blan/7000/0.298），salary_percentage 列表示每位員工薪資在其所屬部門總薪資中的佔比。

cume_dist函數

cume_dist函數用於統計視窗分區內各個值的累計分布。即計算視窗分區內值小於等於當前值的行數占視窗內總行數的比例。傳回值範圍為(0,1]。

文法

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

參數說明

傳回值類型

double類型。

樣本

統計名為bucket00788的OSS Bucket內各個對象的大小的累計分布。

• 查詢和分析語句

  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函數用於視窗分區內值的排名。相同值擁有相同的排名，排名是連續的，例如有兩個相同值的排名為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

• 查詢和分析結果：Marketing 部門中，Blan Stark 和 Smith 薪資均為 9000、排名均為 1，Achilles 薪資為 8000、排名為 2；dev 部門中，Rob 薪資為 9000、排名為 1，Blan 薪資為 8500、排名為 2，Sansa 薪資為 8000、排名為 3。相同薪資獲得相同排名，且排名連續不跳躍。

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

• 查詢和分析結果：查詢結果返回9條記錄，object列均為245-da918c.model，按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 ntile
  FROM  oss-log-store

• 查詢和分析結果：查詢結果返回6行資料，按 object_size 升序排列，object_size 值依次為7442、7635、8221、8272、8706、8988，對應的 ntile（percent_rank）值依次為0.0、0.2、0.4、0.6、0.8、1.0，表明百分比排名在分區內均勻分布。

rank函數

函數用於視窗分區內值的排名。相同值擁有相同的排名，排名不是連續的，例如有兩個相同值的排名為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

• 查詢和分析結果：查詢結果返回6行資料。Marketing部門中，Blan Stark和Smith薪資均為9000、排名均為1，Achilles薪資8000、排名為3；dev部門中，Rob薪資9000排名1，Blan薪資8500排名2，Sansa薪資8000排名3。可見 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

• 查詢和分析結果返回6條資料：Marketing部門中，Blan Stark薪資9000排名1，Smith薪資9000排名2，Achilles薪資8000排名3；dev部門中，Rob薪資9000排名1，Blan薪資8500排名2，Sansa薪資8000排名3。

first_value函數

first_value函數用於返回各個視窗分區內第一行的值。

文法

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

參數說明

傳回值類型

與x的資料類型一致。

樣本

擷取目標OSS Bucket中各個對象的最小值。

• 查詢和分析語句

  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

• 查詢和分析結果：查詢結果返回7行資料，按 object 分為三組。每組內 first_value 列的值一致，均等於該分組中按 object_size 升序排列後第一行的值（即最小的 object_size）。

last_value函數

last_value函數用於返回各個視窗分區內最後一行的值。

文法

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

參數說明

傳回值類型

與x的資料類型一致。

樣本

擷取目標OSS Bucket中各個對象的最大值。

• 查詢和分析語句

  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。對於對象 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。結果表明 last_value 函數返回各分區視窗內按 object_size 排序後的最末值。

lag函數

lag函數用於返回視窗分區內位於當前行上方第offset行的值。

文法

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
    )

• 查詢和分析結果：查詢結果返回 8 行資料（2021-08-02 至 2021-08-09），首行 diff_percentage 值為 Infinity（因 lag 函數取前一日預設值為 0，導致除零），其餘行顯示當日 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-08-26當天，當前一小時網站訪問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
    )

• 查詢和分析結果：查詢結果返回 8 行資料，展示 2021-08-26 00:00 至 07:00 每小時的獨立訪客數（UV）及當前小時 UV 與下一小時 UV 的比值（diff_percentage）。

nth_value函數

nth_value函數用於返回視窗分區中第offset行的值。

文法

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

參數說明

傳回值類型

與x的資料類型一致。

樣本

按照部門分區，統計各個部門中薪水第二高的員工。

• 查詢和分析語句

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

• 查詢和分析結果。查詢結果顯示：dev 部門中薪資第二高的員工為 Blan（薪資 8500），Marketing 部門中薪資第二高的員工為 San（薪資 7000）。各部門所有行的 second_highest_salary 列均返回對應部門第二高薪資的員工姓名。