Adobe Campaignクエリー道場(9)〜RowNum()関数でザ・ベストテン！ #Adobe

往年の名TV番組『ザ・ベストテン』をご存知でしょうか。毎週毎週、胸踊らせて観ていたよ、という方。あるいはネット動画やNight TempoのDJで知ってるよ、という方──様々いらっしゃるでしょう。
今回のお題は、そんな「ベストテン」をクエリーする方法です。

受信者の姓で多いもの上位10件を得たい。

似たような要件って何度かやんなかったっけ？　とお思いでしょうか。しかし、クエリーの結果を自動整列させて順番まで振るというのは、今までのやり方ではできません。新しいテクニックを学びましょう！

Step 1: 姓ごとの集計

「クエリ」アクティビティを作成し、ターゲティングディメンジョンを「受信者」とします。フィルター条件は任意ですが、この環境では「日本人」というフォルダーを対象にします:

フィルター条件を保存して「クエリ」に戻り、「データを追加...」クリックから「フィルタリングディメンジョンにリンクされたデータ」>「フィルタリングディメンジョンのデータ」>「追加するデータ」。ここまですっかりおなじみの手順ですね。
「使用可能フィールド」で「姓」をダブルクリックして「出力列」へ追加:

そして、最初のポイント！　追加した出力列「姓」の「グループ」をチェックします。これももうおなじみでしょう¹:

「出力列」ペイン横の「追加」ボタンをクリック:

「式」に以下をコピペ。前回取り上げたCount()関数で姓カラム(＠lastName)をカウントします:

Count(@lastName)

「エイリアス」に任意のわかりやすい文字列を入力します²。ここでは「num」としました:

「完了」で「クエリ」画面に戻り、「追加データを編集...」>「詳細設定パラメーター...」。この辺も慣れてきましたね:

いつもの、「重複行を削除 (DISTINCT)」をアンチェック、「ターゲティングディメンジョンのプライマリキーの自動追加を無効にする」をチェック:

ここまでクエリー道場第３回や前回とよく似ていることにお気づきかもしれません。姓ごとの集計を取るという点ではそれらの要件と共通してますからね。
で、ここからが新しいところです。

Step 2: 順位づけ

「詳細設定パラメーター」で「OK」をクリックして「追加列」画面へ戻り、また「出力列」ペイン横の「追加」ボタンをクリックします。追加された列の「式」に以下をコピペしましょう:

RowNum(, Desc(OrderBy(Count(@lastName))))

なんだこれ！　──あとで説明するとしてとりあえず進みますよ。
ここでも「エイリアス」に任意のわかりやすい文字列を入力。「rank」としました³:

「完了」をクリックして「クエリ」画面に戻りましょう。

Step 3: 上位10件を絞り込み

「クエリ」画面で「追加データを使用してターゲットを絞り込み...」をクリックします。これは初めてですね:

「追加データを使用してターゲットを絞り込み...」画面で「式」をクリックし、現れた「式を編集」ボタンをさらにクリック:

「式を選択」画面で「ライン数」をダブルクリック:

「完了」クリックで「追加データを使用してターゲットを絞り込み...」画面に戻ります。「オペレーター」のドロップダウンから「次よりも小さいか等しい」を選択。「値」には数値10を入力します:

「OK」を２回クリックしワークフローキャンバスに戻って「保存」。完成です！
「クエリ」アクティビティからの遷移矢印を右クリックしてメニューから「ターゲットを表示...」⁴:

「ライン数」が姓の数の順位です。ベストテンが得られてますね！
過去回ではずっと、「母集団を表示」画面で結果をソートするには明示的にカラム名をクリックしてやる必要がありました。でも今回は何もせずとも順番に並んでますね。というのも──。

解説

Step 2で以下の式を使いました:

RowNum(, Desc(OrderBy(Count(@lastName))))

このRowNum()が赤丸急上昇、今週のスポッッ…トライト！　RowNum()はCampaignのフィルタリング関数のひとつで、行に１、２、３…と連番を振るものです⁵。
以前Case()関数の説明で、「これらの関数は単独で使うことはないため、セットで考えて」という話をしましたが、ここでも同様に以下の関数がセットになります:

RowNum()
PartitionBy()
OrderBy()
Desc()

式をあらためて見てみましょう。「RowNum(, Desc(OrderBy(Count(＠lastName))))」という式ぜんたいで、連番──ここでは姓の数の順位を求めています。RowNum()の第１引数は本来「PartitionBy()」ですが、今回は使わないため、空にして「,」とカンマだけ置きました。第２引数は「Desc(OrderBy(Count(＠lastName)))」。内側から読んでいくと、「Count(＠lastName)」はStep 1で入力した姓の数。OrderBy()は、その姓の数でソートしろというものです。そしてDesc()で、それを降順にしています。姓の数の多い順ということですね。こうして明示的に指定しているためにアクティビティの出力結果が自ずからソートされているというわけです。
以上で、姓の数の多い順から番号を振っていく、つまり順位を得ることができました。

これに対し、バックエンドでは以下のようなSQLが生成されます:

SELECT   R0.sLastName, Count(R0.sLastName), Row_Number() OVER ( ORDER BY Count(R0.sLastName) DESC  ) FROM NmsRecipient R0 WHERE ((R0.iFolderId = 2196762)) AND ((R0.iRecipientId > 0 OR R0.iRecipientId < 0)) GROUP BY R0.sLastName

Step 1に対応するのが「Count(R0.sLastName)」と「GROUP BY R0.sLastName」。姓でグループ化してカウントしています。
そしてStep 2に対応するのが「Row_Number() OVER (ORDER BY Count(R0.sLastName) DESC)」という部分です。Row_Number()は、CampaignのRowNum()に対応するSQLの関数。すると当然、行番号を振るものですね。その際、何に対してどう番号づけするかを指定するのが後続のOVER。「ORDER BY Count(R0.sLastName)」はCampaign上の「OrderBy(Count(＠lastName))」という部分に対応し、姓の数を整列させるということです。そして「DESC」は降順の指定。Campaign上の「Desc(...)」に対応します⁶。
Step1・2の設定がSQLに展開されていることがわかりますね。

じゃあStep 3はどうでしょう。
これまでクエリー道場で取り上げてきた例では１アクティビティに１SQLが対応していました⁷。しかし今回は１アクティビティ内で２段階に分かれます。Step 3の「追加データを使用してターゲットを絞り込み...」が上記のSQLを受けて別SQLを発行しているのです:

SELECT W0.sLastName,W0.iNum,W0.iRank FROM wkf5219086_2_1 W0 WHERE (W0.iRank <= 10)

「wkf5219086_2_1」は、「上記のSQLを受けて」と言ったまさにその部分。Step 1・2の出力結果を持つ一時テーブルです。
「W0.sLastName,W0.iNum,W0.iRank」は「姓」「姓の数」「ライン数」各カラム。Step 2で指定したエイリアス「num」「rank」が「iNum」「iRank」と対応しているところに注目してください。
そして最後に「WHERE (W0.iRank <= 10)」として、「ライン数」カラムの上位10番までを絞り込んでいます⁸。これでザ・ベストテンというわけです。

おつかれさまでした！　華やかなベストテンの裏側に、地道な技術の積み重ねがありましたね。
RowNum()は考え方が独特なため、次回も引き続き説明していきたいと思います。お楽しみに。それでは最後に、はいポーズ！

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

グループ化については以下に繰り返し取り上げています:
Adobe Campaignクエリー道場(2)〜重複値を探す
 Adobe Campaignクエリー道場(3)〜最新日付でビバノンノン
 Adobe Campaignクエリー道場(4)〜HAVING句でブラボー！
Adobe Campaignクエリー道場(8)〜CASE式とグループ化 ↩
「Count(＠lastName)」が自動的に変換され「式」と「ラベル」が「姓の数」と表示されます。 ↩ ↩²
「姓の数」²と同様、Campaignの自動変換で「式」と「ラベル」が「ライン数」と表示されます。 ↩
さいきん書いてませんでしたが、「ターゲットを表示...」にはワークフロープロパティで「２つの実行間の中間母集団の結果を保持」のチェックが必要です。しかしこの設定は本番環境では決してしないでください。 ↩
関数のリストの「ウィンドウイング関数」参照。 ↩
Campaign上の「OrderBy()」「Desc()」が「関数」であるのに対し、SQLで対応する「ORDER BY」「DESC」は、「句」になります。 ↩
内部ログ生成などは勘案せず、ビジネスロジック観点での話です。 ↩
分けなくても前のSQLでlimit=10でいいじゃん、と思うところですが──Campaignインターフェイス上にはその指定方法がなく、本稿のやり方になります。 ↩