- カテゴリ：
ウィンドウ関数の構文と使用法 （ランキング）
NTH_VALUE¶
値が順序付けられたグループ内のn番目の値（最大1000）を返します。
- こちらもご参照ください。
構文¶
NTH_VALUE( <expr> , <n> ) [ FROM { FIRST | LAST } ] [ { IGNORE | RESPECT } NULLS ]
OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <window_frame> ] )
window_frame の構文の詳細については、 ウィンドウ関数の構文と使用法 をご参照ください。
引数¶
nこれは、N番目の値を探すときに使用するNの値を指定します。
expr戻り値を決定する式。
expr1行を分割するための式。単一の式または式のコンマ区切りリストを指定できます。例:
PARTITION BY column_1, column_2
expr2行を並べ替える式。単一の式または式のコンマ区切りリストを指定できます。例:
ORDER BY column_3, column_4
FROM { FIRST | LAST }exprに NULL の値が含まれている場合に、 NULL の値を無視するか尊重するかを指定します。FROM FIRSTは順序付きリストの先頭から始まり、前に進みます。FROM LASTは順序付きリストの最後から始まり、逆方向に進みます。
デフォルト:
FROM FIRST{ IGNORE | RESPECT } NULLSexprに NULL の値が含まれている場合に、 NULL の値を無視するか尊重するかを指定します。IGNORE NULLSは式内の NULL 値をスキップします。RESPECT NULLSは式内のn番目の値である場合に NULL 値を返します。
デフォルト:
RESPECT NULLS
使用上の注意¶
入力値
nは1000より大きくできません。
この関数はランク関連の関数であるため、ウィンドウを指定する必要があります。ウィンドウ句は、次のサブ句で構成されています。
PARTITION BY expr1副次句（オプション）。ORDER BY expr2サブ句（必須）。サポートされているその他の順序オプション（ソート順、 NULL 値の順序など）の詳細については、同じ規則に従う ORDER BY 句のドキュメントをご参照ください。window_frame副次句（オプション）。
ウィンドウ内の行の順序（したがってクエリの結果）は、 ORDER BY 句のキーによって各行が一意になる場合にのみ完全に決定論的です。次の例を考えてみましょう：
... OVER (PARTITION BY p ORDER BY o COLLATE 'lower') ...
パーティションのいずれかに同一の列
oの値が含まれるか、大文字と小文字を区別しない比較で同一である場合、クエリ結果は異なる可能性があります。OVER 句内の ORDER BY 句は、クエリ全体の出力内にある行の順序ではなく、ウィンドウ内の行の順序のみを制御します。出力順序を制御するには、クエリの最も外側のレベルで別の ORDER BY 句を使用します。
オプションの
window_frame（累積またはスライド）は、関数が計算されるウィンドウ内の行のサブセットを指定します。window_frameが指定されていない場合、デフォルトはウィンドウ全体です。ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGこれは、ウィンドウフレームに次のデフォルトを指定するANSI標準とは異なります。
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
構文、使用上の注意、例を含むウィンドウフレームの詳細については、 ウィンドウ関数の構文と使用法 をご参照ください。
例¶
SELECT column1,
column2,
NTH_VALUE(column2, 2) OVER (PARTITION BY column1 ORDER BY column2) AS column2_2nd
FROM VALUES
(1, 10), (1, 11), (1, 12),
(2, 20), (2, 21), (2, 22);
+---------+---------+-------------+
| COLUMN1 | COLUMN2 | COLUMN2_2ND |
|---------+---------+-------------|
| 1 | 10 | 11 |
| 1 | 11 | 11 |
| 1 | 12 | 11 |
| 2 | 20 | 21 |
| 2 | 21 | 21 |
| 2 | 22 | 21 |
+---------+---------+-------------+
次の例は、関連する3つの関数、 FIRST_VALUE、 NTH_VALUE、および LAST_VALUE の結果を返します。
クエリは次を含む3行幅のスライディングウィンドウフレームを作成します。
現在の行の前の行。
現在の行。
現在の行に続く行。
呼び出し
NTH_VALUE(i, 2)の2は、ウィンドウフレームの2番目の行（この場合は現在の行でもある）を指定します。現在の行がウィンドウフレームの最初の行である場合、参照する前の行がないため、 FIRST_VALUE はその行に対して NULL を返します。
フレームの境界がパーティションの行を超えることがありますが、存在しない行はウィンドウ関数の計算に含まれません。例えば、現在の行がパーティションの一番最初の行で、ウィンドウフレームが
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGの場合、リファレンスとなる前の行がないため、 FIRST_VALUE 関数はパーティションの最初の行の値を返します。テーブルのデータからすると、3つの関数すべてで結果が一致することはありません。これらの関数は、フレーム内の各行について、 最初の値、 最後の値、または n番目の値 を選択し、値の選択は各パーティションに個別に適用されます。
この例を実行するには、まずテーブルを作成してロードします。
CREATE TABLE demo1 (i INTEGER, partition_col INTEGER, order_col INTEGER);
INSERT INTO demo1 (i, partition_col, order_col) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),
(5, 1, 5),
(1, 2, 1),
(2, 2, 2),
(3, 2, 3),
(4, 2, 4);
次に以下のクエリを実行します。
SELECT partition_col, order_col, i,
FIRST_VALUE(i) OVER (PARTITION BY partition_col ORDER BY order_col
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS FIRST_VAL,
NTH_VALUE(i, 2) OVER (PARTITION BY partition_col ORDER BY order_col
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS NTH_VAL,
LAST_VALUE(i) OVER (PARTITION BY partition_col ORDER BY order_col
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LAST_VAL
FROM demo1
ORDER BY partition_col, i, order_col;
+---------------+-----------+---+-----------+---------+----------+
| PARTITION_COL | ORDER_COL | I | FIRST_VAL | NTH_VAL | LAST_VAL |
|---------------+-----------+---+-----------+---------+----------|
| 1 | 1 | 1 | 1 | 2 | 2 |
| 1 | 2 | 2 | 1 | 2 | 3 |
| 1 | 3 | 3 | 2 | 3 | 4 |
| 1 | 4 | 4 | 3 | 4 | 5 |
| 1 | 5 | 5 | 4 | 5 | 5 |
| 2 | 1 | 1 | 1 | 2 | 2 |
| 2 | 2 | 2 | 1 | 2 | 3 |
| 2 | 3 | 3 | 2 | 3 | 4 |
| 2 | 4 | 4 | 3 | 4 | 4 |
+---------------+-----------+---+-----------+---------+----------+