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

简介

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

函数列表

分类 函数名称 语法 说明
聚合函数 聚合函数 所有聚合函数都支持在窗口函数中使用。聚合函数列表请参见聚合函数
排名函数 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, defaut_value) 返回窗口分区内位于当前行上方第offset行的值。如果不存在该行,则返回defaut_value
lead函数 lead(x, offset, defaut_value) 返回窗口分区内位于当前行下方第offset行的值。如果不存在该行,则返回defaut_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,
      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
  • 查询和分析结果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, defaut_value) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

参数说明

参数 说明
x 列名,可以为任意数据类型。
offset 偏离量。如果offset为0,则返回当前行的值。
defaut_value 如果不存在指定的偏离行,则返回defaut_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, defaut_value) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

参数说明

参数 说明
x 列名,可以为任意数据类型。
offset 偏离量。如果offset为0,则返回当前行的值。
defaut_value 如果不存在指定的偏离行,则返回defaut_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
          sallary desc
          range between unbounded preceding and unbounded following
      ) as second_highest_sallary from log
  • 查询和分析结果nth_value