WINDOW関数

WINDOW関数は、テーブルシートでデータを分析するための一連の関数です。ウィンドウ （行のグループ） 全体にわたって、現在行に何らかの関連がある計算を実行し、計算結果の列を生成します。関数の評価が行われる行は、現在行と呼ばれます。

WINDOW関数は、特定のウィンドウに対して集計、ランキング、および分析関数を実行し、行ごとに結果を生成します。

メモ：

• WINDOW関数によって生成される結果は、テーブルシートの行順序に影響を与える可能性があります。

  テーブルシートがソートまたはグループ化されている場合、結果はそのままになります。

• テーブルシートの階層データにツリー構造が含まれる場合、WINDOW関数が無効になります。

• テーブルシートフィルタは、WINDOW関数が評価された後に実行され、特定の行のみをフィルタ処理します。

WINDOW は、WINDOW関数でのみ使用されます。WINDOW関数は、デフォルトで行全体をウィンドウとみなします。

構文

WINDOW(window_function, [partitionby_function], [orderby_function], [frame_function])

引数

有効な引数は次のとおりです。

WINDOW

WINDOW関数は、関連するWINDOW関数を適用する前に、ウィンドウ（行の集合）をパーティション分割、ソート、および制限を決定します。

WINDOW 関数は、PARTITIONBY パラメータと ORDERBY パラメータを使用して、行の順序を変更します。

ビューに複数の WINDOW 関数が適用されている場合、行全体の順序が PARTITIONBY および ORDERBY パラメータによって並べ替えられるため、PARTITIONBY および ORDERBY の値は同じであることに注意してください。それ以外の場合は、行全体に最後に適用された WINDOW関数の順序を適用します。

例

WINDOW(ROWNUMBER(), PARTITIONBY([country]))

利用可能なすべてのWINDOW関数の詳細については、「WINDOW関数一覧 」を参照してください。

PARTITIONBY

PARTITIONBY 関数は、行を昇順でパーティションに分割し、 各パーティションにWINDOW関数が個別に適用されます。PARTITIONBY が指定されていない場合は、行全体がウィンドウとして扱われます。PARTITIONBY には 、1 つ以上の列式 (フィールド名または式) が必要です。

構文

PARTITIONBY(field_function [, field_function [, ... ] ])

引数

有効な引数は次のとおりです。

例

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])))

ORDERBY

ORDERBY は、各パーティション内の行の論理的な順序を定義します。指定されるウィンドウとWINDOW関数の計算に影響を与えます。ORDERBY には 、1 つ以上の列式 （フィールド名または式） が必要です。

構文

ORDERBY(field_function [, field_function [, ... ] ])

引数

有効な引数は次のとおりです。

また、ORDERASCとORDERDESCを使用して、データを昇順または降順に並べ替えることもできます。 デフォルトのソート順は 昇順（ORDERASC） で、NULL 値を最小値として扱います。

type=warning

メモ：

• ORDERBY が指定されていない場合、WINDOW関数は FRAMERANGE(-1, -1) に等しいパーティション内のすべての行を使用します。

• ORDERBY が指定されているが、 FRAMEROWS/FRAMERANGE が指定されていない場合、FRAMERANGE(-1, [@]) は、WINDOW関数を計算するためのウィンドウ を限定するデフォルトの範囲式になります。

構文

ORDERASC(field function)

ORDERDESC(field function)

例

WINDOW(SUM([Sales]), ORDERBY([Product], ORDERDESC(QUARTER([@OrderDate]))))

FRAME

FRAMEは、現在行に対してパーティション内のウィンドウに行を集約するための開始点と終了点を指定します。WINDOW関数は、ウィンドウに指定された行グループを使用します。ウィンドウは、開始行、終了行、および現在行で囲まれます。FRAME 関数は、FRAMEROWS または FRAMERANGEとして定義できます。

FRAMEROWS

FRAMEROWS を使用すると、現在行の前後に負でない整数の行数を指定することで、ウィンドウ内の行の集合を限定できます。前後の行数のいずれかが現在のパーティション範囲外にある場合は、パーティションの開始行または終了行が使用されます。また、前後の行数が 「-1」として指定されている場合も、同じ結果が得られます。ただし、前後数の行数がパーティション範囲外にある場合、行が返されません。

type=warning

メモ：FRAMEROWS 関数で、 2 番目のパラメータが指定されない場合、デフォルト値は [@] に設定されます。

構文

FRAMEROWS(beginning_function, [ending_function], [exclude_mode])

引数

有効な引数は次のとおりです。

例

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(QUARTER([@OrderDate])), FRAMEROWS([@-1], [@]))

次の図は、WINDOW関数の集計関数を使用して、各製品の移動平均収益と収益傾向を取得する方法を示しています。

FRAMERANGE

FRAMERANGEは、ORDERBY列に構成される対等行を中心に、現在行と同じ値の距離として非負の数値を指定することにより、ウィンドウの範囲を制限します。

FRAMERANGE 関数には 、２つのパラメータがあり、各パラメータは現在行の対等行との前後の距離を示します。

フレーム範囲は閉区間です。ORDERBY で最初列にデータ型を数値に指定する必要があります。ただし、複数のソート列がある場合は、-1 と [@] のみ指定できます。

ORDERBY で FRAMERANGE を使用

• FRAMERANGEは、現在行の値と同じ値を持つ対等行を検索するために、ORDERBYに1つの数値型の列を指定する必要があります。

• ORDERBY が削除された場合、FRAMERANGE を指定しても、デフォルトのフレーム式が FRAMERANGE(-1, -1) になります。

• ORDERBY に複数列が指定された場合、FRAMERANGE で-1 と [@] のみを使用でき、[@-/+n] のデフォルト値は -1 です。現在同じ値を持つ行は、集計された列値によって定義されます。

NULL 値で FRAMERANGE を使用

• 順序付けされる列に NULL 値がある場合、NULL 値を持つ行がパーティション内の上部/下部に並んで配置されます。

• 現在行の値と同じ値を持つ対等行の値が NULL の場合、[@+/-n] は [@+/-0] と同様扱いで、どちらも [@] と見なします。

• 現在行の値と同じ値を持つ対等行の値が NULL でない場合、[@+/-n] をNULLを見つかるまで検索します。

構文

FRAMERANGE(beginning_function, [ending_function], [exclude_mode])

引数

有効な引数は次のとおりです。

例

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(QUARTER([@OrderDate])), FRAMERANGE([@-1], [@]))

次の図は、WINDOW関数を使用し、各製品の平均収益と売上傾向を取得する方法を示しています。

FRAMEGROUPS

FRAMEGROUPSは、ORDERBY列に構成される対等のグループを中心に、現在のグループと同じ値の距離の開始点と終了点を指定します。

パーティションの境界値を超えたグループ数を設定すると、前後のグループ数が -1 の場合は開始/終了グループにフォールバックしますが、前後のグループ数の両方が境界値を超えた場合は何も返されません。

type=warning

メモ: FRAMEGROUPS 関数で、 2 番目のパラメータが指定されない場合、デフォルト値は [@] に設定されます。

構文

FRAMEGROUPS(beginning_function, [ending_function], [exclude_mode])

引数

この関数の引数は次のとおりです。

例

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEGROUPS([@-1], [@]))

ORDERBYを使用したFRAMEGROUPS

• FRAMEGROUPS では、[@]と同じ値を持つ対等行を検索するために、ORDERBYに1つの数値型の列を指定する必要があります。

• ORDERBY が削除された場合、FRAMEGROUPS が指定されている場合でも、デフォルトのフレーム式は FRAMEGROUPS(-1, -1) になります。

• ORDERBY に複数列が指定された場合、現在行と同じ値を持つ対等行は、結合された列の値に従って定義されます。 例えば、年、四半期、月で並べ替えた場合、現在行の対等行は、それらの同じ列によって識別されます。

EXCLUDE

EXCLUDEはFRAME数式（FRAMEROWS／FRAMERANGE／FRAMEGROUPS）の最後のパラメータで、フレームから特定の部分を除外するかどうかを指定します。EXCLUDEは以下の４つのパターンを指定できます。

EXCLUDE NO OTHERS

除外行がないことを示します。「0」と定義されます。

例

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEGROUPS([@-1], [@], 0))

次の図は、WINDOW関数を使用してフレームを定義する際に、EXCLUDEを「0」に設定し、月次平均収益、各製品の平均収益と売上傾向を取得する方法を示しています。

EXCLUDE CURRENT ROW

現在行が除外されることを示します。 ただし、FRAMEGROUPSとFRAMERANGEの場合はその対等行を除外しません。「1」と定義されます。

例

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEGROUPS([@-1], [@], 1))

次の図は、WINDOW関数を使用してフレームを定義する際に、EXCLUDEを「1」に設定し、月次平均収益、各製品の平均収益と売上傾向を取得する方法を示しています。

EXCLUDE GROUP

現在行とその対等行が除外されることを示します。 FRAMEROWSの場合でも除外します。「2」と定義されます。

例

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEGROUPS([@-1], [@], 2))

次の図は、WINDOW関数を使用してフレームを定義する際に、EXCLUDEを「2」に設定し、月次平均収益、各製品の平均収益と売上傾向を取得する方法を示しています。

EXCLUDE TIES

現在行を除外しないが、その対等行が除外されることを示します。「3」と定義されます。

例

WINDOW(SUM([Sales]), PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEGROUPS([@-1], [@], 3))

次の図は、WINDOW関数を使用してフレームを定義する際に、EXCLUDEを「3」に設定し、月次平均収益、各製品の平均収益と売上傾向を取得する方法を示しています。

Window Chaining

Window Chainingを使用すると、WINDOW関数を利用するときに、ひとつのウィンドウを事前に定義し、新しいウィンドウに再利用する場合にPARTITYIONBYやORDERBY、またはWindow Frameを再指定することができます。

事前に定義されたウィンドウを新しいウィンドウに適用する際に、PARTITIONBY,FRAME,ORDERBYを再利用／上書きすることができます。

例

WINDOWDEF(PARTITIONBY([Product], YEAR([@OrderDate])), ORDERBY(DATEPART([@OrderDate], \"Q\")), FRAMEROWS([@-2], [@], 3))