メインコンテンツまでスキップ
waffle.svg
Domo Knowledge Base

ETL操作:ランクとウィンドウ

Version 4

 

はじめに

ランクとウィンドウでは、複数のランクとウィンドウ関数を列に適用し、新たな列を作成することができます。これらの機能ではいずれも、まず関数の定義を行い、次に関数の順序付け対象とする列を指定し、最後に新たな列の結果を昇順、降順のいずれにするかを指定します。

ランクとウィンドウの関数は、ランク関数、フレーム化関数、オフセット関数の3種類に分類されます。

このセクションで説明する関数の詳細については、http://docs.aws.amazon.com/redshift/...functions.html を参照してください。

ビデオ - ランク関数とウィンドウ関数

 

パーティションについて

関数はいずれも、オプションでパーティションとして使用する列の指定が可能です。例えば、「State」という連続列がある場合、パーティションとして「State」を選択すると、新しい列の値を状態別に分割します。これを次の例で示します。「Amount」列の値は降順でランク付けされ、状態:
 ごとに分割されます。

 

コネチカット州(「CT」)の数値2種類が最も上位に順位付けされ、次にデラウウェア州(「DE」)の数値5つが続きます。

ランク関数

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

Rank

Rank関数では、選択した列にある値それぞれに順位番号を割り当てます。同一の値を含むセルが複数ある場合は、同じ順位番号が振られ、スキップされた数に応じた「ギャップ」が生じます。例えば、列初めの3つのセルの値が100で、その次のセルの値が200、さらに昇順設定となっている場合、初めの3つのセルの順位は「1」となり、4番目のセルの順位は「4」となります。

以下のスクリーンショットは、この例を示したものです。

rank_and_window_rank.png

初めの3つには、同じ$値があるため、順位はすべて1となります。2位、3位はスキップされます。その次にある5つも同じ値が入っているため、順位はすべて4となります。

Rank機能の設定は、

  1. キャンバスで、[ランクとウィンドウ] をクリックします。

  2. (オプション)操作名称を変更するには、をクリックして名称を入力します。

  3. [関数の追加] をクリックします。

  4. 順位結果が返される新しい列の名前(「順位」など)を追加します。

  5. [関数種類の選択] メニューから、[Rank] を選択します。

  6. ダイアログの右上にある [適用] ボタンをクリックします。
    新規のステップが3種類表示されます。

  7. ダイアログのステップ2で、順位を付ける列を選択します(値のある列を推奨)。

  8. ダイアログのステップ3で、列の値に順位付けする順序を選択します。

  9. (オプション)パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します(パーティションの説明については、このページ上部にある「パーティションについて」 を参照してください)。

Dense Rank

Dense Rank関数は、Rank関数と同様ですが、大きな違いが1つあります。同じ値のセルが複数ある場合、Rank関数同様それらのセルに振られる順位番号は同じですが、以降のセルに対しては、その続きで番号が振られます(「ギャップ」が生じない)。例えば、列最初のセル3つの値が100 で、その次のセルの値が200、昇順設定となっている場合、初めのセル3つの順位は「1」となり、4番目のセルの順位は「2」となります。

以下のスクリーンショットは、この例を示したものです。

初めの3つには、同じ$値があるため、順位はすべて1となります。その次にある5つも同じ値が入っているため、順位はすべて2となり、以降同様に続きます。

Dense Rank機能の設定は、

  1. キャンバスで、[ランクとウィンドウ] をクリックします。

  2. (オプション)操作名称を変更するには、をクリックして名称を入力します。

  3. [関数の追加] をクリックします。

  4. 順位結果が返される新しい列の名前(「順位」など)を追加します。

  5. [関数種類の選択] メニューから、[Dense Rank] を選択します。

  6. ダイアログの右上にある [適用] ボタンをクリックします。
    新規のステップが3種類表示されます。

  7. ダイアログのステップ2で、順位を付ける列を選択します(値のある列を推奨)。

  8. ダイアログのステップ3で、列の値に順位付けする順序を選択します。

  9. (オプション)パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します(パーティションの説明については、このページ上部にある「パーティションについて」 を参照してください)。

Row Number

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

rank_and_window_row_numbers.png 

Row Numbers機能の設定は、

  1. キャンバスで、[ランクとウィンドウ] をクリックします。

  2. (オプション)操作名称を変更するには、をクリックして名称を入力します。

  3. [関数の追加] をクリックします。

  4. 順位結果が返される新しい列の名前(「行番号」など)を追加します。

  5. [関数種類の選択] メニューから、[Row Number] を選択します。

  6. ダイアログの右上にある [適用] ボタンをクリックします。
    新規のステップが3種類表示されます。

  7. ダイアログのステップ2で、行番号を付ける列を選択します。

  8. ダイアログのステップ3で、列の値を並べ替える順序を選択します。

  9. (オプション)パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します(パーティションの説明については、このページ上部にある「パーティションについて」 を参照してください)。

フレーム化関数

フレーム化関数では、あるセルとその前(「先行」)と後(「後続」)にある指定した数のセルに対し数学関数を適用します。適用後の出力値は、新しい列に表示されます。 

以下の例で、この機能を簡単に示します。この例では、ユーザーがSum関数を選択し、先行値が1で後続値が2となっています。

rank_and_window_framing_example.png

この例では、「元の値」列にある各値がその1つ上のセルとその下の2つのセルに追加され、結果が「出力値」列の同じ行に表示されています。このため行5の場合、1(行4)、5(行6)、1(行7)に3を加えた値となります。合計の10は、行5の「出力値」列に表示されます。特定の値の上または下に計算を実行するのに十分な値が存在しない場合、これらは単純に省略されます。例えば、行7に対しては後続値が1つしかないため、計算式は単純に、 5 + 1 + 2 = 8となります。

パーティション化を適用している場合は、関数でパーティションを使用します。以下の例では前の例と同様に、ユーザーがSum関数を選択、先行値に1、後続値に2 を指定しています。異なるのは、「Class」列をパーティションとして設定している点です。

パーティションにより行がグループ化されるため、一部の値合計が異なる方法で算出されます。例えば行4では、パーティションにより値「B」すべてが別のグループとして分離されるため、用いることのできる後続値は1つのみとなります。それでこの行の計算式は、2 + 1 + 3 = 6となります。同様に、行6 では、パーティションにより使用できる後続値は2つのみとなり、計算式は5 + 1 + 2 = 8となります。

パーティションの説明については、このページ上部にある「パーティションについて」 を参照してください。

Average

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

rank_and_window_average.png

Average機能の設定は、

  1. キャンバスで、[ランクとウィンドウ] をクリックします。

  2. (オプション)操作名称を変更するには、をクリックして名称を入力します。

  3. [関数の追加] をクリックします。

  4. 出力値が返される新しい列の名前を追加します。 
    「平均(2P・2F)」などのように先行値と後続値の数がわかりやすい名前を推奨します。

  5. [関数種類の選択] メニューから、[Average] を選択します。
    関数種類を選択すると、新規メニューが2種類表示されます。

  6. [列の選択] メニューから、平均の対象となる値のある列を選択します。

  7. [先行] [後続] のフィールドに、平均計算に含める先行値および後続値の数を入力します。

  8. ダイアログの右上にある [適用] ボタンをクリックします。
    新規のステップが表示されます。

  9. ダイアログのステップ2で、順序の基にする列を選択します。

  10. ダイアログのステップ3で、新しい列の値を並べ替える順序を選択します。

  11. (オプション)パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します(パーティションの説明については、このページ上部にある「パーティションについて」 を参照してください)。

 

Count

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

Count機能の設定は、

  1. キャンバスで、[ランクとウィンドウ] をクリックします。

  2. (オプション)操作名称を変更するには、をクリックして名称を入力します。

  3. [関数の追加] をクリックします。

  4. 出力値が返される新しい列の名前を追加します。
    「カウント(2P・2F)」などのように先行値と後続値の数がわかりやすい名前を推奨します。

  5. [関数種類の選択] メニューから、[Count] を選択します。
    関数種類を選択すると、新規メニューが 2 種類表示されます。

  6. [列の選択] メニューから、カウント対象となる値のある列を選択します。

  7. [先行] [後続] のフィールドに、カウント計算に含める先行値および後続値の数を入力します。

  8. ダイアログの右上にある [適用] ボタンをクリックします。
    新規のステップが3種類表示されます。

  9. ダイアログのステップ2 で、順序の基にする列を選択します。

  10. ダイアログのステップ3 で、新しい列の値を並べ替える順序を選択します。

  11. (オプション)パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します(パーティションの説明については、このページ上部にある「パーティションについて」 を参照してください)。

Sum

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

rank_and_window_sum.png

Sum機能の設定は、

  1. キャンバスで、[ランクとウィンドウ] をクリックします。

  2. (オプション)操作名称を変更するには、をクリックして名称を入力します。

  3. [関数の追加] をクリックします。

  4. 出力値が返される新しい列の名前を追加します。
    「合計(2P・2F)」などのように先行値と後続値の数がわかりやすい名前を推奨します。

  5. [関数種類の選択] メニューから、[Sum] を選択します。
    関数種類を選択すると、新規メニューが2種類表示されます。

  6. [列の選択] メニューから、合計計算対象となる値のある列を選択します。

  7. [先行][後続] のフィールドに、合計計算に含める先行値および後続値の数を入力します。

  8. ダイアログの右上にある [適用] ボタンをクリックします。
    新規のステップが3種類表示されます。

  9. ダイアログのステップ2 で、順序の基にする列を選択します。

  10. ダイアログのステップ3 で、新しい列の値を並べ替える順序を選択します。

  11. (オプション)パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します(パーティションの説明については、このページ上部にある「パーティションについて」 を参照してください)。

Offset関数

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

Lag

Lag関数では、新しい列の値は、指定したオフセット後、元の列の値の後ろに出力されます。以下の例では、Lag関数に3を適用し、「Cust」列がパーティションとして追加されています。

「Amount」列の値と「Lag」列の一致する値の間に、オフセット指定された3行が挿入されていることが分かります。また、パーティションにより、行 13~15 がグループ化された「Leo, Inc.」の最初の3行になるため、「Lag」列のセルは空白表示となります。

Lag機能の設定は、

  1. キャンバスで、[ランクとウィンドウ] をクリックします。

  2. (オプション)操作名称を変更するには、をクリックして名称を入力します。

  3. [関数の追加] をクリックします。

  4. 出力値が返される新しい列の名前を追加します。
    ラグのオフセット値がわかりやすい名前を推奨します。

  5. [関数種類の選択] メニューから、[Lag] を選択します。
    関数種類を選択すると、新規メニューが2種類表示されます。

  6. [列の選択] メニューから、新しい列への出力対象となる値のある列を選択します。

  7. [含める行] のフィールドに、任意のオフセット値を入力します。

  8. ダイアログの右上にある [適用] ボタンをクリックします。
    新規のステップが3種類表示されます。

  9. ダイアログのステップ2で、順序の基にする列を選択します。

  10. ダイアログのステップ3で、新しい列の値を並べ替える順序を選択します。

  11. (オプション)パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します(パーティションの説明については、このページ上部にある「パーティションについて」 を参照してください)。

Lead

Lead関数では、新しい列の値は、指定したオフセット後、元の値の前に出力されます。以下の例では、Lead関数に3を適用し、「Cust」列がパーティションとして追加されています。

「Amount」列の値と「Lead」列の一致する値の間に、オフセット指定された3行が挿入されていることが分かります。また、パーティションにより、行17~19がグループ化された「Leo, Inc.」の最後の3行になるため、「Lead」列のセルは空白表示となります。

Lead機能の設定は、

  1. キャンバスで、[ランクとウィンドウ] をクリックします。

  2. (オプション)操作名称を変更するには、をクリックして名称を入力します。

  3. [関数の追加] をクリックします。

  4. 出力値が返される新しい列の名前を追加します。
    リードのオフセット値がわかりやすい名前を推奨します。

  5. [関数種類の選択] メニューから、[Lead] を選択します。
    関数種類を選択すると、新規メニューが2種類表示されます。

  6. [列の選択] メニューから、新しい列への出力対象となる値のある列を選択します。

  7. [含める行] のフィールドに、所望のオフセット値を入力します。

  8. ダイアログの右上にある [適用] ボタンをクリックします。
    新規のステップが3種類表示されます。

  9. ダイアログのステップ2で、順序の基にする列を選択します。

  10. ダイアログのステップ3で、新しい列の値を並べ替える順序を選択します。

  11. (オプション)パーティションに使用する列があれば、ダイアログのステップ4でそれを選択します(パーティションの説明については、このページ上部にある「パーティションについて」 を参照してください)。