開發指南—函數—視窗函數

使用限制

• 視窗函數僅支援用于SELECT語句中。
• 視窗函數禁止與單獨的聚合函數混合使用。例如，在如下語句中，SUM為聚合函數，且未與OVER關鍵字組合，是以您無法使用如下語句進行查詢：

SELECT SUM(NAME),COUNT() OVER(...) FROM SOME_TABLE      

• 若需實作如上查詢，您可以使用如下語句代替：

SELECT SUM(NAME),WIN1 FROM (SELECT NAME,COUNT() OVER(...) AS WIN1 FROM SOME_TABLE) alias      

文法

function OVER ([[partition by column_some1] [order by column_some2] [RANGE|ROWS BETWEEN start AND end]])      

參數	說明
function	該部分指定了視窗函數中支援的函數，取值範圍如下： 可以在視窗函數中結合OVER關鍵字使用如下聚合函數： SUM() COUNT() AVG() MAX() MIN() 專用視窗函數如下： ROW_NUMBER() RANK() DENSE_RANK() PERCENT_RANK() CUME_DIST() FIRST_VALUE() LAST_VALUE() LAG() LEAD() NTH_VALUE() 當使用專用視窗函數 RANK() 或 DENSE_RANK() 時，視窗函數中的 order by 部分不可省略。更多專用視窗函數的介紹，請參見 Window Function Descriptions 。 支援如下專用視窗函數： PERCENT_RANK() CUME_DIST() FIRST_VALUE() LAST_VALUE() LAG() LEAD() NTH_VALUE()
[partition by column_some1]	該部分指定了視窗函數的分區規範，用于将輸入行分散到不同的分區中，過程和GROUP BY子句的分散過程相似。 partition by 部分不支援引用複雜表達式，如您可以引用 column_some1 ，但不可以引用 column_some1 + 1
[order by column_some2]	該部分指定了視窗函數的排序規範，用于确定輸入資料行在視窗函數中執行的順序。 order by column_some2 column_some2 + 1
[RANGE|ROWS BETWEEN start AND end]	該部分指定了視窗函數的視窗區間，支援按照計算列值的範圍（即RANGE）或計算列的行數（即ROWS）等兩種模式來定義區間。 您可以使用 BETWEEN start AND end 指定邊界的可取值，其中： start 取值範圍如下： CURRENT ROW ：目前行 N PRECEDING ：前N行 UNBOUNDED PRECEDING ：直到第1行 end CURRENT ROW N FOLLOWING ：後N行 UNBOUNDED FOLLOWING ：直到最後1行

使用示例

假設已有如下原始資料：

| year | country | product    | profit |
|------|---------|------------|--------|
| 2001 | Finland | Phone      |     10 |
| 2000 | Finland | Computer   |   1500 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Calculator |     75 |
| 2001 | India   | Calculator |     79 |      

• 您可以使用如下聚合函數來統計每個國家的總利潤：

select
    country,
    sum(profit) over (partition by country) sum_profit
from test_window;      

• 傳回結果如下：

| country | sum_profit |
|---------|------------|
| India   |        229 |
| India   |        229 |
| India   |        229 |
| USA     |       1550 |
| USA     |       1550 |
| Finland |       1510 |
| Finland |       1510 |      

• 您可以使用如下專用視窗函數将資料按照國家分組，并将國家内的産品按利潤由小到大排名：

select
    'year',
    country,
    product,
    profit,
    rank() over (partition by country order by profit) as rank
from test_window;      

| year | country | product    | profit | rank |
|------|---------|------------|--------|------|
| 2001 | Finland | Phone      |     10 |    1 |
| 2000 | Finland | Computer   |   1500 |    2 |
| 2001 | USA     | Calculator |     50 |    1 |
| 2001 | USA     | Computer   |   1500 |    2 |
| 2000 | India   | Calculator |     75 |    1 |
| 2000 | India   | Calculator |     75 |    1 |
| 2001 | India   | Calculator |     79 |    3 |      

• 您可以使用如下帶有ROWS指令的語句，查詢根據目前視窗的每行資料計算利潤部分的總和：

select 
    'year',
    country,
    profit,
    sum(profit) over (partition by country order by 'year' ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as sum_win 
from test_window;      

+------+---------+--------+-------------+
| year | country | profit |   sum_win   |
+------+---------+--------+-------------+
| 2001 | USA     |     50 |          50 |
| 2001 | USA     |   1500 |        1550 |
| 2000 | India   |     75 |          75 |
| 2000 | India   |     75 |         150 |
| 2001 | India   |     79 |         229 |
| 2000 | Finland |   1500 |        1500 |
| 2001 | Finland |     10 |        1510 |