翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。 # ウィンドウ関数 ウィンドウ関数を使用すると、分析的なビジネスクエリをより効率的に作成できます。ウィンドウ関数はパーティションまたは結果セットの「ウィンドウ」で演算し、ウィンドウのすべての行に値を返します。それに対して、ウィンドウ以外の関数は、結果セットの行ごとに計算を実行します。結果の行を集計するグループ関数とは異なり、ウィンドウ関数はテーブル式のすべての行を保持します。 戻り値はこのウィンドウの行セットの値を使用して計算されます。ウィンドウはテーブルの各行に、追加の属性を計算するために使用する行のセットを定義します。ウィンドウはウィンドウ仕様 (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\_list* は次のとおりです。 ``` expression | column_name [, expr_list ] ``` *order\_list* は次のとおりです。 ``` expression | column_name [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, order_list ] ``` *frame\_clause* は次のとおりです。 ``` 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\_list* (オプション) PARTITION BY 句は結果セットをパーティションに再分割します。これは GROUP BY 句と似ています。パーティション句が存在する場合、関数は各パーティションの行に対して計算されます。パーティション句が指定されていない場合、1 つのパーティションにテーブル全体が含まれ、関数は完全なテーブルに対して計算されます。 ランク付け関数 DENSE\_RANK、NTILE、RANK、および ROW\_NUMBER では、結果セットのすべての行でグローバルな比較が必要です。PARTITION BY clauseを使用すると、クエリオプティマイザーは、パーティションに応じて複数のスライスにワークロードを分散させることにより、個々の集計を並列で実行できます。PARTITION BY 句がない場合、集計ステップを 1 つのスライスで順次実行する必要があり、特に大規模なクラスターではパフォーマンスに大きな悪影響を与えることがあります。 AWS Clean Roomsは、PARTITION BY 句の文字列リテラルをサポートしていません。 ORDER BY *order\_list* (オプション) ウィンドウ関数は、ORDER BY で順序仕様に従ってソートされた各パーティション内の行に適用されます。この ORDER BY 句は、*frame\_clause* の 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\_name* パーティション化または順序付けされる列の名前。 ASC \| DESC 次のように、式のソート順を定義するオプション: + ASC: 昇順 (数値の場合は低から高、文字列の場合は「A」から「Z」など) オプションを指定しない場合、データはデフォルトでは昇順にソートされます。 + DESC: 降順 (数値の場合は高から低、文字列の場合は「Z」から「A」)。 NULLS FIRST \| NULLS LAST NULL を NULL 以外の値より先に順序付けするか、NULL 以外の値の後に順序付けするかを指定するオプション。デフォルトでは、NULL は昇順ではソートされて最後にランク付けされ、降順ではソートされて最初にランク付けされます。 *frame\_clause* 集計関数では、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\_DIST ウィンドウ関数](WF_CUME_DIST.md) + [DENSE\_RANK ウィンドウ関数](WF_DENSE_RANK.md) + [最初のウィンドウ関数](WF_FIRST.md) + [FIRST\_VALUE ウィンドウ関数](WF_first_value.md) + [LAG ウィンドウ関数](WF_LAG.md) + [LAST ウィンドウ関数](WF-LAST.md) + [LAST\_VALUE ウィンドウ関数](WF_last_value.md) + [LEAD ウィンドウ関数](WF_LEAD.md) サポートされる集計関数は次のとおりです。 + [DENSE\_RANK ウィンドウ関数](WF_DENSE_RANK.md) + [PERCENT\_RANK ウィンドウ関数](WF_PERCENT_RANK.md) + [RANK ウィンドウ関数](WF_RANK.md) + [ROW\_NUMBER ウィンドウ関数](WF_ROW_NUMBER.md) ## ウィンドウ関数例のサンプルテーブル ウィンドウ関数別の例をそれぞれの説明と共に参照できます。一部の例で使用している WINSALES という名前のテーブルには、以下のテーブルのように 11 行が含まれています。 | SALESID | DATEID | SELLERID | BUYERID | QTY | QTY\_SHIPPED | | --- | --- | --- | --- | --- | --- | | 30001 | 8/2/2003 | 3 | B | 10 | 10 | | 10001 | 12/24/2003 | 1 | C | 10 | 10 | | 10005 | 12/24/2003 | 1 | A | 30 | | | 40001 | 1/9/2004 | 4 | A | 40 | | | 10006 | 1/18/2004 | 1 | C | 10 | | | 20001 | 2/12/2004 | 2 | B | 20 | 20 | | 40005 | 2/12/2004 | 4 | A | 10 | 10 | | 20002 | 2/16/2004 | 2 | C | 20 | 20 | | 30003 | 4/18/2004 | 3 | B | 15 | | | 30004 | 4/18/2004 | 3 | B | 20 | | | 30007 | 9/7/2004 | 3 | C | 30 | |