MaxCompute ウィンドウ関数 - 構文、パラメーター、例 - MaxCompute

ウィンドウ関数は、動的に定義されたデータのサブセットに対して集約やその他の計算を実行します。主に時系列処理、ランキング、移動平均などに使用されます。

注意事項

ウィンドウ関数は SELECT 文にのみ記述できます。
ウィンドウ関数を他のウィンドウ関数や集計関数とネストすることはできません。
ウィンドウ関数は、同じレベルで集計関数と一緒に使用することはできません。

インデックス

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

関数	機能
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	ランクをパーセンテージとして計算します。
PERCENTILE_CONT	正確なパーセンタイルを計算します。
PERCENTILE_DISC	指定された列を昇順にソートして、特定のパーセンタイル値を計算します。
RANK	ランクを計算します。ランクは連続しない場合があります。
ROW_NUMBER	1 から始まる行番号を計算します。
STDDEV	母集団標準偏差を計算します。これは STDDEV_POP のエイリアスです。
STDDEV_SAMP	標本標準偏差を計算します。
SUM	ウィンドウ内のデータの合計を計算します。

ウィンドウ関数の構文

ウィンドウ関数の構文：

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

function_name：ビルトインウィンドウ関数、集計関数、またはユーザー定義集計関数 (UDAF)。
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」をご参照ください。

filter_clause

filter_clause の構文：

FILTER (WHERE filter_condition)

filter_condition はブール式であり、select ... from ... where 文の WHERE 句と同じように使用されます。

FILTER 句を指定した場合、filter_condition が true と評価される行のみがウィンドウフレームに含まれます。集計ウィンドウ関数 (COUNT、SUM、AVG、MAX、MIN など) の場合、すべての行に対して値が返されます。ただし、FILTER 式が true と評価されない行 (NULL や false など) は、各行の計算のためのウィンドウフレームには含まれません。NULL は false として扱われます。

例

データの準備

-- テーブルを作成します。
CREATE TABLE IF NOT EXISTS mf_window_fun(key BIGINT,value BIGINT) STORED AS ALIORC;

-- データを挿入します。
insert into mf_window_fun values (1,100),(2,200),(1,150),(2,250),(3,300),(4,400),(5,500),(6,600),(7,700);

-- mf_window_fun テーブルからデータをクエリします。
select * from mf_window_fun;

-- 次の結果が返されます：
+------------+------------+
| key        | value      |
+------------+------------+
| 1          | 100        |
| 2          | 200        |
| 1          | 150        |
| 2          | 250        |
| 3          | 300        |
| 4          | 400        |
| 5          | 500        |
| 6          | 600        |
| 7          | 700        |
+------------+------------+

ウィンドウ内で値が 100 より大きい行の累積合計をクエリします。

select key,sum(value) filter(where value > 100) 
       over (partition by key order by key)  
       from mf_window_fun;

次の結果が返されます：

+------------+------------+
| key        | _c1        |
+------------+------------+
| 1          | NULL       | -- スキップ
| 1          | 150        |
| 2          | 200        |
| 2          | 450        |
| 3          | 300        |
| 4          | 400        |
| 5          | 500        |
| 6          | 600        |
| 7          | 700        |
+------------+------------+

説明

FILTER 句は、filter_condition を満たさない行をクエリ結果から削除しません。ウィンドウ関数の計算から除外するだけです。これらの行を最終的な出力から削除するには、select ... from ... where 句を使用する必要があります。除外された行のウィンドウ関数の値は 0 や NULL ではありません。代わりに、前の行から値を継承します。
FILTER 句は、COUNT、SUM、AVG、MAX、MIN、WM_CONCAT などの集計ウィンドウ関数でのみ使用できます。RANK、ROW_NUMBER、NTILE などの非集計関数で FILTER 句を使用することはできません。使用した場合、構文エラーが発生します。
ウィンドウ関数で FILTER 構文を使用するには、次のセッションフラグを有効にする必要があります：set odps.sql.window.function.newimpl=true;。

frame_clause

frame_clause の構文：

-- フォーマット 1
{ROWS|RANGE|GROUPS} <frame_start> [<frame_exclusion>]
-- フォーマット 2
{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 値は等しいと見なされます。
- GROUPS：パーティション内で同じ order by 列の値を持つすべての行が GROUP を形成します。order by 句が指定されていない場合、パーティション内のすべての行が単一の GROUP を形成します。NULL 値は等しいと見なされます。

frame_start と frame_end：ウィンドウの開始境界と終了境界を指定します。frame_start は必須です。frame_end はオプションです。省略した場合、デフォルト値は CURRENT ROW です。

frame_start で指定された位置は、frame_end で指定された位置より前にあるか、frame_end の位置と一致する必要があります。つまり、frame_start は frame_end よりもパーティションの先頭に近くなります。パーティションの先頭は、ウィンドウ定義の order by 文でデータがソートされた後の最初の行の位置です。次の表は、frame_clause のタイプが ROWS、RANGE、または GROUPS の場合の frame_start と frame_end の有効な値とロジックを説明しています。

frame_clause のタイプ	frame_start/frame_end の値	説明
ROWS, RANGE, GROUPS	UNBOUNDED PRECEDING	パーティションの最初の行。カウントは 1 から始まります。
ROWS, RANGE, GROUPS	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 の最初の行の位置を指します。 frame_end としては、現在の行の GROUP からパーティションの先頭に向かって `offset` 個前の GROUP の最後の行の位置を指します。説明 frame_start を UNBOUNDED FOLLOWING に、または frame_end を UNBOUNDED PRECEDING に設定することはできません。
	offset FOLLOWING	frame_start としては、現在の行の GROUP からパーティションの末尾に向かって `offset` 個後の GROUP の最初の行の位置を指します。 frame_end としては、現在の行の GROUP からパーティションの末尾に向かって `offset` 個後の GROUP の最後の行の位置を指します。説明 frame_start を UNBOUNDED FOLLOWING に、または frame_end を UNBOUNDED PRECEDING に設定することはできません。

frame_exclusion：オプション。ウィンドウからデータの一部を除外するために使用されます。有効な値は次のとおりです：
- EXCLUDE NO OTHERS：どのデータも除外しません。
- EXCLUDE CURRENT ROW：現在の行を除外します。
- EXCLUDE GROUP：GROUP 全体を除外します。つまり、現在の行と同じ order by 値を持つパーティション内のすべてのデータです。
- EXCLUDE TIES：現在の行自体を除き、現在の行と同じ order by 値を共有するすべての行を除外します。

デフォルトの frame_clause

frame_clause を指定しない場合、MaxCompute はデフォルトの frame_clause を使用して、ウィンドウに含まれるデータの境界を決定します。デフォルトの frame_clause は次のとおりです：

Hive 互換モードが有効な場合 (set odps.sql.hive.compatible=true;)、デフォルトの frame_clause は次のようになり、他のほとんどの SQL システムと同じです。
```
RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
```
Hive 互換モードが無効な場合 (set odps.sql.hive.compatible=false;)、order by 句が指定され、ウィンドウ関数が AVG、COUNT、MAX、MIN、STDDEV、STDDEV_POP、STDDEV_SAMP、または SUM の場合、デフォルトの frame_clause は ROWS タイプになります。
```
ROWS 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         |
+------------+------------+------------+

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 として使用される場合、パーティション内の NULL の order by 値を持つ最初の行を指します。frame_end として使用される場合、NULL の order by 値を持つ最後の行を指します。

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) でソートして平均給与を計算します。この関数は、パーティションの最初の行から現在の行までの実行平均を計算します。コマンドは次のとおりです：

-- Hive 互換モードを無効にします。
set odps.sql.hive.compatible=false;
-- 次の SQL コマンドを実行します。
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

次の結果が返されます：

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300.0     |           -- ウィンドウの最初の行。
| 10         | 1300       | 1300.0     |           -- 1 行目から 2 行目までの累積平均。
| 10         | 2450       | 1683.3333333333333 |   -- 1 行目から 3 行目までの累積平均。
| 10         | 2450       | 1875.0     |           -- 1 行目から 4 行目までの累積平均。
| 10         | 5000       | 2500.0     |           -- 1 行目から 5 行目までの累積平均。
| 10         | 5000       | 2916.6666666666665 |   -- 1 行目から 6 行目までの累積平均。
| 20         | 800        | 800.0      |
| 20         | 1100       | 950.0      |
| 20         | 2975       | 1625.0     |
| 20         | 3000       | 1968.75    |
| 20         | 3000       | 2175.0     |
| 30         | 950        | 950.0      |
| 30         | 1250       | 1100.0     |
| 30         | 1250       | 1150.0     |
| 30         | 1500       | 1237.5     |
| 30         | 1600       | 1310.0     |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

例 3：Hive 互換モードで、部署 (deptno) でパーティション分割し、給与 (sal) でソートして平均給与を計算します。この関数は、パーティションの最初の行から現在の行の最後のピアまでの実行平均を計算します (同じ sal を持つ行は同じ平均値になります)。コマンドは次のとおりです：

-- Hive 互換モードを有効にします。
set odps.sql.hive.compatible=true;
-- 次の 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 行目の平均は最初の 2 行の累積平均です。
| 10         | 1300       | 1300.0     |          -- 1 行目から 2 行目までの累積平均。
| 10         | 2450       | 1875.0     |          -- 3 行目と 4 行目の sal が同じため、3 行目の平均は最初の 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([DISTINCT|ALL] <colname>)

-- ウィンドウ内のレコード数をカウントします。
BIGINT COUNT(*) OVER ([partition_clause] [orderby_clause] [frame_clause])
BIGINT COUNT([DISTINCT] <expr>[,...]) OVER ([partition_clause] [orderby_clause] [frame_clause])

パラメーター

パラメーター	必須	説明
`DISTINCT \| ALL`	いいえ	重複の処理を制御します。`ALL` (デフォルト) は、NULL でないすべての行をカウントします。`DISTINCT` は、一意の非 NULL 値のみをカウントします。
`colname`	はい	カウントする列。任意のデータ型を受け入れます。`*` を使用して、列の値が NULL の行を含むすべての行をカウントします。
`expr`	はい	任意のデータ型の式。NULL の行は除外されます。`DISTINCT` を使用すると、一意の非 NULL 値のみがカウントされます。`COUNT([DISTINCT] <expr>[,...]) OVER` は、指定されたすべての式が非 NULL である行をカウントします。
`partition_clause`, `orderby_clause`, `frame_clause`	いいえ	ウィンドウ定義句。詳細については、「ウィンドウ関数の概要」をご参照ください。

戻り値

BIGINT を返します。COUNT(*) を使用しない限り、NULL の行は除外されます。

例

テストデータの準備

すでにデータがある場合は、このステップをスキップできます。

テストデータ test_data.txt をダウンロードします。

テストテーブルを作成します。

CREATE TABLE IF NOT EXISTS emp(
  empno BIGINT,
  ename STRING,
  job STRING,
  mgr BIGINT,
  hiredate DATETIME,
  sal BIGINT,
  comm BIGINT,
  deptno BIGINT
);

データをロードします。

FILE_PATH を実際のデータファイルのパスと名前に置き換えてください。
```
TUNNEL UPLOAD {{FILEPATH}} emp;
```

例 1：ソートなしでウィンドウをパーティション分割

sal でウィンドウをパーティション分割します。ORDER BY がないと、各行はそのパーティション内の行の総数を返します。

SELECT sal, COUNT(sal) OVER (PARTITION BY sal) AS count
FROM emp;

結果：

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- 2 つの行が sal=1250 を共有し、両方とも 2 を返します。
| 1250       | 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：ソートありでウィンドウをパーティション分割 (非 Hive 互換モード)

非 Hive 互換モードでは、ORDER BY を追加すると実行カウントが生成されます。各行は、そのパーティションの最初の行から現在の行までの累積カウントを返します。

-- Hive 互換モードを無効にします。
SET odps.sql.hive.compatible=false;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

結果：

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 1          |  -- 実行カウントはパーティションの最初の行で 1 から始まります。
| 1250       | 2          |  -- 2 番目の行で 2 に増分します。
| 1300       | 1          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 1          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 1          |
| 3000       | 2          |
| 5000       | 1          |
| 5000       | 2          |
+------------+------------+

例 3：ソートありでウィンドウをパーティション分割 (Hive 互換モード)

Hive 互換モードでは、ORDER BY は実行カウントを生成しません。パーティション内のすべての行は、ORDER BY を省略した場合と同じ、パーティションの総カウントを返します。

-- Hive 互換モードを有効にします。
SET odps.sql.hive.compatible=true;

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          |  -- パーティション内の両方の行が、パーティションの総カウントを返します。
| 1250       | 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          |
+------------+------------+

例 4：テーブル内のすべての行をカウント

SELECT COUNT(*) FROM emp;

結果：

+------------+
| _c0        |
+------------+
| 17         |
+------------+

例 5：グループごとの行数をカウント

COUNT を GROUP BY と一緒に使用して、部署ごとの従業員数を取得します。

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

結果：

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 20         | 5          |
| 30         | 6          |
| 10         | 6          |
+------------+------------+

例 6：一意の値をカウント

DISTINCT を使用して、部署のユニークな数をカウントします。

SELECT COUNT(DISTINCT deptno) FROM emp;

結果：

+------------+
| _c0        |
+------------+
| 3          |
+------------+

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：オプション。NULL 値を無視するかどうかを指定する BOOLEAN 値。デフォルト値は False です。このパラメーターが True に設定されている場合、関数はウィンドウフレーム内の expr の最初の非 NULL 値を返します。
- 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：オプション。オフセット。1 以上の BIGINT 定数です。値 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：オプション。NULL 値を無視するかどうかを指定する BOOLEAN 値。デフォルト値は False です。このパラメーターが True に設定されている場合、関数はウィンドウフレーム内の expr の最後の非 NULL 値を返します。
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 句がある場合、デフォルトのウィンドウフレームは `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` です。この関数は現在の行の値を返します。

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：オプション。オフセット。0 以上の BIGINT 定数です。値 0 は現在の行を示し、値 1 は次の行を示します。デフォルト値は 1 です。入力値が STRING または DOUBLE 型の場合、計算のために暗黙的に BIGINT 型に変換されます。
- default：オプション。offset が範囲外の場合に返す値。この値は expr と同じデータ型の定数でなければなりません。デフォルトは NULL です。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 型である必要があります。
  - 入力値が 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])

説明

ウィンドウフレームの N 番目の行から式 expr の値を返します。
パラメーター
- expr：必須。計算する式。
- number：必須。BIGINT 値。1 以上の整数。値が 1 の場合、この関数は FIRST_VALUE と同等です。
- ignore_nulls：オプション。NULL 値を無視するかどうかを指定する BOOLEAN 値。デフォルト値は False です。このパラメーターが True に設定されている場合、関数はウィンドウフレーム内の expr の N 番目の非 NULL 値を返します。
- 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 句がある場合、デフォルトのウィンドウフレームは `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` です。この関数は、ウィンドウフレームの 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」をご参照ください。
戻り値

[0.0, 1.0] の範囲の DOUBLE 値を返します。具体的な戻り値は “(rank - 1) / (partition_row_count - 1)” に等しくなります。ここで、rank はその行の RANK ウィンドウ関数の結果であり、partition_row_count は行が属するパーティションの行数です。パーティションに 1 行しか含まれていない場合、出力は 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        |
+------------+------------+------------+------------+

PERCENTILE_CONT

構文

-- 正確なパーセンタイルを計算
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

-- ウィンドウ内の正確なパーセンタイルを計算
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

説明

正確なパーセンタイルを計算します。線形補間アルゴリズムを使用し、指定された列を昇順にソートし、指定された percentile での正確な値を返します。
パラメーター
- col_name：必須。DOUBLE または DECIMAL 型の列。
- percentile：必須。計算するパーセンタイル。[0, 1] の範囲の DOUBLE 定数。
- isIgnoreNull：オプション。NULL 値を無視するかどうかを指定します。BOOLEAN 定数。デフォルト値は TRUE です。FALSE に設定した場合、NULL 値はソート中に最小値として扱われます。
- partition_clause と orderby_clause：詳細については、「windowing_definition」をご参照ください。
戻り値

計算されたパーセンタイル値を DOUBLE として返します。

例

例 1：NULL 値を無視し、ウィンドウ内の正確なパーセンタイルを計算します。

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- 戻り結果
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| 0.0        | 0.03        | 1.5        | 2.7          | 3.0        | 
+------------+-------------+------------+--------------+------------+

例 2：NULL 値を無視しません。NULL 値はソート中に最小値として扱われます。ウィンドウ内の正確なパーセンタイルを計算します。

SELECT
  PERCENTILE_CONT(x, 0, false) OVER() AS min,
  PERCENTILE_CONT(x, 0.01, false) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5, false) OVER() AS median,
  PERCENTILE_CONT(x, 0.9, false) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1, false) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- 戻り結果
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| NULL       | 0.0         | 1.0        | 2.6          | 3.0        | 
+------------+-------------+------------+--------------+------------+

PERCENTILE_DISC

構文

-- 特定のパーセンタイル値を計算
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

-- ウィンドウ内のパーセンタイル値を計算
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

説明

特定のパーセンタイル値を計算します。まず指定された列を昇順にソートし、次に累積分布が指定されたパーセンタイル以上になる最初の値を返します。
パラメーター
- col_name：必須。ソート可能な任意のデータ型の列。
- percentile：必須。計算するパーセンタイル。[0, 1] の範囲の DOUBLE 定数。
- isIgnoreNull：オプション。NULL 値を無視するかどうかを指定します。BOOLEAN 定数。デフォルト値は TRUE です。FALSE に設定した場合、NULL 値はソート中に最小値として扱われます。
- partition_clause と orderby_clause：詳細については、「windowing_definition」をご参照ください。
戻り値

計算されたパーセンタイル値を返します。データ型は入力の col_name 列と同じです。

例

例 1：NULL 値を無視し、ウィンドウ内のパーセンタイル値を計算します。

SELECT
  x,
  PERCENTILE_DISC(x, 0) OVER() AS min,
  PERCENTILE_DISC(x, 0.5) OVER() AS median,
  PERCENTILE_DISC(x, 1) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);

-- 戻り結果
+------------+------------+------------+------------+
| x          | min        | median     | max        | 
+------------+------------+------------+------------+
| c          | a          | b          | c          | 
| NULL       | a          | b          | c          | 
| b          | a          | b          | c          | 
| a          | a          | b          | c          | 
+------------+------------+------------+------------+

例 2：NULL 値を無視しません。NULL 値はソート中に最小値として扱われます。ウィンドウ内のパーセンタイル値を計算します。

SELECT
  x,
  PERCENTILE_DISC(x, 0, false) OVER() AS min,
  PERCENTILE_DISC(x, 0.5, false) OVER() AS median,
  PERCENTILE_DISC(x, 1, false) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);

-- 戻り結果
+------------+------------+------------+------------+
| x          | min        | median     | max        | 
+------------+------------+------------+------------+
| c          | NULL       | a          | c          | 
| NULL       | NULL       | a          | c          | 
| b          | NULL       | a          | c          | 
| a          | NULL       | a          | c          | 
+------------+------------+------------+------------+

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：非 Hive 互換モードで、部署 (deptno) でパーティション分割し、給与 (sal) の母集団標準偏差を計算し、結果をソートします。この関数は、現在のパーティション (同じ deptno を持つ行) の最初の行から現在の行までの累積母集団標準偏差を返します。コマンドは次のとおりです：

-- Hive 互換モードを無効にします。
set odps.sql.hive.compatible=false;
-- 次の SQL コマンドを実行します。
select deptno, sal, stddev(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       | 542.1151989096865 |    -- 1 行目から 3 行目までの累積母集団標準偏差。
| 10         | 2450       | 575.0      |           -- 1 行目から 4 行目までの累積母集団標準偏差。
| 10         | 5000       | 1351.6656391282572 |
| 10         | 5000       | 1546.1421524412158 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 150.0      |
| 20         | 2975       | 962.4188277460079 |
| 20         | 3000       | 1024.2947268730811 |
| 20         | 3000       | 1004.7387720198718 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 150.0      |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1500       | 194.8557158514987 |
| 30         | 1600       | 226.71568097509268 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

例 3：Hive 互換モードで、部署 (deptno) でパーティション分割し、給与 (sal) の母集団標準偏差を計算し、結果をソートします。この関数は、最初の行から現在の行と同じ値を持つ行までの累積母集団標準偏差を返します (同じ sal を持つ行は同じ母集団標準偏差を持ちます)。コマンドは次のとおりです：

-- Hive 互換モードを有効にします。
set odps.sql.hive.compatible=true;
-- 次の 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 行目の母集団標準偏差は最初の 2 行の累積母集団標準偏差です。
| 10         | 1300       | 0.0        |           -- 1 行目から 2 行目までの累積母集団標準偏差。
| 10         | 2450       | 575.0      |           -- 3 行目と 4 行目の sal が同じため、3 行目の母集団標準偏差は最初の 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 が返されます。ウィンドウに expr の非 NULL 値が 1 つしか含まれていない場合、結果は 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：非 Hive 互換モードで、部署 (deptno) でパーティション分割し、給与 (sal) の合計を計算し、結果をソートします。この関数は、現在のパーティション (同じ deptno を持つ行) の最初の行から現在の行までの累積合計を返します。コマンドは次のとおりです：

-- Hive 互換モードを無効にします。
set odps.sql.hive.compatible=false;
-- 次の SQL コマンドを実行します。
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

次の結果が返されます：

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- ウィンドウの最初の行。
| 10         | 1300       | 2600       |   -- 1 行目から 2 行目までの累積合計。
| 10         | 2450       | 5050       |   -- 1 行目から 3 行目までの累積合計。
| 10         | 2450       | 7500       |
| 10         | 5000       | 12500      |
| 10         | 5000       | 17500      |
| 20         | 800        | 800        |
| 20         | 1100       | 1900       |
| 20         | 2975       | 4875       |
| 20         | 3000       | 7875       |
| 20         | 3000       | 10875      |
| 30         | 950        | 950        |
| 30         | 1250       | 2200       |
| 30         | 1250       | 3450       |
| 30         | 1500       | 4950       |
| 30         | 1600       | 6550       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

例 3：Hive 互換モードで、部署 (deptno) でパーティション分割し、給与 (sal) の合計を計算し、結果をソートします。この関数は、最初の行から現在の行と同じ値を持つ行までの累積合計を返します (同じ sal を持つ行は同じ合計を持ちます)。コマンドは次のとおりです：

-- Hive 互換モードを有効にします。
set odps.sql.hive.compatible=true;
-- 次の 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 行目の合計は最初の 2 行の累積合計です。
| 10         | 1300       | 2600       |   -- 1 行目から 2 行目までの累積合計。
| 10         | 2450       | 7500       |   -- 3 行目と 4 行目の sal が同じため、3 行目の合計は最初の 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       |
+------------+------------+------------+

参考資料

ビルトイン関数がニーズを満たさない場合は、ユーザー定義関数を作成できます。詳細については、「MaxCompute UDF の概要」をご参照ください。
一般的な MaxCompute SQL の問題：
ビルトイン関数の一般的なエラーコード：
- ビルトイン関数の一般的なエラーコード
- ビルトイン関数に関するよくある質問