視窗函數的文法及樣本 - 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。

函數列表

分類	函數名稱	文法	說明	支援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

查詢和分析結果

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

查詢和分析結果

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

查詢和分析結果

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

查詢和分析結果

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

查詢和分析結果

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

查詢和分析結果

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

查詢和分析結果

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

查詢和分析結果

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

查詢和分析結果

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
  )

查詢和分析結果

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

查詢和分析結果