ウィンドウ関数
詳細モードでは、ウィンドウ関数を使用してステートフルな計算を簡潔に表現できます。ウィンドウ関数は、大規模なデータセットから小さいサブセットを取得して、処理と分析を行います。
ウィンドウ関数は、行のグループに対して動作し、各入力行の戻り値を計算します。
ウィンドウ関数を使用して、次のタスクを実行します。
- •アップストリームまたはダウンストリームの行からデータを取得します。
- •行のグループに基づいて累積合計を計算します。
- •行のグループに基づいて累積平均を計算します。
ウィンドウ関数を定義する前に、［ウィンドウ］タブで次のウィンドウプロパティを設定します。
- フレーム
現在の行の位置からの物理オフセットに基づいて、現在の入力行のフレームに含まれる行を定義します。
集計関数をウィンドウ関数として使用する場合は、フレームを構成します。ウィンドウ関数のLEADとLAGは、個々の行を参照し、フレームを無視します。
- パーティションキー
- 入力行を別々のパーティションに分割します。
パーティションキーを定義しない場合は、すべての行が単一パーティションに属します。
- オーダーキー
- パーティション内の行の順序を決定します。
選択するフィールドによって、パーティション内の行の位置が決まります。オーダーキーは昇順または降順にできます。オーダーキーを定義しない場合、行には特定の順序がありません。
ウィンドウ関数を含む式をパラメータ化することはできません。式がパラメータ化されている場合、マッピングタスクでウィンドウ関数を指定することはできません。
フレーム
フレームは、現在の行に対する相対的な位置に基づいて、現在の入力行の計算に含まれる行を決定します。集計関数をウィンドウ関数として使用する場合は、フレームを構成します。
開始オフセットと終了オフセットは、現在の入力行の前後に表示される行の数を表します。オフセット「0」は、現在の入力行を表します。例えば開始オフセットが-3で終了オフセットが0の場合は、現在の入力行と現在の行より前の3つの行を含むフレームを表します。
次の図は、開始オフセットが-1で終了オフセットが1のフレームを示しています。
すべての入力行に対して、関数はフレーム内の行に対して集計操作を実行します。前図のフレームに対してSUMのような集計式を構成する場合、式はフレーム内の値の合計を計算し、入力行に対して値6000を返します。
現在の入力行を含まないフレームを指定することもできます。例えば開始オフセットが10で終了オフセットが15の場合は、現在の入力行より後の10番目の行から15番目の行までの合計6行が含まれるフレームを表します。
［前のすべての行］および［後のすべての行］というオフセットは、パーティションの最初の行とパーティションの最後の行を表します。例えば、開始オフセットが［前のすべての行］で終了オフセットが-1の場合、フレームには現在の行より前の1行と、それより前のすべての行が含まれます。
次の図は、開始オフセットが0で終了オフセットが［後のすべての行］のフレームを示しています。
フレームオフセットがパーティション外部である場合、集計関数はこのフレームを無視します。フレームのオフセットがパーティションまたはテーブルの内部でない場合、集計関数はパーティション内の行のみを処理します。集計関数により、スキップされた行がログファイルに一覧表示されます。この関数は、スキップされた行に対して、デフォルト値のERROR('トランスフォーメーションエラー')、NULL、または事前定義された定数のいずれかの応答を返します。
例えば、テーブルを販売者ID別、オーダーを数量別のパーティションに分けるとします。開始オフセットを-3に設定し、終了オフセットを4に設定します。
次の図に、現在の入力行のパーティションとフレームを示します。
このフレームには、合計8行が含まれますが、計算はパーティション内に留まります。このフレームを使用してAVG関数を定義した場合、この関数はパーティション内の数量の平均を計算し、18.75を返します。
フレームを定義する場合、以下のルールとガイドラインを考慮します。
- •LEADおよびLAGでは、関数の引数で指定するフレームが使用され、［ウィンドウ］タブで設定するフレームは無視されます。
- •開始オフセットは、終了オフセット以下でなければなりません。
パーティションキーおよびオーダーキー
パーティションキーとオーダーキーを構成して、行のグループを形成し、各パーティション内で行の順序またはシーケンスを定義します。
次のキーを使用して、ウィンドウ内の行のグループ化と順序の指定を行います。
- パーティションキー
- すべての行にわたって計算を実行するのではなく、パーティションの境界を定義するようにパーティションキーを設定します。
パーティションキーを指定しない場合は、すべてのデータが同じパーティションに含まれます。
- オーダーキー
- オーダーキーを使用して、パーティション内の行の順序を決定します。オーダーキーは、パーティション内の特定の行の位置を定義します。
また、データを昇順または降順に並べ替える必要もあります。オーダーキーを指定しない場合、パーティション内の行はランダムに並べられます。
パーティションキーとオーダーキーのウィンドウプロパティを定義するときは、次のルールとガイドラインを考慮してください。
- •階層フィールドはパーティションキーまたはオーダーキーとして使用できません。
- •一意のフィールドをパーティションキーおよびオーダーキーとして定義します。
例
あなたはコーヒーと紅茶の店のオーナーです。売り上げが1番目と2番目のコーヒーと紅茶を計算したいと考えています。
以下の表に、製品、対応する製品カテゴリ、および各製品の収益を示しています。
Product | Category | Revenue |
|---|
Espresso | Coffee | 600 |
Black | Tea | 550 |
Cappuccino | Coffee | 500 |
Americano | Coffee | 600 |
Oolong | Tea | 250 |
Macchiato | Coffee | 300 |
Green | Tea | 450 |
White | Tea | 650 |
|
カテゴリ別にデータを分割し、収益の降順でデータを並べます。
以下の表は、カテゴリに従って2つのパーティションにグループ化されたデータを示しています。各パーティション内では、収益は降順に編成されます。
Product | Category | Revenue |
|---|
Espresso | Coffee | 600 |
Americano | Coffee | 600 |
Cappuccino | Coffee | 500 |
Macchiato | Coffee | 300 |
White | Tea | 650 |
Black | Tea | 550 |
Green | Tea | 450 |
Oolong | Tea | 250 |
|
各パーティション内でMAX関数を実行して、売り上げのよいコーヒーの2つはエスプレッソとアメリカーノであり、売り上げのよいお茶の2つは白茶と紅茶であると判断できます。
例: ウィンドウを使用した有効期限の計算
あなたは自分の担当顧客2社の財務プランに関する情報を持つ銀行家です。各プランに、開始日が関連付けられています。
各顧客のために、次のプランの有効化日付に基づいて、現在のプランの有効期限を知りたいとします。前のプランは新しいプランの開始時に終了するため、前のプランの終了日は、次のプランの開始日マイナス1日です。
次の表に、顧客コード、関連プランコード、各プランの開始日を示します。
CustomerCode | PlanCode | StartDate |
|---|
C1 | 00001 | 2014-10-01 |
C2 | 00002 | 2014-10-01 |
C2 | 00002 | 2014-11-01 |
C1 | 00004 | 2014-10-25 |
C1 | 00001 | 2014-09-01 |
C1 | 00003 | 2014-10-10 |
|
有効期限を計算するには、次のタスクを実行します。
- 1パーティションキーおよびオーダーキーを定義します。
次のウィンドウプロパティを設定して、データを顧客コードでパーティション化し、データを開始日の昇順に並べます。
プロパティ | 値 | 説明 |
|---|
フレーム | 指定されていません。 | LEAD関数は、オフセット引数に基づいて行にアクセスし、フレームを無視します。 |
パーティションキー | CustomerCode。 | 行を顧客コード別にグループ化し、計算が個々の顧客に基づくようにします。 |
オーダーキー | StartDate昇順。 | 開始日の昇順で、データを時系列に配置します。 |
次の表に、顧客コードでグループ化された開始日順のデータを示します。
CustomerCode | PlanCode | StartDate |
|---|
C1 | 00001 | 2014-09-01 |
C1 | 00002 | 2014-10-01 |
C1 | 00003 | 2014-10-10 |
C1 | 00004 | 2014-10-25 |
C2 | 00001 | 2014-10-01 |
C2 | 00002 | 2014-11-01 |
|
- 2ウィンドウ関数の定義
入力ごとに次の行にアクセスするLEAD関数を定義します。
次の計算を実行する式フィールドを設定します。
LEAD ( StartDate, 1, '01-Jan-2100' )
LEAD関数の詳細については、『関数リファレンス』を参照してください。
- 3ADD_TO_DATE関数の定義
アクセスした日付から1日を除算するADD_TO_DATE関数を使用します。
次の計算を実行する式フィールドを設定します。
ADD_TO_DATE ( LEAD ( StartDate, 1, '01-Jan-2100' ), 'DD', -1, )
次のプランの開始日から1日を除算することで、現在のプランの終了日がわかります。
次の表に、各プランの終了日を示します。
CustomerCode | PlanCode | StartDate | EndDate |
|---|
C1 | 00001 | 2014-09-01 | 2014-09-30 |
C1 | 00002 | 2014-10-01 | 2014-10-09 |
C1 | 00003 | 2014-10-10 | 2014-10-24 |
C1 | 00004 | 2014-10-25 | 2099-12-31* |
C2 | 00001 | 2014-10-01 | 2014-10-31 |
C2 | 00002 | 2014-11-01 | 2099-12-31* |
|
*LEAD関数は、これらのプランがまだ終了していないために、デフォルト値を返しました。これらの行はパーティション外であるため、ADD_TO_DATE関数は01-Jan-2100から1日を除算し、2099-12-31を返しました。
例: ウィンドウを使用したGPS pingのフラグ付け
あなたの組織はトリップID、イベントID、およびタイムスタンプを含む車両からのGPS pingを受信します。各ping間の時間差を計算し、前の行との時間差が60秒未満である場合、行にスキップとのフラグを付けようとしています。
イベントを時系列で順序付け、イベントをトリップ別にパーティション化します。前の行のイベント時刻にアクセスするウィンドウ関数を定義し、ADD_TO_DATE関数を使用して、2つのイベント間の時間差を計算します。
ウィンドウプロパティ
プロパティ | 値 | 説明 |
|---|
フレーム | 指定されていません | ウィンドウ関数は、オフセット引数に基づいて行にアクセスし、フレームを無視します。 |
パーティションキー | trip_id。 | 行をトリップID別にグループ化し、計算が同じトリップからのイベントに基づくようにします。 |
オーダーキー | _event_id昇順。 | イベントIDの昇順で、データを時系列に配置します。 |
ウィンドウ関数
前の行からイベント時刻を取得する次のLAG関数を定義します。
LAG ( _event_time, 1, NULL )
LAG関数の詳細については、『関数リファレンス』を参照してください。
2つの日付間の時間を計算する次のDATE_DIFF関数を定義します。
DATE_DIFF ( _event_time, LAG ( _event_time, 1, NULL ), 'ss' )
DATE_DIFFが60秒未満の場合、または_event_timeがNULLの場合、行にスキップとのフラグを付けます。
IIF ( DATE_DIFF < 60 or ISNULL ( _event_time ), 'Skip', 'Valid' )
出力
トランスフォーメーションは、次の出力を生成します。
Trip ID | Event ID | Event Time | Time Difference | Flag |
|---|
101 | 1 | 2017-05-03 12:00:00 | NULL* | Skip |
101 | 2 | 2017-05-03 12:00:34 | 34 | Skip |
101 | 3 | 2017-05-03 12:02:00 | 86 | Valid |
101 | 4 | 2017-05-03 12:02:23 | 23 | Skip |
102 | 1 | 2017-05-03 12:00:00 | NULL* | Skip |
102 | 2 | 2017-05-03 12:01:56 | 116 | Valid |
102 | 3 | 2017-05-03 12:02:00 | 4 | Skip |
102 | 4 | 2017-05-03 13:00:00 | 3480 | Valid |
103 | 1 | 2017-05-03 12:00:00 | NULL* | Skip |
103 | 2 | 2017-05-03 12:00:12 | 12 | Skip |
103 | 3 | 2017-05-03 12:01:12 | 60 | Valid |
|
*これらの行の前の行は、パーティションの境界外であるため、LAG関数はNULL値を生成します。
例: ウィンドウでの集計関数の実行
各従業員の給与と対応する部門の平均給与を比較するとします。
次の表に、部門名、従業員ID番号、従業員の給与の一覧を示します。
Department | Employee | Salary |
|---|
Development | 11 | 5200 |
Development | 7 | 4200 |
Development | 9 | 4500 |
Development | 8 | 6000 |
Development | 10 | 5200 |
Personnel | 5 | 3500 |
Personnel | 2 | 3900 |
Sales | 3 | 4800 |
Sales | 1 | 5000 |
Sales | 4 | 4800 |
|
すべての従業員を計算に含めるための、バインドなしフレームを設定し、各従業員の給与とその従業員の部門内の平均給与との間の差異を計算する集計関数を定義します。
ウィンドウプロパティ
プロパティ | 値 | 説明 |
|---|
開始offset | 前のすべての行 | 現在の入力行の前に表示される行数を記述します。 |
終了オフセット | 後のすべての行 | 現在の入力行の後に表示される行数を記述します。 |
オーダーキー | 昇順の給与。 | データを給与の昇順に配置します。 |
パーティションキー | 部門 | 行を部門別にグループ化します。 |
［前のすべての行］および［後のすべての行］を選択した場合は、すべてのパーティション行が含まれます。例えば、現在の行が3行目だとします。3行目は「Development」パーティション内にあるため、このフレームには、「Development」パーティション内の3行目前後のすべての行に加えて、3行目が含まれます。
ウィンドウ関数
式トランスフォーメーションでウィンドウプロパティを設定するため、集計関数はウィンドウ関数として使用できます。
各従業員の給与とその従業員の部門内の平均給与との間の差異を計算する次の集計関数を定義します。
Salary - AVG ( Salary ) = Salary_Diff
アウトプット
トランスフォーメーションは、次の給与の差異を生成します。
Department | Employee | Salary | Salary_Diff |
|---|
Development | 11 | 5200 | -820 |
Development | 7 | 4200 | -520 |
Development | 9 | 4500 | 180 |
Development | 8 | 6000 | 180 |
Development | 10 | 5200 | 980 |
Personnel | 5 | 3500 | 200 |
Personnel | 2 | 3900 | 200 |
Sales | 3 | 4800 | -66 |
Sales | 1 | 5000 | -66 |
Sales | 4 | 4800 | 134 |
|
どの従業員が同一部門の平均給与よりも少なく、または多く稼いでいるかを特定できます。この情報に基づき、その他のトランスフォーメーションを追加し、データをより詳しく調べることができます。例えば、ランクトランスフォーメーションを追加し、各従業員の同一部門内での数値的なランクを生成できます。
