您可以在MaxCompute SQL中使用窗口函数，灵活地分析处理工作。

注意
  • 窗口函数只能出现在select子句中。
  • 窗口函数中不能嵌套使用窗口函数和聚合函数。
  • 窗口函数不能和同级别的聚合函数一起使用。
  • 在一个MaxCompute SQL语句中，最多可以使用5个窗口函数。

窗口函数语法说明

窗口函数的语法声明如下。 
window_func() over (partition by [col1,col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] windowing_clause)
  • partition by部分用来指定开窗的列。
  • 分区列的值相同的行被视为在同一个窗口内。同一窗口内最多包含1亿行数据（建议不超过500万行），否则运行时报错。该限制适用于所有内建窗口函数。
    说明 例如group byUID，同1个UID对应的记录视为同一个窗口，即1个UID对应记录数最多包含1亿行数据（建议不超过500万行）。
  • order by用于指定数据在一个窗口内如何排序。
    说明 当遇到相同的order by值的时候，排序结果不稳定。为了减少随机性，应当尽可能保持order by值的唯一性。
  • windowing_clause部分可以用rows指定开窗方式，有以下两种方式：
    • rows between x preceding|following and y preceding|following表示窗口范围是从前或后x行到前或后y行。
    • rows x preceding|following窗口范围是从前或后第x行到当前行。
    说明
    • x、y必须为大于等于0的整数常量，限定范围0~10000，值为0时表示当前行。您必须指定order by才可以用rows方式指定窗口范围。
    • 并非所有的窗口函数都可以使用rows指定开窗方式。支持这种用法的窗口函数有AVG、COUNT、MAX、MIN、STDDEV和SUM。

COUNT

COUNT的命令格式、说明和示例如下：
  • 命令格式 
    Bigint count([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
  • 命令说明

    该函数用于计算计数值。

  • 参数说明
    • expr：任意类型，当值为NULL时，该行不参与计算。当指定distinct关键字时，表示取唯一值的计数值。
    • partition by [col1, col2…]：指定开窗口的列。
    • order by col1 [asc|desc], col2[asc|desc]：不指定order by时，返回当前窗口内expr的计数值，指定order by时返回结果以指定的顺序排序，并且值为当前窗口内从开始行到当前行的累计计数值。
    说明 当指定distinct关键字时，不能使用order by。
  • 返回值说明

    返回BIGINT类型。

  • 示例
    假设存在表test_src，表中存在BIGINT类型的列user_id。 
    select user_id,count(user_id) over (partition by user_id) as count
from test_src;
+---------+------------+
| user_id |  count     |
+---------+------------+
| 1       | 3          |
| 1       | 3          |
| 1       | 3          |
| 2       | 1          |
| 3       | 1          |
+---------+------------+    
--不指定order by时，返回当前窗口内user_id的计数值。
select user_id,count(user_id) over (partition by user_id order by user_id) as count
from test_src;
+---------+------------+
| user_id | count      |
+---------+------------+
| 1       | 1          |      --窗口为起始点。
| 1       | 2          |      --到当前行共计两条记录，返回2。
| 1       | 3          |
| 2       | 1          |
| 3       | 1          |
+---------+------------+
--指定order by时，返回当前窗口内从开始行到当前行的累计计数值。
如果指定的order by的值重复，非Hive兼容和Hive兼容的处理方式不同：
  • 当非Hive兼容时，返回值为各自行的COUNT。 
    set odps.sql.hive.compatible=false;
select user_id, price, count(price) over
  (partition by user_id order by price) as count from test_src;

+------------+------------+------------+
| user_id    | price      | count      |
+------------+------------+------------+
| 1          | 4.5        | 1          |  -- 窗口起始行
| 1          | 5.5        | 2          |  -- 第2行的count是2
| 1          | 5.5        | 3          |  -- 第3行的count是3
| 1          | 6.5        | 4          |
| 2          | NULL       | 0          |
| 2          | 3.0        | 1          |
| 3          | NULL       | 0          |
| 3          | 4.0        | 1          |
+------------+------------+------------+
  • 当Hive兼容时，返回值为相同值的最后一行的COUNT。 
    set odps.sql.hive.compatible=true;
select user_id, price, count(price) over
  (partition by user_id order by price) as count from test_src;

+------------+------------+------------+
| user_id    | price      | count      |
+------------+------------+------------+
| 1          | 4.5        | 1          | -- 窗口起始行
| 1          | 5.5        | 3          | -- 第2行和第3行的price一致，则第2行的count为第3行的count
| 1          | 5.5        | 3          | -- 第3行的count是3
| 1          | 6.5        | 4          |
| 2          | NULL       | 0          |
| 2          | 3.0        | 1          |
| 3          | NULL       | 0          |
| 3          | 4.0        | 1          |
+------------+------------+------------+

AVG

AVG的命令格式、说明和示例如下：
  • 命令格式 
    avg([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
  • 命令说明

    该函数用于计算平均值。

  • 参数说明
    • distinct：当指定distinct关键字时，表示取唯一值的平均值。
    • expr：DOUBLE类型、DECIMAL类型。
      • 当输入值为STRING、BIGINT类型时，会隐式转换到DOUBLE类型后参与运算，输入其它数据类型抛异常。
      • 当输入值为NULL时，该行不参与计算。
      • BOOLEAN类型不允许参与计算。
    • partition by [col1, col2...]：指定开窗口的列。
    • order by col1[asc|desc], col2[asc|desc]：不指定order by时，返回当前窗口内所有值的平均值。指定order by时，返回结果以指定的方式排序，并且返回窗口内从开始行到当前行的累计平均值。
    说明 指明distinct关键字时，不能使用order by。
  • 返回值说明

    返回DOUBLE类型。

如果指定的order by的值重复，非Hive兼容和Hive兼容的处理方式不同：
  • 当非Hive兼容时，返回值为各自行的AVG。 
    set odps.sql.hive.compatible=false;
select user_id, price, avg(price) over
  (partition by user_id order by price) from test_src;

+------------+------------+-------------------+
| user_id    | price      | _c2               |
+------------+------------+-------------------+
| 1          | 4.5        | 4.5               |    --窗口起始行
| 1          | 5.5        | 5.0               |    --从第1行到第2行的AVG
| 1          | 5.5        | 5.166666666666667 |    --从第1行到第3行的AVG
| 1          | 6.5        | 5.5               |
| 2          | NULL       | NULL              |
| 2          | 3.0        | 3.0               |
| 3          | NULL       | NULL              |
| 3          | 4.0        | 4.0               |
+------------+------------+-------------------+
  • 当Hive兼容时，返回值为相同值的最后一行的AVG。 
    set odps.sql.hive.compatible=true;
select user_id, price, avg(price) over
  (partition by user_id order by price) from test_src;

+------------+------------+-------------------+
| user_id    | price      | _c2               |
+------------+------------+-------------------+
| 1          | 4.5        | 4.5               |  --窗口起始行
| 1          | 5.5        | 5.166666666666667 |  --从第1行到第3行的AVG
| 1          | 5.5        | 5.166666666666667 |  --从第1行到第3行的AVG
| 1          | 6.5        | 5.5               |
| 2          | NULL       | NULL              |
| 2          | 3.0        | 3.0               |
| 3          | NULL       | NULL              |
| 3          | 4.0        | 4.0               |
+------------+------------+-------------------+

MAX

  • 命令格式 
    max([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
  • 命令说明

    该函数用于计算最大值。

  • 参数说明
    • expr：除BOOLEAN外的任意类型。当值为NULL时，该行不参与计算。当指定distinct关键字时，表示取唯一值的最大值（指定该参数与否对结果没有影响）。
    • partition by [col1, col2…]：指定开窗口的列。
    • order by [col1[asc|desc], col2[asc|desc：不指定order by时，返回当前窗口内的最大值。指定order by时，返回结果以指定的方式排序，并且值为当前窗口内从开始行到当前行的最大值。
    说明 指明distinct关键字时，不能使用order by。
  • 返回值说明

    返回值的类型同expr类型。

MIN

  • 命令格式 
    min([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
  • 命令说明

    该函数用于计算最小值。

  • 参数说明
    • expr：除BOOLEAN外的任意类型，当值为NULL时，该行不参与计算。当指定distinct关键字时，表示取唯一值的最小值（指定该参数与否对结果没有影响）。
    • partition by [col1, col2…]：指定开窗口的列。
    • order by [col1[asc|desc], col2[asc|desc：不指定order by时，返回当前窗口内的最小值。指定order by时，返回结果以指定的方式排序，并且值为当前窗口内从开始行到当前行的最小值。
    说明 指明distinct关键字时，不能使用order by。
  • 返回值说明

    返回值类型同expr类型。

MEDIAN

  • 命令格式 
    Double median(Double number1,number2...) over(partition by [col1, col2…])
Decimal median(Decimal number1,number2...) over(partition by [col1,col2…])
  • 命令说明

    该函数用于计算中位数最小值。

  • 参数说明
    • number1,number1…：DOUBLE类型或DECIMAL类型的数字。 最多支持输入255个数字，至少要输入1个数字。
      • 如果输入值是DOUBLE类型，会默认转成DOUBLE类型的数组参与计算。
      • 当输入值为STRING类型或BIGINT类型，会隐式转换到DOUBLE类型后参与运算，输入为其他数据类型则抛异常。
      • 当输入值为NULL时，返回NULL。
    • partition by [col1, col2…]：指定开窗口的列。
  • 返回值说明

    返回值类型为DOUBLE类型或DECIMAL类型。

STDDEV

STDDEV的命令格式、说明和示例如下：
  • 命令格式 
    Double stddev([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
Decimal stddev([distinct] expr) over(partition by [col1, col2…] 
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
  • 命令说明

    该函数用于计算总体标准差。

  • 参数说明
    • expr：DOUBLE类型或DECIMAL类型。
      • 当输入值为STRING类型或BIGINT类型时，会隐式转换到DOUBLE类型后参与运算，其他类型抛异常。
      • 当输入值为NULL时，该行不参与计算。
      • 当指定distinct关键字时，表示计算唯一值的总体标准差。
      说明
      • 当指定distinct关键字时，不能使用order by。
      • stddev还有一个别名函数stddev_pop，用法和stddev一样。
    • partition by [col1, col2..]：指定开窗口的列。
    • order by col1[asc|desc], col2[asc|desc]：不指定order by时，返回当前窗口内的总体标准差。指定order by时，返回结果以指定的方式排序，并且值为当前窗口内从开始行到当前行的总体标准差。
  • 返回值说明

    输入值为DECIMAL类型时，返回DECIMAL类型，否则返回DOUBLE类型。

  • 示例 
    select window, seq, stddev_pop('1\01') over (partition by window order by seq) from dual;
如果指定的order by的值重复，非Hive兼容和Hive兼容的处理方式不同：
  • 当非Hive兼容时，返回值为各自行的STDDEV。 
    set odps.sql.hive.compatible=false;
select user_id, price, stddev(price) over
  (partition by user_id order by price) from test_src;

+------------+------------+--------------------+
| user_id    | price      | _c2                |
+------------+------------+--------------------+
| 1          | 4.5        | 0.0                |  --窗口起始行
| 1          | 5.5        | 0.5                |  --从第1行到第2行的STDDEV
| 1          | 5.5        | 0.4714045207910316 |  --从第1行到第3行的STDDEV
| 1          | 6.5        | 0.7071067811865475 |
| 2          | NULL       | NULL               |
| 2          | 3.0        | 0.0                |
| 3          | NULL       | NULL               |
| 3          | 4.0        | 0.0                |
+------------+------------+--------------------+
  • 当Hive兼容时，返回值为相同值的最后一行的STDDEV。 
    set odps.sql.hive.compatible=true;
select user_id, price, stddev(price) over
  (partition by user_id order by price) from test_src;

+------------+------------+--------------------+
| user_id    | price      | _c2                |
+------------+------------+--------------------+
| 1          | 4.5        | 0.0                | --窗口起始行
| 1          | 5.5        | 0.4714045207910316 | --从第1行到第3行的STDDEV
| 1          | 5.5        | 0.4714045207910316 | --从第1行到第3行的STDDEV
| 1          | 6.5        | 0.7071067811865475 |
| 2          | NULL       | NULL               |
| 2          | 3.0        | 0.0                |
| 3          | NULL       | NULL               |
| 3          | 4.0        | 0.0                |
+------------+------------+--------------------+

STDDEV_SAMP

  • 命令格式 
    Double stddev_samp([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
Decimal stddev_samp([distinct] expr) over((partition by [col1,col2…] 
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
  • 命令说明

    该函数用于计算样本标准差。

  • 参数说明
    • expr：DOUBLE类型或DECIMAL类型。
      • 当输入值为STRING类型或BIGINT类型时，会隐式转换到DOUBLE类型后参与运算，其他类型抛异常。
      • 当输入值为NULL时，该行不参与计算。
      • 当指定distinct关键字时，表示计算唯一值的样本标准差。
      说明 指明distinct关键字时，不能使用order by。
    • partition by [col1, col2..]：指定开窗口的列。
    • order by col1[asc|desc], col2[asc|desc]：不指定order by时，返回当前窗口内的样本标准差。指定order by时，返回结果以指定的方式排序，并且值为当前窗口内从开始行到当前行的样本标准差。
  • 返回值说明

    输入值为DECIMAL类型时，返回DECIMAL类型，否则返回DOUBLE类型。

SUM

SUM的命令格式、说明和示例如下：
  • 命令格式 
    sum([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
  • 命令说明

    该函数用于计算汇总值。

  • 参数说明
    • expr：DOUBLE类型、DECIMAL类型或BIGINT类型。
      • 当输入值为STRING类型时，会隐式转换到DOUBLE类型后参与运算，其他类型抛异常。
      • 当输入值为NULL时，该行不参与计算。
      • 当指定distinct关键字时，表示计算唯一值的汇总值。
    • partition by [col1, col2..]：指定开窗口的列。
    • order by col1[asc|desc], col2[asc|desc]：不指定order by时，返回当前窗口内expr的汇总值。指定order by时，返回结果以指定的方式排序，并且返回当前窗口从首行至当前行的累计汇总值。
    说明 指明distinct关键字时，不能使用order by。
  • 返回值说明
    • 输入值为BIGINT类型时，返回BIGINT类型。
    • 输入值为DECIMAL类型时，返回DECIMAL类型。
    • 输入值为DOUBLE类型或STRING类型时，返回DOUBLE类型。
如果指定的order by的值重复，非Hive兼容和Hive兼容的处理方式不同：
  • 当非Hive兼容时，返回值为各自行的SUM。 
    set odps.sql.hive.compatible=false;
select user_id, price, sum(price) over
  (partition by user_id order by price) from test_src;

+------------+------------+------------+
| user_id    | price      | _c2        |
+------------+------------+------------+
| 1          | 4.5        | 4.5        |  --窗口起始行
| 1          | 5.5        | 10.0       |  --从第1行到第2行的SUM
| 1          | 5.5        | 15.5       |  --从第1行到第3行的SUM
| 1          | 6.5        | 22.0       |
| 2          | NULL       | NULL       |
| 2          | 3.0        | 3.0        |
| 3          | NULL       | NULL       |
| 3          | 4.0        | 4.0        |
+------------+------------+------------+
  • 当Hive兼容时，返回值为相同值的最后一行的SUM。 
    set odps.sql.hive.compatible=true;
select user_id, price, sum(price) over
  (partition by user_id order by price) from test_src;
+------------+------------+------------+
| user_id    | price      | _c2        |
+------------+------------+------------+
| 1          | 4.5        | 4.5        |  --窗口起始行
| 1          | 5.5        | 15.5       |  --从第1行到第3行的SUM
| 1          | 5.5        | 15.5       |  --从第1行到第3行的SUM
| 1          | 6.5        | 22.0       |
| 2          | NULL       | NULL       |
| 2          | 3.0        | 3.0        |
| 3          | NULL       | NULL       |
| 3          | 4.0        | 4.0        |
+------------+------------+------------+

DENSE_RANK

  • 命令格式 
    Bigint dense_rank() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])
  • 命令说明

    该函数用于计算连续排名。col2相同的行数据获得的排名相同。

  • 参数说明
    • partition by [col1, col2..]：指定开窗口的列。
    • order by col1[asc|desc], col2[asc|desc]：指定排名依据的值。
  • 返回值说明

    返回BIGINT类型。

  • 示例
    表emp中的数据如下所示。 
    | empno | ename | job | mgr | hiredate| sal| comm | deptno |
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
    现在需要将所有职工根据部门分组，每个组内根据sal做降序排序，获得职工自己组内的序号。 
    --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.0     | 1          |
| 10         | KING  | 5000.0     | 1          |
| 10         | CLARK | 2450.0     | 2          |
| 10         | WELAN | 2450.0     | 2          |
| 10         | TEBAGE | 1300.0     | 3          |
| 10         | MILLER | 1300.0     | 3          |
| 20         | SCOTT | 3000.0     | 1          |
| 20         | FORD  | 3000.0     | 1          |
| 20         | JONES | 2975.0     | 2          |
| 20         | ADAMS | 1100.0     | 3          |
| 20         | SMITH | 800.0      | 4          |
| 30         | BLAKE | 2850.0     | 1          |
| 30         | ALLEN | 1600.0     | 2          |
| 30         | TURNER | 1500.0     | 3          |
| 30         | MARTIN | 1250.0     | 4          |
| 30         | WARD  | 1250.0     | 4          |
| 30         | JAMES | 950.0      | 5          |
+------------+-------+------------+------------+

RANK

  • 命令格式 
    Bigint rank() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])
  • 命令说明

    该函数用于计算排名。col2相同的行数据获得排名顺序下降。

  • 参数说明
    • partition by [col1, col2..]：指定开窗口的列。
    • order by col1[asc|desc], col2[asc|desc]：指定排名依据的值。
  • 返回值说明

    返回BIGINT类型。

  • 示例
    表emp中的数据如下所示。 
    | empno | ename | job | mgr | hiredate| sal| comm | deptno |
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
    现在需要将所有职工根据部门分组，每个组内根据sal做降序排序，获得职工自己组内的序号。 
    --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.0     | 1          |
| 10         | KING  | 5000.0     | 1          |
| 10         | CLARK | 2450.0     | 3          |
| 10         | WELAN | 2450.0     | 3          |
| 10         | TEBAGE | 1300.0     | 5          |
| 10         | MILLER | 1300.0     | 5          |
| 20         | SCOTT | 3000.0     | 1          |
| 20         | FORD  | 3000.0     | 1          |
| 20         | JONES | 2975.0     | 3          |
| 20         | ADAMS | 1100.0     | 4          |
| 20         | SMITH | 800.0      | 5          |
| 30         | BLAKE | 2850.0     | 1          |
| 30         | ALLEN | 1600.0     | 2          |
| 30         | TURNER | 1500.0     | 3          |
| 30         | MARTIN | 1250.0     | 4          |
| 30         | WARD  | 1250.0     | 4          |
| 30         | JAMES | 950.0      | 6          |
+------------+-------+------------+------------+

LAG

  • 命令格式 
    lag(expr，Bigint offset, default) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]])
  • 命令说明

    按偏移量取当前行之前第几行的值。如果当前行号为rn，则取行号为rn-offset的值。

  • 参数说明
    • expr：任意类型。
    • offset：偏移量，BIGINT类型常量，取值大于0。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后执行。
    • default：当offset指定的范围越界时的缺省值，常量，默认值为NULL。
    • partition by [col1, col2..]：指定开窗口的列。
    • order by col1[asc|desc], col2[asc|desc]：指定返回结果的排序方式。
  • 返回值说明

    返回值类型同expr类型。

  • 示例 
    select seq, lag(seq+100, 1) over (partition by window order by seq) as r from sliding_window;
--执行结果如下。
+------------+------------+
| seq        | r          |
+------------+------------+
| 0          | NULL       |
| 1          | 100        |
| 2          | 101        |
| 3          | 102        |
| 4          | 103        |
| 5          | 104        |
| 6          | 105        |
| 7          | 106        |
| 8          | 107        |
| 9          | 108        |
+------------+------------+

LEAD

  • 命令格式 
    lead(expr，Bigint offset, default) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]])
  • 命令说明

    按偏移量取当前行之后第几行的值。如果当前行号为rn，则取行号为rn+offset的值。

  • 参数说明
    • expr：任意类型。
    • offset：可选，偏移量，BIGINT类型常量，取值大于0。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后执行。
    • default：当offset指定的范围越界时的缺省值，常量，默认值为NULL。
    • partition by [col1, col2..]：指定开窗口的列。
    • order by col1[asc|desc], col2[asc|desc]：指定返回结果的排序方式。
  • 返回值说明

    返回值类型同expr类型。

  • 示例 
    select c_Double_a,c_String_b,c_int_a,lead(c_int_a,1) over(partition by c_Double_a order by c_String_b) from dual;
select c_String_a,c_time_b,c_Double_a,lead(c_Double_a,1) over(partition by c_String_a order by c_time_b) from dual;
select c_String_in_fact_num,c_String_a,c_int_a,lead(c_int_a) over(partition by c_String_in_fact_num order by c_String_a) from dual;

PERCENT_RANK

  • 命令格式 
    percent_rank() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])
  • 命令说明

    该函数用于计算一组数据中某行的相对排名。

  • 参数说明
    • partition by [col1, col2..]：指定开窗口的列。
    • order by col1[asc|desc], col2[asc|desc]：指定排名依据的值。
  • 返回值说明

    返回DOUBLE类型，值域为[0, 1]，相对排名的计算方式为(rank-1)/(number of rows -1)。

ROW_NUMBER

  • 命令格式 
    row_number() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])
  • 命令说明

    该函数用于计算行号，从1开始。

  • 参数说明
    • partition by [col1, col2..]：指定开窗口的列。
    • order by col1[asc|desc], col2[asc|desc]：指定结果返回时的排序的值。
  • 返回值说明

    返回BIGINT类型。

  • 示例
    假设表emp中的数据如下所示。 
    | empno | ename | job | mgr | hiredate| sal| comm | deptno |
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
    现在需要将所有职工根据部门分组，每个组内根据sal做降序排序，获得职工自己组内的序号。 
    SELECT deptno,ename,sal,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums--deptno（部门）作为开窗列，sal（薪水）作为结果返回时需要排序的值。
    FROM emp;
--执行结果如下。
+------------+-------+------------+------------+
| deptno     | ename | sal        | nums       |
+------------+-------+------------+------------+
| 10         | JACCKA | 5000.0     | 1          |
| 10         | KING  | 5000.0     | 2          |
| 10         | CLARK | 2450.0     | 3          |
| 10         | WELAN | 2450.0     | 4          |
| 10         | TEBAGE | 1300.0     | 5          |
| 10         | MILLER | 1300.0     | 6          |
| 20         | SCOTT | 3000.0     | 1          |
| 20         | FORD  | 3000.0     | 2          |
| 20         | JONES | 2975.0     | 3          |
| 20         | ADAMS | 1100.0     | 4          |
| 20         | SMITH | 800.0      | 5          |
| 30         | BLAKE | 2850.0     | 1          |
| 30         | ALLEN | 1600.0     | 2          |
| 30         | TURNER | 1500.0     | 3          |
| 30         | MARTIN | 1250.0     | 4          |
| 30         | WARD  | 1250.0     | 5          |
| 30         | JAMES | 950.0      | 6          |
+------------+-------+------------+------------+

CLUSTER_SAMPLE

  • 命令格式 
    boolean cluster_sample([Bigint x, Bigint y])
over(partition by [col1, col2..])
  • 命令说明

    该函数用于分组抽样。

  • 参数说明
    • x：BIGINT类型常量，x>=1。如果指定参数y，x表示将一个窗口分为x份。否则，x表示在一个窗口中抽取x行记录（即有x行返回值为True）。x为NULL时，返回值为NULL。
    • y：BIGINT类型常量，y>=1，y<=x。表示从一个窗口分的x份中抽取y份记录（即y份记录返回值为True）。y为NULL时，返回值为NULL。
    • partition by [col1, col2]：指定开窗口的列。
  • 返回值说明

    返回BOOLEAN类型。

  • 示例
    假设表test_tbl中有key、value两列。key为分组字段，取值为groupa、groupb；value为分组字段对应的值。如下所示。 
    +------------+--------------------+
| key        | value              |
+------------+--------------------+
| groupa     | -1.34764165478145  |
| groupa     | 0.740212609046718  |
| groupa     | 0.167537127858695  |
| groupa     | 0.630314566185241  |
| groupa     | 0.0112401388646925 |
| groupa     | 0.199165745875297  |
| groupa     | -0.320543343353587 |
| groupa     | -0.273930924365012 |
| groupa     | 0.386177958942063  |
| groupa     | -1.09209976687047  |
| groupb     | -1.10847690938643  |
| groupb     | -0.725703978381499 |
| groupb     | 1.05064697475759   |
| groupb     | 0.135751224393789  |
| groupb     | 2.13313102040396   |
| groupb     | -1.11828960785008  |
| groupb     | -0.849235511508911 |
| groupb     | 1.27913806620453   |
| groupb     | -0.330817716670401 |
| groupb     | -0.300156896191195 |
| groupb     | 2.4704244205196    |
| groupb     | -1.28051882084434  |
+------------+--------------------+
    如果您需要从每组中抽取约10%的值，可以使用以下MaxCompute SQL完成。 
    select key, value
    from (
        select key, value, cluster_sample(10, 1) over(partition by key) as flag
        from tbl
        ) sub
    where flag = true;
--执行结果如下。
+-----+------------+
| key | value      |
+-----+------------+
| groupa | 0.167537127858695 |
| groupb | 0.135751224393789 |
+-----+------------+

CUME_DIST

  • 命令格式 
    cume_dist() over(partition by col1[, col2…] order by col1 [asc|desc][, col2[asc|desc]…]])
  • 命令说明

    求累计分布，相当于求分组中值大于等于当前值的行数占分组总行数的比例。

  • 参数说明

    比较大小的值由order by列指定。

  • 返回值

    分组中值大于等于当前值的行数占分组总行数的比例。

  • 示例
    假设表emp中的数据如下所示。 
    | empno | ename | job | mgr | hiredate| sal| comm | deptno |
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
    现在需要将所有职工根据部门分组，再求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.0     | 33.33%    |
| 10         | KING  | 5000.0     | 33.33%    |
| 10         | CLARK | 2450.0     | 66.67%    |
| 10         | WELAN | 2450.0     | 66.67%    |
| 10         | TEBAGE | 1300.0     | 100.0%    |
| 10         | MILLER | 1300.0     | 100.0%    |
| 20         | SCOTT | 3000.0     | 40.0%     |
| 20         | FORD  | 3000.0     | 40.0%     |
| 20         | JONES | 2975.0     | 60.0%     |
| 20         | ADAMS | 1100.0     | 80.0%     |
| 20         | SMITH | 800.0      | 100.0%    |
| 30         | BLAKE | 2850.0     | 16.67%    |
| 30         | ALLEN | 1600.0     | 33.33%    |
| 30         | TURNER | 1500.0     | 50.0%     |
| 30         | MARTIN | 1250.0     | 83.33%    |
| 30         | WARD  | 1250.0     | 83.33%    |
| 30         | JAMES | 950.0      | 100.0%    |
+------------+-------+------------+-----------+

NTILE

  • 命令格式 
    BIGINT ntile(BIGINT n) over(partition by [col1, col2…]  
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause]))
  • 命令说明

    用于将分组数据按照顺序切分成n片，并返回当前切片值。如果切片不均匀，默认增加第一个切片的分布。

  • 参数说明

    n：BIGINT数据类型。

  • 返回值说明

    返回BIGINT类型。

  • 示例
    假设表emp中的数据如下所示。 
    | empno | ename | job | mgr | hiredate| sal| comm | deptno |
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
    现在需要将所有职工根据部门按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.0     | 1          |
| 10         | KING  | 5000.0     | 1          |
| 10         | WELAN | 2450.0     | 2          |
| 10         | CLARK | 2450.0     | 2          |
| 10         | TEBAGE | 1300.0     | 3          |
| 10         | MILLER | 1300.0     | 3          |
| 20         | SCOTT | 3000.0     | 1          |
| 20         | FORD  | 3000.0     | 1          |
| 20         | JONES | 2975.0     | 2          |
| 20         | ADAMS | 1100.0     | 2          |
| 20         | SMITH | 800.0      | 3          |
| 30         | BLAKE | 2850.0     | 1          |
| 30         | ALLEN | 1600.0     | 1          |
| 30         | TURNER | 1500.0     | 2          |
| 30         | MARTIN | 1250.0     | 2          |
| 30         | WARD  | 1250.0     | 3          |
| 30         | JAMES | 950.0      | 3          |
+------------+-------+------------+------------+

FIRST_VALUE

FIRST_VALUE的命令格式、说明和示例如下：
  • 命令格式 
    first_value(expr) over(partition by col1[, col2…]
        [order by col1 [asc|desc][, col2[asc|desc]…]] [windowing_clause])
  • 命令说明

    该函数用于计算第一个值。

  • 参数说明
    • expr：任意基础数据类型。
    • partition by col1[, col2…]：指定开窗口的列。
    • order by col1 [asc|desc], col2[asc|desc]：不指定order by时，返回当前窗口内开始行的expr值。指定order by时，返回结果以指定的顺序排序，并且值为当前窗口开始行的expr值。
  • 返回值说明

    返回值和expr的数据类型一致。

  • 示例 
    select user_id, price, first_value(price) over
  (partition by user_id) as first_value from test_src;

+------------+------------+-------------+
| user_id    | price      | first_value |
+------------+------------+-------------+
| 1          | 5.5        | 5.5         | --当前窗口的开始行
| 1          | 4.5        | 5.5         |
| 1          | 6.5        | 5.5         |
| 1          | 5.5        | 5.5         |
| 2          | NULL       | NULL        | --当前窗口的开始行
| 2          | 3.0        | NULL        |
| 3          | 4.0        | 4.0         | --当前窗口的开始行
| 3          | NULL       | 4.0         |
+------------+------------+-------------+
-- 没有指定order by时，当前窗口为第一行到最后一行的范围。返回当前窗口的开始行的值。


select user_id, price, first_value(price) over
  (partition by user_id order by price) as first_value from test_src;

+------------+------------+-------------+
| user_id    | price      | first_value |
+------------+------------+-------------+
| 1          | 4.5        | 4.5         | --当前窗口的开始行
| 1          | 5.5        | 4.5         |
| 1          | 5.5        | 4.5         |
| 1          | 6.5        | 4.5         |
| 2          | NULL       | NULL        | --当前窗口的开始行
| 2          | 3.0        | NULL        |
| 3          | NULL       | NULL        | --当前窗口的开始行
| 3          | 4.0        | NULL        |
+------------+------------+-------------+
-- 指定order by时，当前窗口为第一行到当前行的范围。返回当前窗口的开始行的值。

LAST_VALUE

LAST_VALUE的命令格式、说明和示例如下：
  • 命令格式 
    last_value(expr) over(partition by col1[, col2…]
        [order by col1 [asc|desc][, col2[asc|desc]…]] [windowing_clause])
  • 命令说明

    该函数用于计算最后一个值。

  • 参数说明
    • expr：任意基础数据类型。
    • partition by col1[, col2…]：指定开窗口的列。
    • order by col1 [asc|desc], col2[asc|desc]：不指定order by时，返回当前窗口最后一行的expr值。指定order by时，返回结果以指定的顺序排序，并且值为当前窗口当前行的expr值。
  • 返回值说明

    返回值和expr的数据类型一致。

  • 示例 
    select user_id, price, last_value(price) over
  (partition by user_id) as last_value from test_src;

+------------+------------+------------+
| user_id    | price      | last_value |
+------------+------------+------------+
| 1          | 5.5        | 5.5        |
| 1          | 4.5        | 5.5        |
| 1          | 6.5        | 5.5        |
| 1          | 5.5        | 5.5        | -- 当前窗口的最后一行
| 2          | NULL       | 3.0        |
| 2          | 3.0        | 3.0        | -- 当前窗口的最后一行
| 3          | 4.0        | NULL       |
| 3          | NULL       | NULL       | -- 当前窗口的最后一行
+------------+------------+------------+
-- 没有指定order by时，当前窗口为第一行到最后一行的范围，返回当前窗口的最后一行的值。


select user_id, price, last_value(price) over
  (partition by user_id order by price) as last_value from test_src;

+------------+------------+------------+
| user_id    | price      | last_value |
+------------+------------+------------+
| 1          | 4.5        | 4.5        | -- 当前窗口的当前行
| 1          | 5.5        | 5.5        | -- 当前窗口的当前行
| 1          | 5.5        | 5.5        | -- 当前窗口的当前行
| 1          | 6.5        | 6.5        | -- 当前窗口的当前行
| 2          | NULL       | NULL       | -- 当前窗口的当前行
| 2          | 3.0        | 3.0        | -- 当前窗口的当前行
| 3          | NULL       | NULL       | -- 当前窗口的当前行
| 3          | 4.0        | 4.0        | -- 当前窗口的当前行
+------------+------------+------------+
-- 指定order by时，当前窗口为第一行到当前行的范围。返回当前窗口的当前行的值。

NTH_VALUE

NTH_VALUE的命令格式、说明和示例如下：
  • 命令格式 
    nth_value(expr, number [, skipNull]) over(partition by col1[, col2…]
    [order by col1 [asc|desc][, col2[asc|desc]…]] [windowing_clause])
  • 命令说明

    NTH_VALUE用于计算第n个值。如果n超过窗口的最大长度，返回NULL。

  • 参数说明
    • expr：任意基础数据类型。
    • number：大于等于1的整数。
    • skipNull：计算第n个值时是否跳过null，默认为false。
    • partition by col1[, col2…]：指定开窗口的列。
    • order by col1 [asc|desc], col2[asc|desc]：不指定order by时，返回当前窗口第n行的expr值。指定order by时，返回结果以指定的顺序排序，并且值为当前窗口从开始行到当前行的第n行的expr值。
  • 返回值说明

    返回值和expr的数据类型一致。

  • 示例 
    select user_id, price, nth_value(price, 2) over
  (partition by user_id) as nth_value from test_src;

+------------+------------+------------+
| user_id    | price      | nth_value  |
+------------+------------+------------+
| 1          | 5.5        | 4.5        |
| 1          | 4.5        | 4.5        | -- 当前窗口的第2行
| 1          | 6.5        | 4.5        |
| 1          | 5.5        | 4.5        |
| 2          | NULL       | 3.0        |
| 2          | 3.0        | 3.0        | -- 当前窗口的第2行
| 3          | 4.0        | NULL       |
| 3          | NULL       | NULL       | -- 当前窗口的第2行
+------------+------------+------------+
-- 没有指定order by时，当前窗口为第一行到最后一行的范围，返回第2行的值

select user_id, price, nth_value(price, 3) over
  (partition by user_id) as nth_value from test_src;
+------------+------------+------------+
| user_id    | price      | nth_value  |
+------------+------------+------------+
| 1          | 5.5        | 6.5        |
| 1          | 4.5        | 6.5        |
| 1          | 6.5        | 6.5        | -- 当前窗口的第3行
| 1          | 5.5        | 6.5        |
| 2          | NULL       | NULL       |
| 2          | 3.0        | NULL       |
| 3          | 4.0        | NULL       |
| 3          | NULL       | NULL       |
+------------+------------+------------+
-- 没有指定order by时，当前窗口为第一行到最后一行的范围，返回第3行的值。
-- 注意第2个窗口和第3个窗口的长度只有2。


select user_id, price, nth_value(price, 2) over
  (partition by user_id order by price) as nth_value from test_src;
+------------+------------+------------+
| user_id    | price      | nth_value  |
+------------+------------+------------+
| 1          | 4.5        | NULL       | -- 当前窗口只有1行，第2行超过了窗口长度
| 1          | 5.5        | 5.5        |
| 1          | 5.5        | 5.5        |
| 1          | 6.5        | 5.5        |
| 2          | NULL       | NULL       |
| 2          | 3.0        | 3.0        |
| 3          | NULL       | NULL       |
| 3          | 4.0        | 4.0        |
+------------+------------+------------+
-- 指定order by时，当前窗口为第一行到当前行的范围。返回第2行的值。


select user_id, price, nth_value(price, 1, true) over
  (partition by user_id) as nth_value from test_src;
+------------+------------+------------+
| user_id    | price      | nth_value  |
+------------+------------+------------+
| 1          | 5.5        | 5.5        |
| 1          | 4.5        | 5.5        |
| 1          | 6.5        | 5.5        |
| 1          | 5.5        | 5.5        |
| 2          | NULL       | 3.0        | -- 第1行为NULL，被跳过
| 2          | 3.0        | 3.0        |
| 3          | 4.0        | 4.0        |
| 3          | NULL       | 4.0        |
+------------+------------+------------+
-- 没有指定order by时，当前窗口为第一行到最后一行的范围。
-- 返回第1行的值，skipNull设置为true。