Window 関数 - PolarDB - Alibaba Cloud ドキュメントセンター

従来のGROUP BY関数は、データをグループに編成し、グループに基づいてクエリ結果を集約します。この場合、GROUP BYは各データグループに対して1行のみを返します。ただし、オンライン分析処理 (OLAP) 関数とも呼ばれるウィンドウ関数は、クエリ結果を集約することなく、データグループごとに複数の行を返すことができます。これは、従来のGROUP BY関数とは異なります。このトピックでは、ウィンドウ関数の使用方法について説明します。

前提条件

PolarDB-X 1.0インスタンスのバージョンは5.4.8以降です。

制限事項

Window関数はSELECT文でのみ使用できます。
ウィンドウ関数は、個別の集計関数と組み合わせて使用することはできません。
次のステートメントでは、OVERキーワードを含まないSUM関数は集計関数です。したがって、このステートメントは実行できません。
```
SELECT SUM(NAME),COUNT() OVER(...) SOME_TABLEから
```
上記のクエリを実装するには、次のステートメントを使用します。
```
SELECT SUM(NAME) 、WIN1 FROM (SELECT NAME、COUNT() OVER(...) AS WIN1 FROM SOME_TABLE) エイリアス
```

構文

関数OVER ([[partition by column_some1] [order by column_some2] [RANGE | 開始と終了の間の行]])

パラメーター	説明
`関数`	指定できるウィンドウ関数。次の機能がサポートされています。集計関数とOVERキーワードで構成されるウィンドウ関数: `SUM()` `カウント ()` `AVG()` `MAX()` `MIN()` 専用ウィンドウ関数: `ROW_NUMBER()` `ランク ()` `DESNCE_RANK()` `PERCENT_RANK()` `CUME_DIST()` `FIRST_VALUE()` `LAST_VALUE()` `LAG()` `LEAD()` `NTH_VALUE()` 説明 `RANK()` または`DENSE_RANK()` ウィンドウ関数を使用する場合、`ORDER BY`句は省略できません。専用ウィンドウ関数の詳細については、「ウィンドウ関数の説明」をご参照ください。サポートされている値は、バージョンが5.4.9以降のインスタンス (インスタンスのバージョンが5.4.9より前の場合は、バージョンをアップグレードします。詳細については、「バージョンのアップグレード」をご参照ください。 `PERCENT_RANK()` `CUME_DIST()` `FIRST_VALUE()` `LAST_VALUE()` `LAG()` `LEAD()` `NTH_VALUE()`
`[column_some1によるパーティション]`	ウィンドウ関数のパーティションルール。この句は、入力行を異なるパーティションに分割します。このプロセスは、GROUP BY句の分割プロセスと同様です。説明 `PARTITION BY`句で複雑な式を参照することはできません。たとえば、`column_some1`は参照できますが、`column_some1 + 1`は参照できません。
`[column_some2による注文]`	ウィンドウ関数のソートルール。この句は、入力行がウィンドウ関数で計算される順序を定義します。説明 `ORDER BY`句で複雑な式を参照することはできません。たとえば、`column_some2`は参照できますが、`column_some2 + 1`は参照できません。
`[範囲 \| 開始と終了の間の行]`	ウィンドウ関数のウィンドウフレーム。 RANGEまたはROWSを使用してフレームを定義できます。 RANGEは、計算された列の値の範囲によってフレームが定義されることを示します。 ROWSは、フレームが計算された列の行数によって定義されることを示します。 `BETWEEN start AND end`オプションを使用して、ウィンドウ内の境界行を指定できます。 `start`の有効値: `現在の行`: ウィンドウは現在の行から始まります。 `N PRECEDING`: ウィンドウは、前のN番目の行から始まる。 `UNBOUNDED PRECEDING`: ウィンドウは最初の行から始まります。 `end`の有効値: `現在の行`: ウィンドウは現在の行で終了します。 `N FOLLOWING`: ウィンドウは次のN番目の行で終了します。 `UNBOUNDED FOLLOWING`: ウィンドウは最後の行で終了します。

ユースケース

次の生データが作成されたとします。

| 年 | 国 | プロダクト | 利益 |
| ------ | -------- | ---------- | -------- | -------- |
| 2001 | フィンランド | 電話 | 10 |
| 2000 | フィンランド | コンピュータ | 1500 |
| 2001 | アメリカ | 電卓 | 50 |
| 2001 | アメリカ | コンピュータ | 1500 |
| 2000 | シンガポール | 電卓 | 75 |
| 2000 | シンガポール | 電卓 | 75 |
| 2001 | シンガポール | 電卓 | 79 |

次の集計関数を使用して、各国の総利益を計算します。

select
    国,
    sum(profit) over (partition by country) sum_profit
test_windowから。

次の応答が返されます。

| 国 | sum_profit |
| -------- | ------------ |
| シンガポール | 229 |
| シンガポール | 229 |
| シンガポール | 229 |
| アメリカ | 1550 |
| アメリカ | 1550 |
| フィンランド | 1510 |
| フィンランド | 1510 |

次の専用ウィンドウ関数を使用して、国ごとにデータをグループ化し、各国の製品を利益順に昇順でランク付けします。

select
    「年」、
    国,
    プロダクト,
    利益、
    rank() over (partition by country order by profit) as rank
test_windowから。

次の応答が返されます。

| 年 | 国 | プロダクト | 利益 | ランク |
| ------ | -------- | ----------- | -------- | ------- | ------- |
| 2001 | フィンランド | 電話 | 10 | 1 |
| 2000 | フィンランド | コンピュータ | 1500 | 2 |
| 2001 | アメリカ | 電卓 | 50 | 1 |
| 2001 | アメリカ | コンピュータ | 1500 | 2 |
| 2000 | シンガポール | 电卓 | 75 | 1 |
| 2000 | シンガポール | 电卓 | 75 | 1 |
| 2001 | シンガポール | 电卓 | 79 | 3 |

ROWSオプションを含む次のステートメントを実行して、現在のウィンドウの各行の利益の累積合計を計算します。

select 
    「年」、
    国,
    利益、
    sum_winとして、sum (利益) over (「年」行による国別の注文によるパーティション)
test_windowから。

次の応答が返されます。

+ -------------------------------------------- +
| 年 | 国 | 利益 | sum_win |
------ -------- ----------- --------------------
| 2001 | アメリカ | 50 | 50 |
| 2001 | アメリカ | 1500 | 1550 |
| 2000 | シンガポール | 75 |
| 2000 | シンガポール | 75 | 150 |
| 2001 | シンガポール | 79 | 229 |
| 2000 | フィンランド | 1500 | 1500 |
| 2001 | フィンランド | 10 | 1510 |