SQL の例

学習の目的

この単元を完了すると、次のことができるようになります。

SQL ステートメントの関数を認識する。
ストリーミングインサイト SQL ステートメントに期間を含める。
SQL の作成に役立つリソースを見つける。

Data Cloud のストリーミングインサイト

SQL でのインサイトの作成方法を学ぶには、ステートメントの例を見ることが最善の方法です。SQL ステートメントの各セクションが何を行うかを理解することで、自分でステートメントを作成するために利用できるパターンを覚えることができます。例を見る前に、インサイトには計算済みインサイトとストリームインサイトの 2 種類があることを知っておくことが重要です。計算済みインサイトでは、保存されているデータに基づいてクエリを実行して複雑な計算を行うことができます。ストリーミングインサイトは、リアルタイムのデータに基づいて作成されます。

ストリーミングインサイトでは、次のようなことを行えます。

連続的に移動するデータの時系列分析を生成する。
有用なパターンを見つけ、データアクションを介して他のアプリケーションとインサイトを共有する。
インサイトビルダーと SQL を使用してインサイトを作成する。
Java Database Connectivity (JDBC) API や Tableau のようなビジュアライゼーションツールを併用する。

計算済みインサイトの SQL 例

では基本的なインサイトの例から見ていきましょう。この最初の例は、Marketing Cloud でのメールエンゲージメントデータを計算し、統合された個々のプロファイルデータでグループ化しています。

SELECT COUNT( EmailEngagement__dlm.Id__c) as email_open_count__c,
UnifiedIndividual__dlm.Id__c as customer_id__c
FROM
EmailEngagement__dlm
JOIN
IndividualIdentityLink__dlm
ON
IndividualIdentityLink__dlm.SourceRecordId__c = EmailEngagement__dlm.IndividualId__c


and IFNULL(IndividualIdentityLink__dlm.KQ_SourceRecordId__c, ‘’) = IFNULL(EmailEngagement__dlm.KQ_IndividualId__c, ‘’)
and EmailEngagement__dlm.EngagementChannelActionId__c ='Open'
JOIN
UnifiedIndividual__dlm
ON
UnifiedIndividual__dlm.Id__c = IndividualIdentityLink__dlm.UnifiedRecordId__c
GROUPBY
customer_id__c

この SQL ステートメントの各セクションを詳しく見てみましょう。

セクション 1

SELECT COUNT( EmailEngagement__dlm.Id__c) as email_open_count__c,
UnifiedIndividual__dlm.Id__c as customer_id__c

実行する内容: 統合された個人 ID (顧客 ID とも呼ばれます) に応じて、開封されたメールの数を計算します。

セクション 2

FROM
EmailEngagement__dlm

実行する内容: この情報を、メールのエンゲージメント DMO で特定します。

セクション 3

JOIN
IndividualIdentityLink__dlm
ON
IndividualIdentityLink__dlm.SourceRecordId__c = EmailEngagement__dlm.IndividualId__c
and IFNULL(IndividualIdentityLink__dlm.KQ_SourceRecordId__c, ‘’) = IFNULL(EmailEngagement__dlm.KQ_IndividualId__c, ‘’)
and EmailEngagement__dlm.EngagementChannelActionId__c ='Open'
JOIN
UnifiedIndividual__dlm
ON
UnifiedIndividual__dlm. Id__c = IndividualIdentityLink__dlm.UnifiedRecordId__c

実行する内容: このステップでは、メールエンゲージメント DMO と個人 ID リンク DMO を接続します。接続にはソースレコード ID と個人 ID の外部キーとそれぞれのキー修飾子属性を使用し、メールの開封に相当するメールエンゲージメントに基づいて結合します。また、ID と統合レコード ID に基づいて、このデータを統合個人 DMO に接続します。

セクション 4

GROUPBY
customer_id__c

実行する内容: この情報を顧客 ID に基づいてグループ化します。

次の例に進む前に、この例でもう 1 つ注意すべきことがあります。UnifiedIndividual オブジェクトは、統合個人 ID と個人 ID の対応付けが定義されたブリッジオブジェクト (個人 ID リンクなど) を使用して、エンゲージメントオブジェクト (EmailEngagement など) に関連付けられるということです。

次にランキング関数を使った SQL の例を見てみましょう。このステートメントでは、顧客の支出額を計算し、統合個人のすべてにわたる支出に基づいて顧客をランク付けします。

SELECT
UnifiedIndividual__dlm.ssot__Id__c AS customer_id__c,
RANK() OVER (ORDER BY SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) ) AS customer_rank_based_on_spend__c,


SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) AS customer_spend__c
FROM
ssot__SalesOrder__dlm
JOIN
IndividualIdentityLink__dlm
ON (ssot__SalesOrder__dlm.ssot__SoldToCustomerId__c = IndividualIdentityLink__dlm.SourceRecordId__c)
AND IFNULL(ssot__SalesOrder__dlm.KQ_SoldToCustomerId__c, ‘’) = IFNULL(IndividualIdentityLink__dlm.KQ_SourceRecordId__c, ‘’)
LEFT OUTER JOIN UnifiedIndividual__dlm
ON (IndividualIdentityLink__dlm.UnifiedRecordId__c = UnifiedIndividual__dlm.ssot__Id__c)
GROUP BY customer_id__c
HAVING RANK() OVER (ORDER BY SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) ) < 1000

このステートメントをさらに詳しく見ていきましょう。

セクション 1

SELECT
UnifiedIndividual__dlm.ssot__Id__c AS customer_id__c,
RANK() OVER (ORDER BY SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) ) AS customer_rank_based_on_spend__c,
SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) AS customer_spend__c

実行する内容: すべての統合個人を対象に、支出総額に基づいて各顧客をランク付けします。

セクション 2

FROM
ssot__SalesOrder__dlm

実行する内容: この情報を販売注文 DMO で探します。

セクション 3

JOIN
IndividualIdentityLink__dlm
ON (ssot__SalesOrder__dlm.ssot__SoldToCustomerId__c = IndividualIdentityLink__dlm.SourceRecordId__c)
AND IFNULL(ssot__SalesOrder__dlm.KQ_SoldToCustomerId__c, ‘’) = IFNULL(IndividualIdentityLink__dlm.KQ_SourceRecordId__c, ‘’)
LEFT OUTER JOIN UnifiedIndividual__dlm
ON (IndividualIdentityLink__dlm.UnifiedRecordId__c = UnifiedIndividual__dlm.ssot__Id__c)

実行する内容: 顧客 ID と個人 ID、およびそれぞれのキー修飾子属性で、販売注文 DMO から個人 ID リンク DMO にデータを結合します。結合には、統合レコード ID と ID に基づいて、統合個人 DMO で一致するデータを使用します。

セクション 4

GROUP BY customer_id__c

実行する内容: この情報を顧客 ID に基づいてグループ化します。

セクション 5

HAVING RANK() OVER (ORDER BY SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) ) < 1000

実行する内容: 支出総額に従って、1000 人未満の顧客をランキングに含めます。

ストリーミングインサイト

計算済みインサイトの例をいくつか確認しましたので、次はストリーミングインサイト用の SQL の作成を見てみましょう。SQL を使用したストリーミングインサイトの作成は、期間を考慮する必要があること以外は、計算済みインサイトの作成とほぼ同じです。

ストリーミングインサイト SQL の例

5 分間のページビューを表示する例を見てみましょう。

SELECT COUNT( RealTimeMobileEvents__dlm.pageviews__c ) as page_views__c,
ssot__Individual__dlm.ssot__Id__c as customer_id__c,
ssot__Individual__dlm.KQ_Id__c as kq_customer_id__c,
RealTimeMobileEvents__dlm.product__c as product__c,
WINDOW.START as start__c,
WINDOW.END as end__c
FROM
RealTimeMobileEvents__dlm
JOIN
 ssot__Individual__dlm
ON
ssot__Individual__dlm.ssot__Id__c = RealTimeMobileEvents__dlm.deviceId__c
AND IFNULL(ssot__Individual__dlm.KQ_Id__c, ‘’) = IFNULL(RealTimeMobileEvents__dlm.KQ_deviceId__c, ‘’)
GROUP BY
window( RealTimeMobileEvents__dlm.dateTime__c ,'5 MINUTE'),
Customer_id__c, kq_customer_id__c

この SQL ステートメントで計算済みインサイトと明らかに異なるのは、WINDOW コマンドです。これらのコマンドは、結果をどのようにグループ化するかを定義します。この例では 5 分以内の結果をグループ化しています。

WINDOW.START as start__c,
WINDOW.END as end__c
GROUP BY
window( RealTimeMobileEvents__dlm.dateTime__c ,'5 MINUTE'),

この式の結果の例を示します。

START_C

END_C

CUSTOMER_ID_C

PRODUCT_C

PAGE_VIEWS_C

12.05

HK0012

12.05

12.1

JK0078

12.1

12.15

HK0078

別の例を見てみましょう。

SELECT
SUM(
MobileApp_RT_Events__dlm.productPurchaseWeb_orderQuanity__c
) as order_placed__c,
MobileApp_RT_Events__dlm.AddToCartWeb_productId__c as product__c,
WINDOW.START as start__c,
WINDOW.ENDas end__c
FROM
MobileApp_RT_Events__dlm
GROUPBY
window(
MobileApp_RT_Events__dlm.dateTime__c,
'5 MINUTE'
),
MobileApp_RT_Events__dlm.AddToCartWeb_productId__c

このステートメントの各セクションを詳しく見てみましょう。

セクション 1

SELECT
SUM(
MobileApp_RT_Events__dlm.productPurchaseWeb_orderQuanity__c
) as order_placed__c,
MobileApp_RT_Events__dlm.AddToCartWeb_productId__c as product__c,
WINDOW.START as start__c,
WINDOW.END as end__c

実行する内容: 開始時刻と終了時刻の間に、MobileApp イベントストリーミングソースに基づいて発注された注文の合計金額を求めます。

セクション 2


FROM
MobileApp_RT_Events__dlm

実行する内容: MobileApp イベントストリーミングソースを使用します。

セクション 3

GROUPBY
window(
MobileApp_RT_Events__dlm.dateTime__c,
'5 MINUTE'
),
MobileApp_RT_Events__dlm.AddToCartWeb_productId__c

実行する内容: 商品 ID に従って結果を 5 分ごとに集計し、注文数量、商品、開始時刻、終了時刻を結果に含めます。

ステートメントの作成

基本的な概念といくつかの例を理解しましたので、インサイトを作成する際のさまざまなオプションを探りましょう。SQL ステートメントに追加できる関数は他にもたくさんあり、それらを使用して結果をさらに絞り込むことができます。

「計算済みインサイトの SQL ルール」を参照してください。
Data Cloud の Git Hub リポジトリにある SQL サンプルもご覧ください。

そして、計算済みインサイトの作成方法については、ヘルプページの「計算済みインサイト」を参照してください。これで Data Cloud で SQL クエリを最大限に活用する準備が整いました。

予想時間

トピック

ヘルプをお探しですか?

Data Cloud のリソース

SQL の例

学習の目的

Data Cloud のストリーミングインサイト

計算済みインサイトの SQL 例

ストリーミングインサイト

ストリーミングインサイト SQL の例

ステートメントの作成

リソース