翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。 # ウィンドウ関数 ウィンドウ関数を使用すると、分析的なビジネスクエリをより効率的に作成できます。ウィンドウ関数はパーティションまたは結果セットの「ウィンドウ」で演算し、ウィンドウのすべての行に値を返します。それに対して、ウィンドウ以外の関数は、結果セットの行ごとに計算を実行します。結果の行を集計するグループ関数とは異なり、ウィンドウ関数はテーブル式のすべての行を保持します。 戻り値はこのウィンドウの行セットの値を使用して計算されます。ウィンドウはテーブルの各行に、追加の属性を計算するために使用する行のセットを定義します。ウィンドウはウィンドウ仕様 (OVER 句) を使用して定義され、次の 3 つの主要な概念に基づいています。 + *ウィンドウのパーティション*、列のグループを形成 (PARTITION 句) + *ウィンドウの並び順*、各パーティション内の行の順序またはシーケンスの定義 (ORDER BY 句) + *ウィンドウのフレーム*、各行に関連して定義され、行のセットをさらに制限 (ROWS 仕様) ウィンドウ関数は、最後の ORDER BY 句を除いて、クエリで実行される最後の演算のセットです。すべての結合およびすべての WHERE、GROUP BY、および HAVING 句は、ウィンドウ関数が処理される前に完了されます。そのため、ウィンドウ関数は選択リストまたは ORDER BY 句のみに表示できます。複数のウィンドウ関数は、別のフレーム句を持つ 1 つのクエリ内で使用できます。ウィンドウ関数は、CASE などの他のスカラー式でも使用できます。 ## ウィンドウ関数の構文の概要 ウィンドウ関数は、次のような標準構文に従います。 ``` function (expression) OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list [ frame_clause ] ] ) ``` ここで、*function* は、このセクションで説明している関数の 1 つです。 *expr\$1list* は次のとおりです。 ``` expression | column_name [, expr_list ] ``` *order\$1list* は次のとおりです。 ``` expression | column_name [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, order_list ] ``` *frame\$1clause* は次のとおりです。 ``` ROWS { UNBOUNDED PRECEDING | unsigned_value PRECEDING | CURRENT ROW } | { BETWEEN { UNBOUNDED PRECEDING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW} AND { UNBOUNDED FOLLOWING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW }} ``` ### 引数 *function* ウィンドウ関数。詳細については、個々の関数の説明を参照してください。 OVER ウィンドウの仕様を定義する句。OVER 句はウィンドウ関数に必須であり、ウィンドウ関数を他の SQL 関数と区別します。 PARTITION BY *expr\$1list* (オプション) PARTITION BY 句は結果セットをパーティションに再分割します。これは GROUP BY 句と似ています。パーティション句が存在する場合、関数は各パーティションの行に対して計算されます。パーティション句が指定されていない場合、1 つのパーティションにテーブル全体が含まれ、関数は完全なテーブルに対して計算されます。 ランク付け関数 DENSE\$1RANK、NTILE、RANK、および ROW\$1NUMBER では、結果セットのすべての行でグローバルな比較が必要です。PARTITION BY clauseを使用すると、クエリオプティマイザーは、パーティションに応じて複数のスライスにワークロードを分散させることにより、個々の集計を並列で実行できます。PARTITION BY 句がない場合、集計ステップを 1 つのスライスで順次実行する必要があり、特に大規模なクラスターではパフォーマンスに大きな悪影響を与えることがあります。 AWS Clean Roomsは、PARTITION BY 句の文字列リテラルをサポートしていません。 ORDER BY *order\$1list* (オプション) ウィンドウ関数は、ORDER BY で順序仕様に従ってソートされた各パーティション内の行に適用されます。この ORDER BY 句は、*frame\$1clause* の ORDER BY 句とは異なり、両者はまったく無関係です。ORDER BY 句は、PARTITION BY 句なしで使用できます。 ランク付け関数の場合、ORDER BY 句はランク付けの値に使用する基準を特定します。集計関数の場合、パーティションで分割された行は、集計関数がフレームごとに計算される前に順序付けされる必要があります。ウィンドウ関数の種類の詳細については、「[ウィンドウ関数](#Window_functions)」を参照してください。 列識別子または列識別を検証する式は、順序リストで必要とされます。定数も定数式も、列名の代用として使用することはできません。 NULL 値は独自のグループとして扱われ、NULLS FIRST または NULLS LAST オプションに従ってソートおよびランク付けされます。デフォルトでは、NULL 値は昇順ではソートされて最後にランク付けされ、降順ではソートされて最初にランク付けされます。 AWS Clean Roomsは、ORDER BY 句の文字列リテラルをサポートしていません。 ORDER BY 句を省略した場合、行の順序は不確定になります。 ORDER BY 句がデータの一意の合計順序を生成しない場合などAWS Clean Rooms、並列システムでは、行の順序は不確定です。つまり、ORDER BY 式が重複した値を生成する場合 (部分的な順序付け）、それらの行の戻り順序は の実行ごとに異なる場合がありますAWS Clean Rooms。そのため、ウィンドウ関数は予期しない結果または矛盾した結果を返す場合があります。詳細については、「[ウィンドウ関数用データの一意の並び順](#Examples_order_by_WF)」を参照してください。 *column\$1name* パーティション化または順序付けされる列の名前。 ASC \$1 DESC 次のように、式のソート順を定義するオプション: + ASC: 昇順 (数値の場合は低から高、文字列の場合は「A」から「Z」など) オプションを指定しない場合、データはデフォルトでは昇順にソートされます。 + DESC: 降順 (数値の場合は高から低、文字列の場合は「Z」から「A」)。 NULLS FIRST \$1 NULLS LAST NULL を NULL 以外の値より先に順序付けするか、NULL 以外の値の後に順序付けするかを指定するオプション。デフォルトでは、NULL は昇順ではソートされて最後にランク付けされ、降順ではソートされて最初にランク付けされます。 *frame\$1clause* 集計関数では、ORDER BY を使用する場合、フレーム句は関数のウィンドウで行のセットをさらに絞り込みます。これは、順序付けされた結果内の行のセットを含めるか、または除外できるようにします。フレーム句は ROWS キーワードおよび関連する指定子で構成されます。 frame 句は、ランク付け関数には適用されません。また、集計関数の OVER 句の中に ORDER BY 句がない場合は、フレーム句は必要ありません。ORDER BY 句が集計関数に使用される場合、明示的なフレーム句が必要です。 ORDER BY 句が指定されていない場合、暗黙的なフレームはバインドされません (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING と同じ)。 ROWS この句は、現在の行からの物理オフセットを指定してウィンドウフレームを定義します。 この句は、現在のウィンドウの行、または現在の行の値を組み合わせるパーティションを指定します。また、現在の行の前後に配置される行の位置を指定する引数を使用します。すべてのウィンドウフレームの参照点は現在の行です。ウィンドウフレームがパーティションで次にスライドすると、各行は順に現在の行になります。 フレームは、次のように現在の行までと現在の行を含む行の簡易セットである場合があります。 ``` {UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW} ``` また、次の 2 つの境界の間の行のセットである場合があります。 ``` BETWEEN { UNBOUNDED PRECEDING | offset { PRECEDING | FOLLOWING } | CURRENT ROW } AND { UNBOUNDED FOLLOWING | offset { PRECEDING | FOLLOWING } | CURRENT ROW } ``` UNBOUNDED PRECEDING はウィンドウがパーティションの最初の行で開始することを示し、*offset* PRECEDING はウィンドウが現在の行の前のオフセット値と等しい行数で開始することを示します。デフォルトは UNBOUNDED PRECEDING です。 CURRENT ROW は、ウィンドウが現在の行で開始または終了することを示します。 UNBOUNDED FOLLOWING はウィンドウがパーティションの最後の行で終了することを示し、*offset* FOLLOWING はウィンドウが現在の行の後のオフセット値と等しい行数で終了することを示します。 *offset* は、現在の行の前後にある物理的な行数を識別します。この場合、*offset* は、正の数値に評価される定数である必要があります。例えば、5 FOLLOWING は現在の行より 5 行後のフレームを終了します。 BETWEEN が指定されていない場合、フレームは現在の行により暗黙的に区切られます。例えば、`ROWS 5 PRECEDING` は `ROWS BETWEEN 5 PRECEDING AND CURRENT ROW` と同じです。また、`ROWS UNBOUNDED FOLLOWING`は `ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING` と同じです。 開始境界が終了境界よりも大きいフレームを指定することはできません。例えば、以下のフレームはいずれも指定することができません。 ``` between 5 following and 5 preceding between current row and 2 preceding between 3 following and current row ``` ## ウィンドウ関数用データの一意の並び順 ウィンドウ関数の ORDER BY 句がデータの一意および全体の並び順を生成しない場合、行の順序は不確定になります。ORDER BY 式が重複した値 (部分的な順序付け) を生成する場合、これらの行の戻り値の順序は実行時によって異なる可能性があります。この場合、ウィンドウ関数は予期しない結果または矛盾した結果を返す場合があります。 例えば、次のクエリは、複数の実行にわたって異なる結果を返します。これらの異なる結果は、`order by dateid` が SUM Window 関数でデータの一意の順序を生成しないために発生します。 ``` select dateid, pricepaid, sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+------------- 1827 | 1730.00 | 1730.00 1827 | 708.00 | 2438.00 1827 | 234.00 | 2672.00 ... select dateid, pricepaid, sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+------------- 1827 | 234.00 | 234.00 1827 | 472.00 | 706.00 1827 | 347.00 | 1053.00 ... ``` この場合、2 番目の ORDER BY 列をウィンドウ関数に追加すると問題を解決できます。 ``` select dateid, pricepaid, sum(pricepaid) over(order by dateid, pricepaid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+--------- 1827 | 234.00 | 234.00 1827 | 337.00 | 571.00 1827 | 347.00 | 918.00 ... ``` ## サポートされている関数 AWS Clean RoomsSpark SQL は、集計とランキングの 2 種類のウィンドウ関数をサポートしています。 サポートされる集約関数は次のとおりです。 + [CUME\$1DIST ウィンドウ関数](WF_CUME_DIST.md) + [DENSE\$1RANK ウィンドウ関数](WF_DENSE_RANK.md) + [最初のウィンドウ関数](WF_FIRST.md) + [FIRST\$1VALUE ウィンドウ関数](WF_first_value.md) + [LAG ウィンドウ関数](WF_LAG.md) + [LAST ウィンドウ関数](WF-LAST.md) + [LAST\$1VALUE ウィンドウ関数](WF_last_value.md) + [LEAD ウィンドウ関数](WF_LEAD.md) サポートされる集計関数は次のとおりです。 + [DENSE\$1RANK ウィンドウ関数](WF_DENSE_RANK.md) + [PERCENT\$1RANK ウィンドウ関数](WF_PERCENT_RANK.md) + [RANK ウィンドウ関数](WF_RANK.md) + [ROW\$1NUMBER ウィンドウ関数](WF_ROW_NUMBER.md) ## ウィンドウ関数例のサンプルテーブル ウィンドウ関数別の例をそれぞれの説明と共に参照できます。一部の例で使用している WINSALES という名前のテーブルには、以下のテーブルのように 11 行が含まれています。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/clean-rooms/latest/sql-reference/Window_functions.html) # CUME\$1DIST ウィンドウ関数 ウィンドウまたはパーティション内の値の累積分布を計算します。昇順の場合、累積分布は以下の式を使用して特定されます。 `count of rows with values <= x / count of rows in the window or partition` ここで、*x* は ORDER BY 句で指定された列の現在の行の値と等しくなります。以下のデータセットは、この式の使用方法を示しています。 ``` Row# Value Calculation CUME_DIST 1 2500 (1)/(5) 0.2 2 2600 (2)/(5) 0.4 3 2800 (3)/(5) 0.6 4 2900 (4)/(5) 0.8 5 3100 (5)/(5) 1.0 ``` 戻り値の範囲は、0～1 (0 は含みませんが 1 は含みます) です。 ## 構文 ``` CUME_DIST () OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] ) ``` ## 引数 OVER ウィンドウのパーティションを指定する句。OVER 句にウィンドウフレーム仕様を含めることはできません。 PARTITION BY *partition\$1expression* 省略可能。OVER 句の各グループのレコードの範囲を設定する式。 ORDER BY *order\$1list* 累積分布を計算する式。式は、数値データ型を含んでいるか、そのデータ型に暗黙的に変換できる必要があります。ORDER BY を省略した場合、すべての行について戻り値は 1 です。 ORDER BY で一意の並べ替えが行われない場合、行の順序は不確定になります。詳細については、「[ウィンドウ関数用データの一意の並び順](Window_functions.md#Examples_order_by_WF)」を参照してください。 ## 戻り型 FLOAT8 ## 例 次の例は、各販売者の数量の累積配布を計算します。 ``` select sellerid, qty, cume_dist() over (partition by sellerid order by qty) from winsales; sellerid qty cume_dist -------------------------------------------------- 1 10.00 0.33 1 10.64 0.67 1 30.37 1 3 10.04 0.25 3 15.15 0.5 3 20.75 0.75 3 30.55 1 2 20.09 0.5 2 20.12 1 4 10.12 0.5 4 40.23 1 ``` WINSALES テーブルの説明については、「[ウィンドウ関数例のサンプルテーブル](Window_functions.md#Window_function_example)」を参照してください。 # DENSE\$1RANK ウィンドウ関数 DENSE\$1RANK ウィンドウ関数は、OVER 句の ORDER BY 式に基づいて、値のグループの値のランクを特定します。オプションの PARTITION BY 句がある場合、ランク付けは行のグループごとにリセットされます。ランク付け条件が同じ値の行は、同じランクを受け取ります。DENSE\$1RANK 関数はある点において RANK とは異なります。2 行以上で同点となった場合、ランク付けされた値の順位に差はありません。例えば、2 行が 1 位にランク付けされると、次のランクは 2 位になります。 同じクエリに PARTITION BY および ORDER BY 句のあるランク付け関数を使用することができます。 ## 構文 ``` DENSE_RANK () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] ) ``` ## 引数 ( ) この関数は引数を受け取りませんが、空のかっこは必要です。 OVER DENSE\$1RANK 関数のウィンドウ句。 PARTITION BY *expr\$1list* 省略可能。ウィンドウを定義する 1 つ以上の式。 ORDER BY *order\$1list* 省略可能。ランク付けの値が基とする式。PARTITION BY が指定されていない場合、ORDER BY はテーブル全体を使用します。ORDER BY を省略した場合、すべての行について戻り値は 1 です。 ORDER BY で一意の並べ替えが行われない場合、行の順序は不確定になります。詳細については、「[ウィンドウ関数用データの一意の並び順](Window_functions.md#Examples_order_by_WF)」を参照してください。 ## 戻り型 INTEGER ## 例 次の例では、販売数量によってテーブルを順序付けして (降順)、各行にデンス値のランクと標準のランクの両方を割り当てます。結果はウィンドウ関数の結果が提供された後にソートされます。 ``` select salesid, qty, dense_rank() over(order by qty desc) as d_rnk, rank() over(order by qty desc) as rnk from winsales order by 2,1; salesid | qty | d_rnk | rnk ---------+-----+-------+----- 10001 | 10 | 5 | 8 10006 | 10 | 5 | 8 30001 | 10 | 5 | 8 40005 | 10 | 5 | 8 30003 | 15 | 4 | 7 20001 | 20 | 3 | 4 20002 | 20 | 3 | 4 30004 | 20 | 3 | 4 10005 | 30 | 2 | 2 30007 | 30 | 2 | 2 40001 | 40 | 1 | 1 (11 rows) ``` DENSE\$1RANK および RANK 関数が同じクエリで並べて使用される場合、同じ行のセットに割り当てるランク付けの違いに注意してください。WINSALES テーブルの説明については、「[ウィンドウ関数例のサンプルテーブル](Window_functions.md#Window_function_example)」を参照してください。 次の例では、SELLERID によってテーブルをパーティション分割し、数量によって各パーティションを順序付けして (降順)、行ごとにデンス値のランクを割り当てます。結果はウィンドウ関数の結果が提供された後にソートされます。 ``` select salesid, sellerid, qty, dense_rank() over(partition by sellerid order by qty desc) as d_rnk from winsales order by 2,3,1; salesid | sellerid | qty | d_rnk ---------+----------+-----+------- 10001 | 1 | 10 | 2 10006 | 1 | 10 | 2 10005 | 1 | 30 | 1 20001 | 2 | 20 | 1 20002 | 2 | 20 | 1 30001 | 3 | 10 | 4 30003 | 3 | 15 | 3 30004 | 3 | 20 | 2 30007 | 3 | 30 | 1 40005 | 4 | 10 | 2 40001 | 4 | 40 | 1 (11 rows) ``` WINSALES テーブルの説明については、「[ウィンドウ関数例のサンプルテーブル](Window_functions.md#Window_function_example)」を参照してください。 # 最初のウィンドウ関数 順序付けられた行のセットがある場合、FIRST はウィンドウフレームの最初の行に関して指定された式の値を返します。 フレームの最後の行を選択する方法については、「[LAST ウィンドウ関数](WF-LAST.md)」を参照してください。 ## 構文 ``` FIRST( expression )[ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] ) ``` ## 引数 *expression* 関数の対象となる列または式。 IGNORE NULLS このオプションを FIRST で使用すると、関数は NULL (すべての値が NULL の場合は NULL) ではないフレームの最初の値を返します。 RESPECT NULLS が使用する行の決定に null 値を含めるAWS Clean Rooms必要があることを示します。IGNORE NULLS を指定しない場合、RESPECT NULLS はデフォルトでサポートされます。 OVER 関数にウィンドウ句を導入します。 PARTITION BY *expr\$1list* 1 つ以上の式で関数のウィンドウを定義します。 ORDER BY *order\$1list* 各パーティション内の行をソートします。PARTITION BY 句が指定されていない場合、ORDER BY はテーブル全体をソートします。ORDER BY 句を指定する場合、*frame\$1clause* も指定する必要があります。 FIRST 関数の結果は、データの順序によって異なります。以下の場合、結果は不確定になります。 + ORDER BY 句が指定されておらず、パーティションに式に使用する 2 つの異なる値が含まれる場合 + 式が ORDER BY リストの同じ値に対応する異なる値を検証する場合。 *frame\$1clause* ORDER BY 句が集計関数に使用される場合、明示的なフレーム句が必要です。フレーム句は順序付けた結果の行のセットを含めるか除外して、関数のウィンドウの行のセットを絞り込みます。フレーム句は ROWS キーワードおよび関連する指定子で構成されます。「[ウィンドウ関数の構文の概要](Window_functions.md#Window_function_synopsis)」を参照してください。 ## 戻り型 これらの関数は、プリミティブAWS Clean Roomsデータ型を使用する式をサポートします。戻り値の型は*式*のデータ型と同じです。 ## 例 次の例は、収容能力によって順序付けられた結果 (高から低) で、VENUE テーブルの各会場の座席数を返します。FIRST 関数は、フレームの最初の行に対応する会場の名前を選択するために使用されます。この場合は、座席数が最も多い行です。結果は州によってパーティションで分割されるため、VENUESTATE 値が変更されると、新しい最初の値が選択されます。ウィンドウフレームはバインドされていないため、同じ最初の値が各パーティションの行ごとに選択されます。 カリフォルニアでは、`Qualcomm Stadium`が最高座席数 (`70561`) であるため、この名前は `CA` パーティションのすべての行に対する最初の値です。 ``` select venuestate, venueseats, venuename, first(venuename) over(partition by venuestate order by venueseats desc rows between unbounded preceding and unbounded following) from (select * from venue where venueseats >0) order by venuestate; venuestate | venueseats | venuename | first -----------+------------+--------------------------------+------------------------------ CA | 70561 | Qualcomm Stadium | Qualcomm Stadium CA | 69843 | Monster Park | Qualcomm Stadium CA | 63026 | McAfee Coliseum | Qualcomm Stadium CA | 56000 | Dodger Stadium | Qualcomm Stadium CA | 45050 | Angel Stadium of Anaheim | Qualcomm Stadium CA | 42445 | PETCO Park | Qualcomm Stadium CA | 41503 | AT&T Park | Qualcomm Stadium CA | 22000 | Shoreline Amphitheatre | Qualcomm Stadium CO | 76125 | INVESCO Field | INVESCO Field CO | 50445 | Coors Field | INVESCO Field DC | 41888 | Nationals Park | Nationals Park FL | 74916 | Dolphin Stadium | Dolphin Stadium FL | 73800 | Jacksonville Municipal Stadium | Dolphin Stadium FL | 65647 | Raymond James Stadium | Dolphin Stadium FL | 36048 | Tropicana Field | Dolphin Stadium ... ``` # FIRST\$1VALUE ウィンドウ関数 順序付けられた行のセットとすると、FIRST\$1VALUE はウィンドウフレームの最初の行に関して指定された式の値を返します。 フレームの最後の行を選択する方法については、「[LAST\$1VALUE ウィンドウ関数](WF_last_value.md)」を参照してください。 ## 構文 ``` FIRST_VALUE( expression )[ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] ) ``` ## 引数 *expression* 関数の対象となる列または式。 IGNORE NULLS このオプションが FIRST\$1VALUE で使用される場合、関数は NULL ではないフレームの最初の値 (値がすべて NULL の場合は NULL) を返します。 RESPECT NULLS が使用する行の決定に null 値を含めるAWS Clean Rooms必要があることを示します。IGNORE NULLS を指定しない場合、RESPECT NULLS はデフォルトでサポートされます。 OVER 関数にウィンドウ句を導入します。 PARTITION BY *expr\$1list* 1 つ以上の式で関数のウィンドウを定義します。 ORDER BY *order\$1list* 各パーティション内の行をソートします。PARTITION BY 句が指定されていない場合、ORDER BY はテーブル全体をソートします。ORDER BY 句を指定する場合、*frame\$1clause* も指定する必要があります。 FIRST\$1VALUE 関数の結果は、データの並び順によって異なります。以下の場合、結果は不確定になります。 + ORDER BY 句が指定されておらず、パーティションに式に使用する 2 つの異なる値が含まれる場合 + 式が ORDER BY リストの同じ値に対応する異なる値を検証する場合。 *frame\$1clause* ORDER BY 句が集計関数に使用される場合、明示的なフレーム句が必要です。フレーム句は順序付けた結果の行のセットを含めるか除外して、関数のウィンドウの行のセットを絞り込みます。フレーム句は ROWS キーワードおよび関連する指定子で構成されます。「[ウィンドウ関数の構文の概要](Window_functions.md#Window_function_synopsis)」を参照してください。 ## 戻り型 これらの関数は、プリミティブAWS Clean Roomsデータ型を使用する式をサポートします。戻り値の型は*式*のデータ型と同じです。 ## 例 次の例は、収容能力によって順序付けられた結果 (高から低) で、VENUE テーブルの各会場の座席数を返します。FIRST\$1VALUE 関数は、フレームの最初の行 (この場合、最高座席数の行) に対応する会場名を選択するために使用されます。結果は州によってパーティションで分割されるため、VENUESTATE 値が変更されると、新しい最初の値が選択されます。ウィンドウフレームはバインドされていないため、同じ最初の値が各パーティションの行ごとに選択されます。 カリフォルニアでは、`Qualcomm Stadium`が最高座席数 (`70561`) であるため、この名前は `CA` パーティションのすべての行に対する最初の値です。 ``` select venuestate, venueseats, venuename, first_value(venuename) over(partition by venuestate order by venueseats desc rows between unbounded preceding and unbounded following) from (select * from venue where venueseats >0) order by venuestate; venuestate | venueseats | venuename | first_value -----------+------------+--------------------------------+------------------------------ CA | 70561 | Qualcomm Stadium | Qualcomm Stadium CA | 69843 | Monster Park | Qualcomm Stadium CA | 63026 | McAfee Coliseum | Qualcomm Stadium CA | 56000 | Dodger Stadium | Qualcomm Stadium CA | 45050 | Angel Stadium of Anaheim | Qualcomm Stadium CA | 42445 | PETCO Park | Qualcomm Stadium CA | 41503 | AT&T Park | Qualcomm Stadium CA | 22000 | Shoreline Amphitheatre | Qualcomm Stadium CO | 76125 | INVESCO Field | INVESCO Field CO | 50445 | Coors Field | INVESCO Field DC | 41888 | Nationals Park | Nationals Park FL | 74916 | Dolphin Stadium | Dolphin Stadium FL | 73800 | Jacksonville Municipal Stadium | Dolphin Stadium FL | 65647 | Raymond James Stadium | Dolphin Stadium FL | 36048 | Tropicana Field | Dolphin Stadium ... ``` # LAG ウィンドウ関数 LAG ウィンドウ関数は、パーティションの現在の行より上 (前) の指定されたオフセットの行の値を返します。 ## 構文 ``` LAG (value_expr [, offset ]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering ) ``` ## 引数 *value\$1expr* 関数の対象となる列または式。 *offset* 値を返す現在の行より前の行数を指定するオプションのパラメータ。オフセットは整数の定数、または整数を検証する式にすることができます。オフセットを指定しない場合、 AWS Clean Roomsをデフォルト値`1`として使用します。`0` のオフセットは現在の行を示します。 IGNORE NULLS が使用する行を決定する際に null 値をAWS Clean Roomsスキップする必要があることを示すオプションの仕様。IGNORE NULLS がリストされていない場合、Null 値が含まれます。 NVL または COALESCE 式を使用し、Null 値を別の値で置換できます。 RESPECT NULLS が使用する行の決定に null 値を含めるAWS Clean Rooms必要があることを示します。IGNORE NULLS を指定しない場合、RESPECT NULLS はデフォルトでサポートされます。 OVER ウィンドウのパーティションおよび並び順を指定します。OVER 句にウィンドウフレーム仕様を含めることはできません。 PARTITION BY *window\$1partition* OVER 句の各グループのレコードの範囲を設定するオプションの引数。 ORDER BY *window\$1ordering* 各パーティション内の行をソートします。 LAG ウィンドウ関数は、任意のAWS Clean Roomsデータ型を使用する式をサポートします。戻り値の型は *value\$1expr* の型と同じです。 ## 例 次の例は、購入者 ID 3 の購入者に販売されたチケット数と購入者 3 がチケットを購入した時刻を示します。購入者 3 の以前の販売と各販売を比較するには、クエリは販売ごとに以前の販売数を返します。2008 年 1 月 16 日より前に購入されていないため、最初の以前の販売数は Null です。 ``` select buyerid, saletime, qtysold, lag(qtysold,1) over (order by buyerid, saletime) as prev_qtysold from sales where buyerid = 3 order by buyerid, saletime; buyerid | saletime | qtysold | prev_qtysold ---------+---------------------+---------+-------------- 3 | 2008-01-16 01:06:09 | 1 | 3 | 2008-01-28 02:10:01 | 1 | 1 3 | 2008-03-12 10:39:53 | 1 | 1 3 | 2008-03-13 02:56:07 | 1 | 1 3 | 2008-03-29 08:21:39 | 2 | 1 3 | 2008-04-27 02:39:01 | 1 | 2 3 | 2008-08-16 07:04:37 | 2 | 1 3 | 2008-08-22 11:45:26 | 2 | 2 3 | 2008-09-12 09:11:25 | 1 | 2 3 | 2008-10-01 06:22:37 | 1 | 1 3 | 2008-10-20 01:55:51 | 2 | 1 3 | 2008-10-28 01:30:40 | 1 | 2 (12 rows) ``` # LAST ウィンドウ関数 順序付けられた行のセットがある場合、LAST 関数はフレーム内の最後の行に関する式の値を返します。 フレームの最初の行を選択する方法については、「[最初のウィンドウ関数](WF_FIRST.md)」を参照してください。 ## 構文 ``` LAST( expression )[ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] ) ``` ## 引数 *expression* 関数の対象となる列または式。 IGNORE NULLS この関数は NULL ではないフレームの最後の値 (値がすべて NULL の場合は NULL) を返します。 RESPECT NULLS が使用する行の決定に null 値を含めるAWS Clean Rooms必要があることを示します。IGNORE NULLS を指定しない場合、RESPECT NULLS はデフォルトでサポートされます。 OVER 関数にウィンドウ句を導入します。 PARTITION BY *expr\$1list* 1 つ以上の式で関数のウィンドウを定義します。 ORDER BY *order\$1list* 各パーティション内の行をソートします。PARTITION BY 句が指定されていない場合、ORDER BY はテーブル全体をソートします。ORDER BY 句を指定する場合、*frame\$1clause* も指定する必要があります。 結果は、データの並び順によって異なります。以下の場合、結果は不確定になります。 + ORDER BY 句が指定されておらず、パーティションに式に使用する 2 つの異なる値が含まれる場合 + 式が ORDER BY リストの同じ値に対応する異なる値を検証する場合。 *frame\$1clause* ORDER BY 句が集計関数に使用される場合、明示的なフレーム句が必要です。フレーム句は順序付けた結果の行のセットを含めるか除外して、関数のウィンドウの行のセットを絞り込みます。フレーム句は ROWS キーワードおよび関連する指定子で構成されます。「[ウィンドウ関数の構文の概要](Window_functions.md#Window_function_synopsis)」を参照してください。 ## 戻り型 これらの関数は、プリミティブAWS Clean Roomsデータ型を使用する式をサポートします。戻り値の型は*式*のデータ型と同じです。 ## 例 次の例は、収容能力によって順序付けられた結果 (高から低) で、VENUE テーブルの各会場の座席数を返します。LAST 関数は、フレームの最後の行に対応する会場の名前を選択するために使用されます。この場合は、座席数が最も少ない行です。結果は州によってパーティションで分割されるため、VENUESTATE 値が変更されると、新しい最後の値が選択されます。ウィンドウフレームはバインドされていないため、同じ最後の値が各パーティションの行ごとに選択されます。 カリフォルニアでは、`Shoreline Amphitheatre`の座席数が一番低い (`22000`) ため、この値がパーティションのすべての行に返されます。 ``` select venuestate, venueseats, venuename, last(venuename) over(partition by venuestate order by venueseats desc rows between unbounded preceding and unbounded following) from (select * from venue where venueseats >0) order by venuestate; venuestate | venueseats | venuename | last -----------+------------+--------------------------------+------------------------------ CA | 70561 | Qualcomm Stadium | Shoreline Amphitheatre CA | 69843 | Monster Park | Shoreline Amphitheatre CA | 63026 | McAfee Coliseum | Shoreline Amphitheatre CA | 56000 | Dodger Stadium | Shoreline Amphitheatre CA | 45050 | Angel Stadium of Anaheim | Shoreline Amphitheatre CA | 42445 | PETCO Park | Shoreline Amphitheatre CA | 41503 | AT&T Park | Shoreline Amphitheatre CA | 22000 | Shoreline Amphitheatre | Shoreline Amphitheatre CO | 76125 | INVESCO Field | Coors Field CO | 50445 | Coors Field | Coors Field DC | 41888 | Nationals Park | Nationals Park FL | 74916 | Dolphin Stadium | Tropicana Field FL | 73800 | Jacksonville Municipal Stadium | Tropicana Field FL | 65647 | Raymond James Stadium | Tropicana Field FL | 36048 | Tropicana Field | Tropicana Field ... ``` # LAST\$1VALUE ウィンドウ関数 順序付けられた行のセットを考慮し、LAST\$1VALUE 関数は、フレームの最後の行に関する式の値を返します。 フレームの最初の行を選択する方法については、「[FIRST\$1VALUE ウィンドウ関数](WF_first_value.md)」を参照してください。 ## 構文 ``` LAST_VALUE( expression )[ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] ) ``` ## 引数 *expression* 関数の対象となる列または式。 IGNORE NULLS この関数は NULL ではないフレームの最後の値 (値がすべて NULL の場合は NULL) を返します。 RESPECT NULLS が使用する行の決定に null 値を含めるAWS Clean Rooms必要があることを示します。IGNORE NULLS を指定しない場合、RESPECT NULLS はデフォルトでサポートされます。 OVER 関数にウィンドウ句を導入します。 PARTITION BY *expr\$1list* 1 つ以上の式で関数のウィンドウを定義します。 ORDER BY *order\$1list* 各パーティション内の行をソートします。PARTITION BY 句が指定されていない場合、ORDER BY はテーブル全体をソートします。ORDER BY 句を指定する場合、*frame\$1clause* も指定する必要があります。 結果は、データの並び順によって異なります。以下の場合、結果は不確定になります。 + ORDER BY 句が指定されておらず、パーティションに式に使用する 2 つの異なる値が含まれる場合 + 式が ORDER BY リストの同じ値に対応する異なる値を検証する場合。 *frame\$1clause* ORDER BY 句が集計関数に使用される場合、明示的なフレーム句が必要です。フレーム句は順序付けた結果の行のセットを含めるか除外して、関数のウィンドウの行のセットを絞り込みます。フレーム句は ROWS キーワードおよび関連する指定子で構成されます。「[ウィンドウ関数の構文の概要](Window_functions.md#Window_function_synopsis)」を参照してください。 ## 戻り型 これらの関数は、プリミティブAWS Clean Roomsデータ型を使用する式をサポートします。戻り値の型は*式*のデータ型と同じです。 ## 例 次の例は、収容能力によって順序付けられた結果 (高から低) で、VENUE テーブルの各会場の座席数を返します。LAST\$1VALUE 関数は、フレームの最後の行 (この場合、最小座席数の行) に対応する会場名を選択するために使用されます。結果は州によってパーティションで分割されるため、VENUESTATE 値が変更されると、新しい最後の値が選択されます。ウィンドウフレームはバインドされていないため、同じ最後の値が各パーティションの行ごとに選択されます。 カリフォルニアでは、`Shoreline Amphitheatre`の座席数が一番低い (`22000`) ため、この値がパーティションのすべての行に返されます。 ``` select venuestate, venueseats, venuename, last_value(venuename) over(partition by venuestate order by venueseats desc rows between unbounded preceding and unbounded following) from (select * from venue where venueseats >0) order by venuestate; venuestate | venueseats | venuename | last_value -----------+------------+--------------------------------+------------------------------ CA | 70561 | Qualcomm Stadium | Shoreline Amphitheatre CA | 69843 | Monster Park | Shoreline Amphitheatre CA | 63026 | McAfee Coliseum | Shoreline Amphitheatre CA | 56000 | Dodger Stadium | Shoreline Amphitheatre CA | 45050 | Angel Stadium of Anaheim | Shoreline Amphitheatre CA | 42445 | PETCO Park | Shoreline Amphitheatre CA | 41503 | AT&T Park | Shoreline Amphitheatre CA | 22000 | Shoreline Amphitheatre | Shoreline Amphitheatre CO | 76125 | INVESCO Field | Coors Field CO | 50445 | Coors Field | Coors Field DC | 41888 | Nationals Park | Nationals Park FL | 74916 | Dolphin Stadium | Tropicana Field FL | 73800 | Jacksonville Municipal Stadium | Tropicana Field FL | 65647 | Raymond James Stadium | Tropicana Field FL | 36048 | Tropicana Field | Tropicana Field ... ``` # LEAD ウィンドウ関数 LEAD ウィンドウ関数は、パーティションの現在の行より下 (後) の指定されたオフセットの行の値を返します。 ## 構文 ``` LEAD (value_expr [, offset ]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering ) ``` ## 引数 *value\$1expr* 関数の対象となる列または式。 *offset* 値を返す現在の行より下の行数を指定するオプションのパラメータ。オフセットは整数の定数、または整数を検証する式にすることができます。オフセットを指定しない場合、 AWS Clean Roomsをデフォルト値`1`として使用します。`0` のオフセットは現在の行を示します。 IGNORE NULLS が使用する行を決定する際に null 値をAWS Clean Roomsスキップする必要があることを示すオプションの仕様。IGNORE NULLS がリストされていない場合、Null 値が含まれます。 NVL または COALESCE 式を使用し、Null 値を別の値で置換できます。 RESPECT NULLS が使用する行の決定に null 値を含めるAWS Clean Rooms必要があることを示します。IGNORE NULLS を指定しない場合、RESPECT NULLS はデフォルトでサポートされます。 OVER ウィンドウのパーティションおよび並び順を指定します。OVER 句にウィンドウフレーム仕様を含めることはできません。 PARTITION BY *window\$1partition* OVER 句の各グループのレコードの範囲を設定するオプションの引数。 ORDER BY *window\$1ordering* 各パーティション内の行をソートします。 LEAD ウィンドウ関数は、任意のAWS Clean Roomsデータ型を使用する式をサポートしています。戻り値の型は *value\$1expr* の型と同じです。 ## 例 次の例は、チケットが 2008 年 1 月 1 日および 2008 年 1 月 2 日に販売された SALES テーブルのイベントの手数料、および次の販売のチケット販売に支払った手数料を示します。 ``` select eventid, commission, saletime, lead(commission, 1) over (order by saletime) as next_comm from sales where saletime between '2008-01-01 00:00:00' and '2008-01-02 12:59:59' order by saletime; eventid | commission | saletime | next_comm ---------+------------+---------------------+----------- 6213 | 52.05 | 2008-01-01 01:00:19 | 106.20 7003 | 106.20 | 2008-01-01 02:30:52 | 103.20 8762 | 103.20 | 2008-01-01 03:50:02 | 70.80 1150 | 70.80 | 2008-01-01 06:06:57 | 50.55 1749 | 50.55 | 2008-01-01 07:05:02 | 125.40 8649 | 125.40 | 2008-01-01 07:26:20 | 35.10 2903 | 35.10 | 2008-01-01 09:41:06 | 259.50 6605 | 259.50 | 2008-01-01 12:50:55 | 628.80 6870 | 628.80 | 2008-01-01 12:59:34 | 74.10 6977 | 74.10 | 2008-01-02 01:11:16 | 13.50 4650 | 13.50 | 2008-01-02 01:40:59 | 26.55 4515 | 26.55 | 2008-01-02 01:52:35 | 22.80 5465 | 22.80 | 2008-01-02 02:28:01 | 45.60 5465 | 45.60 | 2008-01-02 02:28:02 | 53.10 7003 | 53.10 | 2008-01-02 02:31:12 | 70.35 4124 | 70.35 | 2008-01-02 03:12:50 | 36.15 1673 | 36.15 | 2008-01-02 03:15:00 | 1300.80 ... (39 rows) ``` # PERCENT\$1RANK ウィンドウ関数 指定の行のパーセントランクを計算します。パーセントランクは、以下の式を使用して特定されます。 `(x - 1) / (the number of rows in the window or partition - 1)` ここで、*x* は現在の行のランクです。以下のデータセットは、この式の使用方法を示しています。 ``` Row# Value Rank Calculation PERCENT_RANK 1 15 1 (1-1)/(7-1) 0.0000 2 20 2 (2-1)/(7-1) 0.1666 3 20 2 (2-1)/(7-1) 0.1666 4 20 2 (2-1)/(7-1) 0.1666 5 30 5 (5-1)/(7-1) 0.6666 6 30 5 (5-1)/(7-1) 0.6666 7 40 7 (7-1)/(7-1) 1.0000 ``` 戻り値の範囲は、0～1 (0 と 1 を含みます) です。どのセットも、最初の行の PERCENT\$1RANK は 0 になります。 ## 構文 ``` PERCENT_RANK () OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] ) ``` ## 引数 ( ) この関数は引数を受け取りませんが、空のかっこは必要です。 OVER ウィンドウのパーティションを指定する句。OVER 句にウィンドウフレーム仕様を含めることはできません。 PARTITION BY *partition\$1expression* 省略可能。OVER 句の各グループのレコードの範囲を設定する式。 ORDER BY *order\$1list* 省略可能。パーセントランクを計算する式。式は、数値データ型を含んでいるか、そのデータ型に暗黙的に変換できる必要があります。ORDER BY を省略した場合、すべての行について戻り値は 0 です。 ORDER BY で一意のソートが行われない場合、行の順序は不確定になります。詳細については、「[ウィンドウ関数用データの一意の並び順](Window_functions.md#Examples_order_by_WF)」を参照してください。 ## 戻り型 FLOAT8 ## 例 以下の例では、各販売者の販売数量のパーセントランクを計算します。 ``` select sellerid, qty, percent_rank() over (partition by sellerid order by qty) from winsales; sellerid qty percent_rank ---------------------------------------- 1 10.00 0.0 1 10.64 0.5 1 30.37 1.0 3 10.04 0.0 3 15.15 0.33 3 20.75 0.67 3 30.55 1.0 2 20.09 0.0 2 20.12 1.0 4 10.12 0.0 4 40.23 1.0 ``` WINSALES テーブルの説明については、「[ウィンドウ関数例のサンプルテーブル](Window_functions.md#Window_function_example)」を参照してください。 # RANK ウィンドウ関数 RANK ウィンドウ関数は、OVER 句の ORDER BY 式に基づいて、値のグループの値のランクを決定します。オプションの PARTITION BY 句がある場合、ランク付けは行のグループごとにリセットされます。ランク付け条件の値が等しい行は同じランクを受け取ります。 は、結合された行の数を結合されたランクAWS Clean Roomsに追加して次のランクを計算するため、ランクは連続した数値ではない可能性があります。例えば、2 行が 1 位にランク付けされると、次のランクは 3 位になります。 RANK と [DENSE\$1RANK ウィンドウ関数](WF_DENSE_RANK.md) では異なる点があり、DENSE\$1RANK では、2 行以上で同点となった場合、ランク付けされた値の順位に差はありません。例えば、2 行が 1 位にランク付けされると、次のランクは 2 位になります。 同じクエリに PARTITION BY および ORDER BY 句のあるランク付け関数を使用することができます。 ## 構文 ``` RANK () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] ) ``` ## 引数 ( ) この関数は引数を受け取りませんが、空のかっこは必要です。 OVER RANK 関数のウィンドウ句。 PARTITION BY *expr\$1list* 省略可能。ウィンドウを定義する 1 つ以上の式。 ORDER BY *order\$1list* 省略可能。ランク付けの値が基とする列を定義します。PARTITION BY が指定されていない場合、ORDER BY はテーブル全体を使用します。ORDER BY を省略した場合、すべての行について戻り値は 1 です。 ORDER BY で一意のソートが行われない場合、行の順序は不確定になります。詳細については、「[ウィンドウ関数用データの一意の並び順](Window_functions.md#Examples_order_by_WF)」を参照してください。 ## 戻り型 INTEGER ## 例 次の例では、販売数量でテーブルで順序付けして (デフォルトは昇順)、行ごとにランクを割り当てます。1 のランク値は、もっとも高いランクの値です。結果はウィンドウ関数の結果が提供された後にソートされます。 ``` select salesid, qty, rank() over (order by qty) as rnk from winsales order by 2,1; salesid | qty | rnk --------+-----+----- 10001 | 10 | 1 10006 | 10 | 1 30001 | 10 | 1 40005 | 10 | 1 30003 | 15 | 5 20001 | 20 | 6 20002 | 20 | 6 30004 | 20 | 6 10005 | 30 | 9 30007 | 30 | 9 40001 | 40 | 11 (11 rows) ``` この例の外部 ORDER BY 句には、このクエリが実行されるたびに が一貫してソートされた結果をAWS Clean Rooms返すように、列 2 と 1 が含まれていることに注意してください。例えば、販売 ID 10001 および 10006 の行は、QTY と RNK の値が同じです。列 1 によって最終的な結果セットを順序付けると、行 10001 は常に 10006 の前になります。WINSALES テーブルの説明については、「[ウィンドウ関数例のサンプルテーブル](Window_functions.md#Window_function_example)」を参照してください。 次の例では、ウィンドウ関数 (`order by qty desc`) の順序付けは逆順になります。これで、最高ランク値が最大の QTY 値に適用されます。 ``` select salesid, qty, rank() over (order by qty desc) as rank from winsales order by 2,1; salesid | qty | rank ---------+-----+----- 10001 | 10 | 8 10006 | 10 | 8 30001 | 10 | 8 40005 | 10 | 8 30003 | 15 | 7 20001 | 20 | 4 20002 | 20 | 4 30004 | 20 | 4 10005 | 30 | 2 30007 | 30 | 2 40001 | 40 | 1 (11 rows) ``` WINSALES テーブルの説明については、「[ウィンドウ関数例のサンプルテーブル](Window_functions.md#Window_function_example)」を参照してください。 次の例では、SELLERID によってテーブルをパーティション分割し、数量で各パーティションを順序付けして (降順)、行ごとにランクを割り当てます。結果はウィンドウ関数の結果が提供された後にソートされます。 ``` select salesid, sellerid, qty, rank() over (partition by sellerid order by qty desc) as rank from winsales order by 2,3,1; salesid | sellerid | qty | rank --------+----------+-----+----- 10001 | 1 | 10 | 2 10006 | 1 | 10 | 2 10005 | 1 | 30 | 1 20001 | 2 | 20 | 1 20002 | 2 | 20 | 1 30001 | 3 | 10 | 4 30003 | 3 | 15 | 3 30004 | 3 | 20 | 2 30007 | 3 | 30 | 1 40005 | 4 | 10 | 2 40001 | 4 | 40 | 1 (11 rows) ``` # ROW\$1NUMBER ウィンドウ関数 OVER 句の ORDER BY 式に基づいて、行グループ内における (1 からカウントした) 現在の行の序数が決まります。オプションの PARTITION BY 句がある場合、序数は行グループごとにリセットされます。ORDER BY 式で同じ値を持つ行には、確定的でない方法で異なる行番号が割り当てられます。 ## 構文 ``` ROW_NUMBER () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] ) ``` ## 引数 ( ) この関数は引数を受け取りませんが、空のかっこは必要です。 OVER ROW\$1NUMBER 関数のウィンドウ句。 PARTITION BY *expr\$1list* 省略可能。ROW\$1NUMBER 関数を定義する 1 つ以上の式。 ORDER BY *order\$1list* 省略可能。行番号の基になる列を定義する式。PARTITION BY が指定されていない場合、ORDER BY はテーブル全体を使用します。 ORDER BY で一意の順序付けが行われない、または省略した場合、行の順序は不確定になります。詳細については、「[ウィンドウ関数用データの一意の並び順](Window_functions.md#Examples_order_by_WF)」を参照してください。 ## 戻り型 BIGINT ## 例 次の例では、SELLERID によってテーブルをパーティション化し、QTY によって各パーティションを (昇順で) 順序付けし、各行に行番号を割り当てます。結果はウィンドウ関数の結果が提供された後にソートされます。 ``` select salesid, sellerid, qty, row_number() over (partition by sellerid order by qty asc) as row from winsales order by 2,4; salesid | sellerid | qty | row ---------+----------+-----+----- 10006 | 1 | 10 | 1 10001 | 1 | 10 | 2 10005 | 1 | 30 | 3 20001 | 2 | 20 | 1 20002 | 2 | 20 | 2 30001 | 3 | 10 | 1 30003 | 3 | 15 | 2 30004 | 3 | 20 | 3 30007 | 3 | 30 | 4 40005 | 4 | 10 | 1 40001 | 4 | 40 | 2 (11 rows) ``` WINSALES テーブルの説明については、「[ウィンドウ関数例のサンプルテーブル](Window_functions.md#Window_function_example)」を参照してください。