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

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

Version 3

 

はじめに

ランクとウィンドウ操作を使うと、ランクやウィンドウ関数を列に適用させることで新しい列を作成できます。その操作を行うためには、まず関数を定義し、次に関数を並べる列を指定し、最後に新しい列の結果が昇順か降順かを指定します。

ランクとウインドウ関数は 3 つのタイプに分類されます:ランキング、フレーム、そしてオフセットです。

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

パーティションについて

全ての関数で、パーティションとして使用する列をオプションとして指定することもできます。例えば、「州」という系列がある場合、「州」をパーティションとして選択すると、新しい列の値が州によって分割されます。これは例で示されています。「Amount」の列の値が降順にランク付けされ、また州によってパーティションされています:
 

rank_and_window_partition.png 

コネチカット(「CT」)の2つの金額が最初にランクされ、次にデラウェア(「DE」)の 5 つの金額と続いています。

ランキング関数

ランキング関数は、選択した列の各値に対してランキング番号を導出し、そしてそれを新しい列に表示します。利用可能なランキング関数は 3 つあります:RANK、DENSE RANK、そして ROW NUMBER です

ランク

RANK 関数は、選択した列の各値にランキング番号を割り当てます。列内のセルに同じ値が含まれていると、それらのセルには同じランキング番号が与えられ、そしてスキップされた数に応じて番号に「空白」が現れます。例えば、列の最初の 3 つのセルの値が 100 で、次のセルの値が 200、そして各値が昇順で表示される設定になっている場合は、最初 3 つのセルのランキングは「1」、そして 4 番目のセルのランキングは「4」になります。

以下のスクリーンショットはそれを示しています:

rank_and_window_rank.png

最初の 3 つの州の $ 値が同じなため、これらのランキングは全て 1 になります。そしてランキング番号 2 と 3 はスキップされます。次の 5 つの州の値も同じなので、これらは全てランキング 4 になります。

RANK 操作を設定するには、

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

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

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

  4. ランキング結果が格納される新しい列の名前を追加します(「ランク」など)。

  5. 関数タイプを選択メニューでRANKを選択します。

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

  7. ダイアログのステップ 2 で、ランキングしたい列を選択します。(数値列を推奨します。)

  8. ダイアログのステップ 3 で、列の値をランキングする順番を選択します。

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

Dense Rank

DENSE RANK 関数は RANK 関数と同じですが、ひとつだけ重要な違いがあります。列内のセルに同じ値が含まれている場合は、RANK 関数と同様そのセルには同じランキング番号が与えられます。ただし、番号は次のセルに続きます(「空白」は発生しません。)。例えば、列の最初の 3 つのセルの値が 100 で、次のセルの値が 200、そして各値が昇順で表示される設定になっている場合は、最初 3 つのセルのランキングは「1」、そして 4 番目のセルのランキングは「2」になります。

以下のスクリーンショットはそれを示しています:

rank_and_window_dense_rank.png

最初の 3 つの州の $ 値が同じなため、これらのランキングは全て 1 になります。次の 5 つの州の $ 値も同じなため、ランキングは 2 になります。

DENSE RANK 操作を設定するには、

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

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

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

  4. ランキング結果が格納される新しい列の名前を追加します(「ランク」など)。

  5. 関数タイプを選択メニューで、DENSE RANK を選択します。

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

  7. ダイアログのステップ 2 で、ランキングしたい列を選択します。(数値列を推奨します。)

  8. ダイアログのステップ 3 で、列の値をランキングする順番を選択します。

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

行番号

ROW NUMBER 関数は、選択した列の全ての値の行番号を返します。パーティションを使用している場合は、行はパーティショングループ内の行番号を取ります。必ずしも DataSet での行番号ではありません。これは以下のスクリーンショットで示されています。ROW NUMBER 関数から導出された行番号は、新しいパーティションごとに再開されています。そのため、行 4 から始まり、導出された行番号は DataSet の行番号と同じになっていません。

rank_and_window_row_numbers.png 

ROW NUMBER 操作を設定するには、

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

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

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

  4. ランキング結果が格納される新しい列の名前を追加します(「行番号」など)。

  5. 関数タイプを選択メニューでROW NUMBERを選択します。

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

  7. ダイアログのステップ 2 で、行番号を取得したい列を選択します。

  8. ダイアログのステップ 3 で、列の値をソートする順番を選択します。

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

FRAME 関数

フレーム関数は数学的関数を列のセルとその前の指定数のセル(「前」)、そして」その後(「後」)のセルに適用します。導出された値は、新しい列に現れます。

次の簡単な例で、これがどのように行われるかを説明します。この例では、SUM 関数を選択し、前の値に 1 、そして後の値に 2 を指定しています。

rank_and_window_framing_example.png

この例では、「元の値」列の各値がそのひとつ上のセルとその下 2 つのセルと加算され、結果は「Derived Value」列の同じ行に表示されています。つまり行 5 では、1 に 3 を(行 4 で)加算し、(行 6 で)5 を、そして(行 7 で)1 を加算します。合計の 10 は、「Derived Value」列の行 5 に表示されています。計算に入れる上または下に十分な値がない場合は、単に省略されます。例えば、行 7 には、次の値はひとつしかないため、式は単純に 5 + 1 + 2 = 8 となります。

パーティションが適用されている場合、パーティションは関数でも考慮されます。以下の例では、SUM を選択し、そして前述の例と同様、前の値に 1 そして次の値に 2 を指定しています。ただし、ここでは「Class」列もパーティションとして設定しています。

パーティションで行がグループ化されるため、値の合計の算出方法が異なっています。例えば、行 4 では、パーティションが全ての「B」値を別のグループに分けるため、次の値は 1 つしかありません。そのためこの行の方程式は、2 + 1 + 3 = 6 となります。同様に、行 6 では、パーティションのため次の値は 2 しかありません。よって、5 + 1 + 2 = 8 になります。

(パーティションに関する説明は、このページの冒頭にあるパーティションについてを参照してください。

平均

AVERAGE 関数は、指定されたセルとその前後の値の平均値をとります。次の例では、AVERAGE 関数を「Amount」列に適用し、前の値と後の値を 2 に設定、そしょて「Cust」列をパーティションとして設定しています。

rank_and_window_average.png

AVERAGE 操作を設定するには、

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

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

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

  4. 導出する値を格納する新しい列に名前を追加します。
    「平均(2P と 2F)」など、前後の値の数値を示す名前にすることを推奨します。

  5. 関数タイプを選択メニューで AVERAGE を選択します。
    関数タイプを選択すると、新しいメニューが 2 つ表示されます。

  6. 列を選択メニューで、平均したい列を選択します。

  7. 前の値後の値のフィールドで、平均したい前の値と後の値の数値を入力します。

  8. ダイアログの右上にある適用ボタンをクリックします。
    新しいステップが 3 つ現れます。

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

  10. ダイアログのステップ 3 で、新しい列の値をソートする順番を選択します。

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

 

カウント

COUNT 関数は、指定したセルとその前後値のカウントを返します。次の例では、COUNT 関数を「Amount」列に適用し、前の値と後の値を 2 に設定、そして「Cust」列をパーティションとして設定しました。

rank_and_window_count.png

カウント操作を設定するには。

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

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

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

  4. 導出する値を格納する新しい列に名前を追加します。
    「カウント(2Pと2F)」など、前後の値の数値を示す名前にすることを推奨します。

  5. 関数タイプを選択メニューでCOUNTを選択します。
    関数タイプを選択すると、新しいメニューが 2 つ表示されます。

  6. 列を選択メニューで、カウントしたい列を選択します。

  7. 前の値後の値のフィールドで、カウントしたい前の値と後の値の数値を入力します。

  8. ダイアログの右上にある適用ボタンをクリックします。
    新しいステップが 3 つ現れます。

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

  10. ダイアログのステップ 3 で、新しい列の値をソートする順番を選択します。

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

合計

SUM 関数は、指定したセルとその前後値の合計を返します。次の例では、SUM 関数を「Amount」列に適用し、前の値と後の値を 2 に設定、そして「Cust」列をパーティションとして設定しました。

rank_and_window_sum.png

SUM 操作を設定するには、

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

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

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

  4. 導出する値を格納する新しい列に名前を追加します。
    「合計(2Pと2F)」など、前後の値の数値を示す名前にすることを推奨します。

  5. 関数タイプを選択メニューでSUMを選択します。
    関数タイプを選択すると、新しいメニューが 2 つ表示されます。

  6. 列を選択メニューで、合計を出したい列を選択します。

  7. 前の値後の値のフィールドで、合計を出したい前の値と後の値の数値を入力します。

  8. ダイアログの右上にある適用ボタンをクリックします。
    新しいステップが 3 つ現れます。

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

  10. ダイアログのステップ 3 で、新しい列の値をソートする順番を選択します。

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

オフセット関数

オフセット関数は、選択した列の値と同じ値の列を新規に DataSet に追加します。ただし指定した数だけ行がオフセットされます。オフセット関数には 2 つのタイプがあります:ラグ関数は新しい列の値を元の列の値のにオフセットします。リード関数は新しい列の値を元の列の値のにオフセットします。ラグとリード両方の関数でパーティションは考慮されます。

ラグ

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

rank_and_window_lag.png

「Amount」列の値と「ラグ」列の一致する値の間には 3 行がオフセットとして挿入されています。また、パーティションのため、行 13 から 15 がグループ「Leo, Inc.」の最初の 3 行になり、そのため「ラグ」列のセルが空白になっています。

ラグ操作を設定するには、

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

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

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

  4. 導出する値を格納する新しい列に名前を追加します。
    ラグのオフセット値を示す名前にすることを推奨します。

  5. 関数タイプを選択メニューでラグを選択します。
    関数タイプを選択すると、新しいメニューが 2 つ表示されます。

  6. 列を選択メニューで、新しい列に表示させる値を含んでいる列を選択します。

  7. どの行を含めますかフィールドで目的のオフセットを選択します。

  8. ダイアログの右上にある適用ボタンをクリックします。
    新しいステップが 3 つ現れます。

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

  10. ダイアログのステップ 3 で、新しい列の値をソートする順番を選択します。

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

リード

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

rank_and_window_lead.png

「Amount」列の値と「リード」列の一致する値の間に 3 行のオフセットが挿入されています。また、パーティションのため、行 17 から 19 がグループ「Leo, Inc.」の最後の 3 行になり、「リード」列のセルが空白になっています。

リード操作を設定するには、

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

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

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

  4. 導出する値を格納する新しい列に名前を追加します。
    リードのオフセット値を示す名前にすることを推奨します。

  5. 関数タイプを選択メニューでリードを選択します。
    関数タイプを選択すると、新しいメニューが 2 つ表示されます。

  6. 列を選択メニューで、新しい列に表示させる値を含んでいる列を選択します。

  7. どの行を含めますかフィールドで目的のオフセットを選択します。

  8. ダイアログの右上にある適用ボタンをクリックします。
    新しいステップが 3 つ現れます。

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

  10. ダイアログのステップ 3 で、新しい列の値をソートする順番を選択します。

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