Adobe Campaignクエリー道場、前回の記事では、ユニークな値を持つ行を数えるクエリーを扱いました。今回は逆に、重複する値を取り出すことを考えてみましょう。
よくあるのは、ひとりが同じアドレスで何回も登録しちゃってる、みたいなケース。そんなダブってるアドレスを抽出したいというような場合です。
Campaignのクエリーではどうやるか。さっそく見てみましょう。
「受信者」スキーマで２回以上出現する「Ｅメール」を抽出し、その回数も得るクエリーを作ってみます。
「クエリ」アクティビティをダブルクリックし、ディメンジョンが「受信者」スキーマであることを確認、「フィルター条件」をダブルクリックしてください:
「フィルター」画面に遷移しました。フィルター条件は今回重要ではないので適宜設定していただければOKです。
ポイントはここから。「データグループ化句を追加」をチェックしてください:
「グループ化の条件」で「式」の上をクリック、さらに「式を編集」をクリック1:
「選択するフィールド」で「詳細選択」をクリック:
「数式のタイプ」で「集計関数のプロセス」をクリック。前回紹介した集計関数がまた出てきました。今回は重複を数えるので「ユニーク」をチェックしないように注意してください。「カウント」をクリックしたら「次へ」:
メールアドレスの重複を数えるので「Eメール」をクリックして「完了」:
「フィルター」画面に戻ってきました。「式」が「count(eメール)」となっていることを確認してください。メールアドレスをカウントしてるんだな、ってことは見て取れますね。
じゃあ「重複」はどう考えるんでしょう。それにはまず「演算子」の上をクリックしてドロップダウンリストから「より大きい」を選択:
そして「値」の上をクリックして数字の１を入力します。「重複」とは同じメールアドレスが１個だけじゃないということなので、１より大きい、という条件を設定したのです。
「count(eメール)」「より大きい」「1」となりましたね。「完了」をクリックして「クエリ」画面に戻りましょう:
まだまだ大事なところが残っています。ついてきてください！
戻ってきた「クエリ」画面で「データを追加...」をクリックします:
フィルタリングディメンジョンを選択してください:
「追加するデータ」の「使用可能フィールド」で「Eメール」をダブルクリックし「出力列」に追加:
ここが今回最大のポイント。いま追加した「Eメール」の「グループ」列をチェックして「はい」にしてください:
もう一度「使用可能フィールド」で「Eメール」をダブルクリックし「出力列」に追加:
追加した行の「式」の上をクリックし、こんどは直接「count(＠email)」と入力しましょう2。「ラベル」と「式」の値が「count(eメール)」と変化することを確認してください:
「完了」して「クエリ」に戻り、「追加データを編集...」をクリック:
「追加列」で「詳細設定パラメーター...」をクリック:
前回と同様、今回も「ターゲティングディメンジョンのプライマリキーの自動追加を無効にする」をチェックして「OK」:
あとは「OK」を続けてダイアログを閉じていきワークフローキャンバスに戻れば完成です。結果を見るためにワークフローのプロパティで「2つの実行間の中間母集団の結果を保持」をチェックしてください3:
ワークフローを保存して実行してみましょう。「クエリ」からの遷移矢印を右クリック→「ターゲットを表示...」で結果を見ると:
重複しているメールアドレスとその数が得られました。「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クエラーになってください！
本稿の内容は筆者のオンプレミス型デモ環境(Adobe Campaign Classic 9342@6583a8a・PostgreSQL 11.14)上で実施した検証に基づきます。別環境における同様の動作を保証するものではありません。またデータは架空のものであり、既存の配信や実在の組織とはいっさい関係がありません。