ウィンドウ関数を使用して、ウィンドウ内の指定された列のデータを柔軟に分析および処理できます。このトピックでは、開発を容易にするために、SQL分析機能でサポートされているウィンドウ関数の構文、パラメーター、および例について説明します。
次の表に、SQL分析機能でサポートされているウィンドウ関数を示します。
関数 | 説明 |
ウィンドウ内のデータの平均値を計算します。 | |
データのランダムな行をサンプリングします。true が返された場合、指定されたデータ行がサンプリングされます。 | |
ウィンドウ内の行数を計算します。 | |
パーティション内のデータの累積分布を計算します。 | |
行のグループ内の行のランクを計算します。ランクは連続しています。 | |
現在の行が属するウィンドウの最初のデータ行の計算結果を取得します。 | |
ウィンドウ内の指定されたオフセットで現在の行の前にある N 番目のデータ行の計算結果を取得します。 | |
現在の行が属するウィンドウの最後のデータ行の計算結果を取得します。 | |
ウィンドウ内の指定されたオフセットで現在の行に続く N 番目のデータ行の計算結果を取得します。 | |
ウィンドウ内の最大値を計算します。 | |
ウィンドウ内の中央値を計算します。 | |
ウィンドウ内の最小値を計算します。 | |
パーティション内のデータ行を同じサイズの N 個のグループに分割し、現在の行が属するグループの番号を返します。グループ番号の範囲は 1 ～ N です。 | |
現在の行が属するウィンドウの N 番目のデータ行の計算結果を取得します。 | |
順序付けられた行のグループ内の行のパーセンタイルランクを計算します。この関数はパーセント値を返します。 | |
順序付けられた行のグループ内の行のランクを計算します。ランクは連続した整数ではない場合があります。 | |
行のシーケンス番号を計算します。行番号は 1 から始まります。 | |
すべての入力値の母集団標準偏差を返します。この関数は STDDEV_POP とも呼ばれます。 | |
すべての入力値の標本標準偏差を返します。 | |
ウィンドウ内のデータの合計を計算します。 |
制限事項
ウィンドウ関数を使用する前に、次の制限事項に注意してください。
ウィンドウ関数は
SELECT文でのみサポートされています。ウィンドウ関数には、ネストされたウィンドウ関数またはネストされた集約関数を含めることはできません。
ウィンドウ関数と、同じレベルの集約関数を一緒に使用することはできません。
構文
ウィンドウ関数の構文：
<function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
<function_name>([distinct][<expression> [, ...]]) over <window_name>function_name: 組み込みウィンドウ関数の名前。
expression: ウィンドウ関数の形式。形式は関数の構文によって異なります。
windowing_definition: ウィンドウの定義。 windowing_definition の構文については、windowing_definition をご参照ください。
window_name: ウィンドウの名前。
windowキーワードを使用してウィンドウを設定し、windowing_definition を使用してウィンドウの名前を指定できます。 named_window_def の構文：window <window_name> as (<window_definition>)SQL 文における named_window_def の位置：
select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]
windowing_definition
構文
--partition_clause:
[partition by <expression> [, ...]]
--orderby_clause:
[order by <expression> [asc|desc][nulls {first|last}] [, ...]]
[<frame_clause>]SELECT 文でウィンドウ関数を使用する場合、ウィンドウ関数が実行されるときに、windowing_definition の PARTITION BY および ORDER BY に基づいてデータがパーティション化およびソートされます。SELECT 文に partition by が含まれていない場合は、パーティションは 1 つだけで、そのパーティションにはすべてのデータが含まれます。SELECT 文に order by が含まれていない場合、パーティション内のデータはランダムな順序で配置され、データストリームが生成されます。データストリームが生成された後、windowing_definition の frame_clause に基づいてデータストリームから行のグループが抽出され、現在の行のウィンドウが作成されます。ウィンドウ関数は、現在の行が属するウィンドウに含まれるデータを計算します。
partition by <expression> [, ...]: オプション。このパラメーターは、パーティション情報を指定します。パーティションキー列の値が行のグループで同じである場合、これらの行は同じウィンドウに含まれます。PARTITION BY の形式の詳細については、「テーブル操作」をご参照ください。
order by <expression> [asc|desc][nulls {first|last}] [, ...]: オプション。このパラメーターは、ウィンドウ内のデータ行のソート方法を指定します。
説明order byで指定された列の値が同じ場合、ソート結果は正確でない可能性があります。order byで指定された列の値が一意になるようにすることで、データのランダムな順序付けを減らすことができます。frame_clause: オプション。このパラメーターは、ウィンドウのデータ境界を決定するために使用されます。frame_clause の詳細については、「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: 必須。ROWS、RANGE、および GROUPS は、frame_clause のタイプを示します。frame_start と frame_end の実装ルールは、frame_clause のタイプによって異なります。説明：
ROWS: ウィンドウのデータ境界は、行数に基づいて決定されます。
RANGE: ウィンドウのデータ境界は、
order byで指定された列の値の比較結果に基づいて決定されます。ほとんどの場合、order byは windowing_definition で指定されます。order byが windowing_definition で指定されていない場合、order byで指定された列の値は、パーティション内のすべての行で同じです。NULL 値は同等と見なされます。グループ: パーティションでは、
order byで指定された列の値が同じ行がグループを形成します。order byが指定されていない場合、パーティション内のすべての行が 1 つのグループを形成します。NULL 値は同等と見なされます。
frame_start と frame_end: ウィンドウの開始行と終了行。frame_start は必須です。frame_end はオプションです。frame_end が指定されていない場合は、デフォルト値の CURRENT ROW が使用されます。
frame_start で指定された行は、frame_end で指定された行よりも前であるか、同じである必要があります。 で指定された行と比較して、frame_start で指定された行は、windowing_definition の
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で現在の行よりも前にある N 番目の行を示します。たとえば、0 PRECEDINGが現在の行を示す場合、1 PRECEDINGは前の行を示します。offsetは負でない整数である必要があります。offset FOLLOWING
指定された
offsetにある現在の行の後にある N 番目の行を示します。たとえば、0 FOLLOWINGが現在の行を示す場合、1 FOLLOWINGは次の行を示します。offsetは非負の整数である必要があります。RANGE
CURRENT ROW
frame_start が CURRENT ROW に設定されている場合、
order byで指定された列と同じ値を持つ最初の行が現在の行として使用されます。frame_end が CURRENT ROW に設定されている場合、
order byで指定された列と同じ値を持つ最後の行が現在の行として使用されます。
offset PRECEDING
frame_start および frame_end で指定された行は、
order byで指定されたソート順に基づいて決定されます。たとえば、ウィンドウ内のデータが X でソートされている場合、Xi は i 番目の行に対応する X 値を指定し、Xc は現在の行に対応する X 値を指定します。frame_start および frame_end で指定された行の位置：order byが asc に設定されています:frame_start は、
Xc - Xi ≤ offsetという要件を満たす最初の行を指定します。frame_end は、
Xc - Xi ≥ offsetという要件を満たす最後の行を指定します。
order byが desc に設定されています:frame_start は、
Xi - Xc ≤ offsetという要件を満たす最初の行を指定します。frame_end は、次の要件を満たす最後の行を指定します。
Xi - Xc ≥ offset。
order byで指定された列は、次のデータ型を使用できます：TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、DATETIME、DATE、および TIMESTAMP。offsetを DATE 型で使用する場合の構文:N: N 日または N 秒を示します。非負の整数である必要があります。 DATETIME 型または TIMESTAMP 型のオフセットの場合、N は N 秒です。 DATE 型のオフセットの場合、N は 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 値を指定します。frame_start および frame_end で指定された行の位置：order byが asc に設定されています:frame_start は、
Xi - Xc ≥ offsetという要件を満たす最初の行を指定します。frame_end は、
Xi - Xc ≤ offsetという要件を満たす最後の行を指定します。
order byが desc に設定されています:frame_start は、次の要件を満たす最初の行を指定します。
Xc - Xi >= offset。frame_end は、
Xc - Xi <= offsetという要件を満たす最後の行を指定します。
GROUPS
CURRENT ROW
frame_start が CURRENT ROW に設定されている場合、現在の行が属するグループの最初の行が使用されます。
frame_end が CURRENT ROW に設定されている場合、現在の行が属するグループの最後の行が使用されます。
offset PRECEDING
frame_start がオフセット PRECEDING に設定されている場合、指定された
offsetで現在の行のグループよりも前にある N 番目のグループの最初の行が使用されます。frame_end がオフセット PRECEDING に設定されている場合、指定された
offsetで現在の行のグループよりも前にある N 番目のグループの最後の行が使用されます。
説明frame_start を UNBOUNDED FOLLOWING に設定することはできず、frame_end を UNBOUNED PRECEDING に設定することはできません。
offset FOLLOWING
frame_start がオフセット FOLLOWING に設定されている場合、指定された
offsetで現在の行のグループに続く N 番目のグループの最初の行が使用されます。frame_end が FOLLOWING オフセットに設定されている場合、指定された
offsetで現在の行のグループに続く N 番目のグループの最後の行が使用されます。
説明frame_start を UNBOUNDED FOLLOWING に設定することはできず、frame_end を UNBOUNED PRECEDING に設定することはできません。
frame_exclusion: オプション。このパラメーターは、ウィンドウから特定の行を削除するために使用されます。有効な値：
EXCLUDE NO OTHERS: ウィンドウから行は除外されません。
EXCLUDE CURRENT ROW: 現在の行はウィンドウから除外されます。
EXCLUDE GROUP: パーティション内の行のグループ全体がウィンドウから除外されることを示します。グループ内では、すべての行が、現在の行として
order byで指定された列と同じ値を持ちます。EXCLUDE TIES: 現在の行を除く、行のグループ全体がウィンドウから除外されます。
デフォルトの frame_clause
frame_clause を設定しない場合、frame_clause のデフォルト値は次の形式になります。この形式は、ほとんどの SQL ベースのシステムで使用される形式と同じです。
RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
ウィンドウのデータ境界の例
この例では、tbl という名前のテーブルに、BIGINT 型の 3 つの列 pid、oid、および rid が含まれています。tbl テーブルには、次のデータが含まれています。
+------------+------------+------------+
| 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 |
+------------+------------+------------+次の SQL 文の省略記号（...）を windowing_definition に置き換えて、各データ行が含まれるウィンドウにデータを表示できます。
返された結果の window 列の値が NULL の場合、ウィンドウにはデータが含まれていません。
ROWS モードのウィンドウ
windowing_definition 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] | +------------+------------+------------+--------+windowing_definition 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] | +------------+------------+------------+--------+windowing_definition 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 | +------------+------------+------------+--------+windowing_definition 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] | +------------+------------+------------+--------+windowing_definition 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 | +------------+------------+------------+--------+windowing_definition 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] | +------------+------------+------------+--------+EXCLUDE CURRENT ROW と EXCLUDE GROUP の違いは、windowing_definition 5 と windowing_definition 6 において、
window列の値が 2、4、および 10 である行と、rid列の値を比較することで確認できます。 frame_exclusion が EXCLUDE GROUP に設定されている場合、現在の行と同じoid列の値を持つ行が存在する場合、パーティション内で同じpid列の値を持つ行が抽出されます。
RANGE モードのウィンドウ
windowing_definition 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] | +------------+------------+------------+--------+frame_end が CURRENT ROW に設定されている場合、現在の行と同じ
oid列の値を持ち、order byで指定された順序で最後の行が取得されます。したがって、rid列の値が 1 である行のwindow列の値は [1, 2] です。windowing_definition 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] | +------------+------------+------------+--------+windowing_definition 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] | +------------+------------+------------+--------+oidの値がorder byで NULL である行の場合、frame_start がoffset PRECEDINGまたは offset FOLLOWING に設定されている場合、その行はorder byで oid の値が NULL である最初の行になります（ただし、offsetが unbounded でない場合）。 frame_end が offset PRECEDING または offset FOLLOWING に設定されている場合、その行はorder byで oid の値が NULL である最後の行になります（ただし、offset が unbounded でない場合）。
GROUPS モードのウィンドウ
windowing_definition
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 列の平均値を計算します。ORDER BY は指定されていません。この関数は、現在のウィンドウの最初の行から最後の行までの値の累積平均値を返します。現在のウィンドウには、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 列の平均値を計算します。ORDER BY が指定されています。この関数は、現在のウィンドウの最初の行から、現在の行と同じ sal 値を持つ行までの値の累積平均値を返します。同じ sal 値を持つ行の平均値は同じです。現在のウィンドウには、deptno 値が同じ行が含まれます。ステートメントの例：
-- 次のステートメントを実行します。 select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;結果の例：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300.0 | -- この行はこのウィンドウの最初の行です。最初の行の平均値は、2 つの行の sal 値が同じであるため、1 行目と 2 行目の値の累積平均値です。 | 10 | 1300 | 1300.0 | -- 戻り値は、1 行目と 2 行目の値の累積平均値です。 | 10 | 2450 | 1875.0 | -- 3 行目と 4 行目の sal 値が同じであるため、3 行目の平均値は、1 行目から 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(*) over ([partition_clause] [orderby_clause] [frame_clause]) bigint count([distinct] <expr>[,...]) over ([partition_clause] [orderby_clause] [frame_clause])説明
count(*): 返される行の総数。count([distinct] <expr>[,...]): 行数を計算する場合、expr が NULL の行は無視されます。複数の expr パラメーターが存在する場合、expr が NULL の行は無視されます。さらに、distinct キーワードが指定されている場合は、重複排除後の行数が計算されます。expr が NULL の行は無視されます。
説明
expr: 必須。このパラメーターは、値をカウントする列を指定します。すべてのデータ型がサポートされています。入力値が NULL の場合、この値を含む行は計算に使用されません。DISTINCT キーワードが指定されている場合、個別値のカウント値が取得されます。
partition_clause、orderby_clause、および frame_clause: これらのパラメーターの詳細については、「windowing_definition」をご参照ください。
戻り値
BIGINT タイプの値が返されます。
例
例 1: sal 列を使用してウィンドウを定義します。ORDER BY は指定されていません。この関数は、現在のウィンドウの最初の行から最後の行までの累積カウントを返します。現在のウィンドウには、sal 値が同じ行が含まれます。ステートメントの例：
select sal, count(sal) over (partition by sal) as count from emp;結果の例：
+------------+------------+ | sal | count | +------------+------------+ | 800 | 1 | | 950 | 1 | | 1100 | 1 | | 1250 | 2 | -- この行はこのウィンドウの最初の行です。2 つの行の sal 値が同じであるため、最初の行の COUNT の値は 2 番目の行の累積カウントです。 | 1250 | 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: sal 列を使用してウィンドウを定義します。ORDER BY が指定されています。この関数は、現在のウィンドウの最初の行から最後の行までの累積カウントを返します。現在のウィンドウには、sal 値が同じ行が含まれます。ステートメントの例：
-- 次のステートメントを実行します。 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 | -- この行はこのウィンドウの最初の行です。2 つの行の sal 値が同じであるため、最初の行の COUNT の値は 2 番目の行の累積カウントです。 | 1250 | 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 | +------------+------------+
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) 別にグループ化し、各グループの従業員の累積分布を給与別に計算します。ステートメントの例：
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: オプション。BOOLEAN タイプの値。このパラメーターは、null 値を無視するかどうかを指定します。デフォルト値: 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: オプション。値は BIGINT タイプの定数で、0 以上である必要があります。値 0 は現在の行を示し、値 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: オプション。BOOLEAN タイプの値。このパラメーターは、null 値を無視するかどうかを指定します。デフォルト値: false。このパラメーターが true に設定されている場合、ウィンドウの最後の行に対応する expr の null 以外の値が返されます。
partition_clause、orderby_clause、および frame_clause: これらのパラメーターの詳細については、「windowing_definition」をご参照ください。
戻り値
expr と同じデータ型の値が返されます。
例
deptno 列に基づいてすべての従業員をグループ化し、各グループの最後のデータ行を返します。ステートメントの例：
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 が指定されている場合、最初の行から現在の行までの行が現在のウィンドウに属します。現在のウィンドウの現在の行の値が返されます。
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 で現在の行に続く N 番目の行に対応する expr の値を返します。式 expr は、列、列操作、または関数操作にすることができます。
説明
expr: 必須。返される結果の計算に使用される式。
offset: オプション。値は BIGINT タイプの定数で、0 以上である必要があります。値 0 は現在の行を示し、値 1 は次の行を示します。デフォルト値: 1。入力値が STRING または DOUBLE タイプの場合、計算前に BIGINT タイプの値に暗黙的に変換されます。
default: オプション。offset の値が有効な範囲外の場合のデフォルト値。このパラメーターの値は定数である必要があります。このパラメーターのデフォルト値は null です。このパラメーターの値は、expr の値と同じデータ型である必要があります。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 列の最大値を取得します。ORDER BY 句は指定されていません。この関数は、現在のウィンドウの最大値を返します。現在のウィンドウには、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 列の最大値を取得します。ORDER BY 句が指定されています。この関数は、現在のウィンドウの最初の行から現在の行までの値のうちの最大値を返します。現在のウィンドウには、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 タイプの値。このパラメーターの値は、長さが 1 ～ 255 桁である必要があります。
入力値が 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 | 22450.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 列の最小値を取得します。ORDER BY 句は指定されていません。この関数は、現在のウィンドウの最小値を返します。現在のウィンドウには、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 列の最小値を取得します。ORDER BY 句が指定されています。この関数は、現在のウィンドウの最初の行から現在の行までの値のうちの最小値を返します。現在のウィンドウには、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 個のグループに分割できない場合、最初の M 個のグループに 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: オプション。BOOLEAN タイプの値。このパラメーターは、null 値を無視するかどうかを指定します。デフォルト値: false。このパラメーターが true に設定されている場合、ウィンドウの N 番目の行に対応する expr の 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 が指定されている場合、最初の行から現在の行までの行が現在のウィンドウに属します。現在のウィンドウの 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 行しかないため、NULL が返されます。 | 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」をご参照ください。
戻り値
DOUBLE 型の値が返されます。有効値の範囲は [0.0, 1.0] です。戻り値は、次の数式を使用して計算されます。
(rank - 1)/(partition_row_count - 1)。rankは、現在の行に対応する RANK 関数の戻り値を示します。partition_row_countは、現在の行が属するパーティションの行数を示します。パーティションにデータ行が 1 行しかない場合は、0.0 が返されます。例
sal 列に基づいて、グループ内の各従業員のパーセンタイルランクを計算します。ステートメントの例：
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 | +------------+------------+------------+------------+
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 列の母集団標準偏差を計算します。ORDER BY 句は指定されていません。この関数は、現在のウィンドウの累積母集団標準偏差を返します。現在のウィンドウには、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: deptno 列を使用してウィンドウを定義し、sal 列の母集団標準偏差を計算します。ORDER BY 句が指定されています。この関数は、現在のウィンドウの最初の行から、現在の行と同じ sal 値を持つ行までの値の累積母集団標準偏差を返します。同じ sal 値を持つ行の母集団標準偏差は同じです。現在のウィンドウには、deptno 値が同じ行が含まれます。ステートメントの例：
-- 次のステートメントを実行します。 select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;結果の例：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 0.0 | -- この行はこのウィンドウの最初の行です。2 つの行の sal 値が同じであるため、最初の行の母集団標準偏差は、1 行目と 2 行目の値の累積母集団標準偏差です。 | 10 | 1300 | 0.0 | -- 戻り値は、1 行目と 2 行目の値の累積母集団標準偏差です。 | 10 | 2450 | 575.0 | -- 3 行目と 4 行目の sal 値が同じであるため、3 行目の母集団標準偏差は、1 行目から 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 列の標本標準偏差を計算します。ORDER BY 句は指定されていません。この関数は、現在のウィンドウの累積標本標準偏差を返します。現在のウィンドウには、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 列の標本標準偏差を計算します。ORDER BY 句が指定されています。この関数は、現在のウィンドウの最初の行から現在の行までの値の累積標本標準偏差を返します。現在のウィンドウには、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 列の合計を計算します。ORDER BY 句は指定されていません。この関数は、現在のウィンドウの累積合計を返します。現在のウィンドウには、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: deptno 列を使用してウィンドウを定義し、sal 列の合計を計算します。ORDER BY 句が指定されています。この関数は、現在のウィンドウの最初の行から、現在の行と同じ sal 値を持つ行までの値の累積合計を返します。同じ sal 値を持つ行の合計値は同じです。現在のウィンドウには、deptno 値が同じ行が含まれます。ステートメントの例：
-- 次のステートメントを実行します。 select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;結果の例：
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 2600 | -- この行はこのウィンドウの最初の行です。2 つの行の sal 値が同じであるため、最初の行の合計は、1 行目と 2 行目の値の累積合計です。 | 10 | 1300 | 2600 | -- 戻り値は、1 行目と 2 行目の値の累積合計です。 | 10 | 2450 | 7500 | -- 3 行目と 4 行目の sal 値が同じであるため、3 行目の合計は、1 行目から 4 行目までの値の累積合計です。 | 10 | 2450 | 7500 | -- 戻り値は、1 行目から 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 | +------------+------------+------------+