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方式指定視窗範圍。
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類型。
樣本如下:
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類型。
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類型。
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類型。
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類型。
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類型。
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類型。
樣本如下:
| 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
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類型。
樣本如下:
| 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
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類型。
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類型常量。輸入值為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)
。
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類型。
樣本如下:
| 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
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類型。
樣本如下:
+------------+--------------------+
| 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 |
+------------+--------------------+
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 |
+-----+------------+