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