您可以在MaxCompute SQL中使用窗口函数对指定开窗列的数据灵活地进行分析处理工作。本文为您提供MaxCompute SQL支持的窗口函数的命令格式、参数说明及示例，指导您使用窗口函数完成开发。

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

函数 功能
COUNT 计算计数值。
AVG 计算平均值。
MAX 计算最大值。
MIN 计算最小值。
MEDIAN 计算中位数。
STDDEV 计算总体标准差。
STDDEV_SAMP 计算样本标准差。
SUM 计算汇总值。
DENSE_RANK 计算连续排名。
RANK 计算跳跃排名。
LAG 按偏移量取当前行之前第几行的值。
LEAD 按偏移量取当前行之后第几行的值。
PERCENT_RANK 计算一组数据中某行的相对排名。
ROW_NUMBER 计算行号。
CLUSTER_SAMPLE 用于分组抽样。
CUME_DIST 计算累计分布。
NTILE 将分组数据按照顺序切片，并返回切片值。

使用限制

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

  • 窗口函数只能出现在select语句中。
  • 窗口函数中不能嵌套使用窗口函数和聚合函数。
  • 窗口函数不能和同级别的聚合函数一起使用。
  • 在一个MaxCompute SQL语句中，最多可以使用5个窗口函数。
  • 同一窗口内最多包含1亿行数据，建议不超过500万行，否则运行时报错。

窗口函数语法

窗口函数的语法声明如下。 
window_func() over (partition by <col1>[,<col2>…]
[order by <col1>[asc|desc][, <col2>[asc|desc]…]] <windowing_clause>)
  • partition by <col1>[,<col2>…]：必填。指定开窗的列。

    分区列的值相同的行被视为在同一个窗口内。

    说明 例如 partition by UID，同1个UID对应的记录视为同一个窗口，1个UID对应记录数最多包含1亿行数据，建议不超过500万行。
  • order by <col1>[asc|desc][, <col2>[asc|desc]…]：可选。指定数据在一个窗口内如何排序。
    说明 当遇到相同的 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。

示例数据

为便于理解各函数的使用方法，本文为您提供源数据，基于源数据提供函数相关示例。创建表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

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>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：可选。不指定order by时，返回当前窗口内expr的计数值，指定order by时返回结果以指定的顺序排序，并且值为当前窗口内从开始行到当前行的累计计数值。
      说明
      • 当指定distinct关键字时，不能使用order by。
      • 如果指定的order by的值重复，非Hive兼容和Hive兼容的处理方式不同，请参见该部分的示例。
  • 返回值说明

    返回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          |
+------------+------------+

AVG

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

    计算平均值。

  • 参数说明
    • expr：必填。待计算平均值的列。DOUBLE类型或DECIMAL类型。当指定distinct关键字时，表示取唯一值的平均值。
      • 当输入值为STRING、BIGINT类型时，会隐式转换为DOUBLE类型后参与运算，输入其它数据类型返回报错。
      • 当输入值为NULL时，该行不参与计算。
    • partition by <col1>[, <col2>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：可选。不指定order by时，返回当前窗口内expr的平均值。指定order by时，返回结果以指定的方式排序，并且值为当前窗口内从开始行到当前行的累计平均值。
      说明
      • 当指定distinct关键字时，不能使用order by。
      • 如果指定的order by的值重复，非Hive兼容和Hive兼容的处理方式不同，请参见该部分的示例。
  • 返回值说明

    返回DOUBLE类型。

  • 示例
    • 示例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 |
+------------+------------+------------+

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>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：可选。不指定order by时，返回当前窗口内expr的最大值。指定order by时，返回结果以指定的方式排序，并且值为当前窗口内从开始行到当前行的最大值。
    说明 当指定 distinct关键字时，不能使用 order by。
  • 返回值说明

    返回值的类型同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       |
+------------+------------+------------+

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>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：可选。不指定order by时，返回当前窗口内expr的最小值。指定order by时，返回结果以指定的方式排序，并且值为当前窗口内从开始行到当前行的最小值。
      说明 当指定 distinct关键字时，不能使用 order by。
  • 返回值说明

    返回值类型同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        |
+------------+------------+------------+

MEDIAN

  • 命令格式 
    double median(double <expr>) over (partition by <col1>[, <col2>…])
decimal median(decimal <expr>) over (partition by <col1>[, <col2>…)
  • 命令说明

    计算中位数。

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

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

  • 示例
    指定部门（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     |
+------------+------------+------------+

STDDEV

  • 命令格式 
    double stddev|stddev_pop([distinct] <expr>) over (partition by <col1>[, <col2>…]
[order by <col1>[asc|desc][, <col2>[asc|desc]…]] [<windowing_clause>])
decimal stddev|stddev_pop([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关键字时，表示计算唯一值的总体标准差。
    • partition by <col1>[, <col2>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：可选。不指定order by时，返回当前窗口内expr的总体标准差。指定order by时，返回结果以指定的方式排序，并且值为当前窗口内从开始行到当前行的累计总体标准差。
      说明
      • 当指定distinct关键字时，不能使用order by。
      • 如果指定的order by的值重复，非Hive兼容和Hive兼容的处理方式不同，请参见该部分的示例1和示例2。
  • 返回值说明

    返回值类型同expr类型。

  • 示例
    • 示例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 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关键字时，表示计算唯一值的样本标准差。
    • partition by <col1>[, <col2>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：可选。不指定order by时，返回当前窗口内expr的样本标准差。指定order by时，返回结果以指定的方式排序，并且值为当前窗口内从开始行到当前行的样本标准差。
      说明 当指定 distinct关键字时，不能使用 order by。
  • 返回值说明

    返回值类型同expr类型。

  • 示例
    • 示例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 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>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：可选。不指定order by时，返回当前窗口内expr的汇总值。指定order by时，返回结果以指定的方式排序，并且值为当前窗口内从开始行到当前行的累计汇总值。
      说明
      • 当指定distinct关键字时，不能使用order by。
      • 如果指定的order by的值重复，非Hive兼容和Hive兼容的处理方式不同，请参见该部分的示例。
  • 返回值说明
    • 输入值为BIGINT类型时，返回BIGINT类型。
    • 输入值为DECIMAL类型时，返回DECIMAL类型。
    • 输入值为DOUBLE类型或STRING类型时，返回DOUBLE类型。
  • 示例
    • 示例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       |
+------------+------------+------------+

DENSE_RANK

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

    计算连续排名。相同组的值相同的行数据获得的排名相同，且排名连续。例如两个第二名后为第三名。

  • 参数说明
    • partition by <col1>[, <col2>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：必填。指定排名依据的值。
  • 返回值说明

    返回BIGINT类型。

  • 示例
    将所有职工根据部门（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          |
+------------+------------+------------+------------+

RANK

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

    计算跳跃排名。相同组的值相同的行数据获得的排名相同，且排名跳跃。例如两个第二名后为第四名。

  • 参数说明
    • partition by <col1>[, <col2>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：必填。指定排名依据的值。
  • 返回值说明

    返回BIGINT类型。

  • 示例
    将所有职工根据部门（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          |
+------------+------------+------------+------------+

LAG

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

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

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

    返回值类型同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       |
+------------+------------+------------+------------+

LEAD

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

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

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

    返回值类型同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       |
+------------+------------+------------+------------+

PERCENT_RANK

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

    计算一组数据中某行的相对排名。

  • 参数说明
    • partition by <col1>[, <col2>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：必填。指定返回结果的排序方式。
  • 返回值说明

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

  • 示例

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

    select deptno, ename, sal, percent_rank(sal) 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        |
+------------+------------+------------+------------+

ROW_NUMBER

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

    计算行号，从1开始。

  • 参数说明
    • partition by <col1>[, <col2>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：必填。指定返回结果的排序方式。
  • 返回值说明

    返回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          |
+------------+------------+------------+------------+

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类型常量，x≥y≥1。表示从一个窗口分的x份中抽取y份记录（即y份记录返回值为True）。y为NULL时，返回值为NULL。
    • partition by <col1>[, <col2>…]：至少指定1个开窗口的列。
  • 返回值说明

    返回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        |
+------------+------------+

CUME_DIST

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

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

  • 参数说明
    • partition by <col1>[, <col2>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：必填。指定返回结果的排序方式。
  • 返回值说明

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

  • 示例
    将所有职工根据部门（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%     |
+------------+------------+------------+------------+

NTILE

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

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

  • 参数说明
    • n：必填。切片数量。BIGINT类型。
    • partition by <col1>[, <col2>…]：至少指定1个开窗口的列。
    • order by col1[asc|desc][, col2[asc|desc]…]：可选。指定返回结果的排序方式。
  • 返回值说明

    返回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          |
+------------+------------+------------+------------+