窗口函数

简介

普通的聚合函数只能用来计算一行内的结果或把所有行聚合成一行结果，而窗口函数支持为每一行生成一个结果。窗口函数包含分区、排序和框架这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 列均返回对应部门第二高薪资的员工姓名。