【第178回】 Marketing Cloud SQL 中級編（2）- 集計関数、GROUP BY

これまでに Markeitng Cloud SQL の基本 に始まり、SELECT、FROM、WHERE 、そして Dataview の使用、日付関数、IN（NOT IN）、IS NULL、JOIN、AND、OR、CASE、DISTINCT の使い方について学習しました。

これらの SQL 超入門の記事を学習することで、以下に示すような ①②③④ の事例にも対応できるようになりました。

① データエクステンション内のデータを調査したい
② データビューを使用して、エンゲージ（関与）した顧客を知りたい
③ データビューを活用して、別の配信リストを作りたい
④ 2 つ以上のデータエクステンションを組み合わせて、別のデータエクステンションを作成したい

そして、Marketing Cloud SQL 中級編では、さらに役立つ内容を紹介しています。前回の記事では CASE、DISTINCT を学習し、今回の記事では 集計関数 と GROUP BY を学習したいと思います。

---

■ 集計関数 と GROUP BY について

集計関数とは、その名のイメージ通りになりますが、ある項目のレコード数をカウントしたり、合計したり、平均値を求めたりする関数になります。

この集計関数を使用するには、GROUP BY をセットで使用する必要があります。どの単位で集計しますか？という決めが必要になるからですね。イメージとしては、購読者ごとの単位なのか、送信ジョブごとの単位なのか、ジャーニーごとの単位なのか、そのようなものが思い浮かびます。

それでは、代表的な集計関数を書いておきます。

■ COUNT ・・・ レコードを「カウント」する
■ SUM ・・・ レコードを「合計」をする
■ AVG ・・・ レコードの「平均」を求める
■ MAX ・・・ レコードの「最大値」を出す
■ MIN ・・・ レコードの「最小値」を出す

集計関数の書き方は統一されており、SELECT 句内で項目を指定して、( )「括弧」で括ります。AS（エイリアス）を設けることも、お忘れなく。

SELECT COUNT(Id) AS [CountId]

それでは、ここで、下記のような注文データがあるとします。このデータエクステンション内のレコードは、全体で 999 レコードあります。 

このデータから、各購読者（UserId）の購入回数を確認してみましょう。まずはシンプルに、以下のようなクエリを実行してみます。

SELECT COUNT(UserId) AS [PurchaseCount]
FROM MasterOrder

すると、下記のように、各購読者（UserId）の購入回数ではなく、このデータエクステンション全体のレコード数がカウントされるだけでした。

これではまずいので、ここで登場するのが GROUP BY です。どの単位で集計しますか？というものを指定する必要があるわけですね。

今回は、各購読者（UserId）単位となりますので、GROUP BY UserId という記述を追加します。

以下のような形になりますので、こちらも実行してみます。

SELECT COUNT(UserId) AS [PurchaseCount]
FROM MasterOrder
GROUP BY UserId

これで以下の通り、とりあえず購入回数だけが表示されました。また、このデータエクステンションに 10 名分の購入データがあることも分かります。

但し、これでは誰が購入したか？までは分かっていませんよね。そこで SELECT で UserId の項目を追加します。こちらで実行してみます。

SELECT UserId,
       COUNT(UserId) AS [PurchaseCount]
FROM MasterOrder
GROUP BY UserId

すると、各購読者ごとの購入回数が表示されました。成功です。

それでは少し応用です。

前回の記事で学習した CASE を使うことで、購入回数から顧客ランクを定義することも可能です。例えば、購入回数が 100 以上を「優良顧客」、100 未満を「一般顧客」と定義してみましょう。

以下のクエリを実行します。

SELECT UserId,
       COUNT(UserId) AS [PurchaseCount],
       CASE
           WHEN COUNT(UserId) >= 100 THEN '優良顧客'
           ELSE '一般顧客'
       END COLLATE Japanese_CS_AS_KS_WS AS [CustomerRank]
FROM MasterOrder
GROUP BY UserId

すると、下記の通り 100 回以上購入している場合に「優良顧客」というレコードを持っていることが確認できました。

さて、このようなデータは集計後に、配信マスタや配信リストに組み込むことができます。これまでの学習した内容をフル活用すると、以下のような形になります。

SELECT a.Id,
       a.Email,
       a.Name COLLATE Japanese_CS_AS_KS_WS AS [Name],
       a.Prefecture COLLATE Japanese_CS_AS_KS_WS AS [Prefecture],
       b.PurchaseCount,
       b.CustomerRank
FROM MasterSubscribers a
INNER JOIN (
    SELECT UserId,
           COUNT(UserId) AS [PurchaseCount],
           CASE
               WHEN COUNT(UserId) >= 100 THEN '優良顧客'
               ELSE '一般顧客'
           END COLLATE Japanese_CS_AS_KS_WS AS [CustomerRank]
    FROM MasterOrder
    GROUP BY UserId
) b ON a.Id = b.UserId

ここでのポイントは、集計関数のクエリがサブクエリ（仮想のデータテーブル）として扱われているところです。そのサブクエリと、配信マスタを INNER JOIN しているわけですね。

また、それぞれの購読者の Id の呼び名が異なるので ON a.Id = b.UserId で結合しているのも、間違えやすいポイントです。

これを実行することで、以下のように、配信マスタに「購入回数」と「顧客ランク」を付加することができました。

いかがでしたでしょう？

中級編になって、だんだん複雑になってきましたが、色々できて楽しいかも？と思う気持ちの方が多いのではないでしょうか。

それでは、最後に GROUP BY を説明したオマケで、HAVING についても説明しておきます。

---

■ HAVING について

HAVING とは、GROUP BY を使ったクエリに対して使用ができ、集計関数を使った集計後の値に対して、条件化ができます。

具体例で示しますと、先ほど、各顧客の「顧客ランク」を設定したと思います。この顧客ランクが「優良顧客」だけの人で配信リストを作成したいという場合があるかと思います。

この時、HAVING 「顧客ランク」= '優良顧客' と設定することで、「優良顧客」だけに絞られた配信リストを作成することができます。

但し、この HAVING を使用する際に、AS（エイリアス）で項目名を指定できないので注意が必要です。HAVING CustomerRank = '優良顧客' とはできないということですね。このことはクエリの実行順に関連しています。

よって、以下のようなクエリになります。

SELECT a.Id,
       a.Email,
       a.Name COLLATE Japanese_CS_AS_KS_WS AS [Name],
       a.Prefecture COLLATE Japanese_CS_AS_KS_WS AS [Prefecture],
       b.PurchaseCount,
       b.CustomerRank
FROM MasterSubscribers a
INNER JOIN (
    SELECT UserId,
           COUNT(UserId) AS [PurchaseCount],
           CASE
               WHEN COUNT(UserId) >= 100 THEN '優良顧客'
               ELSE '一般顧客'
           END COLLATE Japanese_CS_AS_KS_WS AS [CustomerRank]
    FROM MasterOrder
    GROUP BY UserId
    HAVING CASE
               WHEN COUNT(UserId) >= 100 THEN '優良顧客'
               ELSE '一般顧客'
           END = '優良顧客'
) b ON a.Id = b.UserId

少し難解ですね。再度、CASE 式の部分を HAVING の後ろにコピペしてあげる必要があるわけです。

こちらで実行しますと、下記の通り「優良顧客」だけに絞られた配信リストが作成できました。成功です。

一旦、この HAVING は使用頻度が低いので、忘れてもらって結構です。

この HAVING を使用せずとも、以下のような WHERE を使った書き方をすれば、結果は同じになりますので、以下のような書き方でも OK です。

SELECT a.Id,
       a.Email,
       a.Name COLLATE Japanese_CS_AS_KS_WS AS [Name],
       a.Prefecture COLLATE Japanese_CS_AS_KS_WS AS [Prefecture],
       b.PurchaseCount,
       b.CustomerRank
FROM MasterSubscribers a
INNER JOIN (
    SELECT UserId,
           COUNT(UserId) AS [PurchaseCount],
           CASE
               WHEN COUNT(UserId) >= 100 THEN '優良顧客'
               ELSE '一般顧客'
           END COLLATE Japanese_CS_AS_KS_WS AS [CustomerRank]
    FROM MasterOrder
    GROUP BY UserId
) b ON a.Id = b.UserId
WHERE b.CustomerRank = '優良顧客'

今回は以上です。

次の記事はこちら

前回の記事はこちら

私の note のトップページはこちら

Nobuyuki Watanabe｜note