古いMagic ETLタイル：ランクとウインドウ

​

はじめに

ランクとウインドウ操作では、複数のランクとウインドウ関数を列に適用し、新たな列を作成することができます。これらの操作ではいずれも、まず関数の定義を行い、次に関数の順序付け対象とする列を指定し、最後に新たな列の結果を昇順、降順のいずれにするかを指定します。 ランクとウインドウ関数は、ランキング関数、フレームされた関数、オフセット関数の3種類に分類されます。 このセクションで説明する関数の詳細については、 http://docs.aws.amazon.com/redshift/latest/dg/c_Window_functions.html を参照してください。 動画 - Magic ETLのランクとウインドウ関数

​

パーティションについて

関数はいずれも、オプションでパーティションとして使用する列の指定が可能です。例えば、「State」（州）という列がある場合、パーティションとして「State」を選択すると、新しい列の値が州別に分割されます。これを次の例で示します。「Amount」（金額）列の値は降順で順位付けされ、州別にパーティション分割されています。 コネチカット州（「CT」）の2つの金額が最も上位に順位付けされ、次にデラウェア州（「DE」）の5つの金額が続きます。

​

ランキング関数

ランキング関数は、選択した列にある値それぞれの順位を求め、新しい列に表示します。ランキング関数には、ランク、Dense Rank、行番号の3種類があります。

​

ランク

ランク関数では、選択した列にある値それぞれに順位番号を割り当てます。同一の値を含むセルが複数ある場合は、同じ順位番号が振られ、スキップされた数に応じた「ギャップ」が生じます。例えば、列の最初の3つのセルの値が100で、その次のセルの値が200、さらに昇順設定となっている場合、初めの3つのセルの順位は「1」となり、4番目のセルの順位は「4」となります。 以下のスクリーンショットは、この例を示したものです。 ] 最初の3つの州は、「$」値が同じであるため順位はすべて1となります。順位番号2と3はスキップされます。その次の5つの州も値が同じで、順位はすべて4となり、以降同様に続きます。 ランク操作を設定するには：

1. キャンバスで ［ランクとウインドウ］ 操作をクリックします。
2. （オプション）操作の名前を変更するには、 をクリックし操作の名前を入力します。
3. ［関数を追加］ をクリックします。
4. 順位結果が返される新しい列の名前（「Rank」など）を追加します。
5. ［関数タイプを選択］ メニューから、 ［ランク］ を選択します。
6. ダイアログの右上にある ［適用］ ボタンをクリックします。 新規のステップが3種類表示されます。
7. ダイアログのステップ2で、順位付けする列を選択します（値のある列を推奨）。
8. ダイアログのステップ3で、列の値に順位付けする順序を選択します。
9. （オプション）パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します（パーティションの説明については、このページ上部にある「 パーティションについて 」を参照してください）。

​

Dense Rank

Dense Rank関数は、ランク関数と同様ですが、大きな違いが1つあります。同じ値のセルが複数ある場合、ランキング関数同様それらのセルに振られる順位番号は同じですが、以降のセルに対しては、その続きで番号が振られます（「ギャップ」が生じない）。例えば、列の最初の3つのセルの値が100で、その次のセルの値が200、昇順設定となっている場合、最初の3つのセルの順位は「1」となり、4番目のセルの順位は「2」となります。 以下のスクリーンショットは、この例を示したものです。 最初の3つの州は、「$」値が同じであるため順位はすべて1となります。その次の5つの州も「$」値が同じで、順位は2となり、以降同様に続きます。 Dense Rank操作を設定するには：

1. キャンバスで ［ランクとウインドウ］ 操作をクリックします。
2. （オプション）操作の名前を変更するには、 をクリックし操作の名前を入力します。
3. ［関数を追加］ をクリックします。
4. 順位結果が返される新しい列の名前（「Rank」など）を追加します。
5. ［関数タイプを選択］ メニューから、 ［Dense Rank］ を選択します。
6. ダイアログの右上にある ［適用］ ボタンをクリックします。 新規のステップが3種類表示されます。
7. ダイアログのステップ2で、順位付けする列を選択します（値のある列を推奨）。
8. ダイアログのステップ3で、列の値に順位付けする順序を選択します。
9. （オプション）パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します（パーティションの説明については、このページ上部にある「 パーティションについて 」を参照してください）。

​

行番号

行番号関数では、選択した列にある値すべての行番号を返します。パーティションを使用する場合、取得される行番号はパーティショングループ内の番号となり、DataSetの行番号とは 異なる 場合がありますのでご注意ください。この例を以下のスクリーンショットで示します。行番号関数から返された行番号が、新しいパーティションごとに再開されています。そのため、行4から始まる出力された行番号はDataSetの行番号とは異なります。 行番号操作を設定するには：

1. キャンバスで ［ランクとウインドウ］ 操作をクリックします。
2. （オプション）操作の名前を変更するには、 をクリックし操作の名前を入力します。
3. ［関数を追加］ をクリックします。
4. 順位結果が返される新しい列の名前（「Row Number」など）を追加します。
5. ［関数タイプを選択］ メニューから、 ［行番号］ を選択します。
6. ダイアログの右上にある ［適用］ ボタンをクリックします。 新規のステップが3種類表示されます。
7. ダイアログのステップ2で、行番号を付ける列を選択します。
8. ダイアログのステップ3で、列の値を並べ替える順序を選択します。
9. （オプション）パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します（パーティションの説明については、このページ上部にある「 パーティションについて 」を参照してください）。

​

フレームされた関数

フレームされた関数では、あるセルとその前（「先行」）と後（「後続」）にある指定した数のセルに対し数学関数を適用します。適用後の出力値は、新しい列に表示されます。 以下の例で、この機能を簡単に示します。この例では、ユーザーが合計関数を選択し、先行値が1で後続値が2となっています。 この例では、「Original Value」列にある各値がその1つ上のセルとその下の2つのセルに追加され、結果が「Derived Value」列の同じ行に表示されています。このため行5の場合、1（行4）、5（行6）、1（行7）に3を加えた値となります。合計の10は、行5の「Derived Value」列に表示されます。特定の値の上または下に計算を実行するのに十分な値が存在しない場合、これらは単純に省略されます。例えば、行7に対しては後続値が1つしかないため、計算式は単純に、5 + 1 + 2 = 8となります。 パーティションを適用している場合は、関数でパーティションを使用します。以下の例では前の例と同様に、ユーザーが合計関数を選択、先行値に1、後続値に2を指定しています。異なるのは、「Class」列をパーティションとして設定している点です。 パーティションにより行がグループ化されるため、一部の値合計が異なる方法で算出されます。例えば行4では、パーティションにより値「B」すべてが別のグループとして分離されるため、用いることのできる後続値は1つのみとなります。それでこの行の計算式は、2 + 1 + 3 = 6となります。同様に、行6では、パーティションにより使用できる後続値は2つのみとなり、計算式は5 + 1 + 2 = 8となります。 パーティションの説明については、このページ上部にある「 パーティションについて 」を参照してください。

​

平均

平均関数では、特定セルとその指定の先行値および後続値の平均を返します。以下の例では、ユーザーが「Amount」列に平均関数を適用、先行値と後続値を2、「Cust」列をパーティションに設定しています。 平均操作を設定するには：

1. キャンバスで ［ランクとウインドウ］ 操作をクリックします。
2. （オプション）操作の名前を変更するには、 をクリックし操作の名前を入力します。
3. ［関数を追加］ をクリックします。
4. 出力値が返される新しい列の名前を追加します。 「平均（2P・2F）」などのように先行値と後続値の数が分かりやすい名前を推奨します。
5. ［関数タイプを選択］ メニューから、 ［平均］ を選択します。 関数タイプを選択すると、新規メニューが2種類表示されます。
6. ［列を選択］ メニューから、平均の対象となる値のある列を選択します。
7. ［先行］ と ［後続］ のフィールドに、平均計算に含める先行値および後続値の数を入力します。
8. ダイアログの右上にある ［適用］ ボタンをクリックします。 新規のステップが3種類表示されます。
9. ダイアログのステップ2で、順序の基にする列を選択します。
10. ダイアログのステップ3で、新しい列の値を並べ替える順序を選択します。
11. （オプション）パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します（パーティションの説明については、このページ上部にある「 パーティションについて 」を参照してください）。

​

カウント

カウント関数では、特定セルとその指定の先行値および後続値の個数を返します。以下の例では、ユーザーが「Amount」列にカウント関数を適用、先行値と後続値を2、「Cust」列をパーティションに設定しています。 カウント操作を設定するには：

1. キャンバスで ［ランクとウインドウ］ 操作をクリックします。
2. （オプション）操作の名前を変更するには、 をクリックし操作の名前を入力します。
3. ［関数を追加］ をクリックします。
4. 出力値が返される新しい列の名前を追加します。 「カウント（2P・2F）」などのように先行値と後続値の数が分かりやすい名前を推奨します。
5. ［関数タイプを選択］ メニューから、 ［カウント］ を選択します。 関数タイプを選択すると、新規メニューが2種類表示されます。
6. ［列を選択］ メニューから、カウント対象となる値のある列を選択します。
7. ［先行］ と ［後続］ のフィールドに、カウント計算に含める先行値および後続値の数を入力します。
8. ダイアログの右上にある ［適用］ ボタンをクリックします。 新規のステップが3種類表示されます。
9. ダイアログのステップ2で、順序の基にする列を選択します。
10. ダイアログのステップ3で、新しい列の値を並べ替える順序を選択します。
11. （オプション）パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します（パーティションの説明については、このページ上部にある「 パーティションについて 」を参照してください）。

​

合計

合計関数では、特定セルとその指定の先行値および後続値の合計を返します。以下の例では、ユーザーが「Amount」列に合計関数を適用、先行値と後続値を2、「Cust」列をパーティションに設定しています。 合計操作を設定するには：

1. キャンバスで ［ランクとウインドウ］ 操作をクリックします。
2. （オプション）操作の名前を変更するには、 をクリックし操作の名前を入力します。
3. ［関数を追加］ をクリックします。
4. 出力値が返される新しい列の名前を追加します。 「合計（2P・2F）」などのように先行値と後続値の数が分かりやすい名前を推奨します。
5. ［関数タイプを選択］ メニューから、 ［合計］ を選択します。 関数タイプを選択すると、新規メニューが2種類表示されます。
6. ［列を選択］ メニューから、合計計算対象となる値のある列を選択します。
7. ［先行］ と ［後続］ のフィールドに、合計計算に含める先行値および後続値の数を入力します。
8. ダイアログの右上にある ［適用］ ボタンをクリックします。 新規のステップが3種類表示されます。
9. ダイアログのステップ2で、順序の基にする列を選択します。
10. ダイアログのステップ3で、新しい列の値を並べ替える順序を選択します。
11. （オプション）パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します（パーティションの説明については、このページ上部にある「 パーティションについて 」を参照してください）。

​

オフセット関数

オフセット関数では、DataSetに新しい列を追加し、この列に選択した列と同じ値を指定した行数分オフセットした上で返します。 オフセット関数は2種類あります。新しい列の値を元の関数の 後ろ にオフセットするのがラグ関数、新しい列の値を元の列の値の 前 にオフセットするのがリード関数です。ラグ関数、リード関数のいずれでも、パーティションを使用します。

​

ラグ

ラグ関数では、新しい列の値は、指定したオフセット後、元の列の値の後ろに出力されます。以下の例では、ラグ関数に3を適用し、「Cust」列がパーティションとして追加されています。 「Amount」列の値と「Lag」列の一致する値の間に、オフセット指定された3行が挿入されていることが分かります。また、パーティションにより、行13～15がグループ化された「Leo, Inc.」の最初の3行になるため、「Lag」列のセルは空白表示となります。 ラグ操作を設定するには：

1. キャンバスで ［ランクとウインドウ］ 操作をクリックします。
2. （オプション）操作の名前を変更するには、 をクリックし操作の名前を入力します。
3. ［関数を追加］ をクリックします。
4. 出力値が返される新しい列の名前を追加します。 ラグのオフセット値が分かりやすい名前を推奨します。
5. ［関数タイプを選択］ メニューから、 ［ラグ］ を選択します。 関数タイプを選択すると、新規メニューが2種類表示されます。
6. ［列を選択］ メニューから、新しい列への出力対象となる値のある列を選択します。
7. ［どの行が含まれるべきですか？］ フィールドに、任意のオフセット値を入力します。
8. ダイアログの右上にある ［適用］ ボタンをクリックします。 新規のステップが3種類表示されます。
9. ダイアログのステップ2で、順序の基にする列を選択します。
10. ダイアログのステップ3で、新しい列の値を並べ替える順序を選択します。
11. （オプション）パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します（パーティションの説明については、このページ上部にある「 パーティションについて 」を参照してください）。

​

リード

リード関数では、新しい列の値は、指定したオフセット後、元の列の値の前に出力されます。以下の例では、リード関数に3を適用し、「Cust」列がパーティションとして追加されています。 「Amount」列の値と「Lead」列の一致する値の間に、オフセット指定された3行が挿入されていることが分かります。また、パーティションにより、行17～19がグループ化された「Leo, Inc.」の最後の3行になるため、「Lead」列のセルは空白表示となります。 リード操作を設定するには：

1. キャンバスで ［ランクとウインドウ］ 操作をクリックします。
2. （オプション）操作の名前を変更するには、 をクリックし操作の名前を入力します。
3. ［関数を追加］ をクリックします。
4. 出力値が返される新しい列の名前を追加します。 リードのオフセット値が分かりやすい名前を推奨します。
5. ［関数タイプを選択］ メニューから、 ［リード］ を選択します。 関数タイプを選択すると、新規メニューが2種類表示されます。
6. ［列を選択］ メニューから、新しい列への出力対象となる値のある列を選択します。
7. ［どの行が含まれるべきですか？］ フィールドに、任意のオフセット値を入力します。
8. ダイアログの右上にある ［適用］ ボタンをクリックします。 新規のステップが3種類表示されます。
9. ダイアログのステップ2で、順序の基にする列を選択します。
10. ダイアログのステップ3で、新しい列の値を並べ替える順序を選択します。
11. （オプション）パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します（パーティションの説明については、このページ上部にある「 パーティションについて 」を参照してください）。

日本語