AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 云原生大数据计算服务 MaxCompute 操作指南 开发参考：SQL 函数参考 内建函数 窗口函数

窗口函数

更新时间：
产品详情
我的收藏

窗口函数支持在一个动态定义的数据子集上执行聚合操作或其他计算，常用于处理时间序列数据、排名、移动平均等问题，本文为您介绍MaxCompute SQL支持的窗口函数的命令格式、参数说明及示例，指导您使用窗口函数完成开发。

MaxCompute SQL支持的窗口函数如下。

函数

功能

AVG

对窗口中的数据求平均值。

CLUSTER_SAMPLE

用户随机抽样。返回True表示该行数据被抽中。

COUNT

计算窗口中的记录数。

CUME_DIST

计算累计分布。

DENSE_RANK

计算排名。排名是连续的。

FIRST_VALUE

取当前行所对应窗口的第一条数据的值。

LAG

取当前行往前（朝分区头部方向）第N行数据的值。

LAST_VALUE

取当前行所对应窗口的最后一条数据的值。

LEAD

取当前行往后（朝分区尾部方向）第N行数据的值。

MAX

计算窗口中的最大值。

MEDIAN

计算窗口中的中位数。

MIN

计算窗口中的最小值。

NTILE

将数据顺序切分成N等份，返回数据所在等份的编号（从1到N）。

NTH_VALUE

取当前行所对应窗口的第N条数据的值。

PERCENT_RANK

计算排名。输出百分比格式。

RANK

计算排名。排名可能不连续。

ROW_NUMBER

计算行号。从1开始递增。

STDDEV

计算总体标准差。是STDDEV_POP的别名。

STDDEV_SAMP

计算样本标准差。

SUM

对窗口中的数据求和。

使用限制

窗口函数的使用限制如下：

  • 窗口函数只能出现在select语句中。

  • 窗口函数中不能嵌套使用窗口函数和聚合函数。

  • 窗口函数不能和同级别的聚合函数一起使用。

窗口函数语法

窗口函数的语法声明如下。 

<function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
<function_name>([distinct][<expression> [, ...]]) over <window_name>

  • function_name：内建窗口函数、聚合函数或用户自定义聚合函数UDAF。

  • expression：函数格式，具体格式以实际函数语法为准。

  • windowing_definition：窗口定义。详细语法格式请参见windowing_definition。

  • window_name：窗口名称。您可以使用window关键字自定义窗口，为windowing_definition定义名称。自定义语句（named_window_def）如下：

    window <window_name> as (<window_definition>)

    自定义语句在SQL中的位置如下：

    select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]

windowing_definition

windowing_definition的语法声明如下。

--partition_clause：
[partition by <expression> [, ...]]
--orderby_clause：
[order by <expression> [asc|desc][nulls {first|last}] [, ...]]
[<frame_clause>]

在SELECT语句中加入窗口函数，计算窗口函数的结果时，数据会按照窗口定义中的partition by和order by语句进行分区和排序。如果没有partition by语句，则仅有一个分区，包含全部数据。如果没有order by语句，则分区内的数据会按照任意顺序排布，最终生成一个数据流。之后对于每一行数据（当前行），会按照窗口定义中的frame_clause从数据流中截取一段数据，构成当前行的窗口。窗口函数会根据窗口中包含的数据，计算得到窗口函数针对当前行对应的输出结果。

  • partition by <expression> [, ...]：可选。指定分区。分区列的值相同的行被视为在同一个窗口内。详细格式请参见表操作。

  • order by <expression> [asc|desc][nulls {first|last}] [, ...]：可选。指定数据在一个窗口内如何排序。

    说明

    当遇到相同的order by值时，排序结果不稳定。为减少随机性，应当尽可能保持order by值的唯一性。

  • frame_clause：可选。用于确定数据边界，更多frame_clause信息，请参见frame_clause。

filter_clause

filter_clause的语法声明如下。

FILTER (WHERE filter_condition)

其中filter_condition为布尔表达式，和select ... from ... where语句中的where用法完全相同。

如果提供了FILTER子句，则只有filter_condition值为true的行才会包含在窗口frame中。对于聚合窗口函数（包括：COUNT、SUM、AVG、MAX、MIN、WM_CONCAT等）仍为每行返回一个值，但FILTER表达式计算结果为true以外的值（即NULL或false，NULL按false处理）不会包含在任何行的窗口frame中。

示例

  • 数据准备

    --创建表
CREATE TABLE IF NOT EXISTS mf_window_fun(key BIGINT,value BIGINT) STORED AS ALIORC;

--插入数据
insert into mf_window_fun values (1,100),(2,200),(1,150),(2,250),(3,300),(4,400),(5,500),(6,600),(7,700);

--查询mf_window_fun表数据
select * from mf_window_fun;

--返回结果
+------------+------------+
| key        | value      |
+------------+------------+
| 1          | 100        |
| 2          | 200        |
| 1          | 150        |
| 2          | 250        |
| 3          | 300        |
| 4          | 400        |
| 5          | 500        |
| 6          | 600        |
| 7          | 700        |
+------------+------------+

  • 查询窗口内value值大于100的行的累积和

    select key,sum(value) filter(where value > 100) 
       over (partition by key order by key)  
       from mf_window_fun;

    返回结果：

    +------------+------------+
| key        | _c1        |
+------------+------------+
| 1          | NULL       | --跳过计算
| 1          | 150        |
| 2          | 200        |
| 2          | 450        |
| 3          | 300        |
| 4          | 400        |
| 5          | 500        |
| 6          | 600        |
| 7          | 700        |
+------------+------------+
说明

  • FILTER子句并不会在查询结果中去掉不满足filter_condition的行，只是在窗口函数计算时认为这一行不存在。如果想要去掉相应的行，仍然需要在select ... from ... where的条件中指定。而这一行的窗口函数值也并不是0或者NULL，而是沿用其前一行的窗口函数值。

  • 只有当窗口函数为聚合类函数（包括：COUNT、SUM、AVG、MAX、MIN、WM_CONCAT等）时，才能使用FILTER子句，非聚合类函数（例如：RANK、ROW_NUMBER、NTILE）不能使用FILTER子句，否则会出现语法错误。

  • 在窗口函数中使用FILTER语法时需要开启如下开关：set odps.sql.window.function.newimpl=true;。

frame_clause

frame_clause的语法声明如下。

--格式一。
{ROWS|RANGE|GROUPS} <frame_start> [<frame_exclusion>]
--格式二。
{ROWS|RANGE|GROUPS} between <frame_start> and <frame_end> [<frame_exclusion>]

frame_clause是一个闭区间，用于确定数据边界，包含frame_start和frame_end位置的数据行。

  • ROWS|RANGE|GROUPS：必填。frame_clause的类型，各类型的frame_start和frame_end实现规则不相同。其中：

    • ROWS类型：通过数据行数确定数据边界。

    • RANGE类型：通过比较order by列值的大小关系来确定数据边界。一般在窗口定义中会指定order by，未指定order by时，一个分区中的所有数据行具有相同的order by列值。NULL与NULL被认为是相等的。

    • GROUPS：一个分区中所有具有相同order by列值的数据组成一个GROUP。未指定order by时，分区中的所有数据组成一个GROUP。NULL与NULL被认为是相等的。

  • frame_start、frame_end：表示窗口的起始和终止边界。frame_start必填。frame_end可选，省略时默认值为CURRENT ROW。

    frame_start确定的位置必须在frame_end确定的位置的前面，或者等于frame_end的位置，即frame_start相比frame_end更靠近分区头部。分区头部是指数据按窗口定义中的order by语句排序之后第1行数据的位置。ROWS、RANGE、GROUPS类型对应的取值范围及实现逻辑如下。

    frame_clause类型

    frame_start/frame_end取值

    说明

    ROWS、RANGE、GROUPS

    UNBOUNDED PRECEDING

    表示分区的第一行，从1开始计数。

    UNBOUNDED FOLLOWING

    表示分区的最后一行。

    ROWS

    CURRENT ROW

    指当前行的位置。每一行数据都会对应一个窗口函数的结果值，当前行是指在给哪一行数据计算窗口函数的结果。

    offset PRECEDING

    指从当前行位置，向分区头部位置移动offset行的位置。例如0 PRECEDING指当前行，1 PRECEDING指前一行。offset必须为非负整数。

    offset FOLLOWING

    指从当前行位置，向分区尾部移动offset行的位置。例如0 FOLLOWING指当前行，1 FOLLOWING指下一行。offset必须为非负整数。

    RANGE

    CURRENT ROW

    • 作为frame_start时，指第一条与当前行具有相同order by列值的数据的位置。

    • 作为frame_end时，指最后一条与当前行具有相同order by列值的数据的位置。

    offset PRECEDING

    frame_start和frame_end的位置与order by的顺序相关。假设窗口按照X进行排序，Xi表示第i行数据对应的X值，Xc表示当前行数据对应X值。位置说明如下：

    • 当order by为升序时：

      • frame_start：指第一条满足Xc - Xi <= offset数据的位置。

      • frame_end：指最后一条满足Xc - Xi >= offset数据的位置。

    • 当order by为降序时：

      • frame_start：指第一条满足Xi - Xc <= offset数据的位置。

      • frame_end：指最后一条满足Xi - Xc >= offset数据的位置。

    order by的列支持的数据类型为：TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、DATETIME、DATE、TIMESTAMP。

    日期类型数据的offset语法如下：

    • N：表示N天或N秒。非负整数。对于DATETIME和TIMESTAMP，表示N秒；对于DATE，表示N天。

    • interval 'N' {YEAR\MONTH\DAY\HOUR\MINUTE\SECOND}：表示N年/月/日/小时/分钟/秒。例如INTERVAL '3' YEAR表示3年。

    • INTERVAL 'N-M' YEAR TO MONTH：表示N年M月。例如INTERVAL '1-3' YEAR TO MONTH表示1年3个月。

    • INTERVAL 'D[ H[:M[:S[:N]]]]' DAY TO SECOND：表示D天H小时M分钟S秒N纳秒。例如INTERVAL '1 2:3:4:5' DAY TO SECOND表示1天2小时3分钟4秒5纳秒。

    offset FOLLOWING

    frame_start和frame_end的位置与order by的顺序相关。假设窗口按照X进行排序，Xi表示第i行数据对应的X值，Xc表示当前行数据对应X值。位置说明如下：

    • 当order by为升序时：

      • frame_start：指第一条满足Xi - Xc >= offset数据的位置。

      • frame_end：指最后一条满足Xi - Xc <= offset数据的位置。

    • 当order by为降序时：

      • frame_start：指第一条满足Xc - Xi >= offset数据的位置。

      • frame_end：指最后一条满足Xc - Xi <= offset数据的位置。

    GROUPS

    CURRENT ROW

    • 作为frame_start时，指当前行所属GROUP的第一条数据。

    • 作为frame_end时，指当前行所属GROUP的最后一行数据。

    offset PRECEDING

    • 作为frame_start时，指从当前行所属GROUP开始，朝分区头部移动offset个GROUP之后，所在GROUP的第一条数据的位置。

    • 作为frame_end时，指从当前行所属GROUP开始，朝分区头部移动offset个GROUP之后，所在GROUP的最后一条数据的位置。

    说明

    frame_start不能设置为UNBOUNDED FOLLOWING，frame_end不能设置为UNBOUNED PRECEDING。

    offset FOLLOWING

    • 作为frame_start时，指从当前行所属GROUP开始，朝分区尾部移动offset个GROUP之后，所在GROUP的第一条数据的位置。

    • 作为frame_end时，指从当前行所属GROUP开始，朝分区尾部移动offset个GROUP之后，所在GROUP的最后一条数据的位置。

    说明

    frame_start不能设置为UNBOUNDED FOLLOWING，frame_end不能设置为UNBOUNED PRECEDING。

  • frame_exclusion：可选。用于从窗口中剔除一部分数据。取值范围如下：

    • EXCLUDE NO OTHERS：表示不剔除任何数据。

    • EXCLUDE CURRENT ROW：表示剔除当前行。

    • EXCLUDE GROUP：表示剔除整个GROUP，即分区中与当前行具有相同order by值的所有数据。

    • EXCLUDE TIES：表示剔除整个GROUP，但保留当前行。

默认frame_clause

未显示设置frame_clause时，MaxCompute会使用默认的frame_clause来决定窗口所包含数据的边界。默认的frame_clause为：

  • 当开启Hive兼容模式（set odps.sql.hive.compatible=true;）时，默认的frame_clause如下，与大部分SQL系统相同。

    RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS

  • 当关闭Hive兼容模式（set odps.sql.hive.compatible=false;），同时窗口定义中指定了order by语句，且窗口函数为AVG、COUNT、MAX、MIN、STDDEV、STEDEV_POP、STDDEV_SAMP或SUM时，会使用ROWS类型的默认frame_clause。

    ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS

窗口边界示例

假设表tbl结构为pid: bigint, oid: bigint, rid: bigint，表中包含如下数据：

+------------+------------+------------+
| pid        | oid        | rid        |
+------------+------------+------------+
| 1          | NULL       | 1          |
| 1          | NULL       | 2          |
| 1          | 1          | 3          |
| 1          | 1          | 4          |
| 1          | 2          | 5          |
| 1          | 4          | 6          |
| 1          | 7          | 7          |
| 1          | 11         | 8          |
| 2          | NULL       | 9          |
| 2          | NULL       | 10         |
+------------+------------+------------+

  • ROW类型窗口

    • 窗口定义1

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW
--SQL语句如下。
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

      返回结果如下：

      +------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1]    |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9]    |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

    • 窗口定义2

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
--SQL语句如下。
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as window from tbl;

      返回结果如下：

      +------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 3          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 4          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

    • 窗口定义3

      partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING
--SQL语句如下。
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between 1 FOLLOWING and 3 FOLLOWING) as window from tbl;

      返回结果如下：

      +------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [2, 3, 4] |
| 1          | NULL       | 2          | [3, 4, 5] |
| 1          | 1          | 3          | [4, 5, 6] |
| 1          | 1          | 4          | [5, 6, 7] |
| 1          | 2          | 5          | [6, 7, 8] |
| 1          | 4          | 6          | [7, 8] |
| 1          | 7          | 7          | [8]    |
| 1          | 11         | 8          | NULL   |
| 2          | NULL       | 9          | [10]   |
| 2          | NULL       | 10         | NULL   |
+------------+------------+------------+--------+

    • 窗口定义4

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW
--SQL语句如下。
select pid, 
oid, 
rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW) as window from tbl;

      返回结果如下：

      +------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | NULL   |
| 1          | NULL       | 2          | [1]    |
| 1          | 1          | 3          | [1, 2] |
| 1          | 1          | 4          | [1, 2, 3] |
| 1          | 2          | 5          | [1, 2, 3, 4] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
| 2          | NULL       | 9          | NULL   |
| 2          | NULL       | 10         | [9]    |
+------------+------------+------------+--------+

    • 窗口定义5

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP
--SQL语句如下。
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP) as window from tbl;

      返回结果如下：

      +------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | NULL   |
| 1          | NULL       | 2          | NULL   |
| 1          | 1          | 3          | [1, 2] |
| 1          | 1          | 4          | [1, 2] |
| 1          | 2          | 5          | [1, 2, 3, 4] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
| 2          | NULL       | 9          | NULL   |
| 2          | NULL       | 10         | NULL   |
+------------+------------+------------+--------+

    • 窗口定义6

      partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES
--SQL语句如下。
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES) as window from tbl;

      返回结果如下：

      +------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1]    |
| 1          | NULL       | 2          | [2]    |
| 1          | 1          | 3          | [1, 2, 3] |
| 1          | 1          | 4          | [1, 2, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9]    |
| 2          | NULL       | 10         | [10]   |
+------------+------------+------------+--------+

      对比本示例与前一个示例中rid为2、4、10的window结果，可以观察到EXCLUDE CURRENT ROW与EXCLUDE GROUP的差异，即对于EXCLUDE GROUP，同一个分区中（pid相等），与当前行为相同oid的数据都被剔除了。

  • RANGE类型窗口

    • 窗口定义1

      partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW
--SQL语句如下。
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

      返回结果如下：

      +------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3, 4] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

      CURRENT ROW作为frame_end时，取与当前行具有相同order by值oid的最后一条数据，因此rid为1的记录的window结果为[1, 2]。

    • 窗口定义2

      partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING
--SQL语句如下。
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING) as window from tbl;

      返回结果如下：

      +------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 3          | [3, 4, 5, 6, 7, 8] |
| 1          | 1          | 4          | [3, 4, 5, 6, 7, 8] |
| 1          | 2          | 5          | [5, 6, 7, 8] |
| 1          | 4          | 6          | [6, 7, 8] |
| 1          | 7          | 7          | [7, 8] |
| 1          | 11         | 8          | [8]    |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

    • 窗口定义3

      partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING
--SQL语句如下。

select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between 3 PRECEDING and 1 PRECEDING) as window from tbl;

      返回结果如下：

      +------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | NULL   |
| 1          | 1          | 4          | NULL   |
| 1          | 2          | 5          | [3, 4] |
| 1          | 4          | 6          | [3, 4, 5] |
| 1          | 7          | 7          | [6]    |
| 1          | 11         | 8          | NULL   |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

      order by值oid为NULL的行，对于offset {PRECEDING|FOLLOWING}，只要offset不为UNBOUNDED，则作为frame_start，指向分区中第一条order by值为NULL的数据；作为frame_end，指向最后一条order by值为NULL的数据。

  • GROUPS类型窗口

    窗口定义如下：

    partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW
--SQL语句如下。
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid GROUPS between 2 PRECEDING and CURRENT ROW) as window from tbl;

    返回结果如下：

    +------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3, 4] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [3, 4, 5, 6] |
| 1          | 7          | 7          | [5, 6, 7] |
| 1          | 11         | 8          | [6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

示例数据

为便于理解各函数的使用方法，本文为您提供源数据，基于源数据提供函数相关示例。创建表emp，并添加数据，命令示例如下：

create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp;

emp.txt中的数据如下：

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

AVG

  • 命令格式 

    double avg([distinct] double <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal avg([distinct] decimal <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

  • 命令说明

    返回窗口中expr的平均值。

  • 参数说明

    • expr：必填。计算返回结果的表达式。DOUBLE类型或DECIMAL类型。

      • 当输入值为STRING、BIGINT类型时，会隐式转换为DOUBLE类型后参与运算，输入其他数据类型返回报错。

      • 当输入值为NULL时，该行不参与计算。

      • 当指定distinct关键字时，表示取唯一值的平均值。

    • partition_clause、orderby_clause及frame_clause：详情请参见windowing_definition。

  • 返回值说明

    expr为DECIMAL类型时，返回DECIMAL类型。其他情况下返回DOUBLE类型。expr的值都为NULL时，返回结果为NULL。

  • 示例

    • 示例1：指定部门（deptno）为开窗列，计算薪水（sal）平均值，不排序，返回当前窗口（相同deptno）从开始行到最后一行的累计平均值。命令示例如下：

      select deptno, sal, avg(sal) over (partition by deptno) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2916.6666666666665 |   --窗口开始行，从第1行到第6行的累计平均值。
| 10         | 2450       | 2916.6666666666665 |   --从第1行到第6行的累计平均值。
| 10         | 5000       | 2916.6666666666665 |   --从第1行到第6行的累计平均值。
| 10         | 1300       | 2916.6666666666665 |
| 10         | 5000       | 2916.6666666666665 |
| 10         | 2450       | 2916.6666666666665 |
| 20         | 3000       | 2175.0     |
| 20         | 3000       | 2175.0     |
| 20         | 800        | 2175.0     |
| 20         | 1100       | 2175.0     |
| 20         | 2975       | 2175.0     |
| 30         | 1500       | 1566.6666666666667 |
| 30         | 950        | 1566.6666666666667 |
| 30         | 1600       | 1566.6666666666667 |
| 30         | 1250       | 1566.6666666666667 |
| 30         | 1250       | 1566.6666666666667 |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

    • 示例2：非Hive兼容模式下，指定部门（deptno）为开窗列，计算薪水（sal）平均值，并排序，返回当前窗口（相同deptno）从开始行到当前行的累计平均值。命令示例如下：

      --关闭Hive兼容模式。
set odps.sql.hive.compatible=false;
--执行如下SQL命令。
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300.0     |           --窗口开始行。
| 10         | 1300       | 1300.0     |           --从第1行到第2行的累计平均值。
| 10         | 2450       | 1683.3333333333333 |   --从第1行到第3行的累计平均值。
| 10         | 2450       | 1875.0     |           --从第1行到第4行的累计平均值。
| 10         | 5000       | 2500.0     |           --从第1行到第5行的累计平均值。
| 10         | 5000       | 2916.6666666666665 |   --从第1行到第6行的累计平均值。
| 20         | 800        | 800.0      |
| 20         | 1100       | 950.0      |
| 20         | 2975       | 1625.0     |
| 20         | 3000       | 1968.75    |
| 20         | 3000       | 2175.0     |
| 30         | 950        | 950.0      |
| 30         | 1250       | 1100.0     |
| 30         | 1250       | 1150.0     |
| 30         | 1500       | 1237.5     |
| 30         | 1600       | 1310.0     |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

    • 示例3：Hive兼容模式下，指定部门（deptno）为开窗列，计算薪水（sal）平均值，并排序，返回当前窗口（相同deptno）从开始行到与当前行相同值所在行（相同sal值的平均值相同）的累计平均值。命令示例如下：

      --开启Hive兼容模式。
set odps.sql.hive.compatible=true;
--执行如下SQL命令。
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300.0     |          --窗口开始行。第1行和第2行的sal一致，则第1行的平均值为第1行到第2行的累计平均值。
| 10         | 1300       | 1300.0     |          --从第1行到第2行的累计平均值。
| 10         | 2450       | 1875.0     |          --第3行和第4行的sal一致，则第3行的平均值为从第1行到第4行的累计平均值。
| 10         | 2450       | 1875.0     |          --从第1行到第4行的累计平均值。
| 10         | 5000       | 2916.6666666666665 |
| 10         | 5000       | 2916.6666666666665 |
| 20         | 800        | 800.0      |
| 20         | 1100       | 950.0      |
| 20         | 2975       | 1625.0     |
| 20         | 3000       | 2175.0     |
| 20         | 3000       | 2175.0     |
| 30         | 950        | 950.0      |
| 30         | 1250       | 1150.0     |
| 30         | 1250       | 1150.0     |
| 30         | 1500       | 1237.5     |
| 30         | 1600       | 1310.0     |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

CLUSTER_SAMPLE

  • 命令格式 

    boolean cluster_sample(bigint <N>) OVER ([partition_clause])
boolean cluster_sample(bigint <N>, bigint <M>) OVER ([partition_clause])

  • 命令说明

    • cluster_sample(bigint <N>)：表示随机抽取N条数据。

    • cluster_sample(bigint <N>, bigint <M>)：表示按比例（M/N）随机抽取。即抽取partition_row_count×M / N条数据。partition_row_count指分区中的数据行数。

  • 参数说明

    • N：必填。BIGINT类型常量。N为NULL时，返回值为NULL。

    • M：必填。BIGINT类型常量。M为NULL时，返回值为NULL。

    • partition_clause：可选。详情请参见windowing_definition。

  • 返回值说明

    返回BOOLEAN类型。

  • 示例

    如果您需要从每组中抽取约20%的值，命令示例如下：

    select deptno, sal
    from (
        select deptno, sal, cluster_sample(5, 1) over (partition by deptno) as flag
        from emp
        ) sub
    where flag = true;

    返回结果如下：

    +------------+------------+
| deptno     | sal        |
+------------+------------+
| 10         | 1300       |
| 20         | 3000       |
| 30         | 950        |
+------------+------------+

COUNT

  • 命令格式 

    bigint count(*) over ([partition_clause] [orderby_clause] [frame_clause])
bigint count([distinct] <expr>[,...]) over ([partition_clause] [orderby_clause] [frame_clause])

  • 命令说明

    • count(*)：返回总行数。

    • count([distinct] <expr>[,...])：计算行数时会忽略expr值为NULL的行，如果有多个expr，则任意expr值为NULL都被忽略。此外如果指定了distinct关键字，则计算去重之后的数据行数，任意expr值为NULL的行同样会被忽略。

  • 参数说明

    • expr：必填。待计算计数值的列。可以为任意类型。当值为NULL时，该行不参与计算。当指定DISTINCT关键字时，表示取唯一值的计数值。

    • partition_clause、orderby_clause及frame_clause：详情请参见windowing_definition。

  • 返回值说明

    返回BIGINT类型。

  • 示例

    • 示例1：指定薪水（sal）为开窗列，不排序，返回当前窗口（相同sal）的从开始行到最后一行的累计计数值。命令示例如下：

      select sal, count(sal) over (partition by sal) as count from emp;

      返回结果如下：

      +------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |   --窗口开始行。第1行和第2行的sal一致，则第1行的count为第2行的累计计数值。
| 1250       | 2          |   --当前窗口从第1行到第2行的累计计数值。
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

    • 示例2：非Hive兼容模式下，指定薪水（sal）为开窗列，并排序，返回当前窗口（相同sal）从开始行到当前行的累计计数值。命令示例如下：

      --关闭Hive兼容模式。
set odps.sql.hive.compatible=false;
--执行如下SQL命令。
select sal, count(sal) over (partition by sal order by sal) as count from emp;

      返回结果如下：

      +------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 1          |   --窗口开始行。第1行的累计计数值是1。
| 1250       | 2          |   --第2行的累计计数值是2。
| 1300       | 1          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 1          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 1          |
| 3000       | 2          |
| 5000       | 1          |
| 5000       | 2          |
+------------+------------+

    • 示例3：Hive兼容模式下，指定薪水（sal）为开窗列，并排序，返回当前窗口（相同sal）从开始行至最后一行的累计计数值。命令示例如下：

      --开启Hive兼容模式。
set odps.sql.hive.compatible=true;
--执行如下SQL命令。
select sal, count(sal) over (partition by sal order by sal) as count from emp;

      返回结果如下：

      +------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |   --窗口开始行。第1行和第2行的sal一致，则第1行的count为第2行的累计计数值。
| 1250       | 2          |   --当前窗口从第1行到第2行的累计计数值。
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

CUME_DIST

  • 命令格式 

    double cume_dist() over([partition_clause] [orderby_clause])

  • 命令说明

    求累计分布，相当于求分区中大于等于当前行的数据在分区中的占比。大小关系由orderby_clause判定。

  • 参数说明

    partition_clause及orderby_clause：详情请参见windowing_definition。

  • 返回值说明

    返回DOUBLE类型。具体的返回值等于row_number_of_last_peer / partition_row_count，其中：row_number_of_last_peer指当前行所属GROUP的最后一行数据的ROW_NUMBER窗口函数返回值，partition_row_count为该行数据所属分区的数据行数。

  • 示例

    将所有职工根据部门（deptno）分组（作为开窗列），计算薪水（sal）在同一组内的前百分之几。命令示例如下：

    select deptno, ename, sal, concat(round(cume_dist() over (partition by deptno order by sal desc)*100,2),'%') as cume_dist from emp;

    返回结果如下：

    +------------+------------+------------+------------+
| deptno     | ename      | sal        | cume_dist  |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 33.33%     |
| 10         | KING       | 5000       | 33.33%     |
| 10         | CLARK      | 2450       | 66.67%     |
| 10         | WELAN      | 2450       | 66.67%     |
| 10         | TEBAGE     | 1300       | 100.0%     |
| 10         | MILLER     | 1300       | 100.0%     |
| 20         | SCOTT      | 3000       | 40.0%      |
| 20         | FORD       | 3000       | 40.0%      |
| 20         | JONES      | 2975       | 60.0%      |
| 20         | ADAMS      | 1100       | 80.0%      |
| 20         | SMITH      | 800        | 100.0%     |
| 30         | BLAKE      | 2850       | 16.67%     |
| 30         | ALLEN      | 1600       | 33.33%     |
| 30         | TURNER     | 1500       | 50.0%      |
| 30         | MARTIN     | 1250       | 83.33%     |
| 30         | WARD       | 1250       | 83.33%     |
| 30         | JAMES      | 950        | 100.0%     |
+------------+------------+------------+------------+

DENSE_RANK

  • 命令格式 

    bigint dense_rank() over ([partition_clause] [orderby_clause])

  • 命令说明

    计算当前行在分区中按照orderby_clause排序后所处的排名。从1开始计数。分区中具有相同order by值的行的排名相等。每当order by值发生变化时，排名加1。

  • 参数说明

    partition_clause及orderby_clause：详情请参见windowing_definition。

  • 返回值说明

    返回BIGINT类型。未指定orderby_clause时，返回结果全为1。

  • 示例

    将所有职工根据部门（deptno）分组（作为开窗列），每个组内根据薪水（sal）做降序排序，获得职工自己组内的序号。命令示例如下：

    select deptno, ename, sal, dense_rank() over (partition by deptno order by sal desc) as nums from emp;

    返回结果如下：

    +------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 2          |
| 10         | WELAN      | 2450       | 2          |
| 10         | TEBAGE     | 1300       | 3          |
| 10         | MILLER     | 1300       | 3          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 2          |
| 20         | ADAMS      | 1100       | 3          |
| 20         | SMITH      | 800        | 4          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 4          |
| 30         | JAMES      | 950        | 5          |
+------------+------------+------------+------------+

FIRST_VALUE

  • 命令格式 

    first_value(<expr>[, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

  • 命令说明

    返回表达式expr在窗口的第一条数据上进行运算的结果。

  • 参数说明

    • expr：必填。待计算返回结果的表达式。

    • ignore_nulls：可选。BOOLEAN类型。表示是否忽略NULL值。默认值为False。当参数的值为True时，返回窗口中第一条非NULL的expr值。

    • partition_clause、orderby_clause及frame_clause：详情请参见windowing_definition。

  • 返回值说明

    返回值类型同expr类型。

  • 示例

    将所有职工根据部门分组，返回每组中的第一行数据。命令示例如下：

    • 不指定order by：

      select deptno, ename, sal, first_value(sal) over (partition by deptno) as first_value from emp;

      返回结果如下：

      +------------+------------+------------+-------------+
| deptno     | ename      | sal        | first_value |
+------------+------------+------------+-------------+
| 10         | TEBAGE     | 1300       | 1300        |   --当前窗口的开始行。
| 10         | CLARK      | 2450       | 1300        |
| 10         | KING       | 5000       | 1300        |
| 10         | MILLER     | 1300       | 1300        |
| 10         | JACCKA     | 5000       | 1300        |
| 10         | WELAN      | 2450       | 1300        |
| 20         | FORD       | 3000       | 3000        |   --当前窗口的开始行。
| 20         | SCOTT      | 3000       | 3000        |
| 20         | SMITH      | 800        | 3000        |
| 20         | ADAMS      | 1100       | 3000        |
| 20         | JONES      | 2975       | 3000        |
| 30         | TURNER     | 1500       | 1500        |   --当前窗口的开始行。
| 30         | JAMES      | 950        | 1500        |
| 30         | ALLEN      | 1600       | 1500        |
| 30         | WARD       | 1250       | 1500        |
| 30         | MARTIN     | 1250       | 1500        |
| 30         | BLAKE      | 2850       | 1500        |
+------------+------------+------------+-------------+

    • 指定order by：

      select deptno, ename, sal, first_value(sal) over (partition by deptno order by sal desc) as first_value from emp;

      返回结果如下：

      +------------+------------+------------+-------------+
| deptno     | ename      | sal        | first_value |
+------------+------------+------------+-------------+
| 10         | JACCKA     | 5000       | 5000        |   --当前窗口的开始行。
| 10         | KING       | 5000       | 5000        |
| 10         | CLARK      | 2450       | 5000        |
| 10         | WELAN      | 2450       | 5000        |
| 10         | TEBAGE     | 1300       | 5000        |
| 10         | MILLER     | 1300       | 5000        |
| 20         | SCOTT      | 3000       | 3000        |   --当前窗口的开始行。
| 20         | FORD       | 3000       | 3000        |
| 20         | JONES      | 2975       | 3000        |
| 20         | ADAMS      | 1100       | 3000        |
| 20         | SMITH      | 800        | 3000        |
| 30         | BLAKE      | 2850       | 2850        |   --当前窗口的开始行。
| 30         | ALLEN      | 1600       | 2850        |
| 30         | TURNER     | 1500       | 2850        |
| 30         | MARTIN     | 1250       | 2850        |
| 30         | WARD       | 1250       | 2850        |
| 30         | JAMES      | 950        | 2850        |
+------------+------------+------------+-------------+

LAG

  • 命令格式 

    lag(<expr>[，bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

  • 命令说明

    返回当前行往前（朝分区头部方向）第offset行数据对应的表达式expr的值。表达式expr可以是列、列运算或者函数运算等。

  • 参数说明

    • expr：必填。待计算返回结果的表达式。

    • offset：可选。偏移量，BIGINT类型常量，取值大于等于1。值为1时表示前一行，以此类推，默认值为1。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后进行运算。

    • default：可选。当offset指定的范围越界时的缺省值，常量，默认值为NULL。需要与expr对应的数据类型相同。如果expr非常量，则基于当前行进行求值。

    • partition_clause及orderby_clause：详情请参见windowing_definition。

  • 返回值说明

    返回值类型同expr类型。

  • 示例

    将所有职工根据部门（deptno）分组（作为开窗列），每位员工的薪水（sal）做偏移。命令示例如下：

    select deptno, ename, sal, lag(sal, 1) over (partition by deptno order by sal) as sal_new from emp;

    返回结果如下：

    +------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | NULL       |
| 10         | MILLER     | 1300       | 1300       |
| 10         | CLARK      | 2450       | 1300       |
| 10         | WELAN      | 2450       | 2450       |
| 10         | KING       | 5000       | 2450       |
| 10         | JACCKA     | 5000       | 5000       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | 800        |
| 20         | JONES      | 2975       | 1100       |
| 20         | SCOTT      | 3000       | 2975       |
| 20         | FORD       | 3000       | 3000       |
| 30         | JAMES      | 950        | NULL       |
| 30         | MARTIN     | 1250       | 950        |
| 30         | WARD       | 1250       | 1250       |
| 30         | TURNER     | 1500       | 1250       |
| 30         | ALLEN      | 1600       | 1500       |
| 30         | BLAKE      | 2850       | 1600       |
+------------+------------+------------+------------+

LAST_VALUE

  • 命令格式 

    last_value(<expr>[, <ignore_nulls>]) over([partition_clause] [orderby_clause] [frame_clause])

  • 命令说明

    返回表达式expr在窗口的最后一条数据上进行运算的结果。

  • 参数说明

    • expr：必填。待计算返回结果的表达式。

    • ignore_nulls：可选。BOOLEAN类型。表示是否忽略NULL值。默认值为False。当参数的值为True时，返回窗口中最后一条非NULL的expr值。

    • partition_clause、orderby_clause及frame_clause：详情请参见windowing_definition。

  • 返回值说明

    返回值类型同expr类型。

  • 示例

    将所有职工根据部门分组，返回每组中的最后一行数据。命令示例如下：

    • 不指定order by，当前窗口为第一行到最后一行的范围，返回当前窗口的最后一行的值。

      select deptno, ename, sal, last_value(sal) over (partition by deptno) as last_value from emp;

      返回结果如下：

      +------------+------------+------------+-------------+
| deptno     | ename      | sal        | last_value |
+------------+------------+------------+-------------+
| 10         | TEBAGE     | 1300       | 2450        |
| 10         | CLARK      | 2450       | 2450        |
| 10         | KING       | 5000       | 2450        |
| 10         | MILLER     | 1300       | 2450        |
| 10         | JACCKA     | 5000       | 2450        |   
| 10         | WELAN      | 2450       | 2450        |   --当前窗口的最后一行。
| 20         | FORD       | 3000       | 2975        |
| 20         | SCOTT      | 3000       | 2975        |
| 20         | SMITH      | 800        | 2975        |
| 20         | ADAMS      | 1100       | 2975        |
| 20         | JONES      | 2975       | 2975        |   --当前窗口的最后一行。
| 30         | TURNER     | 1500       | 2850        |
| 30         | JAMES      | 950        | 2850        |
| 30         | ALLEN      | 1600       | 2850        |
| 30         | WARD       | 1250       | 2850        |
| 30         | MARTIN     | 1250       | 2850        |
| 30         | BLAKE      | 2850       | 2850        |   --当前窗口的最后一行。
+------------+------------+------------+-------------+

    • 指定order by，当前窗口为第一行到当前行的范围。返回当前窗口的当前行的值。

      select deptno, ename, sal, last_value(sal) over (partition by deptno order by sal desc) as last_value from emp;

      返回结果如下：

      +------------+------------+------------+-------------+
| deptno     | ename      | sal        | last_value |
+------------+------------+------------+-------------+
| 10         | JACCKA     | 5000       | 5000        |   --当前窗口的当前行。
| 10         | KING       | 5000       | 5000        |   --当前窗口的当前行。
| 10         | CLARK      | 2450       | 2450        |   --当前窗口的当前行。
| 10         | WELAN      | 2450       | 2450        |   --当前窗口的当前行。
| 10         | TEBAGE     | 1300       | 1300        |   --当前窗口的当前行。
| 10         | MILLER     | 1300       | 1300        |   --当前窗口的当前行。
| 20         | SCOTT      | 3000       | 3000        |   --当前窗口的当前行。
| 20         | FORD       | 3000       | 3000        |   --当前窗口的当前行。
| 20         | JONES      | 2975       | 2975        |   --当前窗口的当前行。
| 20         | ADAMS      | 1100       | 1100        |   --当前窗口的当前行。
| 20         | SMITH      | 800        | 800         |   --当前窗口的当前行。
| 30         | BLAKE      | 2850       | 2850        |   --当前窗口的当前行。
| 30         | ALLEN      | 1600       | 1600        |   --当前窗口的当前行。
| 30         | TURNER     | 1500       | 1500        |   --当前窗口的当前行。
| 30         | MARTIN     | 1250       | 1250        |   --当前窗口的当前行。
| 30         | WARD       | 1250       | 1250        |   --当前窗口的当前行。
| 30         | JAMES      | 950        | 950         |   --当前窗口的当前行。
+------------+------------+------------+-------------+

LEAD

  • 命令格式 

    lead(<expr>[, bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

  • 命令说明

    返回当前行往后（朝分区尾部方向）第offset行数据对应的表达式expr的值。表达式expr可以是列、列运算或者函数运算等。

  • 参数说明

    • expr：必填。待计算返回结果的表达式。

    • offset：可选。偏移量，BIGINT类型常量，取值大于等于0。值为0时表示当前行，为1时表示后一行，以此类推。默认值为1。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后进行运算。

    • default：可选。当offset指定的范围越界时的缺省值，常量，默认值为NULL。需要与expr对应的数据类型相同。如果expr非常量，则基于当前行进行求值。

    • partition_clause及orderby_clause：详情请参见windowing_definition。

  • 返回值说明

    返回值类型同expr类型。

  • 示例

    将所有职工根据部门（deptno）分组（作为开窗列），每位员工的薪水（sal）做偏移。命令示例如下：

    select deptno, ename, sal, lead(sal, 1) over (partition by deptno order by sal) as sal_new from emp;

    返回结果如下：

    +------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | 1300       |
| 10         | MILLER     | 1300       | 2450       |
| 10         | CLARK      | 2450       | 2450       |
| 10         | WELAN      | 2450       | 5000       |
| 10         | KING       | 5000       | 5000       |
| 10         | JACCKA     | 5000       | NULL       |
| 20         | SMITH      | 800        | 1100       |
| 20         | ADAMS      | 1100       | 2975       |
| 20         | JONES      | 2975       | 3000       |
| 20         | SCOTT      | 3000       | 3000       |
| 20         | FORD       | 3000       | NULL       |
| 30         | JAMES      | 950        | 1250       |
| 30         | MARTIN     | 1250       | 1250       |
| 30         | WARD       | 1250       | 1500       |
| 30         | TURNER     | 1500       | 1600       |
| 30         | ALLEN      | 1600       | 2850       |
| 30         | BLAKE      | 2850       | NULL       |
+------------+------------+------------+------------+

MAX

  • 命令格式 

    max(<expr>) over([partition_clause] [orderby_clause] [frame_clause])

  • 命令说明

    返回窗口中expr的最大值。

  • 参数说明

    • expr：必填。用于计算最大值的表达式。除BOOLEAN外的任意类型，当值为NULL时，该行不参与计算。

    • partition_clause、orderby_clause及frame_clause：详情请参见windowing_definition。

  • 返回值说明

    返回值的类型同expr类型。

  • 示例

    • 示例1：指定部门（deptno）为开窗列，计算薪水（sal）最大值，不排序，返回当前窗口（相同deptno）的最大值。执行命令如下：

      select deptno, sal, max(sal) over (partition by deptno) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 5000       |   --窗口开始行，从第1行到第6行的最大值。
| 10         | 2450       | 5000       |   --从第1行到第6行的最大值。
| 10         | 5000       | 5000       |   --从第1行到第6行的最大值。
| 10         | 1300       | 5000       |
| 10         | 5000       | 5000       |
| 10         | 2450       | 5000       |
| 20         | 3000       | 3000       |
| 20         | 3000       | 3000       |
| 20         | 800        | 3000       |
| 20         | 1100       | 3000       |
| 20         | 2975       | 3000       |
| 30         | 1500       | 2850       |
| 30         | 950        | 2850       |
| 30         | 1600       | 2850       |
| 30         | 1250       | 2850       |
| 30         | 1250       | 2850       |
| 30         | 2850       | 2850       |
+------------+------------+------------+

    • 示例2：指定部门（deptno）为开窗列，计算薪水（sal）最大值，并排序，返回当前窗口（相同deptno）从开始行到当前行的最大值。执行命令如下：

      select deptno, sal, max(sal) over (partition by deptno order by sal) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   --窗口开始行。
| 10         | 1300       | 1300       |   --从第1行到第2行的最大值。
| 10         | 2450       | 2450       |   --从第1行到第3行的最大值。
| 10         | 2450       | 2450       |   --从第1行到第4行的最大值。
| 10         | 5000       | 5000       |
| 10         | 5000       | 5000       |
| 20         | 800        | 800        |
| 20         | 1100       | 1100       |
| 20         | 2975       | 2975       |
| 20         | 3000       | 3000       |
| 20         | 3000       | 3000       |
| 30         | 950        | 950        |
| 30         | 1250       | 1250       |
| 30         | 1250       | 1250       |
| 30         | 1500       | 1500       |
| 30         | 1600       | 1600       |
| 30         | 2850       | 2850       |
+------------+------------+------------+

MEDIAN

  • 命令格式 

    median(<expr>) over ([partition_clause] [orderby_clause] [frame_clause])

  • 命令说明

    计算窗口中expr的中位数。

  • 参数说明

    • expr：必填。待计算中位数的表达式。DOUBLE类型或DECIMAL类型。最多支持输入255个数字，至少要输入1个数字。

      • 当输入值为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算，输入为其他数据类型则返回报错。

      • 当输入值为NULL时，返回NULL。

    • partition_clause、orderby_clause及frame_clause：详情请参见windowing_definition。

  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。所有expr为NULL时，返回结果为NULL。

  • 示例

    指定部门（deptno）为开窗列，计算薪水（sal）中位数，返回当前窗口（相同deptno）的中位数。执行命令如下：

    select deptno, sal, median(sal) over (partition by deptno) from emp;

    返回结果如下：

    +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2450.0     |   --窗口开始行，从第1行到第6行的中位数。
| 10         | 2450       | 2450.0     |
| 10         | 5000       | 2450.0     |
| 10         | 1300       | 2450.0     |
| 10         | 5000       | 2450.0     |
| 10         | 2450       | 2450.0     |
| 20         | 3000       | 2975.0     |
| 20         | 3000       | 2975.0     |
| 20         | 800        | 2975.0     |
| 20         | 1100       | 2975.0     |
| 20         | 2975       | 2975.0     |
| 30         | 1500       | 1375.0     |
| 30         | 950        | 1375.0     |
| 30         | 1600       | 1375.0     |
| 30         | 1250       | 1375.0     |
| 30         | 1250       | 1375.0     |
| 30         | 2850       | 1375.0     |
+------------+------------+------------+

MIN

  • 命令格式 

    min(<expr>) over([partition_clause] [orderby_clause] [frame_clause])

  • 命令说明

    返回窗口中expr的最小值。

  • 参数说明

    • expr：必填。用于计算最小值的表达式。除BOOLEAN外的任意类型，当值为NULL时，该行不参与计算。

    • partition_clause、orderby_clause及frame_clause：详情请参见windowing_definition。

  • 返回值说明

    返回值类型同expr类型。

  • 示例

    • 示例1：指定部门（deptno）为开窗列，计算薪水（sal）最小值，不排序，返回当前窗口（相同deptno）的最小值。执行命令如下：

      select deptno, sal, min(sal) over (partition by deptno) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   --窗口开始行，从第1行到第6行的最小值。
| 10         | 2450       | 1300       |   --从第1行到第6行的最小值。
| 10         | 5000       | 1300       |   --从第1行到第6行的最小值。
| 10         | 1300       | 1300       |
| 10         | 5000       | 1300       |
| 10         | 2450       | 1300       |
| 20         | 3000       | 800        |
| 20         | 3000       | 800        |
| 20         | 800        | 800        |
| 20         | 1100       | 800        |
| 20         | 2975       | 800        |
| 30         | 1500       | 950        |
| 30         | 950        | 950        |
| 30         | 1600       | 950        |
| 30         | 1250       | 950        |
| 30         | 1250       | 950        |
| 30         | 2850       | 950        |
+------------+------------+------------+

    • 示例2：指定部门（deptno）为开窗列，计算薪水（sal）最小值，并排序，返回当前窗口（相同deptno）从开始行到当前行的最小值。执行命令如下：

      select deptno, sal, min(sal) over (partition by deptno order by sal) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   --窗口开始行。
| 10         | 1300       | 1300       |   --从第1行到第2行的最小值。
| 10         | 2450       | 1300       |   --从第1行到第3行的最小值。
| 10         | 2450       | 1300       |
| 10         | 5000       | 1300       |
| 10         | 5000       | 1300       |
| 20         | 800        | 800        |
| 20         | 1100       | 800        |
| 20         | 2975       | 800        |
| 20         | 3000       | 800        |
| 20         | 3000       | 800        |
| 30         | 950        | 950        |
| 30         | 1250       | 950        |
| 30         | 1250       | 950        |
| 30         | 1500       | 950        |
| 30         | 1600       | 950        |
| 30         | 2850       | 950        |
+------------+------------+------------+

NTILE

  • 命令格式 

    bigint ntile(bigint <N>) over ([partition_clause] [orderby_clause])

  • 命令说明

    用于将分区中的数据按照顺序切分成N等份，并返回数据所在等份的编号。如果分区中的数据不能被均匀地切分成N等份时，最前面的等份（编号较小的）会优先多分配1条数据。

  • 参数说明

    • N：必填。切片数量。BIGINT类型。

    • partition_clause及orderby_clause：详情请参见windowing_definition。

  • 返回值说明

    返回BIGINT类型。

  • 示例

    将所有职工根据部门按薪水（sal）从高到低切分为3组，并获得职工自己所在组的序号。命令示例如下：

    select deptno, ename, sal, ntile(3) over (partition by deptno order by sal desc) as nt3 from emp;

    返回结果如下：

    +------------+------------+------------+------------+
| deptno     | ename      | sal        | nt3        |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 2          |
| 10         | WELAN      | 2450       | 2          |
| 10         | TEBAGE     | 1300       | 3          |
| 10         | MILLER     | 1300       | 3          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 2          |
| 20         | ADAMS      | 1100       | 2          |
| 20         | SMITH      | 800        | 3          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 1          |
| 30         | TURNER     | 1500       | 2          |
| 30         | MARTIN     | 1250       | 2          |
| 30         | WARD       | 1250       | 3          |
| 30         | JAMES      | 950        | 3          |
+------------+------------+------------+------------+

NTH_VALUE

  • 命令格式 

    nth_value(<expr>, <number> [, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

  • 命令说明

    返回表达式expr在窗口的第N条数据进行运算的结果。

  • 参数说明

    • expr：必填。待计算返回结果的表达式。

    • number：必填。BIGINT类型。大于等于1的整数。值为1时与FIRST_VALUE等价。

    • ignore_nulls：可选。BOOLEAN类型。表示是否忽略NULL值。默认值为False。当参数的值为True时，返回窗口中第N条非NULL的expr值。

    • partition_clause、orderby_clause及frame_clause：详情请参见windowing_definition。

  • 返回值说明

    返回值类型同expr类型。

  • 示例

    将所有职工根据部门分组，返回每组中的第6行数据。命令示例如下：

    • 不指定order by，当前窗口为第一行到最后一行的范围，返回当前窗口第6行的值。

      select deptno, ename, sal, nth_value(sal,6) over (partition by deptno) as nth_value from emp;

      返回结果如下：

      +------------+------------+------------+------------+
| deptno     | ename      | sal        | nth_value  |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | 2450       |
| 10         | CLARK      | 2450       | 2450       |
| 10         | KING       | 5000       | 2450       |
| 10         | MILLER     | 1300       | 2450       |
| 10         | JACCKA     | 5000       | 2450       |
| 10         | WELAN      | 2450       | 2450       |   --当前窗口的第6行。
| 20         | FORD       | 3000       | NULL       |
| 20         | SCOTT      | 3000       | NULL       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | NULL       |
| 20         | JONES      | 2975       | NULL       |   --当前窗口的没有第6行，返回NULL。
| 30         | TURNER     | 1500       | 2850       |
| 30         | JAMES      | 950        | 2850       |
| 30         | ALLEN      | 1600       | 2850       |
| 30         | WARD       | 1250       | 2850       |
| 30         | MARTIN     | 1250       | 2850       |
| 30         | BLAKE      | 2850       | 2850       |   --当前窗口的第6行。
+------------+------------+------------+------------+

    • 指定order by，当前窗口为第一行到当前行的范围，返回当前窗口第6行的值。

      select deptno, ename, sal, nth_value(sal,6) over (partition by deptno order by sal) as nth_value from emp;

      返回结果如下：

      +------------+------------+------------+------------+
| deptno     | ename      | sal        | nth_value  |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | NULL       |   
| 10         | MILLER     | 1300       | NULL       |   --当前窗口只有2行，第6行超过了窗口长度。
| 10         | CLARK      | 2450       | NULL       |
| 10         | WELAN      | 2450       | NULL       |
| 10         | KING       | 5000       | 5000       |  
| 10         | JACCKA     | 5000       | 5000       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | NULL       |
| 20         | JONES      | 2975       | NULL       |
| 20         | SCOTT      | 3000       | NULL       |
| 20         | FORD       | 3000       | NULL       |
| 30         | JAMES      | 950        | NULL       |
| 30         | MARTIN     | 1250       | NULL       |
| 30         | WARD       | 1250       | NULL       |
| 30         | TURNER     | 1500       | NULL       |
| 30         | ALLEN      | 1600       | NULL       |
| 30         | BLAKE      | 2850       | 2850       |
+------------+------------+------------+------------+

PERCENT_RANK

  • 命令格式 

    double percent_rank() over([partition_clause] [orderby_clause])

  • 命令说明

    计算当前行在分区中按照orderby_clause排序后的百分比排名。

  • 参数说明

    partition_clause及orderby_clause：详情请参见windowing_definition。

  • 返回值说明

    返回DOUBLE类型，值域为[0.0, 1.0]。具体的返回值等于“(rank - 1) / (partition_row_count - 1)”，其中：rank为该行数据的RANK窗口函数的返回结果，partition_row_count为该行数据所属分区的数据行数。当分区中只有一行数据时，输出结果为0.0。

  • 示例

    计算员工薪水在组内的百分比排名。命令示例如下：

    select deptno, ename, sal, percent_rank() over (partition by deptno order by sal desc) as sal_new from emp;

    返回结果如下：

    +------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 0.0        |
| 10         | KING       | 5000       | 0.0        |
| 10         | CLARK      | 2450       | 0.4        |
| 10         | WELAN      | 2450       | 0.4        |
| 10         | TEBAGE     | 1300       | 0.8        |
| 10         | MILLER     | 1300       | 0.8        |
| 20         | SCOTT      | 3000       | 0.0        |
| 20         | FORD       | 3000       | 0.0        |
| 20         | JONES      | 2975       | 0.5        |
| 20         | ADAMS      | 1100       | 0.75       |
| 20         | SMITH      | 800        | 1.0        |
| 30         | BLAKE      | 2850       | 0.0        |
| 30         | ALLEN      | 1600       | 0.2        |
| 30         | TURNER     | 1500       | 0.4        |
| 30         | MARTIN     | 1250       | 0.6        |
| 30         | WARD       | 1250       | 0.6        |
| 30         | JAMES      | 950        | 1.0        |
+------------+------------+------------+------------+

RANK

  • 命令格式 

    bigint rank() over ([partition_clause] [orderby_clause])

  • 命令说明

    计算当前行在分区中按照orderby_clause排序后所处的排名。从1开始计数。

  • 参数说明

    partition_clause及orderby_clause：详情请参见windowing_definition。

  • 返回值说明

    返回BIGINT类型。返回值可能重复、且不连续。具体的返回值为该行数据所在GROUP的第一条数据的ROW_NUMBER()值。未指定orderby_clause时，返回结果全为1。

  • 示例

    将所有职工根据部门（deptno）分组（作为开窗列），每个组内根据薪水（sal）做降序排序，获得职工自己组内的序号。命令示例如下：

    select deptno, ename, sal, rank() over (partition by deptno order by sal desc) as nums from emp;

    返回结果如下：

    +------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 3          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 5          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 4          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

ROW_NUMBER

  • 命令格式 

    row_number() over([partition_clause] [orderby_clause])

  • 命令说明

    计算当前行在分区中的行号，从1开始递增。

  • 参数说明

    请参见windowing_definition。不允许包含frame_clause。

  • 返回值说明

    返回BIGINT类型。

  • 示例

    将所有职工根据部门（deptno）分组（作为开窗列），每个组内根据薪水（sal）做降序排序，获得职工在自己组内的序号。命令示例如下：

    select deptno, ename, sal, row_number() over (partition by deptno order by sal desc) as nums from emp;

    返回结果如下：

    +------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 2          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 4          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 6          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 2          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 5          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

STDDEV

  • 命令格式

    double stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

  • 命令说明

    计算总体标准差，STDDEV_POP函数的别名。

  • 参数说明

    • expr：必填。待计算总体标准差的表达式。DOUBLE类型或DECIMAL类型。

      • 当输入值为STRING类型或BIGINT类型时，会隐式转换为DOUBLE类型后参与运算，其他类型返回报错。

      • 当输入值为NULL时，该行不参与计算。

      • 当指定distinct关键字时，表示计算唯一值的总体标准差。

    • partition_clause、orderby_clause及frame_clause：详情请参见windowing_definition。

  • 返回值说明

    返回值类型同expr类型。所有expr为NULL时，返回结果为NULL。

  • 示例

    • 示例1：指定部门（deptno）为开窗列，计算薪水（sal）总体标准差，不排序，返回当前窗口（相同deptno）的累计总体标准差。命令示例如下：

      select deptno, sal, stddev(sal) over (partition by deptno) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1546.1421524412158 |   --窗口开始行，从第1行到第6行的累计总体标准差。
| 10         | 2450       | 1546.1421524412158 |   --从第1行到第6行的累计总体标准差。
| 10         | 5000       | 1546.1421524412158 |
| 10         | 1300       | 1546.1421524412158 |
| 10         | 5000       | 1546.1421524412158 |
| 10         | 2450       | 1546.1421524412158 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 800        | 1004.7387720198718 |
| 20         | 1100       | 1004.7387720198718 |
| 20         | 2975       | 1004.7387720198718 |
| 30         | 1500       | 610.1001739241042 |
| 30         | 950        | 610.1001739241042 |
| 30         | 1600       | 610.1001739241042 |
| 30         | 1250       | 610.1001739241042 |
| 30         | 1250       | 610.1001739241042 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

    • 示例2：非Hive兼容模式下，指定部门（deptno）为开窗列，计算薪水（sal）总体标准差，并排序，返回当前窗口（相同deptno）从开始行到当前行的累计总体标准差。命令示例如下：

      --关闭Hive兼容模式。
set odps.sql.hive.compatible=false;
--执行如下SQL命令。
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |           --窗口开始行。
| 10         | 1300       | 0.0        |           --从第1行到第2行的累计总体标准差。
| 10         | 2450       | 542.1151989096865 |    --从第1行到第3行的累计总体标准差。
| 10         | 2450       | 575.0      |           --从第1行到第4行的累计总体标准差。
| 10         | 5000       | 1351.6656391282572 |
| 10         | 5000       | 1546.1421524412158 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 150.0      |
| 20         | 2975       | 962.4188277460079 |
| 20         | 3000       | 1024.2947268730811 |
| 20         | 3000       | 1004.7387720198718 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 150.0      |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1500       | 194.8557158514987 |
| 30         | 1600       | 226.71568097509268 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

    • 示例3：Hive兼容模式下，指定部门（deptno）为开窗列，计算薪水（sal）总体标准差，并排序，返回当前窗口（相同deptno）从开始行到与当前行相同值所在行（相同sal值的总体标准差相同）的累计总体标准差。命令示例如下：

      --开启Hive兼容模式。
set odps.sql.hive.compatible=true;
--执行如下SQL命令。
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |           --窗口开始行。第1行和第2行的sal一致，则第1行的总体标准差为第1行到第2行的累计总体标准差。
| 10         | 1300       | 0.0        |           --从第1行到第2行的累计总体标准差。
| 10         | 2450       | 575.0      |           --第3行和第4行的sal一致，则第3行的总体标准差为从第1行到第4行的累计总体标准差。
| 10         | 2450       | 575.0      |           --从第1行到第4行的累计总体标准差。
| 10         | 5000       | 1546.1421524412158 |
| 10         | 5000       | 1546.1421524412158 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 150.0      |
| 20         | 2975       | 962.4188277460079 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 3000       | 1004.7387720198718 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1500       | 194.8557158514987 |
| 30         | 1600       | 226.71568097509268 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

STDDEV_SAMP

  • 命令格式 

    double stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])
decimal stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])

  • 命令说明

    计算样本标准差。

  • 参数说明

    • expr：必填。待计算样本标准差的表达式。DOUBLE类型或DECIMAL类型。

      • 当输入值为STRING类型或BIGINT类型时，会隐式转换为DOUBLE类型后参与运算，其他类型返回报错。

      • 当输入值为NULL时，该行不参与计算。

      • 当指定distinct关键字时，表示计算唯一值的样本标准差。

    • partition_clause、orderby_clause及frame_clause：详情请参见windowing_definition。

  • 返回值说明

    返回值类型同expr类型。所有expr为NULL时，返回结果为NULL。窗口仅包含1条expr值非NULL的数据时，结果为0。

  • 示例

    • 示例1：指定部门（deptno）为开窗列，计算薪水（sal）样本标准差，不排序，返回当前窗口（相同deptno）的累计样本标准差。命令示例如下：

      select deptno, sal, stddev_samp(sal) over (partition by deptno) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1693.7138680032904 |   --窗口开始行，第1行到第6行的累计样本标准差。
| 10         | 2450       | 1693.7138680032904 |   --从第1行到第6行的累计样本标准差。
| 10         | 5000       | 1693.7138680032904 |   --从第1行到第6行的累计样本标准差。
| 10         | 1300       | 1693.7138680032904 |     
| 10         | 5000       | 1693.7138680032904 |
| 10         | 2450       | 1693.7138680032904 |
| 20         | 3000       | 1123.3320969330487 |
| 20         | 3000       | 1123.3320969330487 |
| 20         | 800        | 1123.3320969330487 |
| 20         | 1100       | 1123.3320969330487 |
| 20         | 2975       | 1123.3320969330487 |
| 30         | 1500       | 668.331255192114 |
| 30         | 950        | 668.331255192114 |
| 30         | 1600       | 668.331255192114 |
| 30         | 1250       | 668.331255192114 |
| 30         | 1250       | 668.331255192114 |
| 30         | 2850       | 668.331255192114 |
+------------+------------+------------+

    • 示例2：指定部门（deptno）为开窗列，计算薪水（sal）样本标准差，并排序，返回当前窗口（相同deptno）从开始行到当前行的累计样本标准差。命令示例如下：

      select deptno, sal, stddev_samp(sal) over (partition by deptno order by sal) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |          --窗口开始行。
| 10         | 1300       | 0.0        |          --从第1行到第2行的累计样本标准差。
| 10         | 2450       | 663.9528095680697 |   --从第1行到第3行的累计样本标准差。
| 10         | 2450       | 663.9528095680696 |
| 10         | 5000       | 1511.2081259707413 |
| 10         | 5000       | 1693.7138680032904 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 212.13203435596427 |
| 20         | 2975       | 1178.7175234126282 |
| 20         | 3000       | 1182.7536725793752 |
| 20         | 3000       | 1123.3320969330487 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 212.13203435596427 |
| 30         | 1250       | 173.20508075688772 |
| 30         | 1500       | 225.0      |
| 30         | 1600       | 253.4758371127315 |
| 30         | 2850       | 668.331255192114 |
+------------+------------+------------+

SUM

  • 命令格式 

    sum([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

  • 命令说明

    返回窗口中expr之和。

  • 参数说明

    • expr：必填。待计算汇总值的列。DOUBLE类型、DECIMAL类型或BIGINT类型。

      • 当输入值为STRING类型时，会隐式转换为DOUBLE类型后参与运算，其他类型返回报错。

      • 当输入值为NULL时，该行不参与计算。

      • 当指定distinct关键字时，表示计算唯一值的汇总值。

    • partition_clause、orderby_clause及frame_clause：详情请参见windowing_definition。

  • 返回值说明

    • 输入值为BIGINT类型时，返回BIGINT类型。

    • 输入值为DECIMAL类型时，返回DECIMAL类型。

    • 输入值为DOUBLE类型或STRING类型时，返回DOUBLE类型。

    • 输入值都为NULL时，返回NULL。

  • 示例

    • 示例1：指定部门（deptno）为开窗列，计算薪水（sal）汇总值，不排序，返回当前窗口（相同deptno）的累计汇总值。命令示例如下：

      select deptno, sal, sum(sal) over (partition by deptno) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 17500      |   --窗口开始行。第1行到第6行的累计汇总值。
| 10         | 2450       | 17500      |   --从第1行到第6行的累计汇总值。
| 10         | 5000       | 17500      |   --从第1行到第6行的累计汇总值。
| 10         | 1300       | 17500      |
| 10         | 5000       | 17500      |
| 10         | 2450       | 17500      |
| 20         | 3000       | 10875      |
| 20         | 3000       | 10875      |
| 20         | 800        | 10875      |
| 20         | 1100       | 10875      |
| 20         | 2975       | 10875      |
| 30         | 1500       | 9400       |
| 30         | 950        | 9400       |
| 30         | 1600       | 9400       |
| 30         | 1250       | 9400       |
| 30         | 1250       | 9400       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

    • 示例2：非Hive兼容模式下，指定部门（deptno）为开窗列，计算薪水（sal）汇总值，并排序，返回当前窗口（相同deptno）从开始行到当前行的累计汇总值。命令示例如下：

      --关闭Hive兼容模式。
set odps.sql.hive.compatible=false;
--执行如下SQL命令。
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   --窗口开始行。
| 10         | 1300       | 2600       |   --从第1行到第2行的累计汇总值。
| 10         | 2450       | 5050       |   --从第1行到第3行的累计汇总值。
| 10         | 2450       | 7500       |
| 10         | 5000       | 12500      |
| 10         | 5000       | 17500      |
| 20         | 800        | 800        |
| 20         | 1100       | 1900       |
| 20         | 2975       | 4875       |
| 20         | 3000       | 7875       |
| 20         | 3000       | 10875      |
| 30         | 950        | 950        |
| 30         | 1250       | 2200       |
| 30         | 1250       | 3450       |
| 30         | 1500       | 4950       |
| 30         | 1600       | 6550       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

    • 示例3：Hive兼容模式下，指定部门（deptno）为开窗列，计算薪水（sal）汇总值，并排序，返回当前窗口（相同deptno）从开始行到与当前行相同值所在行（相同sal值的汇总值相同）的累计汇总值。命令示例如下：

      --开启Hive兼容模式。
set odps.sql.hive.compatible=true;
--执行如下SQL命令。
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

      返回结果如下：

      +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2600       |   --窗口开始行。第1行和第2行的sal一致，则第1行的汇总值为第1行到第2行的累计汇总值。
| 10         | 1300       | 2600       |   --从第1行到第2行的累计汇总值。
| 10         | 2450       | 7500       |   --第3行和第4行的sal一致，则第3行的汇总值为从第1行到第4行的累计汇总值。
| 10         | 2450       | 7500       |   --从第1行到第4行的累计汇总值。
| 10         | 5000       | 17500      |
| 10         | 5000       | 17500      |
| 20         | 800        | 800        |
| 20         | 1100       | 1900       |
| 20         | 2975       | 4875       |
| 20         | 3000       | 10875      |
| 20         | 3000       | 10875      |
| 30         | 950        | 950        |
| 30         | 1250       | 3450       |
| 30         | 1250       | 3450       |
| 30         | 1500       | 4950       |
| 30         | 1600       | 6550       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

相关文档

上一篇：WIDTH_BUCKET下一篇：AVG