SQL の概念の理解

学習の目的

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

SQL の用語と概念を理解する。
Data Cloud インサイトの SQL 構造を理解する。

Data Cloud のインサイト

データは複数のソースから Salesforce Data Cloud に取り込まれます。インサイトでは、新しいメトリクスを作成したり、取引先のデータを整理、グループ化、または操作したりすることができます。構造化クエリ言語 (SQL) に基づき、インサイトには計算済みインサイトとストリーミングインサイトの 2 種類があります。計算済みインサイトでは、保存されているデータに基づいて複雑な計算を行うことができます。ストリーミングインサイトは、リアルタイムのデータに基づいて作成されます。「Data Cloud でインサイトを作成する」で説明しているように、Data Cloud で新しいインサイトを作成するにはさまざまな方法があります。SQL はその 1 つです。インサイトは SQL のエキスパートでなくても作成できますが、インサイトの作成で SQL がどのように使用されるかを知っておくと役に立ちます。このモジュールでは、基本的な SQL の概念を説明し、自分のアカウントでインサイトを作成する際に役立つ例を提供します。

インサイトの作成に関する用語

まず、インサイトに関連する一般的な用語について説明します。

データストリーム: データストリームは、Salesforce Data Cloud に取り込まれるデータソースです。たとえば、Marketing Cloud の顧客データエクステンションなどです。
データモデルオブジェクト (DMO): データストリームは、DMO に従ってデータモデルに対応付けられます。一般的な DMO には、販売注文、関係者、エンゲージメントデータなどがあります。
属性: 属性とは、データストリームに含まれる情報の項目です。たとえば、個人の名や顧客 ID などです。
メジャー: メジャーには、支出総額や平均注文金額などの属性の集計値が含まれます。
ディメンション: ディメンションには、メジャーを分類するために使用される定性的な値が含まれます。たとえば、すべての顧客の支出総額を確認したい場合、顧客 ID をディメンションとして支出総額のメジャーに関連付けられることができます。
外部キー: 外部キーとは、リレーショナルデータベースにおいて、データソース間のリンクを提供する列のことで、たとえば、顧客 ID 番号などです。
完全修飾キー: 完全修飾キー (FQK) は、CRM の取引先責任者 ID や Salesforce Marketing Cloud の購読者キーなどのソースキーとキー修飾子で構成される複合キーです。さまざまなソースから取得したデータを Data Cloud データモデルでハーモナイズするときに、完全修飾キーを使用してキーの競合を回避します。
プライマリキー: ユーザーが選択したレコードの一意の識別子。たとえば、顧客の電子メールアドレスや商品 SKU などです。

SQL 構造

用語を理解したところで、Data Cloud のインサイトを作成するために使用する SQL のキーワードと構造を見てみましょう。SQL はキーワードで構成されたステートメントまたは式として記述されます。

次のキーワードを使用できます。

SELECT: 属性の項目 API 参照名と実行する計算を選択します。

FROM: 検索するデータソースのオブジェクト API 参照名を特定します。

JOIN (省略可能): このキーワードを使用すると、指定した条件に基づいて別のデータソースからもデータを検索できます。WHERE (省略可能): データに関する条件ステートメントを含めるには、このキーワードを使用します。GROUP BY: 選択したメジャーをどのように整理または分類するかを指定します。

SQL ステートメントは次のように書きます。

SELECT <Attributes>, <Aggregation[_Measures_]>
FROM <Data Model Object>
JOIN [Inner | Left | Right | Full] <Data Model Object> [Optional]
WHERE <predicate on rows> [Optional]
GROUP BY <columns[_Dimensions_]>

例

この例では、顧客の支出総額を照会しています。


SELECT
  SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) AS customer_spend__c,
  ssot__Individual__dlm.ssot__Id__c AS customer_id__c,
  ssot_Individual__dlm.KQ_Id__c AS kq_customer_id__c
FROM ssot__SalesOrder__dlm
JOIN ssot__Individual__dlm
ON (ssot__SalesOrder__dlm.ssot__SoldToCustomerId__c = ssot__Individual__dlm.ssot__Id__c)
AND IFNULL(ssot__SalesOrder__dlm.KQ_SoldToCustomerId__c, ‘’) = IFNULL(ssot__Individual__dlm.KQ_Id__c, ‘’)
WHERE
(ssot__SalesOrder__dlm.ssot__CreatedDate__c <= date_add(current_date(), 365.0))
GROUP BY
customer_id__c, kq_customer_id__c

それぞれのセクションをさらに詳しく説明しましょう。

セクション 1

SELECT
  SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) AS customer_spend__c,
  ssot__Individual__dlm.ssot__Id__c AS customer_id__c,
  ssot_Individual__dlm.KQ_Id__c AS kq_customer_id__c

実行する内容: 探している情報 (顧客消費額) と、そのメジャーを集計する方法 (合計) を定義します。メトリクス (顧客消費額) が計算されると、メジャー customer_spend__c としてシステムに保存されます。

セクション 2

FROM ssot__SalesOrder__dlm

実行する内容: データのソース (SalesOrder DMO) を特定します。

セクション 3


JOIN
 ssot__Individual__dlm
ON
(ssot__SalesOrder__dlm.ssot__SoldToCustomerId__c = ssot__Individual__dlm.ssot__Id__c)
AND IFNULL(ssot__SalesOrder__dlm.KQ_SoldToCustomerId__c, ‘’) = IFNULL(ssot__Individual__dlm.KQ_Id__c, ‘’)

実行する内容: 別の DMO (Individual DMO) のデータを販売注文 DMO に結合します。ON は、DMO 間の接続ポイントまたは外部キーを決定します。この例では、2 つの DMO 間の外部キーには、SalesOrder DMO の当事者 ID とキー修飾子項目、および Individual DMO の ID と対応キー修飾子項目の 2 つの項目があります。テーブル結合にキー修飾子属性を含めることで、データの正確性が保証されます。基礎となるすべての DLO でキー修飾子が設定されていない場合は、IFNULL() 関数を使用します。空白が混在している可能性があり、IFNULL() は JOIN の精度を向上させるために整合性を保証します。

セクション 4

WHERE
ssot__SalesOrder__dlm.ssot__CreatedDate__c <= date_add(current_date(), 365.0))

実行する内容: どのようなデータを含めて、どのデータを含めないかを指定します。たとえば、この例のクエリでは、現在の日付から 365 日前までの期間に商品を購入した顧客のみを対象としています。

セクション 5

GROUP BY
customer_id__c, kq_customer_id__c

実行する内容: ディメンション、つまりデータをグループ化する方法を決定します (ここでは顧客 ID と顧客 ID のキー修飾子属性を使用しています)。

データモデルオブジェクト

SQL 式の最初の部分では、データモデルオブジェクト (DMO) に基づいて属性とメジャーを選択しています。そのため、データモデルについて理解しておくことは非常に重要です。一般的な DMO と、それらに関連付けられる測定可能な属性を見てみましょう。

DMO	使用方法	測定可能な属性の例
関係者	顧客など、リレーションを持つ相手を定義します。関係者には、計算済みインサイトでよく使用される、統合された個人プロファイルが含まれています。	7 日後が誕生日
商品	販売予定の商品、またはサービスの目的で追跡する商品の一部を定義します。	$50 を超えるレディースの靴
販売注文	収益、商談、購入、販売注文を定義します。	カートの支出総額が $500 以上
エンゲージメントデータ	顧客とのやり取りや活動を定義し、通常は電子メールのエンゲージメントに関連します。	5 回以上のメールクリック
ケースデータ	ログに記録された問題やサポートチケットを定義します。	過去 1 年間のオープンチケット >1 件

SQL 関数

SQL の用語、構造、そしてデータについて学びました。次は、さらに複雑な関数を追加して、データを照会したり操作したりできるようにします。では、使用できる関数を詳しく見ていきましょう。

SQL 結合

SQL 結合を理解するために、結合の種別によってクエリでどのようなデータが提供されるかを確認しましょう。2 つのサンプル DMO を使用して、以下の 4 つの結合結合を説明します。

DMO 1: 販売注文
DMO 2: 個人顧客

内部結合、左結合、右結合、完全結合。

結合または内部結合

左結合

右結合

完全結合

説明

両方の DMO に一致する値を持つレコードを返します。

DMO 2 から不要なレコードを除外します。

DMO 1 から不要なレコードを除外します。

両方の DMO で一致するすべてのレコードを返します。

結果

特定の顧客に対する特定の販売注文。

すべての販売注文と特定顧客への販売注文。

すべての顧客と、特定の顧客に対する少数の販売注文。

すべての販売注文とすべての顧客 (一部の一致レコードを含む)。

DMO は統合プロファイルとどのようにリンクしているのでしょうか。データのリンクと結合の詳細については、こちらのヘルプページをご覧ください。

CASE

もう 1 つの一般的な SQL 関数は CASE ステートメントで、新しいディメンションを作成するために使用します。CASE を使用すると、条件が一致したときに値を返すことができます。

SELECT CASE
WHEN
SUM(SALESORDER__dlm.grand_total_amount__c) < 100 THEN 'Low Spender'
End as Spend_Type__c

実行する内容: $100 未満しか購入しない顧客を定額支出者として識別します。条件が真になると、読み取りを中止して結果を返します。条件が真でなければ、ELSE 句の値を返します。ELSE の部分がなく、どの条件も真でなければ NULL を返します。

書式設定関数

最終的な計算済みインサイトを作成する前に、クリーンアップが必要になることがあります。クリーンアップには、いくつかの関数を使用できます。たとえば、ROUND 関数を使用すると、指定した小数位桁数に値を丸めることができます。

この関数の構文は次のとおりです。

ROUND(expression, number of decimal places)

次の例では、この式を使用して売上注文を丸めています。

SELECT ROUND(Salesorder_dlm.total_amount, 2)

実行する内容: 顧客の支出総額が $143.5555 である場合、この関数は結果を小数点第 2 位までに切り詰めて、$143.55 を返します。

その他の関数

SQL ステートメントに追加できる関数は他にもたくさんあり、それらを使用して結果をさらに絞り込むことができます。使用事例に応じて、希望するデータを取得できるように、いろいろな関数を試してください。

使用できるすべての関数については、「Data Cloud での ANSI SQL ステートメントの使用」を参照してください。

例

基本的な SQL の概念を理解し、構造を理解しましたので、次の単元では、自分自身の使用事例の解決に役立つ例をいくつか見てみましょう。

リソース

先頭に戻る

ヘルプをお探しですか?

もっと探す