窗口函数

简介

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

• 查询和分析结果

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

• 查询和分析结果

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

• 查询和分析结果

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

• 查询和分析结果

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

• 查询和分析结果

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

• 查询和分析结果

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

• 查询和分析结果

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

• 查询和分析结果

lag函数

lag函数用于返回窗口分区内位于当前行上方第offset行的值。

语法

lag(x, offset, defaut_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
    )

• 查询和分析结果

lead函数

函数用于返回窗口分区内位于当前行下方第offset行的值。

语法

lead(x, offset, defaut_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
    )

• 查询和分析结果

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

• 查询和分析结果