ウィンドウ関数 - Hologres - Alibaba Cloud ドキュメントセンター

Hologres は PostgreSQL と互換性があり、データ開発に標準の PostgreSQL 構文を使用できます。このトピックでは、Hologres でサポートされているウィンドウ関数について説明し、関数の使用方法の例を示します。

説明

PostgreSQL と互換性のあるウィンドウ関数の詳細については、PostgreSQL ドキュメントの「ウィンドウ関数」をご参照ください。

関数	説明
DENSE_RANK	ウィンドウ内の現在の行のランクを返します。この関数は連続したランクを割り当てます。
FIRST_VALUE	ウィンドウの最初の行にあるフィールドの値を返します。
LAG	ウィンドウ内の特定の値の現在の行の前の行にある値を返します。
LAST_VALUE	ウィンドウの最後の行にあるフィールドの値を返します。
LEAD	ウィンドウ内の特定の値の現在の行の次の行にある値を返します。
PERCENT_RANK	ウィンドウ内の現在の行のランクをパーセンテージ形式で返します。パーセンテージ形式のランクは、次の式を使用して計算されます。`(現在の行のランク - 1)/(ウィンドウ内の行の総数 - 1)`。
RANK	ウィンドウ内の現在の行のランクを返します。この関数は、同順位の行の数を同順位のランクに加算して次のランクを計算します。したがって、ランクは連続した数値ではない場合があります。
ROW_NUMBER	ウィンドウ内の現在の行の番号を返します。番号は 1 からカウントされます。

サンプルデータ

このトピックで提供されているウィンドウ関数の例では、test_wf テーブルのデータを使用します。test_wf テーブルを作成し、テーブルにデータを挿入するには、次のサンプル文を実行します。

CREATE TABLE test_wf(
    ID INT,
    c1 TEXT
);

INSERT INTO test_wf ("id","c1") VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d');

関数

ROW_NUMBER

説明：ウィンドウ内の現在の行の番号を返します。番号は 1 からカウントされます。
```
ROW_NUMBER()
```
使用上の注意：この関数は、Hologres V1.1 以降の Hologres Query Engine（HQE）でサポートされています。
戻り値の型：
BIGINT

例：

SELECT c1, ROW_NUMBER() OVER (ORDER BY c1) FROM test_wf;

返される結果：

 c1 | row_number
----+------------
 a  |          1
 b  |          2
 c  |          3
 d  |          4

RANK

説明：ウィンドウ内の現在の行のランクを返します。この関数は、同順位の行の数を同順位のランクに加算して次のランクを計算します。したがって、ランクは連続した数値ではない場合があります。
```
RANK()
```
使用上の注意：この関数は、Hologres V1.1 以降の HQE でサポートされています。
戻り値の型：
BIGINT

例：

SELECT c1, RANK() OVER (ORDER BY c1) FROM test_wf;

返される結果：

 c1 | rank
----+------
 a  |    1
 b  |    2
 c  |    3
 d  |    4

DENSE_RANK

説明：ウィンドウ内の現在の行のランクを返します。この関数は連続したランクを割り当てます。
```
DENSE_RANK()
```
使用上の注意：この関数は、Hologres V1.1 以降の HQE でサポートされています。
戻り値の型：
BIGINT

例：

SELECT c1, DENSE_RANK() OVER (ORDER BY c1) FROM test_wf;

返される結果：

 c1 | dense_rank
----+------------
 a  |          1
 b  |          2
 c  |          3
 d  |          4

PERCENT_RANK

説明：ウィンドウ内の現在の行のランクをパーセンテージ形式で返します。パーセンテージ形式のランクは、次の式を使用して計算されます。(現在の行のランク - 1)/(ウィンドウ内の行の総数 - 1)。
```
PERCENT_RANK()
```
使用上の注意：
この関数は、Postgres Query Engine（PQE）でサポートされています。
戻り値の型：
DOUBLE PRECISION

例：

SELECT c1, PERCENT_RANK() OVER (ORDER BY c1) FROM test_wf;

返される結果：

 c1 |   percent_rank
----+-------------------
 a  |                 0
 b  | 0.333333333333333
 c  | 0.666666666666667
 d  |                 1

LAG

説明：ウィンドウ内の特定の値の現在の行の前の行にある値を返します。
```
LAG(<value> ANYELEMENT [, <offset> INTEGER [, <default> ANYELEMENT]])
```
使用上の注意：
この関数は、Hologres V1.1.71 以降の PQE でサポートされています。
パラメーターの説明：
- value：前の行で値を取得する列または式。このパラメーターは必須です。
- offset：行オフセット。デフォルト値：1。このパラメーターはオプションです。
- default：現在の行がグループの最初の行で、前の行が存在しない場合に返されるデフォルト値。デフォルトでは、このパラメーターの値は空のままです。このパラメーターはオプションです。

例：

SELECT c1, LAG(c1) OVER (ORDER BY c1) FROM test_wf;

返される結果：

 c1 | lag
----+-----
 a  |
 b  | a
 c  | b
 d  | c

LEAD

説明：ウィンドウ内の特定の値の現在の行の次の行にある値を返します。
```
LEAD(<value> ANYELEMENT [, <offset> INTEGER [, <default> ANYELEMENT]])
```
使用上の注意：
Hologres V1.1.71 以降の HQE では、この関数の入力として単一のパラメーターが許可されます。
パラメーターの説明：
- value：次の行で値を取得する列または式。このパラメーターは必須です。
- offset：行オフセット。デフォルト値：1。このパラメーターはオプションです。
- default：現在の行がグループの最後の行で、次の行が存在しない場合に返されるデフォルト値。デフォルトでは、このパラメーターの値は空のままです。このパラメーターはオプションです。

例：

SELECT c1, LEAD(c1) OVER (ORDER BY c1) FROM test_wf;

返される結果：

 c1 | lead
----+------
 a  | b
 b  | c
 c  | d
 d  |

FIRST_VALUE

説明：ウィンドウの最初の行にあるフィールドの値を返します。
```
FIRST_VALUE(<value> ANYELEMENT)
```
使用上の注意：
この関数は、Hologres V1.1.71 以降の HQE でサポートされています。

例：

SELECT c1, FIRST_VALUE(c1) OVER (ORDER BY c1) FROM test_wf;

返される結果：

 c1 | first_value
----+-------------
 a  | a
 b  | a
 c  | a
 d  | a

LAST_VALUE

説明：ウィンドウの最後の行にあるフィールドの値を返します。
```
LAST_VALUE(<value> ANYELEMENT)
```
使用上の注意：
この関数は、Hologres V1.1.71 以降の HQE でサポートされています。

例：

SELECT c1, LAST_VALUE(c1) OVER (ORDER BY c1) FROM test_wf;

返される結果：

 c1 | last_value
----+------------
 a  | a
 b  | b
 c  | c
 d  | d

ウィンドウ関数の特性

ウィンドウ関数を呼び出すときは、ウィンドウ関数の後に関数パラメーターを設定した OVER 句を使用する必要があります。OVER 句は、ウィンドウ関数によってフィールド値が処理される行を指定するために使用されます。

OVER 句で PARTITION BY キーワードを使用する場合、同じパーティションキー値を持つ行は同じウィンドウに属します。ウィンドウ関数は、行が属するウィンドウに基づいて各行で計算を実行します。
OVER 句で ORDER BY キーワードを使用する場合、ウィンドウ関数は ORDER BY キーワードで定義された順序で計算を実行します。

同じウィンドウに属する行のセットは、ウィンドウフレームと呼ばれます。一部のウィンドウ関数は、現在の行が属するウィンドウではなく、ウィンドウフレームでのみ計算を実行します。たとえば、OVER 句で ORDER BY キーワードを使用する場合、ウィンドウフレームには、現在の行が属するウィンドウの最初の行から現在の行までの行が含まれます。OVER 句で ORDER BY キーワードを使用しない場合、ウィンドウフレームには、現在の行と同じウィンドウに属するすべての行が含まれます。

ORDER BY キーワードを使用するかどうかによって、ウィンドウ関数の計算結果が決まります。次の 2 つの例を示します。例では、SUM 関数が使用されています。

例 1：

SELECT id, c1, SUM(id) OVER (ORDER BY id) FROM test_wf;

返される結果：

id | c1 | sum
----+----+-----
  1 | a  |   1
  2 | b  |   3
  3 | c  |   6
  4 | d  |  10

例 2：

SELECT id, c1, SUM(id) OVER () FROM test_wf;

返される結果：

 id | c1 | sum
----+----+-----
  3 | c  |  10
  1 | a  |  10
  2 | b  |  10
  4 | d  |  10

ORDER BY 句を使用すると、SUM 関数は最初の行から現在の行までの値の合計を返します。これには、現在の行と同じ値を持つ行も含まれます。ORDER BY 句を使用しない場合、SUM 関数はテーブル内のすべての行の値の合計を返します。