このページは解答編です。
↓ネ
↓タ
↓バ
↓レ
↓防
↓止
答えと解説
設問はこちらでした。
P-039: レシート明細データ（receipt.csv）から、売上日数の多い顧客の上位20件を抽出したデータと、売上金額合計の多い顧客の上位20件を抽出したデータをそれぞれ作成し、さらにその2つを完全外部結合せよ。ただし、非会員（顧客IDが”Z”から始まるもの）は除外すること。
※出力データの並びは、売上日数の降順かつ、売上金額の降順、顧客IDの昇順でソートすること
※同一数の場合はcustomer_idの小さな方を採用する
解答ワークフローは以下のようになります。
今回は、一つのデータから複数のデータストリームに分け、データ加工を行ったのちに結合する問題です。ポイントは完全外部結合でしょうか。
完全外部結合とは
SQLを勉強していると、左結合、右結合、内部結合、完全外部結合、などの言葉が出てきます。これの違いをまず解説します。
そもそもの話として、結合の仕方でこの使い分けがあります。基本的には左側にあるデータ、右側にあるデータ、これらをどのように結合するかで、左結合、右結合という言葉となっています。
つまりベン図を考えてみましょう。以下のように左右に二つのデータがあるとします。
これを結合したときに、どの部分のデータを取り出すか、というのがこの一連の言葉になります。
左外部結合（LEFT OUTER JOIN）の場合は以下の通り、重なっている部分と左側の部分になります。
完全外部結合は、結合できたデータと結合できなかったデータすべてを一つのデータストリームにまとめるものが完全外部結合（FULL OUTER JOIN）です。つまり以下のようになります。
なお、Alteryxの結合ツールは内部結合、左結合、右結合となります。つまり以下のようになっています。
左外部結合にしようとすると、L出力とJ出力を組み合わせる必要がありますし、今回のように完全外部結合の場合は、L、J、Rすべての出力を組み合わせる必要があります。これを実現するには、ユニオンツールでまとめます。
以下のようなワークフローになるわけです。
ワークフローの作成
それでは実際にワークフローの作成に取り掛かりましょう。
まず、「非会員（顧客IDが”Z”から始まるもの）は除外」ということなので、フィルターツールで取り除いていきます。
!StartsWith([customer_id], "Z")次に、データ型を変更します。今回は、amountの合計を取るため、数値型にしていきましょう。
次に、集計を行います。sales_ymdの重複なしカウントとamountの合計を取っていく必要があります。別々に集計ツールを使っても良いですし、ひとまとめにしてもオッケーです。今回はひとまとめに集計を行いたいと思います。
customer_idでグループ化、amountで合計、sales_ymdで重複を除いてカウントを追加します。
それぞれamountの合計はamount、sales_ymdのカウントはsales_ymdと出力フィールド名は変えています。
売上日数の多い顧客の上位20件の抽出
ここで、二つのデータストリームに分けて処理を行います。売上日数の多い顧客の抽出ということなので、ソートツールを使いsales_ymdの降順に並べます。なお、同一数の場合はcustomer_idの小さな方を採用するので、これもcustomer_idの昇順ということでソートの条件に追加します。
次に、上位２０件の抽出なので、サンプリングツールで最初の２０行を抽出します。
次に不要な項目をセレクトツールで削除します。
これで、「売上日数の多い顧客の上位20件の抽出」が完了しました。
売上金額合計の多い顧客の上位20件の抽出
次に、売上金額合計の多い顧客の上位20件の抽出ということなので、ソートツールを使いamountの降順に並べます。なお、同一数の場合はcustomer_idの小さな方を採用するので、これもcustomer_idの昇順ということでソートの条件に追加します。
次に、上位２０件の抽出なので、サンプリングツールで最初の２０行を抽出します。
次に不要な項目をセレクトツールで削除します。
これで、「売上金額合計の多い顧客の上位20件」が完了しました。
二つのデータストリームを結合してまとめる
それではここまでで作成したデータストリームの外部結合を作成していきましょう。
まず、結合ツールにそれぞれの出力を入力します。「売上日数の多い顧客の上位20件の抽出」はL入力、「売上金額合計の多い顧客の上位20件の抽出」はR入力にそれぞれインプットします。
設定は以下の通りで、特定のフィールドで結合を選択し、customer_idをキーフィールドとします。不要なRightのcustomer_idは削除しましょう。
次に、完全外部結合のために３つの出力（L、J、R）をユニオンツールでまとめていきます。設定はデフォルトで構いません。
これで基本的なデータは完成しています。あとは出力のためにデータを整えていきます。ソートツールで、sales_ymdの降順、amountの降順、customer_idの昇順に並べ替えます。
これで完成です。
まとめ
今回も、結合ツールでデータの結合を行いました。データ結合はSQLで行う場合の一般的な考え方と異なり、例えばSQLで頻繁に行うような左外部結合などはユニオンツールとの組み合わせが必要です。SQLに慣れている方は、この考え方を理解しておいてください。
コメント