視窗函數支援在一個動態定義的資料子集上執行彙總操作或其他計算，常用於處理時間序列資料、排名、移動平均等問題。本文介紹MaxCompute SQL支援的視窗函數的命令格式、參數說明及樣本。
適用範圍
視窗函數只能出現在
SELECT語句中。視窗函數中不能嵌套使用視窗函數和彙總函式。
視窗函數不能和同層級的彙總函式一起使用。
索引
MaxCompute SQL支援的視窗函數如下。
函數 | 功能 |
對視窗中的資料求平均值。 | |
使用者隨機抽樣。返回True表示該行資料被抽中。 | |
計算視窗中的記錄數。 | |
計算累計分布。 | |
計算排名。排名是連續的。 | |
取當前行所對應視窗的第一條資料的值。 | |
取當前行往前（朝分區頭部方向）第N行資料的值。 | |
取當前行所對應視窗的最後一條資料的值。 | |
取當前行往後（朝分區尾部方向）第N行資料的值。 | |
計算視窗中的最大值。 | |
計算視窗中的中位元。 | |
計算視窗中的最小值。 | |
將資料順序切分成N等份，返回資料所在等份的編號（從1到N）。 | |
取當前行所對應視窗的第N條資料的值。 | |
計算排名。輸出百分比格式。 | |
計算精確的百分位元。 | |
計算給定的百分位元值，對指定列升序排列。 | |
計算排名。排名可能不連續。 | |
計算行號。從1開始遞增。 | |
計算總體標準差。是STDDEV_POP的別名。 | |
計算樣本標準差。 | |
對視窗中的資料求和。 |
視窗函數文法
視窗函數的文法聲明如下。
<function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
<function_name>([distinct][<expression> [, ...]]) over <window_name>expression：函數格式，具體格式以實際函數文法為準。
windowing_definition：視窗定義。詳細文法格式請參見windowing_definition。
window_name：視窗名稱。您可以使用
window關鍵字自訂視窗，為windowing_definition定義名稱。自訂語句（named_window_def）如下：window <window_name> as (<window_definition>)自訂語句在SQL中的位置如下：
select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]
windowing_definition
windowing_definition的文法聲明如下。
--partition_clause：
[partition by <expression> [, ...]]
--orderby_clause：
[order by <expression> [asc|desc][nulls {first|last}] [, ...]]
[<frame_clause>]在SELECT語句中加入視窗函數，計算視窗函數的結果時，資料會按照視窗定義中的partition by和order by語句進行分區和排序。如果沒有partition by語句，則僅有一個分區，包含全部資料。如果沒有order by語句，則分區內的資料會按照任意順序排布，最終產生一個資料流。之後對於每一行資料（當前行），會按照視窗定義中的frame_clause從資料流中截取一段資料，構成當前行的視窗。視窗函數會根據視窗中包含的資料，計算得到視窗函數針對當前行對應的輸出結果。
partition by <expression> [, ...]：可選。指定分區。分區列的值相同的行被視為在同一個視窗內。詳細格式請參見表操作。
order by <expression> [asc|desc][nulls {first|last}] [, ...]：可選。指定資料在一個視窗內如何排序。
說明當遇到相同的
order by值時，排序結果不穩定。為減少隨機性，應當儘可能保持order by值的唯一性。frame_clause：可選。用於確定資料邊界，更多frame_clause資訊，請參見frame_clause。
filter_clause
filter_clause的文法聲明如下。
FILTER (WHERE filter_condition)其中filter_condition為布林運算式，和select ... from ... where語句中的where用法完全相同。
如果提供了FILTER子句，則只有filter_condition值為true的行才會包含在視窗frame中。對於彙總視窗函數（包括：COUNT、SUM、AVG、MAX、MIN、WM_CONCAT等）仍為每行返回一個值，但FILTER運算式計算結果為true以外的值（即NULL或false，NULL按false處理）不會包含在任何行的視窗frame中。
樣本
資料準備
--建立表 CREATE TABLE IF NOT EXISTS mf_window_fun(key BIGINT,value BIGINT) STORED AS ALIORC; --插入資料 insert into mf_window_fun values (1,100),(2,200),(1,150),(2,250),(3,300),(4,400),(5,500),(6,600),(7,700); --查詢mf_window_fun表資料 select * from mf_window_fun; --返回結果 +------------+------------+ | key | value | +------------+------------+ | 1 | 100 | | 2 | 200 | | 1 | 150 | | 2 | 250 | | 3 | 300 | | 4 | 400 | | 5 | 500 | | 6 | 600 | | 7 | 700 | +------------+------------+查詢時段內value值大於100的行的累積和
select key,sum(value) filter(where value > 100) over (partition by key order by key) from mf_window_fun;返回結果：
+------------+------------+ | key | _c1 | +------------+------------+ | 1 | NULL | --跳過計算 | 1 | 150 | | 2 | 200 | | 2 | 450 | | 3 | 300 | | 4 | 400 | | 5 | 500 | | 6 | 600 | | 7 | 700 | +------------+------------+
FILTER子句並不會在查詢結果中去掉不滿足filter_condition的行，只是在視窗Function Compute時認為這一行不存在。如果想要去掉相應的行，仍然需要在
select ... from ... where的條件中指定。而這一行的視窗函數值也並不是0或者NULL，而是沿用其前一行的視窗函數值。只有當視窗函數為彙總類函數（包括：COUNT、SUM、AVG、MAX、MIN、WM_CONCAT等）時，才能使用FILTER子句，非彙總類函數（例如：RANK、ROW_NUMBER、NTILE）不能使用FILTER子句，否則會出現語法錯誤。
在視窗函數中使用FILTER文法時需要開啟如下開關：
set odps.sql.window.function.newimpl=true;。
frame_clause
frame_clause的文法聲明如下。
--格式一。
{ROWS|RANGE|GROUPS} <frame_start> [<frame_exclusion>]
--格式二。
{ROWS|RANGE|GROUPS} between <frame_start> and <frame_end> [<frame_exclusion>]frame_clause是一個閉區間，用於確定資料邊界，包含frame_start和frame_end位置的資料行。
ROWS|RANGE|GROUPS：必填。frame_clause的類型，各類型的frame_start和frame_end實現規則不相同。其中：
ROWS類型：通過資料行數確定資料邊界。
RANGE類型：通過比較
order by列值的大小關係來確定資料邊界。一般在視窗定義中會指定order by，未指定order by時，一個分區中的所有資料行具有相同的order by列值。NULL與NULL被認為是相等的。GROUPS：一個分區中所有具有相同
order by列值的資料群組成一個GROUP。未指定order by時，分區中的所有資料群組成一個GROUP。NULL與NULL被認為是相等的。
frame_start、frame_end：表示視窗的起始和終止邊界。frame_start必填。frame_end可選，省略時預設值為CURRENT ROW。
frame_start確定的位置必須在frame_end確定的位置的前面，或者等於frame_end的位置，即frame_start相比frame_end更靠近分區頭部。分區頭部是指資料按視窗定義中的
order by語句排序之後第1行資料的位置。ROWS、RANGE、GROUPS類型對應的取值範圍及實現邏輯如下。frame_clause類型
frame_start/frame_end取值
說明
ROWS、RANGE、GROUPS
UNBOUNDED PRECEDING
表示分區的第一行，從1開始計數。
UNBOUNDED FOLLOWING
表示分區的最後一行。
ROWS
CURRENT ROW
指當前行的位置。每一行資料都會對應一個視窗函數的結果值，當前行是指在給哪一行資料計算視窗函數的結果。
offset PRECEDING
指從當前行位置，向分區頭部位置移動
offset行的位置。例如0 PRECEDING指當前行，1 PRECEDING指前一行。offset必須為非負整數。offset FOLLOWING
指從當前行位置，向分區尾部移動
offset行的位置。例如0 FOLLOWING指當前行，1 FOLLOWING指下一行。offset必須為非負整數。RANGE
CURRENT ROW
作為frame_start時，指第一條與當前行具有相同
order by列值的資料的位置。作為frame_end時，指最後一條與當前行具有相同
order by列值的資料的位置。
offset PRECEDING
frame_start和frame_end的位置與
order by的順序相關。假設視窗按照X進行排序，Xi表示第i行資料對應的X值，Xc表示當前行資料對應X值。位置說明如下：當
order by為升序時：frame_start：指第一條滿足
Xc - Xi <= offset資料的位置。frame_end：指最後一條滿足
Xc - Xi >= offset資料的位置。
當
order by為降序時：frame_start：指第一條滿足
Xi - Xc <= offset資料的位置。frame_end：指最後一條滿足
Xi - Xc >= offset資料的位置。
order by的列支援的資料類型為：TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、DATETIME、DATE、TIMESTAMP。日期類型資料的
offset文法如下：N：表示N天或N秒。非負整數。對於DATETIME和TIMESTAMP，表示N秒；對於DATE，表示N天。interval 'N' {YEAR\MONTH\DAY\HOUR\MINUTE\SECOND}：表示N年/月/日/小時/分鐘/秒。例如INTERVAL '3' YEAR表示3年。INTERVAL 'N-M' YEAR TO MONTH：表示N年M月。例如INTERVAL '1-3' YEAR TO MONTH表示1年3個月。INTERVAL 'D[ H[:M[:S[:N]]]]' DAY TO SECOND：表示D天H小時M分鐘S秒N納秒。例如INTERVAL '1 2:3:4:5' DAY TO SECOND表示1天2小時3分鐘4秒5納秒。
offset FOLLOWING
frame_start和frame_end的位置與
order by的順序相關。假設視窗按照X進行排序，Xi表示第i行資料對應的X值，Xc表示當前行資料對應X值。位置說明如下：當
order by為升序時：frame_start：指第一條滿足
Xi - Xc >= offset資料的位置。frame_end：指最後一條滿足
Xi - Xc <= offset資料的位置。
當
order by為降序時：frame_start：指第一條滿足
Xc - Xi >= offset資料的位置。frame_end：指最後一條滿足
Xc - Xi <= offset資料的位置。
GROUPS
CURRENT ROW
作為frame_start時，指當前行所屬GROUP的第一條資料。
作為frame_end時，指當前行所屬GROUP的最後一行資料。
offset PRECEDING
作為frame_start時，指從當前行所屬GROUP開始，朝分區頭部移動
offset個GROUP之後，所在GROUP的第一條資料的位置。作為frame_end時，指從當前行所屬GROUP開始，朝分區頭部移動
offset個GROUP之後，所在GROUP的最後一條資料的位置。
說明frame_start不能設定為UNBOUNDED FOLLOWING，frame_end不能設定為UNBOUNED PRECEDING。
offset FOLLOWING
作為frame_start時，指從當前行所屬GROUP開始，朝分區尾部移動
offset個GROUP之後，所在GROUP的第一條資料的位置。作為frame_end時，指從當前行所屬GROUP開始，朝分區尾部移動
offset個GROUP之後，所在GROUP的最後一條資料的位置。
說明frame_start不能設定為UNBOUNDED FOLLOWING，frame_end不能設定為UNBOUNED PRECEDING。
frame_exclusion：可選。用於從視窗中剔除一部分資料。取值範圍如下：
EXCLUDE NO OTHERS：表示不剔除任何資料。
EXCLUDE CURRENT ROW：表示剔除當前行。
EXCLUDE GROUP：表示剔除整個GROUP，即分區中與當前行具有相同
order by值的所有資料。EXCLUDE TIES：表示剔除整個GROUP，但保留當前行。
預設frame_clause
未顯示設定frame_clause時，MaxCompute會使用預設的frame_clause來決定視窗所包含資料的邊界。預設的frame_clause為：
當開啟Hive相容模式（
set odps.sql.hive.compatible=true;）時，預設的frame_clause如下，與大部分SQL系統相同。RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS當關閉Hive相容模式（
set odps.sql.hive.compatible=false;），同時視窗定義中指定了order by語句，且視窗函數為AVG、COUNT、MAX、MIN、STDDEV、STEDEV_POP、STDDEV_SAMP或SUM時，會使用ROWS類型的預設frame_clause。ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
視窗邊界樣本
假設表tbl結構為pid: bigint, oid: bigint, rid: bigint，表中包含如下資料：
+------------+------------+------------+
| pid | oid | rid |
+------------+------------+------------+
| 1 | NULL | 1 |
| 1 | NULL | 2 |
| 1 | 1 | 3 |
| 1 | 1 | 4 |
| 1 | 2 | 5 |
| 1 | 4 | 6 |
| 1 | 7 | 7 |
| 1 | 11 | 8 |
| 2 | NULL | 9 |
| 2 | NULL | 10 |
+------------+------------+------------+ROW類型視窗
視窗定義1
partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW --SQL語句如下。 select pid, oid, rid, collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;返回結果如下：
+------------+------------+------------+--------+ | pid | oid | rid | window | +------------+------------+------------+--------+ | 1 | NULL | 1 | [1] | | 1 | NULL | 2 | [1, 2] | | 1 | 1 | 3 | [1, 2, 3] | | 1 | 1 | 4 | [1, 2, 3, 4] | | 1 | 2 | 5 | [1, 2, 3, 4, 5] | | 1 | 4 | 6 | [1, 2, 3, 4, 5, 6] | | 1 | 7 | 7 | [1, 2, 3, 4, 5, 6, 7] | | 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7, 8] | | 2 | NULL | 9 | [9] | | 2 | NULL | 10 | [9, 10] | +------------+------------+------------+--------+視窗定義2
partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING --SQL語句如下。 select pid, oid, rid, collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as window from tbl;返回結果如下：
+------------+------------+------------+--------+ | pid | oid | rid | window | +------------+------------+------------+--------+ | 1 | NULL | 1 | [1, 2, 3, 4, 5, 6, 7, 8] | | 1 | NULL | 2 | [1, 2, 3, 4, 5, 6, 7, 8] | | 1 | 1 | 3 | [1, 2, 3, 4, 5, 6, 7, 8] | | 1 | 1 | 4 | [1, 2, 3, 4, 5, 6, 7, 8] | | 1 | 2 | 5 | [1, 2, 3, 4, 5, 6, 7, 8] | | 1 | 4 | 6 | [1, 2, 3, 4, 5, 6, 7, 8] | | 1 | 7 | 7 | [1, 2, 3, 4, 5, 6, 7, 8] | | 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7, 8] | | 2 | NULL | 9 | [9, 10] | | 2 | NULL | 10 | [9, 10] | +------------+------------+------------+--------+視窗定義3
partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING --SQL語句如下。 select pid, oid, rid, collect_list(rid) over(partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING) as window from tbl;返回結果如下：
+------------+------------+------------+--------+ | pid | oid | rid | window | +------------+------------+------------+--------+ | 1 | NULL | 1 | [2, 3, 4] | | 1 | NULL | 2 | [3, 4, 5] | | 1 | 1 | 3 | [4, 5, 6] | | 1 | 1 | 4 | [5, 6, 7] | | 1 | 2 | 5 | [6, 7, 8] | | 1 | 4 | 6 | [7, 8] | | 1 | 7 | 7 | [8] | | 1 | 11 | 8 | NULL | | 2 | NULL | 9 | [10] | | 2 | NULL | 10 | NULL | +------------+------------+------------+--------+視窗定義4
partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW --SQL語句如下。 select pid, oid, rid, collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW) as window from tbl;返回結果如下：
+------------+------------+------------+--------+ | pid | oid | rid | window | +------------+------------+------------+--------+ | 1 | NULL | 1 | NULL | | 1 | NULL | 2 | [1] | | 1 | 1 | 3 | [1, 2] | | 1 | 1 | 4 | [1, 2, 3] | | 1 | 2 | 5 | [1, 2, 3, 4] | | 1 | 4 | 6 | [1, 2, 3, 4, 5] | | 1 | 7 | 7 | [1, 2, 3, 4, 5, 6] | | 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7] | | 2 | NULL | 9 | NULL | | 2 | NULL | 10 | [9] | +------------+------------+------------+--------+視窗定義5
partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP --SQL語句如下。 select pid, oid, rid, collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP) as window from tbl;返回結果如下：
+------------+------------+------------+--------+ | pid | oid | rid | window | +------------+------------+------------+--------+ | 1 | NULL | 1 | NULL | | 1 | NULL | 2 | NULL | | 1 | 1 | 3 | [1, 2] | | 1 | 1 | 4 | [1, 2] | | 1 | 2 | 5 | [1, 2, 3, 4] | | 1 | 4 | 6 | [1, 2, 3, 4, 5] | | 1 | 7 | 7 | [1, 2, 3, 4, 5, 6] | | 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7] | | 2 | NULL | 9 | NULL | | 2 | NULL | 10 | NULL | +------------+------------+------------+--------+視窗定義6
partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES --SQL語句如下。 select pid, oid, rid, collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES) as window from tbl;返回結果如下：
+------------+------------+------------+--------+ | pid | oid | rid | window | +------------+------------+------------+--------+ | 1 | NULL | 1 | [1] | | 1 | NULL | 2 | [2] | | 1 | 1 | 3 | [1, 2, 3] | | 1 | 1 | 4 | [1, 2, 4] | | 1 | 2 | 5 | [1, 2, 3, 4, 5] | | 1 | 4 | 6 | [1, 2, 3, 4, 5, 6] | | 1 | 7 | 7 | [1, 2, 3, 4, 5, 6, 7] | | 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7, 8] | | 2 | NULL | 9 | [9] | | 2 | NULL | 10 | [10] | +------------+------------+------------+--------+對比本樣本與前一個樣本中
rid為2、4、10的window結果，可以觀察到EXCLUDE CURRENT ROW與EXCLUDE GROUP的差異，即對於EXCLUDE GROUP，同一個分區中（pid相等），與當前行為相同oid的資料都被剔除了。
RANGE類型視窗
視窗定義1
partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW --SQL語句如下。 select pid, oid, rid, collect_list(rid) over(partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;返回結果如下：
+------------+------------+------------+--------+ | pid | oid | rid | window | +------------+------------+------------+--------+ | 1 | NULL | 1 | [1, 2] | | 1 | NULL | 2 | [1, 2] | | 1 | 1 | 3 | [1, 2, 3, 4] | | 1 | 1 | 4 | [1, 2, 3, 4] | | 1 | 2 | 5 | [1, 2, 3, 4, 5] | | 1 | 4 | 6 | [1, 2, 3, 4, 5, 6] | | 1 | 7 | 7 | [1, 2, 3, 4, 5, 6, 7] | | 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7, 8] | | 2 | NULL | 9 | [9, 10] | | 2 | NULL | 10 | [9, 10] | +------------+------------+------------+--------+CURRENT ROW作為frame_end時，取與當前行具有相同
order by值oid的最後一條資料，因此rid為1的記錄的window結果為[1, 2]。視窗定義2
partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING --SQL語句如下。 select pid, oid, rid, collect_list(rid) over(partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING) as window from tbl;返回結果如下：
+------------+------------+------------+--------+ | pid | oid | rid | window | +------------+------------+------------+--------+ | 1 | NULL | 1 | [1, 2, 3, 4, 5, 6, 7, 8] | | 1 | NULL | 2 | [1, 2, 3, 4, 5, 6, 7, 8] | | 1 | 1 | 3 | [3, 4, 5, 6, 7, 8] | | 1 | 1 | 4 | [3, 4, 5, 6, 7, 8] | | 1 | 2 | 5 | [5, 6, 7, 8] | | 1 | 4 | 6 | [6, 7, 8] | | 1 | 7 | 7 | [7, 8] | | 1 | 11 | 8 | [8] | | 2 | NULL | 9 | [9, 10] | | 2 | NULL | 10 | [9, 10] | +------------+------------+------------+--------+視窗定義3
partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING --SQL語句如下。 select pid, oid, rid, collect_list(rid) over(partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING) as window from tbl;返回結果如下：
+------------+------------+------------+--------+ | pid | oid | rid | window | +------------+------------+------------+--------+ | 1 | NULL | 1 | [1, 2] | | 1 | NULL | 2 | [1, 2] | | 1 | 1 | 3 | NULL | | 1 | 1 | 4 | NULL | | 1 | 2 | 5 | [3, 4] | | 1 | 4 | 6 | [3, 4, 5] | | 1 | 7 | 7 | [6] | | 1 | 11 | 8 | NULL | | 2 | NULL | 9 | [9, 10] | | 2 | NULL | 10 | [9, 10] | +------------+------------+------------+--------+order by值oid為NULL的行，對於offset {PRECEDING|FOLLOWING}，只要offset不為UNBOUNDED，則作為frame_start，指向分區中第一條order by值為NULL的資料；作為frame_end，指向最後一條order by值為NULL的資料。
GROUPS類型視窗
視窗定義如下：
partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW --SQL語句如下。 select pid, oid, rid, collect_list(rid) over(partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW) as window from tbl;返回結果如下：
+------------+------------+------------+--------+ | pid | oid | rid | window | +------------+------------+------------+--------+ | 1 | NULL | 1 | [1, 2] | | 1 | NULL | 2 | [1, 2] | | 1 | 1 | 3 | [1, 2, 3, 4] | | 1 | 1 | 4 | [1, 2, 3, 4] | | 1 | 2 | 5 | [1, 2, 3, 4, 5] | | 1 | 4 | 6 | [3, 4, 5, 6] | | 1 | 7 | 7 | [5, 6, 7] | | 1 | 11 | 8 | [6, 7, 8] | | 2 | NULL | 9 | [9, 10] | | 2 | NULL | 10 | [9, 10] | +------------+------------+------------+--------+
樣本資料
為便於理解各函數的使用方法，本文為您提供來源資料，基於來源資料提供函數相關樣本。建立表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,,10AVG
命令格式
double avg([distinct] double <expr>) over ([partition_clause] [orderby_clause] [frame_clause]) decimal avg([distinct] decimal <expr>) over ([partition_clause] [orderby_clause] [frame_clause])命令說明
返回視窗中expr的平均值。
參數說明
expr：必填。計算返回結果的運算式。DOUBLE類型或DECIMAL類型。
當輸入值為STRING、BIGINT類型時，會隱式轉換為DOUBLE類型後參與運算，輸入其他資料類型返回報錯。
當輸入值為NULL時，該行不參與計算。
當指定distinct關鍵字時，表示取唯一值的平均值。
partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。
傳回值說明
expr為DECIMAL類型時，返回DECIMAL類型。其他情況下返回DOUBLE類型。expr的值都為NULL時，返回結果為NULL。
樣本
樣本1：指定部門（deptno）為開窗列，計算薪水（sal）平均值，不排序，返回當前視窗（相同deptno）從開始行到最後一行的累計平均值。命令樣本如下：
select deptno, sal, avg(sal) over (partition by deptno) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 2916.6666666666665 | --視窗開始行，從第1行到第6行的累計平均值。 | 10 | 2450 | 2916.6666666666665 | --從第1行到第6行的累計平均值。 | 10 | 5000 | 2916.6666666666665 | --從第1行到第6行的累計平均值。 | 10 | 1300 | 2916.6666666666665 | | 10 | 5000 | 2916.6666666666665 | | 10 | 2450 | 2916.6666666666665 | | 20 | 3000 | 2175.0 | | 20 | 3000 | 2175.0 | | 20 | 800 | 2175.0 | | 20 | 1100 | 2175.0 | | 20 | 2975 | 2175.0 | | 30 | 1500 | 1566.6666666666667 | | 30 | 950 | 1566.6666666666667 | | 30 | 1600 | 1566.6666666666667 | | 30 | 1250 | 1566.6666666666667 | | 30 | 1250 | 1566.6666666666667 | | 30 | 2850 | 1566.6666666666667 | +------------+------------+------------+樣本2：非Hive相容模式下，指定部門（deptno）為開窗列，計算薪水（sal）平均值，並排序，返回當前視窗（相同deptno）從開始行到當前行的累計平均值。命令樣本如下：
--關閉Hive相容模式。 set odps.sql.hive.compatible=false; --執行如下SQL命令。 select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300.0 | --視窗開始行。 | 10 | 1300 | 1300.0 | --從第1行到第2行的累計平均值。 | 10 | 2450 | 1683.3333333333333 | --從第1行到第3行的累計平均值。 | 10 | 2450 | 1875.0 | --從第1行到第4行的累計平均值。 | 10 | 5000 | 2500.0 | --從第1行到第5行的累計平均值。 | 10 | 5000 | 2916.6666666666665 | --從第1行到第6行的累計平均值。 | 20 | 800 | 800.0 | | 20 | 1100 | 950.0 | | 20 | 2975 | 1625.0 | | 20 | 3000 | 1968.75 | | 20 | 3000 | 2175.0 | | 30 | 950 | 950.0 | | 30 | 1250 | 1100.0 | | 30 | 1250 | 1150.0 | | 30 | 1500 | 1237.5 | | 30 | 1600 | 1310.0 | | 30 | 2850 | 1566.6666666666667 | +------------+------------+------------+樣本3：Hive相容模式下，指定部門（deptno）為開窗列，計算薪水（sal）平均值，並排序，返回當前視窗（相同deptno）從開始行到與當前行相同值所在行（相同sal值的平均值相同）的累計平均值。命令樣本如下：
--開啟Hive相容模式。 set odps.sql.hive.compatible=true; --執行如下SQL命令。 select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300.0 | --視窗開始行。第1行和第2行的sal一致，則第1行的平均值為第1行到第2行的累計平均值。 | 10 | 1300 | 1300.0 | --從第1行到第2行的累計平均值。 | 10 | 2450 | 1875.0 | --第3行和第4行的sal一致，則第3行的平均值為從第1行到第4行的累計平均值。 | 10 | 2450 | 1875.0 | --從第1行到第4行的累計平均值。 | 10 | 5000 | 2916.6666666666665 | | 10 | 5000 | 2916.6666666666665 | | 20 | 800 | 800.0 | | 20 | 1100 | 950.0 | | 20 | 2975 | 1625.0 | | 20 | 3000 | 2175.0 | | 20 | 3000 | 2175.0 | | 30 | 950 | 950.0 | | 30 | 1250 | 1150.0 | | 30 | 1250 | 1150.0 | | 30 | 1500 | 1237.5 | | 30 | 1600 | 1310.0 | | 30 | 2850 | 1566.6666666666667 | +------------+------------+------------+
CLUSTER_SAMPLE
命令格式
boolean cluster_sample(bigint <N>) OVER ([partition_clause]) boolean cluster_sample(bigint <N>, bigint <M>) OVER ([partition_clause])命令說明
cluster_sample(bigint <N>)：表示隨機抽取N條資料。cluster_sample(bigint <N>, bigint <M>)：表示按比例（M/N）隨機抽取。即抽取partition_row_count×M / N條資料。partition_row_count指分區中的資料行數。
參數說明
N：必填。BIGINT類型常量。N為NULL時，傳回值為NULL。
M：必填。BIGINT類型常量。M為NULL時，傳回值為NULL。
partition_clause：可選。詳情請參見windowing_definition。
傳回值說明
返回BOOLEAN類型。
樣本
如果您需要從每組中抽取約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 | +------------+------------+
COUNT
命令格式
-- 計算記錄數
BIGINT COUNT([DISTINCT|ALL] <colname>)
-- 計算視窗中的記錄數
BIGINT COUNT(*) OVER ([partition_clause] [orderby_clause] [frame_clause])
BIGINT COUNT([DISTINCT] <expr>[,...]) OVER ([partition_clause] [orderby_clause] [frame_clause])參數說明
DISTINCT|ALL：可選。表示在計數時是否去除重複記錄，預設為ALL，即計算全部記錄。如果指定DISTINCT，則只計算唯一值數量。
colname：必填。列值可以為任意類型。colname可以為
*，即COUNT(*)，此時返回所有行數。colname值為NULL時，該行不參與計算。expr：必填。待計算計數值的列。可以為任意類型。當值為NULL時，該行不參與計算。當指定DISTINCT關鍵字時，表示取唯一值的計數值。
COUNT([DISTINCT] <expr>[,...])：計算指定視窗記錄中所有值均不為NULL的行數。若指定DISTINCT關鍵字，則對這些行去重後計數。partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。
傳回值說明
返回BIGINT類型。colname值為NULL時，該行不參與計算。
使用樣本
準備測試資料
如果已有資料，可忽略該步驟。
下載測試資料test_data.txt。
建立測試表
CREATE TABLE IF NOT EXISTS emp( empno BIGINT, ename STRING, job STRING, mgr BIGINT, hiredate DATETIME, sal BIGINT, comm BIGINT, deptno BIGINT );載入資料
根據資料檔案的實際path(路徑以及名稱)替換
FILE_PATH。TUNNEL UPLOAD FILE_PATH emp;
樣本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相容模式下，指定某一列為開窗列，返回排序後累計計數值
非Hive相容模式下，指定薪水（sal）為開窗列，並排序，返回當前視窗（相同sal）從開始行到當前行的累計計數值。
命令樣本
-- 關閉Hive相容模式。 SET odps.sql.hive.compatible=false; 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相容模式下，指定某一列為開窗列，返回排序後累計計數值
Hive相容模式下，指定薪水（sal）為開窗列，並排序，返回當前視窗（相同sal）從開始行至最後一行的累計計數值。
命令樣本
-- 開啟Hive相容模式。 SET odps.sql.hive.compatible=true; 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 | +------------+------------+
樣本4：返回總行數
計算所有部門的總職工人數。
命令樣本
SELECT COUNT(*) FROM emp;返回結果
+------------+ | _c0 | +------------+ | 17 | +------------+
樣本5：分組計算各組總數
與GROUP BY配合使用，對所有職工按照部門（deptno）分組，計算各部門（deptno）的職工人數。
命令樣本
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;返回結果
+------------+------------+ | deptno | _c1 | +------------+------------+ | 20 | 5 | | 30 | 6 | | 10 | 6 | +------------+------------+
樣本6：去重計數
通過DISTINCT去重，計算部門數量。
命令樣本
SELECT COUNT(DISTINCT deptno) FROM emp;返回結果
+------------+ | _c0 | +------------+ | 3 | +------------+
CUME_DIST
命令格式
double cume_dist() over([partition_clause] [orderby_clause])命令說明
求累計分布，相當於求分區中大於等於當前行的資料在分區中的佔比。大小關係由orderby_clause判定。
參數說明
partition_clause及orderby_clause：詳情請參見windowing_definition。
傳回值說明
返回DOUBLE類型。具體的傳回值等於
row_number_of_last_peer / partition_row_count，其中：row_number_of_last_peer指當前行所屬GROUP的最後一行資料的ROW_NUMBER視窗函數傳回值，partition_row_count為該行資料所屬分區的資料行數。樣本
將所有職工根據部門（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% | +------------+------------+------------+------------+
DENSE_RANK
命令格式
bigint dense_rank() over ([partition_clause] [orderby_clause])命令說明
計算當前行在分區中按照orderby_clause排序後所處的排名。從1開始計數。分區中具有相同
order by值的行的排名相等。每當order by值發生變化時，排名加1。參數說明
partition_clause及orderby_clause：詳情請參見windowing_definition。
傳回值說明
返回BIGINT類型。未指定orderby_clause時，返回結果全為1。
樣本
將所有職工根據部門（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 | +------------+------------+------------+------------+
FIRST_VALUE
命令格式
first_value(<expr>[, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])命令說明
返回運算式expr在視窗的第一條資料上進行運算的結果。
參數說明
expr：必填。待計算返回結果的運算式。
ignore_nulls：可選。BOOLEAN類型。表示是否忽略NULL值。預設值為False。當參數的值為True時，返回視窗中第一條非NULL的expr值。
partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。
傳回值說明
傳回值類型同expr類型。
樣本
將所有職工根據部門分組，返回每組中的第一行資料。命令樣本如下：
不指定order by：
select deptno, ename, sal, first_value(sal) over (partition by deptno) as first_value from emp;返回結果如下：
+------------+------------+------------+-------------+ | deptno | ename | sal | first_value | +------------+------------+------------+-------------+ | 10 | TEBAGE | 1300 | 1300 | --當前視窗的開始行。 | 10 | CLARK | 2450 | 1300 | | 10 | KING | 5000 | 1300 | | 10 | MILLER | 1300 | 1300 | | 10 | JACCKA | 5000 | 1300 | | 10 | WELAN | 2450 | 1300 | | 20 | FORD | 3000 | 3000 | --當前視窗的開始行。 | 20 | SCOTT | 3000 | 3000 | | 20 | SMITH | 800 | 3000 | | 20 | ADAMS | 1100 | 3000 | | 20 | JONES | 2975 | 3000 | | 30 | TURNER | 1500 | 1500 | --當前視窗的開始行。 | 30 | JAMES | 950 | 1500 | | 30 | ALLEN | 1600 | 1500 | | 30 | WARD | 1250 | 1500 | | 30 | MARTIN | 1250 | 1500 | | 30 | BLAKE | 2850 | 1500 | +------------+------------+------------+-------------+指定order by：
select deptno, ename, sal, first_value(sal) over (partition by deptno order by sal desc) as first_value from emp;返回結果如下：
+------------+------------+------------+-------------+ | deptno | ename | sal | first_value | +------------+------------+------------+-------------+ | 10 | JACCKA | 5000 | 5000 | --當前視窗的開始行。 | 10 | KING | 5000 | 5000 | | 10 | CLARK | 2450 | 5000 | | 10 | WELAN | 2450 | 5000 | | 10 | TEBAGE | 1300 | 5000 | | 10 | MILLER | 1300 | 5000 | | 20 | SCOTT | 3000 | 3000 | --當前視窗的開始行。 | 20 | FORD | 3000 | 3000 | | 20 | JONES | 2975 | 3000 | | 20 | ADAMS | 1100 | 3000 | | 20 | SMITH | 800 | 3000 | | 30 | BLAKE | 2850 | 2850 | --當前視窗的開始行。 | 30 | ALLEN | 1600 | 2850 | | 30 | TURNER | 1500 | 2850 | | 30 | MARTIN | 1250 | 2850 | | 30 | WARD | 1250 | 2850 | | 30 | JAMES | 950 | 2850 | +------------+------------+------------+-------------+
LAG
命令格式
lag(<expr>[，bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)命令說明
返回當前行往前（朝分區頭部方向）第offset行資料對應的運算式expr的值。運算式expr可以是列、列運算或者函數運算等。
參數說明
expr：必填。待計算返回結果的運算式。
offset：可選。位移量，BIGINT類型常量，取值大於等於1。值為1時表示前一行，以此類推，預設值為1。輸入值為STRING類型、DOUBLE類型則隱式轉換為BIGINT類型後進行運算。
default：可選。當offset指定的範圍越界時的預設值，常量，預設值為NULL。需要與expr對應的資料類型相同。如果expr非常量，則基於當前行進行求值。
partition_clause及orderby_clause：詳情請參見windowing_definition。
傳回值說明
傳回值類型同expr類型。
樣本
將所有職工根據部門（deptno）分組（作為開窗列），每位員工的薪水（sal）做位移。命令樣本如下：
select deptno, ename, sal, lag(sal, 1) over (partition by deptno order by sal) as sal_new from emp;返回結果如下：
+------------+------------+------------+------------+ | deptno | ename | sal | sal_new | +------------+------------+------------+------------+ | 10 | TEBAGE | 1300 | NULL | | 10 | MILLER | 1300 | 1300 | | 10 | CLARK | 2450 | 1300 | | 10 | WELAN | 2450 | 2450 | | 10 | KING | 5000 | 2450 | | 10 | JACCKA | 5000 | 5000 | | 20 | SMITH | 800 | NULL | | 20 | ADAMS | 1100 | 800 | | 20 | JONES | 2975 | 1100 | | 20 | SCOTT | 3000 | 2975 | | 20 | FORD | 3000 | 3000 | | 30 | JAMES | 950 | NULL | | 30 | MARTIN | 1250 | 950 | | 30 | WARD | 1250 | 1250 | | 30 | TURNER | 1500 | 1250 | | 30 | ALLEN | 1600 | 1500 | | 30 | BLAKE | 2850 | 1600 | +------------+------------+------------+------------+
LAST_VALUE
命令格式
last_value(<expr>[, <ignore_nulls>]) over([partition_clause] [orderby_clause] [frame_clause])命令說明
返回運算式expr在視窗的最後一條資料上進行運算的結果。
參數說明
expr：必填。待計算返回結果的運算式。
ignore_nulls：可選。BOOLEAN類型。表示是否忽略NULL值。預設值為False。當參數的值為True時，返回視窗中最後一條非NULL的expr值。
partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。
傳回值說明
傳回值類型同expr類型。
樣本
將所有職工根據部門分組，返回每組中的最後一行資料。命令樣本如下：
不指定order by，當前視窗為第一行到最後一行的範圍，返回當前視窗的最後一行的值。
select deptno, ename, sal, last_value(sal) over (partition by deptno) as last_value from emp;返回結果如下：
+------------+------------+------------+-------------+ | deptno | ename | sal | last_value | +------------+------------+------------+-------------+ | 10 | TEBAGE | 1300 | 2450 | | 10 | CLARK | 2450 | 2450 | | 10 | KING | 5000 | 2450 | | 10 | MILLER | 1300 | 2450 | | 10 | JACCKA | 5000 | 2450 | | 10 | WELAN | 2450 | 2450 | --當前視窗的最後一行。 | 20 | FORD | 3000 | 2975 | | 20 | SCOTT | 3000 | 2975 | | 20 | SMITH | 800 | 2975 | | 20 | ADAMS | 1100 | 2975 | | 20 | JONES | 2975 | 2975 | --當前視窗的最後一行。 | 30 | TURNER | 1500 | 2850 | | 30 | JAMES | 950 | 2850 | | 30 | ALLEN | 1600 | 2850 | | 30 | WARD | 1250 | 2850 | | 30 | MARTIN | 1250 | 2850 | | 30 | BLAKE | 2850 | 2850 | --當前視窗的最後一行。 +------------+------------+------------+-------------+指定order by，當前視窗為第一行到當前行的範圍。返回當前視窗的當前行的值。
select deptno, ename, sal, last_value(sal) over (partition by deptno order by sal desc) as last_value from emp;返回結果如下：
+------------+------------+------------+-------------+ | deptno | ename | sal | last_value | +------------+------------+------------+-------------+ | 10 | JACCKA | 5000 | 5000 | --當前視窗的當前行。 | 10 | KING | 5000 | 5000 | --當前視窗的當前行。 | 10 | CLARK | 2450 | 2450 | --當前視窗的當前行。 | 10 | WELAN | 2450 | 2450 | --當前視窗的當前行。 | 10 | TEBAGE | 1300 | 1300 | --當前視窗的當前行。 | 10 | MILLER | 1300 | 1300 | --當前視窗的當前行。 | 20 | SCOTT | 3000 | 3000 | --當前視窗的當前行。 | 20 | FORD | 3000 | 3000 | --當前視窗的當前行。 | 20 | JONES | 2975 | 2975 | --當前視窗的當前行。 | 20 | ADAMS | 1100 | 1100 | --當前視窗的當前行。 | 20 | SMITH | 800 | 800 | --當前視窗的當前行。 | 30 | BLAKE | 2850 | 2850 | --當前視窗的當前行。 | 30 | ALLEN | 1600 | 1600 | --當前視窗的當前行。 | 30 | TURNER | 1500 | 1500 | --當前視窗的當前行。 | 30 | MARTIN | 1250 | 1250 | --當前視窗的當前行。 | 30 | WARD | 1250 | 1250 | --當前視窗的當前行。 | 30 | JAMES | 950 | 950 | --當前視窗的當前行。 +------------+------------+------------+-------------+
LEAD
命令格式
lead(<expr>[, bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)命令說明
返回當前行往後（朝分區尾部方向）第offset行資料對應的運算式expr的值。運算式expr可以是列、列運算或者函數運算等。
參數說明
expr：必填。待計算返回結果的運算式。
offset：可選。位移量，BIGINT類型常量，取值大於等於0。值為0時表示當前行，為1時表示後一行，以此類推。預設值為1。輸入值為STRING類型、DOUBLE類型則隱式轉換為BIGINT類型後進行運算。
default：可選。當offset指定的範圍越界時的預設值，常量，預設值為NULL。需要與expr對應的資料類型相同。如果expr非常量，則基於當前行進行求值。
partition_clause及orderby_clause：詳情請參見windowing_definition。
傳回值說明
傳回值類型同expr類型。
樣本
將所有職工根據部門（deptno）分組（作為開窗列），每位員工的薪水（sal）做位移。命令樣本如下：
select deptno, ename, sal, lead(sal, 1) over (partition by deptno order by sal) as sal_new from emp;返回結果如下：
+------------+------------+------------+------------+ | deptno | ename | sal | sal_new | +------------+------------+------------+------------+ | 10 | TEBAGE | 1300 | 1300 | | 10 | MILLER | 1300 | 2450 | | 10 | CLARK | 2450 | 2450 | | 10 | WELAN | 2450 | 5000 | | 10 | KING | 5000 | 5000 | | 10 | JACCKA | 5000 | NULL | | 20 | SMITH | 800 | 1100 | | 20 | ADAMS | 1100 | 2975 | | 20 | JONES | 2975 | 3000 | | 20 | SCOTT | 3000 | 3000 | | 20 | FORD | 3000 | NULL | | 30 | JAMES | 950 | 1250 | | 30 | MARTIN | 1250 | 1250 | | 30 | WARD | 1250 | 1500 | | 30 | TURNER | 1500 | 1600 | | 30 | ALLEN | 1600 | 2850 | | 30 | BLAKE | 2850 | NULL | +------------+------------+------------+------------+
MAX
命令格式
max(<expr>) over([partition_clause] [orderby_clause] [frame_clause])命令說明
返回視窗中expr的最大值。
參數說明
expr：必填。用於計算最大值的運算式。除BOOLEAN外的任意類型，當值為NULL時，該行不參與計算。
partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。
傳回值說明
傳回值的類型同expr類型。
樣本
樣本1：指定部門（deptno）為開窗列，計算薪水（sal）最大值，不排序，返回當前視窗（相同deptno）的最大值。執行命令如下：
select deptno, sal, max(sal) over (partition by deptno) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 5000 | --視窗開始行，從第1行到第6行的最大值。 | 10 | 2450 | 5000 | --從第1行到第6行的最大值。 | 10 | 5000 | 5000 | --從第1行到第6行的最大值。 | 10 | 1300 | 5000 | | 10 | 5000 | 5000 | | 10 | 2450 | 5000 | | 20 | 3000 | 3000 | | 20 | 3000 | 3000 | | 20 | 800 | 3000 | | 20 | 1100 | 3000 | | 20 | 2975 | 3000 | | 30 | 1500 | 2850 | | 30 | 950 | 2850 | | 30 | 1600 | 2850 | | 30 | 1250 | 2850 | | 30 | 1250 | 2850 | | 30 | 2850 | 2850 | +------------+------------+------------+樣本2：指定部門（deptno）為開窗列，計算薪水（sal）最大值，並排序，返回當前視窗（相同deptno）從開始行到當前行的最大值。執行命令如下：
select deptno, sal, max(sal) over (partition by deptno order by sal) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300 | --視窗開始行。 | 10 | 1300 | 1300 | --從第1行到第2行的最大值。 | 10 | 2450 | 2450 | --從第1行到第3行的最大值。 | 10 | 2450 | 2450 | --從第1行到第4行的最大值。 | 10 | 5000 | 5000 | | 10 | 5000 | 5000 | | 20 | 800 | 800 | | 20 | 1100 | 1100 | | 20 | 2975 | 2975 | | 20 | 3000 | 3000 | | 20 | 3000 | 3000 | | 30 | 950 | 950 | | 30 | 1250 | 1250 | | 30 | 1250 | 1250 | | 30 | 1500 | 1500 | | 30 | 1600 | 1600 | | 30 | 2850 | 2850 | +------------+------------+------------+
MEDIAN
命令格式
median(<expr>) over ([partition_clause] [orderby_clause] [frame_clause])命令說明
計算視窗中expr的中位元。
參數說明
expr：必填。待計算中位元的運算式。DOUBLE類型或DECIMAL類型。最多支援輸入255個數字，至少要輸入1個數字。
當輸入值為STRING類型或BIGINT類型，會隱式轉換為DOUBLE類型後參與運算，輸入為其他資料類型則返回報錯。
當輸入值為NULL時，返回NULL。
partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。
傳回值說明
返回DOUBLE類型或DECIMAL類型。所有expr為NULL時，返回結果為NULL。
樣本
指定部門（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 | +------------+------------+------------+
MIN
命令格式
min(<expr>) over([partition_clause] [orderby_clause] [frame_clause])命令說明
返回視窗中expr的最小值。
參數說明
expr：必填。用於計算最小值的運算式。除BOOLEAN外的任意類型，當值為NULL時，該行不參與計算。
partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。
傳回值說明
傳回值類型同expr類型。
樣本
樣本1：指定部門（deptno）為開窗列，計算薪水（sal）最小值，不排序，返回當前視窗（相同deptno）的最小值。執行命令如下：
select deptno, sal, min(sal) over (partition by deptno) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300 | --視窗開始行，從第1行到第6行的最小值。 | 10 | 2450 | 1300 | --從第1行到第6行的最小值。 | 10 | 5000 | 1300 | --從第1行到第6行的最小值。 | 10 | 1300 | 1300 | | 10 | 5000 | 1300 | | 10 | 2450 | 1300 | | 20 | 3000 | 800 | | 20 | 3000 | 800 | | 20 | 800 | 800 | | 20 | 1100 | 800 | | 20 | 2975 | 800 | | 30 | 1500 | 950 | | 30 | 950 | 950 | | 30 | 1600 | 950 | | 30 | 1250 | 950 | | 30 | 1250 | 950 | | 30 | 2850 | 950 | +------------+------------+------------+樣本2：指定部門（deptno）為開窗列，計算薪水（sal）最小值，並排序，返回當前視窗（相同deptno）從開始行到當前行的最小值。執行命令如下：
select deptno, sal, min(sal) over (partition by deptno order by sal) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300 | --視窗開始行。 | 10 | 1300 | 1300 | --從第1行到第2行的最小值。 | 10 | 2450 | 1300 | --從第1行到第3行的最小值。 | 10 | 2450 | 1300 | | 10 | 5000 | 1300 | | 10 | 5000 | 1300 | | 20 | 800 | 800 | | 20 | 1100 | 800 | | 20 | 2975 | 800 | | 20 | 3000 | 800 | | 20 | 3000 | 800 | | 30 | 950 | 950 | | 30 | 1250 | 950 | | 30 | 1250 | 950 | | 30 | 1500 | 950 | | 30 | 1600 | 950 | | 30 | 2850 | 950 | +------------+------------+------------+
NTILE
命令格式
bigint ntile(bigint <N>) over ([partition_clause] [orderby_clause])命令說明
用於將分區中的資料按照順序切分成N等份，並返回資料所在等份的編號。如果分區中的資料不能被均勻地切分成N等份時，最前面的等份（編號較小的）會優先多分配1條資料。
參數說明
N：必填。切片數量。BIGINT類型。
partition_clause及orderby_clause：詳情請參見windowing_definition。
傳回值說明
返回BIGINT類型。
樣本
將所有職工根據部門按薪水（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 | +------------+------------+------------+------------+
NTH_VALUE
命令格式
nth_value(<expr>, <number> [, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])命令說明
返回運算式expr在視窗的第N條資料進行運算的結果。
參數說明
expr：必填。待計算返回結果的運算式。
number：必填。BIGINT類型。大於等於1的整數。值為1時與FIRST_VALUE等價。
ignore_nulls：可選。BOOLEAN類型。表示是否忽略NULL值。預設值為False。當參數的值為True時，返回視窗中第N條非NULL的expr值。
partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。
傳回值說明
傳回值類型同expr類型。
樣本
將所有職工根據部門分組，返回每組中的第6行資料。命令樣本如下：
不指定order by，當前視窗為第一行到最後一行的範圍，返回當前視窗第6行的值。
select deptno, ename, sal, nth_value(sal,6) over (partition by deptno) as nth_value from emp;返回結果如下：
+------------+------------+------------+------------+ | deptno | ename | sal | nth_value | +------------+------------+------------+------------+ | 10 | TEBAGE | 1300 | 2450 | | 10 | CLARK | 2450 | 2450 | | 10 | KING | 5000 | 2450 | | 10 | MILLER | 1300 | 2450 | | 10 | JACCKA | 5000 | 2450 | | 10 | WELAN | 2450 | 2450 | --當前視窗的第6行。 | 20 | FORD | 3000 | NULL | | 20 | SCOTT | 3000 | NULL | | 20 | SMITH | 800 | NULL | | 20 | ADAMS | 1100 | NULL | | 20 | JONES | 2975 | NULL | --當前視窗的沒有第6行，返回NULL。 | 30 | TURNER | 1500 | 2850 | | 30 | JAMES | 950 | 2850 | | 30 | ALLEN | 1600 | 2850 | | 30 | WARD | 1250 | 2850 | | 30 | MARTIN | 1250 | 2850 | | 30 | BLAKE | 2850 | 2850 | --當前視窗的第6行。 +------------+------------+------------+------------+指定order by，當前視窗為第一行到當前行的範圍，返回當前視窗第6行的值。
select deptno, ename, sal, nth_value(sal,6) over (partition by deptno order by sal) as nth_value from emp;返回結果如下：
+------------+------------+------------+------------+ | deptno | ename | sal | nth_value | +------------+------------+------------+------------+ | 10 | TEBAGE | 1300 | NULL | | 10 | MILLER | 1300 | NULL | --當前視窗只有2行，第6行超過了視窗長度。 | 10 | CLARK | 2450 | NULL | | 10 | WELAN | 2450 | NULL | | 10 | KING | 5000 | 5000 | | 10 | JACCKA | 5000 | 5000 | | 20 | SMITH | 800 | NULL | | 20 | ADAMS | 1100 | NULL | | 20 | JONES | 2975 | NULL | | 20 | SCOTT | 3000 | NULL | | 20 | FORD | 3000 | NULL | | 30 | JAMES | 950 | NULL | | 30 | MARTIN | 1250 | NULL | | 30 | WARD | 1250 | NULL | | 30 | TURNER | 1500 | NULL | | 30 | ALLEN | 1600 | NULL | | 30 | BLAKE | 2850 | 2850 | +------------+------------+------------+------------+
PERCENT_RANK
命令格式
double percent_rank() over([partition_clause] [orderby_clause])命令說明
計算當前行在分區中按照orderby_clause排序後的百分比排名。
參數說明
partition_clause及orderby_clause：詳情請參見windowing_definition。
傳回值說明
返回DOUBLE類型，範圍為[0.0, 1.0]。具體的傳回值等於
“(rank - 1) / (partition_row_count - 1)”，其中：rank為該行資料的RANK視窗函數的返回結果，partition_row_count為該行資料所屬分區的資料行數。當分區中只有一行資料時，輸出結果為0.0。樣本
計算員工薪水在組內的百分比排名。命令樣本如下：
select deptno, ename, sal, percent_rank() over (partition by deptno order by sal desc) as sal_new from emp;返回結果如下：
+------------+------------+------------+------------+ | deptno | ename | sal | sal_new | +------------+------------+------------+------------+ | 10 | JACCKA | 5000 | 0.0 | | 10 | KING | 5000 | 0.0 | | 10 | CLARK | 2450 | 0.4 | | 10 | WELAN | 2450 | 0.4 | | 10 | TEBAGE | 1300 | 0.8 | | 10 | MILLER | 1300 | 0.8 | | 20 | SCOTT | 3000 | 0.0 | | 20 | FORD | 3000 | 0.0 | | 20 | JONES | 2975 | 0.5 | | 20 | ADAMS | 1100 | 0.75 | | 20 | SMITH | 800 | 1.0 | | 30 | BLAKE | 2850 | 0.0 | | 30 | ALLEN | 1600 | 0.2 | | 30 | TURNER | 1500 | 0.4 | | 30 | MARTIN | 1250 | 0.6 | | 30 | WARD | 1250 | 0.6 | | 30 | JAMES | 950 | 1.0 | +------------+------------+------------+------------+
PERCENTILE_CONT
命令格式
-- 計算精確的百分位元 PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) -- 計算視窗中精確的百分位元 PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])命令說明
計算精確的百分位元，採用線性插值演算法，對指定列升序排列，返回精確的第percentile位百分數。
參數說明
col_name：必填。值為 DOUBLE 類型或 DECIMAL 類型的列。
percentile：必填。需要計算的百分位元。DOUBLE類型常量，取值在[0,1]範圍內。
isIgnoreNull：可選。是否忽略NULL值。BOOLEAN類型常量，預設為TRUE。若取值為FALSE，排序時NULL值會作為最小值。
partition_clause及orderby_clause：詳情請參見視窗函數。
傳回值說明
返回計算的百分位元值，類型為DOUBLE。
樣本
樣本1：忽略NULL值，計算視窗中精確的百分位元。
SELECT PERCENTILE_CONT(x, 0) OVER() AS min, PERCENTILE_CONT(x, 0.01) OVER() AS percentile1, PERCENTILE_CONT(x, 0.5) OVER() AS median, PERCENTILE_CONT(x, 0.9) OVER() AS percentile90, PERCENTILE_CONT(x, 1) OVER() AS max FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1; -- 返回結果。 +------------+-------------+------------+--------------+------------+ | min | percentile1 | median | percentile90 | max | +------------+-------------+------------+--------------+------------+ | 0.0 | 0.03 | 1.5 | 2.7 | 3.0 | +------------+-------------+------------+--------------+------------+樣本2：不忽略NULL值則排序時NULL值作為最小值，計算視窗中精確的百分位元。
SELECT PERCENTILE_CONT(x, 0, false) OVER() AS min, PERCENTILE_CONT(x, 0.01, false) OVER() AS percentile1, PERCENTILE_CONT(x, 0.5, false) OVER() AS median, PERCENTILE_CONT(x, 0.9, false) OVER() AS percentile90, PERCENTILE_CONT(x, 1, false) OVER() AS max FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1; -- 返回結果。 +------------+-------------+------------+--------------+------------+ | min | percentile1 | median | percentile90 | max | +------------+-------------+------------+--------------+------------+ | NULL | 0.0 | 1.0 | 2.6 | 3.0 | +------------+-------------+------------+--------------+------------+
PERCENTILE_DISC
命令格式
-- 計算給定的百分位元值 PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) -- 計算視窗中的百分位元值 PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])命令說明
計算給定的百分位元值，先對指定列升序排列，返回累計分布大於或等於給定百分位元的第一個值。
參數說明
col_name：必填。任意可排序值列。
percentile：必填。需要計算的百分位元。DOUBLE類型常量，取值在[0,1]範圍內。
isIgnoreNull：可選。是否忽略NULL值。BOOLEAN類型常量，預設為TRUE。若取值為FALSE，排序時NULL值會作為最小值。
partition_clause及orderby_clause：詳情請參見視窗函數。
傳回值說明
返回計算的百分位元值，類型與輸入col_name列的資料類型一致。
樣本
樣本1：忽略NULL值，計算視窗中的百分位元值。
SELECT x, PERCENTILE_DISC(x, 0) OVER() AS min, PERCENTILE_DISC(x, 0.5) OVER() AS median, PERCENTILE_DISC(x, 1) OVER() AS max FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x); -- 返回結果。 +------------+------------+------------+------------+ | x | min | median | max | +------------+------------+------------+------------+ | c | a | b | c | | NULL | a | b | c | | b | a | b | c | | a | a | b | c | +------------+------------+------------+------------+樣本2：不忽略NULL值則排序時NULL值作為最小值，計算視窗中的百分位元值。
SELECT x, PERCENTILE_DISC(x, 0, false) OVER() AS min, PERCENTILE_DISC(x, 0.5, false) OVER() AS median, PERCENTILE_DISC(x, 1, false) OVER() AS max FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x); -- 返回結果。 +------------+------------+------------+------------+ | x | min | median | max | +------------+------------+------------+------------+ | c | NULL | a | c | | NULL | NULL | a | c | | b | NULL | a | c | | a | NULL | a | c | +------------+------------+------------+------------+
RANK
命令格式
bigint rank() over ([partition_clause] [orderby_clause])命令說明
計算當前行在分區中按照orderby_clause排序後所處的排名。從1開始計數。
參數說明
partition_clause及orderby_clause：詳情請參見windowing_definition。
傳回值說明
返回BIGINT類型。傳回值可能重複、且不連續。具體的傳回值為該行資料所在GROUP的第一條資料的
ROW_NUMBER()值。未指定orderby_clause時，返回結果全為1。樣本
將所有職工根據部門（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 | +------------+------------+------------+------------+
ROW_NUMBER
命令格式
row_number() over([partition_clause] [orderby_clause])命令說明
計算當前行在分區中的行號，從1開始遞增。
參數說明
請參見windowing_definition。不允許包含frame_clause。
傳回值說明
返回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 | +------------+------------+------------+------------+
STDDEV
命令格式
double stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause]) decimal stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])命令說明
計算總體標準差，STDDEV_POP函數的別名。
參數說明
expr：必填。待計算總體標準差的運算式。DOUBLE類型或DECIMAL類型。
當輸入值為STRING類型或BIGINT類型時，會隱式轉換為DOUBLE類型後參與運算，其他類型返回報錯。
當輸入值為NULL時，該行不參與計算。
當指定distinct關鍵字時，表示計算唯一值的總體標準差。
partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。
傳回值說明
傳回值類型同expr類型。所有expr為NULL時，返回結果為NULL。
樣本
樣本1：指定部門（deptno）為開窗列，計算薪水（sal）總體標準差，不排序，返回當前視窗（相同deptno）的累計總體標準差。命令樣本如下：
select deptno, sal, stddev(sal) over (partition by deptno) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1546.1421524412158 | --視窗開始行，從第1行到第6行的累計總體標準差。 | 10 | 2450 | 1546.1421524412158 | --從第1行到第6行的累計總體標準差。 | 10 | 5000 | 1546.1421524412158 | | 10 | 1300 | 1546.1421524412158 | | 10 | 5000 | 1546.1421524412158 | | 10 | 2450 | 1546.1421524412158 | | 20 | 3000 | 1004.7387720198718 | | 20 | 3000 | 1004.7387720198718 | | 20 | 800 | 1004.7387720198718 | | 20 | 1100 | 1004.7387720198718 | | 20 | 2975 | 1004.7387720198718 | | 30 | 1500 | 610.1001739241042 | | 30 | 950 | 610.1001739241042 | | 30 | 1600 | 610.1001739241042 | | 30 | 1250 | 610.1001739241042 | | 30 | 1250 | 610.1001739241042 | | 30 | 2850 | 610.1001739241042 | +------------+------------+------------+樣本2：非Hive相容模式下，指定部門（deptno）為開窗列，計算薪水（sal）總體標準差，並排序，返回當前視窗（相同deptno）從開始行到當前行的累計總體標準差。命令樣本如下：
--關閉Hive相容模式。 set odps.sql.hive.compatible=false; --執行如下SQL命令。 select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 0.0 | --視窗開始行。 | 10 | 1300 | 0.0 | --從第1行到第2行的累計總體標準差。 | 10 | 2450 | 542.1151989096865 | --從第1行到第3行的累計總體標準差。 | 10 | 2450 | 575.0 | --從第1行到第4行的累計總體標準差。 | 10 | 5000 | 1351.6656391282572 | | 10 | 5000 | 1546.1421524412158 | | 20 | 800 | 0.0 | | 20 | 1100 | 150.0 | | 20 | 2975 | 962.4188277460079 | | 20 | 3000 | 1024.2947268730811 | | 20 | 3000 | 1004.7387720198718 | | 30 | 950 | 0.0 | | 30 | 1250 | 150.0 | | 30 | 1250 | 141.4213562373095 | | 30 | 1500 | 194.8557158514987 | | 30 | 1600 | 226.71568097509268 | | 30 | 2850 | 610.1001739241042 | +------------+------------+------------+樣本3：Hive相容模式下，指定部門（deptno）為開窗列，計算薪水（sal）總體標準差，並排序，返回當前視窗（相同deptno）從開始行到與當前行相同值所在行（相同sal值的總體標準差相同）的累計總體標準差。命令樣本如下：
--開啟Hive相容模式。 set odps.sql.hive.compatible=true; --執行如下SQL命令。 select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 0.0 | --視窗開始行。第1行和第2行的sal一致，則第1行的總體標準差為第1行到第2行的累計總體標準差。 | 10 | 1300 | 0.0 | --從第1行到第2行的累計總體標準差。 | 10 | 2450 | 575.0 | --第3行和第4行的sal一致，則第3行的總體標準差為從第1行到第4行的累計總體標準差。 | 10 | 2450 | 575.0 | --從第1行到第4行的累計總體標準差。 | 10 | 5000 | 1546.1421524412158 | | 10 | 5000 | 1546.1421524412158 | | 20 | 800 | 0.0 | | 20 | 1100 | 150.0 | | 20 | 2975 | 962.4188277460079 | | 20 | 3000 | 1004.7387720198718 | | 20 | 3000 | 1004.7387720198718 | | 30 | 950 | 0.0 | | 30 | 1250 | 141.4213562373095 | | 30 | 1250 | 141.4213562373095 | | 30 | 1500 | 194.8557158514987 | | 30 | 1600 | 226.71568097509268 | | 30 | 2850 | 610.1001739241042 | +------------+------------+------------+
STDDEV_SAMP
命令格式
double stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause]) decimal stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])命令說明
計算樣本標準差。
參數說明
expr：必填。待計算樣本標準差的運算式。DOUBLE類型或DECIMAL類型。
當輸入值為STRING類型或BIGINT類型時，會隱式轉換為DOUBLE類型後參與運算，其他類型返回報錯。
當輸入值為NULL時，該行不參與計算。
當指定distinct關鍵字時，表示計算唯一值的樣本標準差。
partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。
傳回值說明
傳回值類型同expr類型。所有expr為NULL時，返回結果為NULL。視窗僅包含1條expr值非NULL的資料時，結果為0。
樣本
樣本1：指定部門（deptno）為開窗列，計算薪水（sal）樣本標準差，不排序，返回當前視窗（相同deptno）的累計樣本標準差。命令樣本如下：
select deptno, sal, stddev_samp(sal) over (partition by deptno) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1693.7138680032904 | --視窗開始行，第1行到第6行的累計樣本標準差。 | 10 | 2450 | 1693.7138680032904 | --從第1行到第6行的累計樣本標準差。 | 10 | 5000 | 1693.7138680032904 | --從第1行到第6行的累計樣本標準差。 | 10 | 1300 | 1693.7138680032904 | | 10 | 5000 | 1693.7138680032904 | | 10 | 2450 | 1693.7138680032904 | | 20 | 3000 | 1123.3320969330487 | | 20 | 3000 | 1123.3320969330487 | | 20 | 800 | 1123.3320969330487 | | 20 | 1100 | 1123.3320969330487 | | 20 | 2975 | 1123.3320969330487 | | 30 | 1500 | 668.331255192114 | | 30 | 950 | 668.331255192114 | | 30 | 1600 | 668.331255192114 | | 30 | 1250 | 668.331255192114 | | 30 | 1250 | 668.331255192114 | | 30 | 2850 | 668.331255192114 | +------------+------------+------------+樣本2：指定部門（deptno）為開窗列，計算薪水（sal）樣本標準差，並排序，返回當前視窗（相同deptno）從開始行到當前行的累計樣本標準差。命令樣本如下：
select deptno, sal, stddev_samp(sal) over (partition by deptno order by sal) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 0.0 | --視窗開始行。 | 10 | 1300 | 0.0 | --從第1行到第2行的累計樣本標準差。 | 10 | 2450 | 663.9528095680697 | --從第1行到第3行的累計樣本標準差。 | 10 | 2450 | 663.9528095680696 | | 10 | 5000 | 1511.2081259707413 | | 10 | 5000 | 1693.7138680032904 | | 20 | 800 | 0.0 | | 20 | 1100 | 212.13203435596427 | | 20 | 2975 | 1178.7175234126282 | | 20 | 3000 | 1182.7536725793752 | | 20 | 3000 | 1123.3320969330487 | | 30 | 950 | 0.0 | | 30 | 1250 | 212.13203435596427 | | 30 | 1250 | 173.20508075688772 | | 30 | 1500 | 225.0 | | 30 | 1600 | 253.4758371127315 | | 30 | 2850 | 668.331255192114 | +------------+------------+------------+
SUM
命令格式
sum([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])命令說明
返回視窗中expr之和。
參數說明
expr：必填。待計算匯總值的列。DOUBLE類型、DECIMAL類型或BIGINT類型。
當輸入值為STRING類型時，會隱式轉換為DOUBLE類型後參與運算，其他類型返回報錯。
當輸入值為NULL時，該行不參與計算。
當指定distinct關鍵字時，表示計算唯一值的匯總值。
partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。
傳回值說明
輸入值為BIGINT類型時，返回BIGINT類型。
輸入值為DECIMAL類型時，返回DECIMAL類型。
輸入值為DOUBLE類型或STRING類型時，返回DOUBLE類型。
輸入值都為NULL時，返回NULL。
樣本
樣本1：指定部門（deptno）為開窗列，計算薪水（sal）匯總值，不排序，返回當前視窗（相同deptno）的累計匯總值。命令樣本如下：
select deptno, sal, sum(sal) over (partition by deptno) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 17500 | --視窗開始行。第1行到第6行的累計匯總值。 | 10 | 2450 | 17500 | --從第1行到第6行的累計匯總值。 | 10 | 5000 | 17500 | --從第1行到第6行的累計匯總值。 | 10 | 1300 | 17500 | | 10 | 5000 | 17500 | | 10 | 2450 | 17500 | | 20 | 3000 | 10875 | | 20 | 3000 | 10875 | | 20 | 800 | 10875 | | 20 | 1100 | 10875 | | 20 | 2975 | 10875 | | 30 | 1500 | 9400 | | 30 | 950 | 9400 | | 30 | 1600 | 9400 | | 30 | 1250 | 9400 | | 30 | 1250 | 9400 | | 30 | 2850 | 9400 | +------------+------------+------------+樣本2：非Hive相容模式下，指定部門（deptno）為開窗列，計算薪水（sal）匯總值，並排序，返回當前視窗（相同deptno）從開始行到當前行的累計匯總值。命令樣本如下：
--關閉Hive相容模式。 set odps.sql.hive.compatible=false; --執行如下SQL命令。 select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300 | --視窗開始行。 | 10 | 1300 | 2600 | --從第1行到第2行的累計匯總值。 | 10 | 2450 | 5050 | --從第1行到第3行的累計匯總值。 | 10 | 2450 | 7500 | | 10 | 5000 | 12500 | | 10 | 5000 | 17500 | | 20 | 800 | 800 | | 20 | 1100 | 1900 | | 20 | 2975 | 4875 | | 20 | 3000 | 7875 | | 20 | 3000 | 10875 | | 30 | 950 | 950 | | 30 | 1250 | 2200 | | 30 | 1250 | 3450 | | 30 | 1500 | 4950 | | 30 | 1600 | 6550 | | 30 | 2850 | 9400 | +------------+------------+------------+樣本3：Hive相容模式下，指定部門（deptno）為開窗列，計算薪水（sal）匯總值，並排序，返回當前視窗（相同deptno）從開始行到與當前行相同值所在行（相同sal值的匯總值相同）的累計匯總值。命令樣本如下：
--開啟Hive相容模式。 set odps.sql.hive.compatible=true; --執行如下SQL命令。 select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;返回結果如下：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 2600 | --視窗開始行。第1行和第2行的sal一致，則第1行的匯總值為第1行到第2行的累計匯總值。 | 10 | 1300 | 2600 | --從第1行到第2行的累計匯總值。 | 10 | 2450 | 7500 | --第3行和第4行的sal一致，則第3行的匯總值為從第1行到第4行的累計匯總值。 | 10 | 2450 | 7500 | --從第1行到第4行的累計匯總值。 | 10 | 5000 | 17500 | | 10 | 5000 | 17500 | | 20 | 800 | 800 | | 20 | 1100 | 1900 | | 20 | 2975 | 4875 | | 20 | 3000 | 10875 | | 20 | 3000 | 10875 | | 30 | 950 | 950 | | 30 | 1250 | 3450 | | 30 | 1250 | 3450 | | 30 | 1500 | 4950 | | 30 | 1600 | 6550 | | 30 | 2850 | 9400 | +------------+------------+------------+