全部產品
Search
文件中心

Simple Log Service:視窗函數

更新時間:Jun 30, 2024

本文介紹視窗函數的基本文法及樣本。

簡介

普通的彙總函式只能用來計算一行內的結果或把所有行彙總成一行結果,而視窗函數支援為每一行產生一個結果。視窗函數包含分區、排序和架構這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

函數列表

分類

函數名稱

文法

說明

支援SQL

支援SPL

彙總函式

彙總函式

所有彙總函式都支援在視窗函數中使用。彙總函式列表請參見彙總函式

×

次序函數

cume_dist函數

cume_dist()

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

×

dense_rank函數

dense_rank()

視窗分區內值的排名。相同值擁有相同的排名,排名是連續的,例如有兩個相同值的排名為1,則下一個值的排名為2。

×

ntile函數

ntile(n)

將視窗分區內資料按照順序分成N組。

×

percent_rank函數

percent_rank()

計算視窗分區內各行的百分比排名。

×

rank函數

rank()

視窗分區內值的排名。相同值擁有相同的排名,排名不是連續的,例如有兩個相同值的排名為1,則下一個值的排名為3。

×

row_number函數

row_number()

視窗分區內值的排名。每個值擁有唯一的序號,從1開始。三個相同值的排名為1、2、3。

×

位移函數

first_value函數

first_value(x)

返回各個視窗分區內第一行的值。

×

last_value函數

last_value(x)

返回各個視窗分區內最後一行的值。

×

lag函數

lag(x, offset, default_value)

返回視窗分區內位於當前行上方第offset行的值。如果不存在該行,則返回default_value

×

lead函數

lead(x, offset, default_value)

返回視窗分區內位於當前行下方第offset行的值。如果不存在該行,則返回default_value

×

nth_value函數

nth_value(x, offset)

返回視窗分區中第offset行的值。

×

彙總函式

所有彙總函式都支援在視窗函數中使用。彙總函式列表請參見彙總函式。此處以sum函數為例。

文法

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

參數說明

參數

說明

partition by partition_expression

視窗分區,根據分區運算式將資料劃分成不同的分區。

order by order_expression

視窗排序,根據排序運算式對各個分區內的每一行進行排序。

frame

視窗架構,例如range between unbounded preceding and unbounded following

傳回值類型

double類型。

樣本

按照部門分區,擷取每個員工薪水在部門內的佔比。

  • 查詢和分析語句

    * |
    SELECT
      department,
      staff_name,
      salary,
      round ( salary * 1.0 / sum(salary) over(partition by department), 3) AS salary_percentage 
  • 查詢和分析結果sum

cume_dist函數

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

文法

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

參數說明

參數

說明

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
  • 查詢和分析結果cume_dist

dense_rank函數

dense_rank函數用於視窗分區內值的排名。相同值擁有相同的排名,排名是連續的,例如有兩個相同值的排名為1,則下一個值的排名為2。

文法

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

參數說明

參數

說明

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
  • 查詢和分析結果dense_rank

ntile函數

ntile函數用於將視窗分區內資料按照順序分成N組。

文法

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

參數說明

參數

說明

n

組數。

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
  • 查詢和分析結果ntile

percent_rank函數

函數用於計算視窗分區內各行的百分比排名。計算公式為(rank - 1) / (total_rows - 1) ,其中rank為當前行的排名,total_rows為當前視窗分區內的總行數。

文法

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

參數說明

參數

說明

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
  • 查詢和分析結果percent_rank

rank函數

函數用於視窗分區內值的排名。相同值擁有相同的排名,排名不是連續的,例如有兩個相同值的排名為1,則下一個值的排名為3。

文法

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

參數說明

參數

說明

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
  • 查詢和分析結果rank

row_number函數

row_number函數用於視窗分區內值的排名。每個值擁有唯一的序號,從1開始。

文法

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

參數說明

參數

說明

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
  • 查詢和分析結果row_number

first_value函數

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

文法

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

參數說明

參數

說明

x

列名,可以為任意資料類型。

partition by partition_expression

視窗分區,根據分區運算式將資料劃分成不同的分區。

order by order_expression

視窗排序,根據排序運算式對各個分區內的每一行進行排序。

frame

視窗架構,例如range between unbounded preceding and unbounded following

傳回值類型

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
  • 查詢和分析結果first_value

last_value函數

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

文法

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

參數說明

參數

說明

x

列名,可以為任意資料類型。

partition by partition_expression

視窗分區,根據分區運算式將資料劃分成不同的分區。

order by order_expression

視窗排序,根據排序運算式對各個分區內的每一行進行排序。

frame

視窗架構,例如range between unbounded preceding and unbounded following

傳回值類型

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
  • 查詢和分析結果last_value

lag函數

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

文法

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

參數說明

參數

說明

x

列名,可以為任意資料類型。

offset

偏離量。如果offset為0,則返回當前行的值。

default_value

如果不存在指定的偏離行,則返回default_value

partition by partition_expression

視窗分區,根據分區運算式將資料劃分成不同的分區。

order by order_expression

視窗排序,根據排序運算式對各個分區內的每一行進行排序。

frame

視窗架構,例如range between unbounded preceding and unbounded following

傳回值類型

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函數

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

文法

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

參數說明

參數

說明

x

列名,可以為任意資料類型。

offset

偏離量。如果offset為0,則返回當前行的值。

default_value

如果不存在指定的偏離行,則返回default_value

partition by partition_expression

視窗分區,根據分區運算式將資料劃分成不同的分區。

order by order_expression

視窗排序,根據排序運算式對各個分區內的每一行進行排序。

frame

視窗架構,例如range between unbounded preceding and unbounded following

傳回值類型

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
      )
  • 查詢和分析結果lead

nth_value函數

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

文法

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

參數說明

參數

說明

x

列名,可以為任意資料類型。

offset

偏離量。

partition by partition_expression

視窗分區,根據分區運算式將資料劃分成不同的分區。

order by order_expression

視窗排序,根據排序運算式對各個分區內的每一行進行排序。

frame

視窗架構,例如range between unbounded preceding and unbounded following

傳回值類型

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
  • 查詢和分析結果nth_value