Adobe Campaignクエリー道場(2)〜重複値を探す

Adobe Campaignクエリー道場、[前回](https://qiita.com/JJSlider/items/dff8bb8a8c48ddad40bf "Adobe Campaignクエリー道場(1)〜ユニークな件数")の記事では、ユニークな値を持つ行を数えるクエリーを扱いました。今回は逆に、重複する値を取り出すことを考えてみましょう。
よくあるのは、ひとりが同じアドレスで何回も登録しちゃってる、みたいなケース。そんなダブってるアドレスを抽出したいというような場合です。
Campaignのクエリーではどうやるか。さっそく見てみましょう。

「受信者」スキーマで２回以上出現する「Ｅメール」を抽出し、その回数も得るクエリーを作ってみます。
「クエリ」アクティビティをダブルクリックし、ディメンジョンが「受信者」スキーマであることを確認、「フィルター条件」をダブルクリックしてください:
![filtering-conditions.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/1df97030-b74d-8c02-85a3-2450b45b4c12.png)
「フィルター」画面に遷移しました。フィルター条件は今回重要ではないので適宜設定していただければOKです。
ポイントはここから。「データグループ化句を追加」をチェックしてください:
![add-group-by-clause.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/3b60f4bd-3b70-0c10-c4ef-394a0e5e7060.png)
「グループ化の条件」で「式」の上をクリック、さらに「式を編集」をクリック[^1]:
![edit-expression.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/97963c95-36a2-562d-058b-dadffb8591e9.png)
「選択するフィールド」で「詳細選択」をクリック:
![shosai-sentaku.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/7021e3d1-e162-5cf6-0cb8-e3dd1142deb2.png)
「数式のタイプ」で「集計関数のプロセス」をクリック。前回紹介した集計関数がまた出てきました。今回は重複を数えるので「ユニーク」をチェックしないように注意してください。「カウント」をクリックしたら「次へ」:
![aggregation-count.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/b69d15fe-1b19-c996-1301-8291f4845452.png)
メールアドレスの重複を数えるので「Eメール」をクリックして「完了」:
![field-to-select.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/d46691f2-666b-0e33-69dd-4a5ae9a2b493.png)
「フィルター」画面に戻ってきました。「式」が「count(eメール)」となっていることを確認してください。メールアドレスをカウントしてるんだな、ってことは見て取れますね。
じゃあ「重複」はどう考えるんでしょう。それにはまず「演算子」の上をクリックしてドロップダウンリストから「より大きい」を選択:
![greater-than.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/090974b2-4607-b542-fa33-01e9822a9645.png)
そして「値」の上をクリックして数字の１を入力します。「重複」とは同じメールアドレスが１個だけじゃないということなので、１より大きい、という条件を設定したのです。
「count(eメール)」「より大きい」「1」となりましたね。「完了」をクリックして「クエリ」画面に戻りましょう:
![having-done.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/f417168b-6ecf-7c76-bb38-28c59433687d.png)
まだまだ大事なところが残っています。ついてきてください！
戻ってきた「クエリ」画面で「データを追加...」をクリックします:
![add-complementary-data.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/1b92a631-38ea-36b6-3580-e887598453f4.png)
フィルタリングディメンジョンを選択してください:
![select-data-type-to-add.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/ebe24540-552b-1fe5-16f8-276449d2600e.png)
![select-field-to-add.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/dec21c08-1477-0662-d56e-e0a878b22a32.png)
「追加するデータ」の「使用可能フィールド」で「Eメール」をダブルクリックし「出力列」に追加:
![2022-10-07_8-34-41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/1125c408-1a1e-3ccb-ac1a-1d909f4e0a63.png)
ここが今回最大のポイント。いま追加した「Eメール」の「グループ」列をチェックして「はい」にしてください:
![check-on-grouping.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/548b7be2-3ee1-8b49-d057-439bb675a358.png)
もう一度「使用可能フィールド」で「Eメール」をダブルクリックし「出力列」に追加:
![add-email.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/eca37cf2-6d73-7cb0-bf9e-c926f0baebf1.png)
追加した行の「式」の上をクリックし、こんどは直接「count(＠email)」と入力しましょう[^2]。「ラベル」と「式」の値が「count(eメール)」と変化することを確認してください:
![count-second-row.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/e2cf18af-3a46-ea55-caf3-7d7f02bd6cff.png)
「完了」して「クエリ」に戻り、「追加データを編集...」をクリック:
![edit-additional-data.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/b1b62c4e-0d06-ad3e-9b8d-96e72bf135b6.png)
「追加列」で「詳細設定パラメーター...」をクリック:
![advanced_parameters.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/3617d7de-4aef-c405-15d2-d626ed4c505b.png)
前回と同様、今回も「ターゲティングディメンジョンのプライマリキーの自動追加を無効にする」をチェックして「OK」:
![disable-adding-primary-keys.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/6a9cb969-2db4-4f41-9b6e-70b7ad6d98e6.png)
あとは「OK」を続けてダイアログを閉じていきワークフローキャンバスに戻れば完成です。結果を見るためにワークフローのプロパティで「2つの実行間の中間母集団の結果を保持」をチェックしてください[^3]:
![keep-interim-results.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/b1f9adab-15cb-dc3e-81bb-212c3595520c.png)
ワークフローを保存して実行してみましょう。「クエリ」からの遷移矢印を右クリック→「ターゲットを表示...」で結果を見ると:
![results.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2516149/266626b5-d746-ddc0-283a-f40965dec60c.png)
重複しているメールアドレスとその数が得られました。「count(eメール)」の見出しをクリックすると並べ替えができます。画面は▼で降順にしたところ。画面例では同アドレスを３回も登録している方もいることがわかりますね。

このとき発行されているSQLを見てみましょう:
```
SELECT   R0.sEmail, count(R0.sEmail) FROM NmsRecipient R0 WHERE ((R0.iRecipientId > 0 OR R0.iRecipientId < 0)) GROUP BY R0.sEmail HAVING (count(R0.sEmail) > 1)
```
「GROUP BY R0.sEmail」が、上記手順中の追加データで「グループ」にしたところ。出力行をメールアドレスごとにまとめています。
続く「HAVING (count(R0.sEmail) > 1)」が、上記手順中「データグループ化句を追加」による部分。同じメールアドレス数(count(R0.sEmail))が１個より多い(>1)という条件を与えることで「重複」を選び出しています。
SQLでは、group by 〜 havingは頻出の基本テクニック。「SQLの本質を理解するための重要な鍵」と言う識者もいるほどです。ぜひ上記操作手順と対照させながら、その意味と動きをつかんでみてください。

いかがでしょうか。重複を探したい、と思うことはなにげにけっこう多いのではないかと思います。とはいえなかなかクセが強いCampaignのインターフェイス。SQLならすらすら書ける、という方でも戸惑うことがあるかもしれません。でもこのシリーズではまだまだ、そんな迷える子羊たちを導く実践テクを紹介していくつもりです。フォローして使いこなしを身につけ、誇り高きCampaignクエラーになってください！

::: note warn
本稿の内容は筆者のオンプレミス型デモ環境(Adobe Campaign Classic 9342@6583a8a・PostgreSQL 11.14)上で実施した検証に基づきます。別環境における同様の動作を保証するものではありません。またデータは架空のものであり、既存の配信や実在の組織とはいっさい関係がありません。
:::

[^1]: ここで直接「count(＠email)」と入力してしまってもOKです。
[^2]: 注１で言及した直接入力です。
[^3]: 本番環境ではこの設定は決して行わないでください。本稿では学習目的のため一時的に設定しています。