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万行),否则运行时报错。
  • 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

命令格式如下
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时返回结果以指定的顺序排序,并且值为当前窗口内从开始行到当前行的累计计数值。

返回值

返回Bigint类型。

说明
当指定distinct关键字时,不能写order by。

示例如下

假设存在表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时,返回当前窗口内从开始行到当前行的累计计数值。

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时,返回结果以指定的方式排序,并且返回窗口内从开始行到当前行的累计平均值。

返回值

返回Double类型。

说明
指明distinct关键字时,不能写order by。

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时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的最大值。

返回值

返回值的类型同expr类型。

说明
指明distinct关键字时,不能写order by。

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时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的最小值。

返回值

返回值类型同expr类型。

说明
指明distinct关键字时,不能写order by。

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类型的1到255个数字。
    • 当输入值为String类型或Bigint类型,会隐式转换到Double类型后参与运算,其他类型抛异常。
    • 当输入值为null时,返回null。
    • 如果传入的参数是Double类型,会默认转成Double的Array。
  • partition by [col1, col2…]:指定开窗口的列。

返回值

返回值类型同Double类型。

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关键字时,表示计算唯一值的总体标准差。
  • 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;
说明
  • 当指定distinct关键字时,不能写order by。
  • stddev还有一个别名函数stddev_pop,用法和stddev一样。

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..]:指定开窗口的列。
  • order by col1[asc|desc], col2[asc|desc]:不指定order by时,返回当前窗口内的样本标准差。指定order by时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的样本标准差。

返回值

输入值为Decimal类型时,返回Decimal类型,否则返回Double类型。

说明
指明distinct关键字时,不能写order by。

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时,返回结果以指定的方式排序,并且返回当前窗口从首行至当前行的累计汇总值。
返回值
  • 输入值为Bigint类型时,返回Bigint类型。
  • 输入值为Decimal类型时,返回Decimal类型。
  • 输入值为Double类型或String类型时,返回Double类型。
说明
指明distinct关键字时,不能写order by。

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做降序排序,获得职工自己组内的序号。
SELECT deptno, ename, sal, DENSE_RANK() 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     | 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做降序排序,获得职工自己组内的序号。
SELECT deptno,ename,sal, RANK() 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     | 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类型常量。输入值为String、Double到Bigint的隐式转换,offset>0。
  • default:当offset指定的范围越界时的缺省值,常量,默认值为null。
  • partition by [col1, col2..]:指定开窗口的列。
  • order by col1[asc|desc], col2[asc|desc]:指定返回结果的排序方式。

返回值

返回值类型同expr类型。

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类型常量。输入值为String、Decimal、Double到Bigint的隐式转换,offset>0。
  • default:可选,当offset指定的范围越界时的缺省值,常量。
  • 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)

说明
目前限制单个窗口内的行数不超过10,000,000条。

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

S28新函数

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