MaxCompute窗口函数介绍

发布日期：2025-01-03 18:01    点击次数：175

窗口函数支持在一个动态定义的数据子集上执行聚合操作或其他计算，常用于处理时间序列数据、排名、移动平均等问题，本文为您介绍MaxCompute SQL支持的窗口函数的命令格式、参数说明及示例，指导您使用窗口函数完成开发。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在SELECT语句中加入窗口函数，计算窗口函数的结果时，数据会按照窗口定义中的partition by和order by语句进行分区和排序。如果没有partition by语句，则仅有一个分区，包含全部数据。如果没有order by语句，则分区内的数据会按照任意顺序排布，最终生成一个数据流。之后对于每一行数据（当前行），会按照窗口定义中的frame_clause从数据流中截取一段数据，构成当前行的窗口。窗口函数会根据窗口中包含的数据，计算得到窗口函数针对当前行对应的输出结果。partition by <expression> [, ...]：可选。指定分区。分区列的值相同的行被视为在同一个窗口内。详细格式请参见表操作。order by <expression> [asc|desc][nulls {first|last}] [, ...]：可选。指定数据在一个窗口内如何排序。 frame_clause：可选。用于确定数据边界，更多frame_clause信息，请参见frame_clause。 filter_clausefilter_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 | +------------+------------+frame_clauseROWS|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、GROUPSUNBOUNDED PRECEDING表示分区的第一行，从1开始计数。UNBOUNDED FOLLOWING表示分区的最后一行。ROWSCURRENT ROW指当前行的位置。每一行数据都会对应一个窗口函数的结果值，当前行是指在给哪一行数据计算窗口函数的结果。offset PRECEDING指从当前行位置，向分区头部位置移动offset行的位置。例如0 PRECEDING指当前行，1 PRECEDING指前一行。offset必须为非负整数。offset FOLLOWING指从当前行位置，向分区尾部移动offset行的位置。例如0 FOLLOWING指当前行，1 FOLLOWING指下一行。offset必须为非负整数。RANGECURRENT ROW作为frame_start时，指第一条与当前行具有相同order by列值的数据的位置。作为frame_end时，指最后一条与当前行具有相同order by列值的数据的位置。offset PRECEDINGframe_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 FOLLOWINGframe_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数据的位置。GROUPSCURRENT ROW作为frame_start时，指当前行所属GROUP的第一条数据。作为frame_end时，指当前行所属GROUP的最后一行数据。offset PRECEDING作为frame_start时，指从当前行所属GROUP开始，朝分区头部移动offset个GROUP之后，所在GROUP的第一条数据的位置。作为frame_end时，指从当前行所属GROUP开始，朝分区头部移动offset个GROUP之后，所在GROUP的最后一条数据的位置。offset FOLLOWING作为frame_start时，指从当前行所属GROUP开始，朝分区尾部移动offset个GROUP之后，所在GROUP的第一条数据的位置。作为frame_end时，指从当前行所属GROUP开始，朝分区尾部移动offset个GROUP之后，所在GROUP的最后一条数据的位置。frame_exclusion：可选。用于从窗口中剔除一部分数据。取值范围如下：EXCLUDE NO OTHERS：表示不剔除任何数据。EXCLUDE CURRENT ROW：表示剔除当前行。EXCLUDE GROUP：表示剔除整个GROUP，即分区中与当前行具有相同order by值的所有数据。EXCLUDE TIES：表示剔除整个GROUP，但保留当前行。默认frame_clause窗口边界示例ROW类型窗口窗口定义1partition 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] | +------------+------------+------------+--------+窗口定义2partition 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] | +------------+------------+------------+--------+窗口定义3partition 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 | +------------+------------+------------+--------+窗口定义4partition 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] | +------------+------------+------------+--------+窗口定义5partition 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 | +------------+------------+------------+--------+窗口定义6partition 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类型窗口窗口定义1partition 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]。窗口定义2partition 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] | +------------+------------+------------+--------+窗口定义3partition 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] | +------------+------------+------------+--------+示例数据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类型。示例 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为该行数据所属分区的数据行数。示例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。示例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。示例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类型。示例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。示例ROW_NUMBER命令格式 row_number() over([partition_clause] [orderby_clause])命令说明 计算当前行在分区中的行号，从1开始递增。参数说明 请参见windowing_definition。不允许包含frame_clause。返回值说明 返回BIGINT类型。示例 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 | +------------+------------+------------+相关文档如果上述内建函数不满足需求，MaxCompute还支持自定义函数。有关自定义函数的详细信息，请参见MaxCompute UDF概述。MaxCompute SQL常见问题，详情请参见：DDL操作常见问题DML操作常见问题DQL操作常见问题SQL其他常见问题MaxCompute内建函数常见错误码和常见问题，详情请参见：内建函数常见错误码内建函数常见问题