カテゴリ:

ウィンドウ関数の構文と使用法 (ランキング)

LAST_VALUE

値が順序付けられたグループ内の最後の値を返します。

こちらもご参照ください。

FIRST_VALUENTH_VALUE

構文

LAST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ]
  OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ <window_frame> ] )
Copy

window_frame の構文の詳細については、 ウィンドウ関数の構文と使用法 をご参照ください。

引数

expr

戻り値を決定する式。

expr1

行を分割するための式。単一の式または式のコンマ区切りリストを指定できます。例:

PARTITION BY column_1, column_2
Copy
expr2

行を並べ替える式。単一の式または式のコンマ区切りリストを指定できます。例:

ORDER BY column_3, column_4
Copy
{ IGNORE | RESPECT } NULLS

expr に NULL の値が含まれている場合に、 NULL の値を無視するか尊重するかを指定します。

  • IGNORE NULLS は NULL ではない最後の値を返します。

  • RESPECT NULLS は、式の最後の値であれば NULL を返します。

デフォルト: RESPECT NULLS

使用上の注意

  • この関数はランク関連の関数であるため、ウィンドウを指定する必要があります。ウィンドウ句は、次のサブ句で構成されています。

    • PARTITION BY expr1 副次句(オプション)。

    • ORDER BY expr2 サブ句(必須)。サポートされているその他の順序オプション(ソート順、 NULL 値の順序など)の詳細については、同じ規則に従う ORDER BY 句のドキュメントをご参照ください。

    • window_frame 副次句(オプション)。

  • ウィンドウ内の行の順序(したがってクエリの結果)は、 ORDER BY 句のキーによって各行が一意になる場合にのみ完全に決定論的です。次の例を考えてみましょう:

    ... OVER (PARTITION BY p ORDER BY o COLLATE 'lower') ...
    
    Copy

    パーティションのいずれかに同一の列 o の値が含まれるか、大文字と小文字を区別しない比較で同一である場合、クエリ結果は異なる可能性があります。

  • OVER 句内の ORDER BY 句は、クエリ全体の出力内にある行の順序ではなく、ウィンドウ内の行の順序のみを制御します。出力順序を制御するには、クエリの最も外側のレベルで別の ORDER BY 句を使用します。

  • オプションの window_frame は、関数が計算されるウィンドウ内の行のサブセットを指定します。ウィンドウフレームが指定されていない場合、デフォルトのフレームはウィンドウ全体となります。

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

    この動作は、ウィンドウフレームに次のデフォルトを指定する ANSI 規格とは 異なります

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

window_frame の構文の詳細については、 ウィンドウ関数の構文と使用法 をご参照ください。

最初の例は、 column1 でパーティション分割された column2 に対する LAST_VALUE の結果を返します。

SELECT
    column1,
    column2,
    LAST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2) AS column2_last
  FROM VALUES
    (1, 10), (1, 11), (1, 12),
    (2, 20), (2, 21), (2, 22);
Copy
+---------+---------+--------------+
| COLUMN1 | COLUMN2 | COLUMN2_LAST |
|---------+---------+--------------|
|       1 |      10 |           12 |
|       1 |      11 |           12 |
|       1 |      12 |           12 |
|       2 |      20 |           22 |
|       2 |      21 |           22 |
|       2 |      22 |           22 |
+---------+---------+--------------+

次の例は、関連する3つの関数、 FIRST_VALUENTH_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);
Copy

次に以下のクエリを実行します。

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;
Copy
+---------------+-----------+---+-----------+---------+----------+
| 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 |
+---------------+-----------+---+-----------+---------+----------+