全部產品
Search

搜尋本產品

    IoT Platform：視窗函數

    文件中心

    IoT Platform：視窗函數

    更新時間：Jun 30, 2024

    您可以使用視窗函數對指定開窗列的資料靈活地進行分析處理工作。本文為您提供支援的視窗函數的命令格式、參數說明及樣本，指導您使用視窗函數完成開發。

    支援的視窗函數如下。

    函數

    功能

    AVG

    對視窗中的資料求平均值。

    CLUSTER_SAMPLE

    使用者隨機抽樣。返回True表示該行資料被抽中。

    COUNT

    計算視窗中的記錄數。

    CUME_DIST

    計算累計分布。

    DENSE_RANK

    計算排名。排名是連續的。

    FIRST_VALUE

    取當前行所對應視窗的第一條資料的值。

    LAG

    取當前行往前（朝分區頭部方向）第N行資料的值。

    LAST_VALUE

    取當前行所對應視窗的最後一條資料的值。

    LEAD

    取當前行往後（朝分區尾部方向）第N行資料的值。

    MAX

    計算視窗中的最大值。

    MEDIAN

    計算視窗中的中位元。

    MIN

    計算視窗中的最小值。

    NTILE

    將資料順序切分成N等份，返回資料所在等份的編號（從1到N）。

    NTH_VALUE

    取當前行所對應視窗的第N條資料的值。

    PERCENT_RANK

    計算排名。輸出百分比格式。

    RANK

    計算排名。排名可能不連續。

    ROW_NUMBER

    計算行號。從1開始遞增。

    STDDEV

    計算總體標準差。是STDDEV_POP的別名。

    STDDEV_SAMP

    計算樣本標準差。

    SUM

    對視窗中的資料求和。

    使用限制

    視窗函數的使用限制如下：

    • 視窗函數只能出現在select語句中。

    • 視窗函數中不能嵌套使用視窗函數和彙總函式。

    • 視窗函數不能和同層級的彙總函式一起使用。

    視窗函數文法

    視窗函數的文法聲明如下。 

    <function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
<function_name>([distinct][<expression> [, ...]]) over <window_name>

    • function_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。

    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時，預設的frame_clause如下，與大部分SQL系統相同。

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

    您可以將如下SQL語句中的...替換為視窗定義語句windowing_definition來展示每一條資料所對應的視窗包含的資料列表：

    說明

    Window列為NULL時，不包含任何資料。

    • 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,,10

    AVG

    • 命令格式 

      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）從開始行到與當前行相同值所在行（相同sal值的平均值相同）的累計平均值。命令樣本如下：

        --執行如下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(*) over ([partition_clause] [orderby_clause] [frame_clause])
bigint count([distinct] <expr>[,...]) over ([partition_clause] [orderby_clause] [frame_clause])

    • 命令說明

      • count(*)：返回總行數。

      • count([distinct] <expr>[,...])：計算行數時會忽略expr值為NULL的行，如果有多個expr，則任意expr值為NULL都被忽略。此外如果指定了distinct關鍵字，則計算去重之後的資料行數，任意expr值為NULL的行同樣會被忽略。

    • 參數說明

      • expr：必填。待計算計數值的列。可以為任意類型。當值為NULL時，該行不參與計算。當指定DISTINCT關鍵字時，表示取唯一值的計數值。

      • partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。

    • 傳回值說明

      返回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：指定薪水（sal）為開窗列，並排序，返回當前視窗（相同sal）從開始行至最後一行的累計計數值。命令樣本如下：

        --執行如下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          |
+------------+------------+

    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類型常量，取值大於等於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, 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        |
+------------+------------+------------+------------+

    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：指定部門（deptno）為開窗列，計算薪水（sal）總體標準差，並排序，返回當前視窗（相同deptno）從開始行到與當前行相同值所在行（相同sal值的總體標準差相同）的累計總體標準差。命令樣本如下：

        --執行如下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：指定部門（deptno）為開窗列，計算薪水（sal）匯總值，並排序，返回當前視窗（相同deptno）從開始行到與當前行相同值所在行（相同sal值的匯總值相同）的累計匯總值。命令樣本如下：

        --執行如下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       |
+------------+------------+------------+