AnalyticDB for MySQLでサポートされるウィンドウ関数 - AnalyticDB

AnalyticDB for MySQLは、次のウィンドウ関数をサポートしています。

集計関数
ソート関数
- CUME_DIST: 値のセット内の各値の累積分布を返します。
- RANK: データセット内の各値のランクを返します。
- DENSE_RANK: 値のセット内の各値のランクを返します。
- NTILE: 各ウィンドウパーティション内のデータをn個のバケットに分割します。バケツは1からnまで番号が付けられています。
- ROW_NUMBER: 1から始まる、ウィンドウパーティション内の行のシーケンスに基づいて、各行の一意のシーケンシャル番号を返します。
- PERCENT_RANK: データセット内の各値のランキング率を (r - 1)/(n - 1) 形式で返します。 rは、rank () によって計算された現在の行のランクであり、nは、現在のウィンドウパーティション内の行の総数である。
値関数
- FIRST_VALUE: ウィンドウパーティション内の最初の行の値を返します。
- LAST_VALUE: ウィンドウパーティション内の最後の行の値を返します。
- LAG: ウィンドウ内のオフセット行によって現在の行の前にある行の値を返します。
- LEAD: ウィンドウ内の行をオフセットして、現在の行に続く行の値を返します。
- NTH_VALUE: ウィンドウ内の指定されたオフセット行数だけ行の値を返します。オフセットは1から始まります。

概要

ウィンドウ関数は、クエリ結果からの行データに基づいて集計値を計算します。ウィンドウ関数は、HAVING句の後およびORDER BY句の前に実行されます。 OVER句を使用してウィンドウを指定すると、ウィンドウ関数がトリガーされます。

AnalyticDB for MySQLは、集計関数、ソート関数、値関数の3種類のウィンドウ関数をサポートしています。

構文

function over (b RANGEによる注文によるパーティション | 開始と終了の間の行)

ウィンドウ関数には、次の部分が含まれます。

パーティションルール: 入力行を異なるパーティションに分割します。このプロセスは、GROUP BY句のグループ化プロセスと同様です。
ソートルール: 入力行がウィンドウ関数で実行される順序を決定します。
ウィンドウフレーム: ウィンドウ関数が計算を実行するデータの境界を指定します。
ウィンドウフレームは、RANGEおよびROWSモードをサポートします。
- RANGEは列値の範囲を定義します。
- ROWSは、現在の行に対する相対行数を定義します。
- RANGEおよびROWSでは、BETWEEN start and endを使用して境界値を指定できます。 BETWEEN start AND endの引数の有効な値:
  - 現在の行: 現在の行
  - N PRECEDING: 前のn行
  - UNBOUNDED PRECEDING: 最初の行まで
  - N FOLLOWING: 次のn行
  - UNBOUNDED FOLLOWING: 最後の行まで

たとえば、次のクエリでは、現在のウィンドウのデータの各行に基づいて利益の部分合計を計算します。

、テストウィンドウからスライドウィンドウとして、年、国、利益、合計 (利益) を選択します。------ -------- ----------- --------------------
| 年 | 国 | 利益 | slidewindow |
------ -------- ----------- --------------------
| 2001 | アメリカ | 50 | 50 |
| 2001 | アメリカ | 1500 | 1550 |
| 2000 | ドイツ | 75 |
| 2000 | ドイツ | 75 | 150 |
| 2001 | ドイツ | 79 | 229 |
| 2000 | フィンランド | 1500 | 1500 |
| 2001 | フィンランド | 10 | 1510 |

次のクエリでは、利益の合計のみを計算できます。

選択国、合計 (利益) 以上 (国別パーティション) からtestwindow;
+ --------- + ----------------------------------------- +
| country | sum(profit) OVER (PARTITION BY country) |
+ --------- + ----------------------------------------- +
| ドイツ | 229 |
| ドイツ | 229 |
| ドイツ | 229 |
| アメリカ | 1550 |
| アメリカ | 1550 |
| フィンランド | 1510 |
| フィンランド | 1510 |

使用上の注意

境界値が次の要件を満たしていることを確認します。

startはUNBOUNDED FOLLOWINGできません。それ以外の場合、Window frame start cannot be UNBOUNDED FOLLOWINGエラーが返されます。
endをUNBOUNDED PRECEDINGにすることはできません。それ以外の場合は、Window frame end cannot be UNBOUNDED PRECEDINGエラーが返されます。
startがCURRENT ROW、endがN PRECEDINGの場合、Window frame starting from CURRENT ROW cannot end with PRECEDINGエラーが返されます。
startがN FOLLOWING、endがN PRECEDINGの場合、Window frame starting from FOLLOWING cannot end with PRECEDINGエラーが返されます。
startがN FOLLOWINGで、endがCURRENT ROWの場合、Window frame starting from FOLLOWING cannot end with CURRENT ROWエラーが返されます。

ウィンドウフレームがRANGEモードの場合、次のルールが適用されます。

startまたはendがN PRECEDINGの場合、Window frame RANGE PRECEDING is supported only with UNBOUNDEDエラーが返されます。
startまたはendがN FOLLOWINGの場合、Window frame RANGE FOLLOWING is supported only with UNBOUNDEDエラーが返されます。

準備

このトピックでは、testwindowテーブルのデータをウィンドウ関数の例で使用します。

作成テーブルtestwindow(year int、country varchar(20) 、product varchar(20) 、profit int) 配布された

ハッシュ (year); testwindowに値を挿入する (2000、'Finland' 、'Computer' 、1500);
テストウィンドウの値 (2001、「フィンランド」、「電話」、10) に挿入します。テストウィンドウの値に挿入します (2000、「ドイツ」、「計算機」、75) 。テストウィンドウの値に挿入します (2000、「ドイツ」、「計算機」、75) 。テストウィンドウの値に挿入します (2001、「ドイツ」、「計算機」、79) 。テストウィンドウの値に挿入します (2001、'USA' 、'Calculator' 、50) 。testwindowの値 (2001、'USA' 、'Computer' 、1500) に挿入します。

SELECT * FROM testwindow;
------ -------- --------------------------------
| 年 | 国 | 製品 | 利益 |
------ -------- --------------------------------
| 2000 | フィンランド | コンピュータ | 1500 |
| 2001 | フィンランド | 電話 | 10 |
| 2000 | ドイツ | 電卓 | 75 |
| 2000 | ドイツ | 電卓 | 75 |
| 2001 | ドイツ | 電卓 | 79 |
| 2001 | アメリカ | 電卓 | 50 |
| 2001 | アメリカ | コンピュータ | 1500 |

集計関数

OVER句を追加することで、すべての集計関数をウィンドウ関数として使用できます。集計関数は、現在のスライディングウィンドウ内の行に基づいてデータの各行を計算します。詳細については、「集計関数」をご参照ください。

たとえば、次のクエリでは、各クラークの日付ごとの注文価格のローリング合計が生成されます。

SELECT clerk, orderdate, orderkey, totalprice,sum(totalprice) OVER (PARTITION BY clerk ORDER BY orderdate) AS rolling_sum FROM ORDER ORDER BY clerk, orderdate, orderkey

CUME_DIST

CUME_DIST()

説明: この関数は、値のセット内の各値の累積分布を返します。
Return result: 現在の行と現在の行の前のデータ行の数を含む、ウィンドウパーティション内でソートした後に取得されたデータセット。ソートにおける任意の関連する値は、同じ分布値に対して計算される。
戻り値のデータ型: DOUBLE。

例：

年、国、製品、利益、cume_dist() をtestwindowからcume_distとして選択します。------ -------- --------------------------------------- -------------------- +
| 年 | 国 | product | profit | cume_dist |
------ -------- --------------------------------------- -------------------- +
| 2001 | アメリカ | 電卓 | 50 | 0.5 |
| 2001 | アメリカ | コンピュータ | 1500 | 1.0 |
| 2001 | フィンランド | 電話 | 10 | 0.5 |
| 2000 | フィンランド | コンピュータ | 1500 | 1.0 |
| 2000 | ドイツ | 電卓 | 75 | 0.6666666666666666 |
| 2000 | ドイツ | 電卓 | 75 | 0.6666666666666666 |
| 2001 | ドイツ | 電卓 | 79 | 1.0 |

RANK

ランク ()

説明: この関数は、データセット内の各値のランクを返します。
rank値は、現在の行に先行する行の数に1を加えたものであるが、現在の行の数は含まない。したがって、ソーティングにおける関連する値は、シーケンス内にギャップを生成し得る。ランクは、各ウィンドウ区画に対して計算される。
戻り値のデータ型: BIGINT。

例：

はtestwindowからのランクとして年、国、プロダクト、利益、ランク () 上の (利益による国の順序による仕切り) を選びます;
------ -------- --------------------------------------------------
| 年 | 国 | 製品 | 利益 | ランク |
------ -------- --------------------------------------------------
| 2001 | フィンランド | 電話 | 10 | 1 |
| 2000 | フィンランド | コンピュータ | 1500 | 2 |
| 2001 | アメリカ | 電卓 | 50 | 1 |
| 2001 | アメリカ | コンピュータ | 1500 | 2 |
| 2000 | ドイツ | 電卓 | 75 | 1 |
| 2000 | ドイツ | 電卓 | 75 | 1 |
| 2001 | ドイツ | 電卓 | 79 | 3 |

DENSE_RANK

DENSE_RANK()

説明: この関数は、値のセット内の各値のランクを返します。
DENSE_RANK() にはRANK() と同様の特徴がありますが、DENSE_RANK() の関連する値はシーケンスにギャップを生成しません。
戻り値のデータ型: BIGINT。

例：

年、国、製品、利益、dense_rank() をtestwindowからdense_rankとして選択します (利益による国の注文によるパーティション) 。------ -------- -----------------------------------------
| 年 | 国 | 製品 | 利益 | dense_rank |
------ -------- -----------------------------------------
| 2001 | フィンランド | 電話 | 10 | 1 |
| 2000 | フィンランド | コンピュータ | 1500 | 2 |
| 2001 | アメリカ | 電卓 | 50 | 1 |
| 2001 | アメリカ | コンピュータ | 1500 | 2 |
| 2000 | ドイツ | 電卓 | 75 | 1 |
| 2000 | ドイツ | 電卓 | 75 | 1 |
| 2001 | ドイツ | 電卓 | 79 | 2 |

NTILE

NTILE(n)

説明: この関数は、各ウィンドウパーティション内のデータをn個のバケットに分割します。バケツは1からnまで番号が付けられています。
バケット番号の最大間隔は1です。ウィンドウパーティション内のデータ行が各バケットに均等に分散されていない場合、残りのデータは最初のバケットから分散され、各バケットに1つのデータ行が含まれます。例えば、4つのバケットに対して6つの行が存在する場合、行は、1、1、2、2、3、および4の方法でバケットに分配される。
戻り値のデータ型: BIGINT。

例：

はtestwindowからのntile2として年、国、プロダクト、利益、ntile(2) 上の (利益による国順序による仕切り) を選びます;
------ -------- ----------------------------------------------
| 年 | 国 | 製品 | 利益 | ntile2 |
------ -------- ----------------------------------------------
| 2001 | アメリカ | 電卓 | 50 | 1 |
| 2001 | アメリカ | コンピュータ | 1500 | 2 |
| 2001 | フィンランド | 電話 | 10 | 1 |
| 2000 | フィンランド | コンピュータ | 1500 | 2 |
| 2000 | ドイツ | 電卓 | 75 | 1 |
| 2000 | ドイツ | 電卓 | 75 | 1 |
| 2001 | ドイツ | 電卓 | 79 | 2 |

ROW_NUMBER

ROW_NUMBER()

説明: この関数は、1から始まるウィンドウパーティション内の行のシーケンスに基づいて、各行に一意のシーケンシャル番号を返します。
戻り値のデータ型: BIGINT。

例：

SELECT年、国、製品、利益、ROW_NUMBER() オーバー (国別のパーティー) AS row_num1 FROM testwindow;
------ -------- ------------------------------------------
| 年 | 国 | 製品 | 利益 | row_num1 |
------ -------- ------------------------------------------
| 2001 | アメリカ | 電卓 | 50 | 1 |
| 2001 | アメリカ | コンピュータ | 1500 | 2 |
| 2000 | ドイツ | 電卓 | 75 | 1 |
| 2000 | ドイツ | 电卓 | 75 | 2 |
| 2001 | ドイツ | 電卓 | 79 | 3 |
| 2000 | フィンランド | コンピュータ | 1500 | 1 |
| 2001 | フィンランド | 電話 | 10 | 2 |

PERCENT_RANK

PERCENT_RANK()

説明: この関数は、データセット内の各値のランキング率を (r - 1)/(n - 1) 形式で返します。 rは、rank () によって計算された現在の行のランクであり、nは、現在のウィンドウパーティション内の行の総数である。
戻り値のデータ型: DOUBLE。

例：

はtestwindowからのntile3として年、国、プロダクト、利益、PERCENT_RANK() 上の (利益による国順序による仕切り) を選びます;
------ -------- ----------------------------------------------
| 年 | 国 | 製品 | 利益 | ntile3 |
------ -------- ----------------------------------------------
| 2001 | フィンランド | 電話 | 10 | 0.0 |
| 2000 | フィンランド | コンピュータ | 1500 | 1.0 |
| 2001 | アメリカ | 電卓 | 50 | 0.0 |
| 2001 | アメリカ | コンピュータ | 1500 | 1.0 |
| 2000 | ドイツ | 電卓 | 75 | 0.0 |
| 2000 | ドイツ | 電卓 | 75 | 0.0 |
| 2001 | ドイツ | 電卓 | 79 | 1.0 |

FIRST_VALUE

FIRST_VALUE(x)

説明: この関数は、ウィンドウパーティション内の最初の行の値を返します。
戻り値のデータ型: 入力引数型と同じ。

例：

年、国、製品、利益、first_value (利益) をtestwindowからfirstValueとして選択します。------ -------- -----------------------------------------
| 年 | 国 | 製品 | 利益 | firstValue |
------ -------- -----------------------------------------
| 2000 | ドイツ | 電卓 | 75 |
| 2000 | ドイツ | 電卓 | 75 |
| 2001 | ドイツ | 電卓 | 79 | 75 |
| 2001 | アメリカ | 電卓 | 50 |
| 2001 | アメリカ | コンピュータ | 1500 | 50 |
| 2001 | フィンランド | 電話 | 10 |
| 2000 | フィンランド | コンピュータ | 1500 | 10 |

LAST_VALUE

LAST_VALUE(x)

説明: この関数は、ウィンドウパーティション内の最後の行の値を返します。 LAST_VALUEのデフォルトのウィンドウフレームは、先行行と現在行の間の行で、現在行のデータと先行行のデータを比較します。 LAST_VALUEが最後の行の値を返す場合は、ORDER BY句の後に次のステートメントを追加します。
戻り値のデータ型: 入力引数型と同じ。

例 1：

年、国、製品、利益、last_value (利益) をtestwindowからfirstValueとして選択します。+ ---------------- + ------------------- + ------------------- + ------------------ +
| 年 | 国 | 製品 | 利益 | firstValue |
+ ---------------- + ------------------- + ------------------- + ------------------ +
| 2001 | アメリカ | 電卓 | 50 |
| 2001 | アメリカ | コンピュータ | 1500 | 1500 |
| 2001 | フィンランド | 電話 | 10 |
| 2000 | フィンランド | コンピュータ | 1500 | 1500 |
| 2000 | ドイツ | 電卓 | 75 |
| 2000 | ドイツ | 電卓 | 75 |
| 2001 | ドイツ | 电卓 | 79 | 79 |

例 2：

、testwindowからlastValueとして、年、国、製品、利益、last_value (利益) (無制限の先行と無制限の後続の間の収益による国別注文によるパーティション) を選択します。------ -------- -----------------------------------------
| 年 | 国 | 製品 | 利益 | lastValue |
------ -------- -----------------------------------------
| 2001 | フィンランド | 電話 | 10 | 1500 |
| 2000 | フィンランド | コンピュータ | 1500 | 1500 |
| 2000 | ドイツ | 電卓 | 75 | 79 |
| 2000 | ドイツ | 電卓 | 75 | 79 |
| 2001 | ドイツ | 電卓 | 79 |
| 2001 | アメリカ | 電卓 | 50 | 1500 |
| 2001 | アメリカ | コンピュータ | 1500 | 1500 |
------ -------- ------------ ------------------------------

LAG

LAG(x[, offset[, default_value]])

説明: この関数は、ウィンドウ内の現在の行の前にある行の値をoffsetだけ返します。
開始オフセット値は0で、現在のデータ行を示します。オフセット値は、スカラー式とすることができる。デフォルトのオフセット値は1です。
オフセット値がnullまたはウィンドウの長さより大きい場合、default_valueが返されます。 default_valueを指定しない場合は、nullが返されます。
戻り値のデータ型: 入力引数型と同じ。

例：

年、国、製品、利益、ラグ (利益) をテストウィンドウからラグとして選択します。------ -------- --------------------------------------------------
| 年 | 国 | 製品 | 利益 | ラグ |
------ -------- --------------------------------------------------
| 2001 | アメリカ | 電卓 | 50 | NULL |
| 2001 | アメリカ | コンピュータ | 1500 | 50 |
| 2000 | ドイツ | 電卓 | 75 | NULL |
| 2000 | ドイツ | 電卓 | 75 |
| 2001 | ドイツ | 電卓 | 79 | 75 |
| 2001 | フィンランド | 電話 | 10 | NULL |
| 2000 | フィンランド | コンピュータ | 1500 | 10 |

LEAD

LEAD(x[,offset[, default_value]])

説明: この関数は、ウィンドウ内の現在の行に続く行の値をオフセットして返します。
開始オフセット値は0で、現在のデータ行を示します。オフセット値は、スカラー式とすることができる。デフォルトのオフセット値は1です。
オフセット値がnullまたはウィンドウの長さより大きい場合、default_valueが返されます。 default_valueを指定しない場合は、nullが返されます。
戻り値のデータ型: 入力引数型と同じ。

例：

は、テストウィンドウからのリードとして年、国、製品、利益、リード (利益) 以上 (利益による国の注文によるパーティション) を選択します。------ -------- --------------------------------------------------
| 年 | 国 | 製品 | 利益 | リード |
------ -------- --------------------------------------------------
| 2000 | ドイツ | 電卓 | 75 |
| 2000 | ドイツ | 電卓 | 75 | 79 |
| 2001 | ドイツ | 電卓 | 79 | NULL |
| 2001 | フィンランド | 電話 | 10 | 1500 |
| 2000 | フィンランド | コンピュータ | 1500 | NULL |
| 2001 | アメリカ | 電卓 | 50 | 1500 |
| 2001 | アメリカ | コンピュータ | 1500 | NULL |

NTH_VALUE

NTH_VALUE(x、オフセット)

説明: この関数は、ウィンドウ内の指定されたオフセット行数だけ行の値を返します。オフセットは1から始まります。
offset値がnullまたはウィンドウ内の値の数より大きい場合、nullが返されます。 オフセット値が0または負の場合、エラーが表示されます。
戻り値のデータ型: 入力引数型と同じ。

例：

、年、国、製品、利益、nth_value (利益、1) をテストウィンドウからnth_valueとして選択します。------ -------- -----------------------------------------
| 年 | 国 | 製品 | 利益 | nth_value |
------ -------- -----------------------------------------
| 2001 | フィンランド | 電話 | 10 |
| 2000 | フィンランド | コンピュータ | 1500 | 10 |
| 2001 | アメリカ | 電卓 | 50 |
| 2001 | アメリカ | コンピュータ | 1500 | 50 |
| 2000 | ドイツ | 電卓 | 75 |
| 2000 | ドイツ | 電卓 | 75 |
| 2001 | ドイツ | 電卓 | 79 | 75 |