窗口函数的语法及示例 - 日志服务

本文介绍窗口函数的基本语法及示例。

简介

普通的聚合函数只能用来计算一行内的结果或把所有行聚合成一行结果，而窗口函数支持为每一行生成一个结果。窗口函数包含分区、排序和框架这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

查询和分析结果