SQL窗口函数

参考链接：https://help.aliyun.com/zh/sls/user-guide/window-functions

简介

普通的聚合函数只能用来计算一行内的结果或者把所有行聚合成一行结果，而窗口函数支持为每一行生成一个结果。窗口函数包含分区、排序和框架这三个核心元素。

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。

聚合函数

所有聚合函数都支持在窗口函数中使用。此处以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

如果不使用窗口函数，可以使用子查询来实现：

SELECT
  t.department,
  t.staff_name,
  t.salary,
  ROUND(t.salary * 1.0 / total_salary.total, 3) AS salary_percentage
FROM your_table t
JOIN (
  SELECT department, SUM(salary) AS total
  FROM your_table
  GROUP BY department
) AS total_salary ON t.department = total_salary.department
ORDER BY t.department, t.staff_name;

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

查询结果

另一个简单的例子是，假设有一个表，包含department和salary列，我们想计算每个员工薪水的累积分布：

SELECT
  department,
  staff_name,
  salary,
  CUME_DIST() OVER (ORDER BY salary) AS cumulative_distribution
FROM your_table;

薪水的累积分布是指在一组数据中，某个具体薪水值（或以下）在整个数据集中所占的累积比例。这个比例反映了薪水相对于整体薪水分布的相对位置。

举个例子：

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