ウィンドウ関数は、動的に定義されたデータのサブセットに対して集約やその他の計算を実行します。これらは、時系列データの処理、ランキング、移動平均の計算などのタスクによく使用されます。このトピックでは、MaxCompute SQL がサポートするウィンドウ関数のコマンド構文、パラメーター、および例について説明します。
適用範囲
ウィンドウ関数は
SELECT文でのみ使用できます。ウィンドウ関数は、他のウィンドウ関数や集計関数とネストすることはできません。
ウィンドウ関数は、同じレベルで集計関数と一緒に使用することはできません。
インデックス
MaxCompute SQL は、次のウィンドウ関数をサポートしています。
関数 | 特徴 |
ウィンドウ内のデータの平均値を計算します。 | |
ランダムサンプリングを実行します。行がサンプリングされた場合は true を返します。 | |
ウィンドウ内のレコード数をカウントします。 | |
累積分布を計算します。 | |
ランクを計算します。ランクは連続しています。 | |
現在の行のウィンドウフレーム内の最初の行の値を返します。 | |
パーティション内で現在の行より N 行前の値を返します。 | |
現在の行のウィンドウフレーム内の最後の行の値を返します。 | |
パーティション内で現在の行より N 行後の値を返します。 | |
ウィンドウ内の最大値を計算します。 | |
ウィンドウ内の中央値を計算します。 | |
ウィンドウ内の最小値を計算します。 | |
順序付けられたデータを N 個の同じサイズのグループに分割し、各行のグループ番号 (1 から N) を返します。 | |
現在の行のウィンドウフレーム内の N 番目の行の値を返します。 | |
ランクをパーセンテージで計算します。 | |
正確なパーセンタイルを計算します。 | |
指定された列を昇順にソートして、特定のパーセンタイル値を計算します。 | |
ランクを計算します。ランクは連続しない場合があります。 | |
1 から始まる行番号を計算します。 | |
母集団標準偏差を計算します。これは STDDEV_POP のエイリアスです。 | |
サンプル標準偏差を計算します。 | |
ウィンドウ内のデータの合計を計算します。 |
ウィンドウ関数の構文
ウィンドウ関数の構文は次のとおりです。
<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列の値を持つすべての行が 1 つのグループを形成します。order by句が指定されていない場合、パーティション内のすべての行が単一のグループを形成します。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 から始まります。
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 としては、現在の行が属するグループの最初の行を指します。
frame_end としては、現在の行が属するグループの最後の行を指します。
offset PRECEDING
frame_start としては、現在の行のグループからパーティションの先頭に向かって
offsetグループ前のグループの最初の行の位置を指します。frame_end としては、現在の行のグループからパーティションの先頭に向かって
offsetグループ前のグループの最後の行の位置を指します。
説明frame_start を UNBOUNDED FOLLOWING に、または frame_end を UNBOUNDED PRECEDING に設定することはできません。
offset FOLLOWING
frame_start としては、現在の行のグループからパーティションの末尾に向かって
offsetグループ後のグループの最初の行の位置を指します。frame_end としては、現在の行のグループからパーティションの末尾に向かって
offsetグループ後のグループの最後の行の位置を指します。
説明frame_start を UNBOUNDED FOLLOWING に、または frame_end を UNBOUNDED PRECEDING に設定することはできません。
frame_exclusion：(任意) ウィンドウからデータの一部を除外するために使用されます。有効な値は次のとおりです：
EXCLUDE NO OTHERS：データを一切除外しません。
EXCLUDE CURRENT ROW：現在の行を除外します。
EXCLUDE 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 OTHERSHive 互換モードが無効な場合 (
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,,10AVG
構文
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 行目です。値は 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 で、すべてのレコードをカウントします。DISTINCT を指定した場合、関数は一意のレコードのみをカウントします。
colname：(必須) 値をカウントする列。このパラメーターは任意のデータ型にすることができます。
COUNT(*)のように、colname に*を指定できます。この場合、関数は総行数を返します。colname の値が NULL の行は計算に含まれません。expr：(必須) 式。このパラメーターは任意のデータ型にすることができます。式が NULL と評価される行は計算に含まれません。DISTINCT キーワードを指定した場合、関数は一意の値の数を返します。
COUNT([DISTINCT] <expr>[,...])：指定されたウィンドウ内で、指定されたすべての式の値が NULL でない行の数をカウントします。DISTINCTキーワードを指定した場合、関数は重複行を削除した後の行数をカウントします。partition_clause、orderby_clause、および frame_clause：詳細については、「windowing_definition」をご参照ください。
戻り値
この関数は BIGINT 値を返します。colname が 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 FILE_PATH emp;
例 1：ウィンドウに列を指定し、ソートせずに累積カウントを返す
この例では、給与 (sal) 列でウィンドウをパーティション分割します。データはソートされません。この関数は、現在のウィンドウ内のすべての行の累積カウントを返します。ウィンドウには、同じ sal 値を持つすべての行が含まれます。
サンプルコマンド
SELECT sal, COUNT(sal) OVER (PARTITION BY sal) AS count FROM emp;結果
+------------+------------+ | sal | count | +------------+------------+ | 800 | 1 | | 950 | 1 | | 1100 | 1 | | 1250 | 2 | -- パーティションには sal が 1250 の行が 2 つ含まれています。関数は両方の行に対して 2 を返します。 | 1250 | 2 | -- パーティション内の 2 番目の行のカウントも 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 互換モードでは、この例は給与 (sal) 列でウィンドウをパーティション分割し、データをソートします。この関数は、現在のウィンドウの最初の行から現在の行までの累積カウントを返します。ウィンドウには、同じ sal 値を持つすべての行が含まれます。
サンプルコマンド
-- 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 互換モードでは、この例は給与 (sal) 列でウィンドウをパーティション分割し、データをソートします。この関数は、現在のウィンドウの最初の行から最後の行までの累積カウントを返します。ウィンドウには、同じ sal 値を持つすべての行が含まれます。
サンプルコマンド
-- 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 | -- パーティションには sal が 1250 の行が 2 つ含まれています。関数は両方の行に対して 2 を返します。 | 1250 | 2 | -- パーティション内の 2 番目の行のカウントも 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 を使用して、すべての従業員を部署 (deptno) ごとにグループ化し、各部署の従業員数を計算します。
サンプルコマンド
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は現在の行のグループの最後の行に対する 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：詳細については、「ウィンドウ関数」をご参照ください。
戻り値
計算されたパーセンタイル値を 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：詳細については、「ウィンドウ関数」をご参照ください。
戻り値
計算されたパーセンタイル値を返します。データ型は入力の 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 値を返します。戻り値は重複し、非連続になる場合があります。具体的な戻り値は、データ行が属するグループの最初の行の
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 はユーザー定義関数もサポートしています。ユーザー定義関数の詳細については、「MaxCompute UDF の概要」をご参照ください。
MaxCompute SQL の一般的な問題に関する詳細については、以下をご参照ください：
MaxCompute ビルトイン関数の一般的なエラーコードと問題に関する詳細については、以下をご参照ください：