集計分析ルール - AWS Clean Rooms

翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。

集計分析ルール

AWS Clean Rooms の集計分析ルールでは、COUNT、SUM、または AVG 関数を使用して、任意のディメンションで統計を集約します。設定済みテーブルに集計分析ルールを追加すると、クエリを行えるメンバーが設定済みテーブルに対してクエリを実行できるようになります。

集計分析ルールは、キャンペーン計画、メディアリーチ、頻度測定、アトリビューションなどのユースケースに対応します。

サポートされているクエリ構造と構文は、「集約クエリの構造と構文」で定義されています。

集計分析ルール - クエリコントロール」で定義されている分析ルールのパラメータには、クエリコントロールとクエリ結果コントロールがあります。クエリコントロールでは、直接または間接的にクエリを実行できるメンバーが所有する 1 つ以上の設定済みテーブルに、1 つの設定済みテーブルを結合することを要求できます。この要求により、クエリを自分のテーブルと相手のテーブルの交差部分 (INNERJOIN) で実行することが可能になります。

集約クエリの構造と構文

集計分析ルールが追加されたテーブルに対するクエリは、次の構文に従う必要があります。

--select_aggregate_function_expression SELECT aggregation_function(column_name) [[AS] column_alias ] [, ...] --select_grouping_column_expression [, {column_name|scalar_function(arguments)} [[AS] column_alias ]][, ...] --table_expression FROM table_name [[AS] table_alias ] [[INNER] JOIN table_name [[AS] table_alias] ON join_condition] [...] --where_expression [WHERE where_condition] --group_by_expression [GROUP BY {column_name|scalar_function(arguments)}, ...]] --having_expression [HAVING having_condition] --order_by_expression [ORDER BY {column_name|scalar_function(arguments)} [{ASC|DESC}]] [,...]]

次の表は、前述の構文で示したそれぞれの式について説明しています。

定義
select_aggregate_function_expression

次の式を含むカンマ区切りリストです。

  • select_aggregation_function_expression

  • select_aggregate_expression

注記

select_aggregate_expression には少なくとも 1 つの select_aggregation_function_expression が必要です。

SELECT SUM(PRICE), user_segment

select_aggregation_function_expression

1 つ以上の列に適用される、1 つ以上のサポートされている集約関数です。集約関数の引数として指定できるのは列だけです。

注記

select_aggregate_expression には少なくとも 1 つの select_aggregation_function_expression が必要です。

AVG(PRICE)

COUNT(DISTINCT user_id)

select_grouping_column_expression

以下を使用する任意の式を含めることができる式です。

  • テーブルの列名

  • サポートされているスカラー関数

  • [String literals] (文字列リテラル)

  • 数値リテラル

注記

select_aggregate_expression では AS パラメータの有無にかかわらず、列にエイリアスを指定できます。詳細については、「AWS Clean Rooms SQL リファレンス」を参照してください。

TRUNC(timestampColumn)

UPPER(campaignName)

table_expression

join_condition で結合条件式を連結するテーブル、またはテーブルの結合。

join_condition はブール値を返します。

table_expression では、以下がサポートされています。

  • 特定の JOIN 型 (INNER JOIN)

  • join_condition 内の等価比較条件 (=)

  • 論理演算子 (ANDOR)

FROM consumer_table INNER JOIN provider_table ON consumer_table.identifier1 = provider_table.identifier1 AND consumer_table.identifier2 = provider_table.identifier2
where_expression

ブール値を返す条件式です。次ような要素で構成されています。

  • テーブルの列名

  • サポートされているスカラー関数

  • 算術演算子

  • [String literals] (文字列リテラル)

  • 数値リテラル

サポートされている比較条件は (=, >, <, <=, >=, <>, !=, NOT, IN, NOT IN, LIKE, IS NULL, IS NOT NULL) です。

サポートされている論理演算子は (AND, OR) です。

where_expression はオプションです。

WHERE where_condition

WHERE price > 100

WHERE TRUNC(timestampColumn) = '1/1/2022'

WHERE timestampColumn = timestampColumn2 - 14

group_by_expression

select_grouping_column_expression の要件に合致する式のカンマ区切りリストです。

GROUP BY TRUNC(timestampColumn), UPPER(campaignName), segment

having_expression

ブール値を返す条件式です。サポートされている集約関数が 1 つの列に適用され (例えば SUM(price))、数値リテラルと比較されます。

サポートされている条件は (=, >, <, <=, >=, <>, !=) です。

サポートされている論理演算子は (AND, OR) です。

having_expression はオプションです。

HAVING SUM(SALES) > 500

order_by_expression

前述の select_aggregate_expression で定義されているものと同じ要件と互換性のある式のカンマ区切りリストです。

order_by_expression はオプションです。

注記

order_by_expression では ASCDESC のパラメータを使用できます。詳細については、「AWS Clean Rooms SQL リファレンス」で ASC DESC パラメータを参照してください。

ORDER BY SUM(SALES), UPPER(campaignName)

集約クエリの構造と構文については、次の点に注意してください。

  • SELECT 以外の SQL コマンドはサポートされていません。

  • サブクエリと共通テーブル式 (WITH など) はサポートされていません。

  • 複数のクエリを組み合わせる演算子 (UNION など) はサポートされていません。

  • TOP、LIMIT、および OFFSET パラメータはサポートされていません。

集計分析ルール - クエリコントロール

集約クエリコントロールを使用すると、テーブル内の列を使用してテーブルにクエリを実行する方法を制御できます。例えば、どの列を結合に使用するか、どの列がカウントされるようにするか、WHERE ステートメントでどの列を使用できるようにするかを制御できます。

以下のセクションでは、それぞれのコントロールについて説明します。

集約コントロール

集約コントロールを使用すると、どの集約関数を許可し、どの列に適用するかを定義できます。集約関数は、SELECT、HAVING、および ORDER BY の式で使用できます。

コントロール 定義 使用方法
aggregateColumns 集約関数での使用を許可する設定済みテーブル列の列。

aggregateColumns は、SELECT、HAVING、および ORDER BY の式の集約関数で使用できます。

一部の aggregateColumnsjoinColumn としても分類されることがあります (後述)。

特定の aggregateColumndimensionColumn としても分類されることはありません (後述)。

function aggregateColumns で使用できる COUNT 関数、SUM 関数、および AVG 関数。

function は関連付けられている aggregateColumns に適用できます。

結合コントロール

JOIN 句を使用して、2 つ以上のテーブルの行を、テーブル間の関連する列に基づいて結合します。

結合コントロールを使用することで、テーブルを table_expression 内の他のテーブルに結合する方法を制御できます。AWS Clean Rooms は、INNER JOIN のみをサポートしています。INNER JOIN ステートメントでは、定義するコントロールに従い、分析ルールで明示的に joinColumn として分類された列のみを使用できます。

INNER JOIN は、自身の設定済みテーブルの joinColumn と、コラボレーション内のもう 1 つの設定済みテーブルの joinColumn で動作する必要があります。テーブルのどの列を joinColumn として使用できるようにするかはテーブルの所有者が決定します。

ON 句内の一致条件ごとに、2 つの列間の等価比較条件 (=) を使用する必要があります。

ON 句では次のようにして複数の一致条件を使用できます。

  • AND 論理演算子を使用して組み合わせる

  • OR 論理演算子を使用して区切る

注記

JOIN の一致条件では、必ず JOIN の各側の 1 つの行が一致しなければなりません。OR または AND 論理演算子で接続されるすべての条件がこの要件を満たす必要があります。

AND 論理演算子を使用したクエリの例を以下に示します。

SELECT some_col, other_col FROM table1 JOIN table2 ON table1.id = table2.id AND table1.name = table2.name

OR 論理演算子を使用したクエリの例を以下に示します。

SELECT some_col, other_col FROM table1 JOIN table2 ON table1.id = table2.id OR table1.name = table2.name
コントロール 定義 使用方法
joinColumns クエリを行えるメンバーに INNER JOIN ステートメントでの使用を許可する列 (ある場合)。

特定の joinColumnaggregateColumn としても分類されることがあります (「集約コントロール」を参照)。

同じ列を joinColumndimensionColumns の両方として使用することはできません (後述)。

aggregateColumn としても分類されていない限り、INNER JOIN 以外のクエリの他のどの部分でも joinColumn を使用することはできません。

joinRequired クエリを行えるメンバーの設定済みテーブルとの INNER JOIN を必須にするかどうかを制御します。

このパラメータを有効した場合、INNER JOIN は必須になります。このパラメータを有効にしない場合、INNER JOIN はオプションになります。

このパラメータを有効にすると、クエリを行えるメンバーは、自身が所有するテーブルを INNER JOIN に必ず含めなければなりません。クエリを行えるメンバーは、自身のテーブルを相手のテーブルと直接または間接的 (つまり自分のテーブルを、相手のテーブルと結合されている別のテーブルに結合) に JOIN する必要があります。

以下は間接的な結合の例です。

ON my_table.identifer = third_party_table.identifier .... ON third_party_table.identifier = member_who_can_query_table.id
注記

クエリを行えるメンバーが、joinRequired パラメータを使用することもできます。その場合、クエリで自分のテーブルを少なくとも 1 つの他のテーブルと結合する必要があります。

ディメンションコントロール

ディメンションコントロールは、集約列をフィルタリング、グループ化、または集計する際の基準となる列を制御します。

コントロール 定義 使用方法
dimensionColumns

クエリを行えるメンバーに SELECT、WHERE、GROUP、BY、および ORDER BY での使用を許可する列 (ある場合)。

dimensionColumn は、SELECT (select_grouping_column_expression)、WHERE、GROUP、BY、および ORDER BY で使用できます。

同じ列を dimensionColumnjoinColumn または aggregateColumn の両方として使用することはできません。

スカラー関数

スカラー関数は、どのスカラー関数をディメンション列に使用できるかを制御します。

コントロール 定義 使用方法
scalarFunctions

クエリの dimensionColumns で使用できるスカラー関数。

dimensionColumns での適用を許可するスカラー関数 (CAST など) を指定します (ある場合)。

スカラー関数を他の関数と重ねて使用したり、他の関数内で使用したりすることはできません。スカラー関数の引数には、列、文字列リテラル、または数値リテラルを使用できます。

以下のスカラー関数がサポートされています。

  • 数学関数 – ABS、CEILING、FLOOR、LOG、LN、ROUND、SQRT

  • データ型フォーマット関数 – CAST, CONVERT, TO_CHAR, TO_DATE, TO_NUMBER, TO_TIMESTAMP

  • 文字列関数 – LOWER、UPPER、TRIM、RTRIM、SUBSTRING

    • RTRIM では、カスタム文字セットをトリミングすることはできません。

  • 条件式 – COALESCE

  • 日付関数 – EXTRACT、GETDATE、CURRENT_DATE、DATEADD

  • その他の関数 – TRUNC

詳細については、「AWS Clean Rooms SQL リファレンス」を参照してください。

集計分析ルール - クエリ結果コントロール

集約クエリ結果コントロールでは、返される各出力行が満たす必要がある条件を 1 つ以上指定することで、どの結果を返すかを制御できます。AWS Clean Rooms では、COUNT (DISTINCT column) >= X という形式の集約制約をサポートしています。この形式では、設定済みテーブルから、選択した少なくとも X 個の個別値 (例えば、個別の user_id 値の最小数) を各行が集約することになります。送信されたクエリ自体が指定された列を使用しない場合でも、この最小数のしきい値は自動的に適用されます。これらは、コラボレーション内の各メンバーの設定済みテーブルから、クエリ内の各設定済みテーブルにまとめて適用されます。

各設定済みテーブルの分析ルールには、少なくとも 1 つの集約制約が必要です。設定済みテーブルの所有者が複数の columnName や関連付けられた minimum を追加すると、それらはまとめて適用されます。

集約制約

集約制約は、クエリ結果のどの行を返すかを制御します。行が返されるには、その行が、集約制約で指定された各列で、指定された個別値の最小数を満たす必要があります。この要件は、クエリや分析ルールの他の部分にその列が明示的に記述されていない場合でも適用されます。

コントロール 定義 使用方法
columnName

各出力行が満たす必要がある条件で使用される aggregateColumn です。

設定済みテーブル内のどの列でもかまいません。

minimum

クエリ結果で返される出力行に含まれていなければならない、関連付けられた aggregateColumn の個別値の最小数です (COUNT DISTINCT など)。

minimum の値は 2 以上にする必要があります。

集計分析ルールの構造

次の例は、集計分析ルールの事前定義された構造を示しています。

次の例では、MyTable がデータテーブルを表しています。各ユーザー入力プレースホルダーは、独自の情報に置き換えることができます。

{ "aggregateColumns": [ { "columnNames": [MyTable column names], "function": [Allowed Agg Functions] }, ], "joinRequired": ["QUERY_RUNNER"], "joinColumns": [MyTable column names], "dimensionColumns": [MyTable column names], "scalarFunctions": [Allowed Scalar functions], "outputConstraints": [ { "columnName": [MyTable column names], "minimum": [Numeric value] }, ] }

集計分析ルール - 例

次の例は、2 つの企業が AWS Clean Rooms で集計分析を使用してコラボレーションを行う方法を示しています。

A 社には顧客データと売上データがあります。A 社は製品の返品状況を把握したいと考えています。B 社は A 社の小売業者の一社で、返品データを保有しています。B 社には、A 社にとって有益な、顧客に関するセグメント属性 (関連製品を購入した、小売業者からカスタマーサービスを利用したなど) のデータもあります。B 社は、行レベルの顧客返品データや属性情報は提供したくありません。B 社の希望は、重複する顧客に関する統計情報を最小数の集約しきい値で A 社が取得できるよう、一連のクエリを有効にすることです。

A 社が製品の返品状況を把握し、B 社やその他のチャネルでより良い製品を提供できるように、A 社と B 社は協力することにしました。

コラボレーションを作成して集計分析を行うために、両社は次のことを行います。

  1. A 社がコラボレーションを作成し、メンバーシップを作成します。このコラボレーションには、B 社がコラボレーションの相手方メンバーとして参加します。A 社はコラボレーションでのクエリログ記録を有効にし、自社アカウントでのクエリログの記録を有効にします。

  2. B 社がコラボレーションでメンバーシップを作成し、そのアカウントでのクエリログの記録を有効にします。

  3. A 社が、設定済み売上テーブルを作成します。

  4. A 社が、設定済み売上テーブルに次の集計分析ルールを追加します。

    { "aggregateColumns": [ { "columnNames": [ "identifier" ], "function": "COUNT_DISTINCT" }, { "columnNames": [ "purchases" ], "function": "AVG" }, { "columnNames": [ "purchases" ], "function": "SUM" } ], "joinColumns": [ "hashedemail" ], "dimensionColumns": [ "demoseg", "purchasedate", "productline" ], "scalarFunctions": [ "CAST", "COALESCE", "TRUNC" ], "outputConstraints": [ { "columnName": "hashedemail", "minimum": 2, "type": "COUNT_DISTINCT" }, ] }

    aggregateColumns – A 社は、売上データと返品データで重複している一意の顧客の数をカウントしたいと考えています。また A 社は、purchases の数を合計して、returns の数と比較したいと考えています。

    joinColumns – A 社は、identifier を使用して売上データの顧客と返品データの顧客を照合したいと考えています。これにより、A 社は返品を適切な購入と照合できるようになります。また、A 社が重複する顧客をセグメント化するのにも役立ちます。

    dimensionColumns – A 社は、dimensionColumns を使用して、特定の製品での絞り込みを行い、一定期間にわたって購入と返品を比較して返品日が購入日付より後になるものを確認し、重複する顧客をセグメント化します。

    scalarFunctions – A 社は、A 社がコラボレーションに関連付けた設定済みテーブルに基づいて、必要に応じてデータ型フォーマットを更新できるよう、CAST スカラー関数を選択します。また、必要に応じて列のフォーマットに役立つスカラー関数も追加します。

    outputConstraints – A 社は最小数の出力制約を設定します。アナリストは売上テーブルから行レベルのデータを確認できるため、結果を制約する必要はありません。

    注記

    企業 A は分析ルールに joinRequired を追加しません。これにより、アナリストは売上テーブルだけに対して柔軟にクエリを実行できます。

  5. B 社が、設定済み返品テーブルを作成します。

  6. B 社が、設定済み返品テーブルに次の集計分析ルールを追加します。

    { "aggregateColumns": [ { "columnNames": [ "identifier" ], "function": "COUNT_DISTINCT" }, { "columnNames": [ "returns" ], "function": "AVG" }, { "columnNames": [ "returns" ], "function": "SUM" } ], "joinColumns": [ "hashedemail" ], "joinRequired": [ "QUERY_RUNNER" ], "dimensionColumns": [ "state", "popularpurchases", "customerserviceuser", "productline", "returndate" ], "scalarFunctions": [ "CAST", "LOWER", "UPPER", "TRUNC" ], "outputConstraints": [ { "columnName": "hashedemail", "minimum": 100, "type": "COUNT_DISTINCT" }, { "columnName": "producttype", "minimum": 2, "type": "COUNT_DISTINCT" } ] }

    aggregateColumns – B 社は、A 社が returns の数を合計して購入数と比較できるようにします。集約クエリを有効にしているため、少なくとも 1 つは集約列があります。

    joinColumns – B 社は、A 社が返品データの顧客と売上データの顧客を照合できるよう、identifier での結合を有効にします。identifier データは特に機密性が高く、joinColumn として指定すれば、データがクエリで出力されることはありません。

    joinRequired – B 社は、返品データと売上データの重複に関するクエリを必須にします。A 社が B 社のデータセット内のすべての個人を照会できるようにはしたくありません。その制限についてはコラボレーション契約でも合意しています。

    dimensionColumns – B 社 は、A 社が statepopularpurchasescustomerserviceuser の属性でフィルタとグループ化を実行できるようにします。これらは A 社の分析に役立つ固有の属性です。B 社 は、A 社 が returndate を使用して purchasedate の後に発生した returndate で出力をフィルタリングできるようにします。このフィルタリングにより、出力がより正確になり、製品変更の影響を評価できるようになります。

    scalarFunctions – B 社は以下を有効にします。

    • 日付の TRUNC

    • producttype が異なる形式でデータに入力された場合の LOWER と UPPER

    • A 社が売上のデータ型を返品のデータ型と同じものに変換する必要がある場合の CAST

    A 社は、他のスカラー関数はクエリに必要ではないと考えているため、有効にしていません。

    outputConstraints – B 社は、顧客の再識別が困難になるように、hashedemail に最小数の出力制約を設定します。また、返品された特定の製品の再識別が困難になるように、producttype にも最小数の出力制約を設定します。出力のディメンション (state など) によっては、特定の製品タイプがより優勢になる可能性があります 。A 社がデータに追加した出力の制約にかかわらず、B 社の出力の制約が常に適用されます。

  7. A 社が、売上テーブルとコラボレーションとの関連付けを作成します。

  8. B 社が、返品テーブルとコラボレーションとの関連付けを作成します。

  9. A 社が、B 社の返品数を 2022 年の場所別の購入総数と比較して詳しく把握するために、次の例のようなクエリを実行します。

    SELECT companyB.state, SUM(companyB.returns), COUNT(DISTINCT companyA.hashedemail) FROM sales companyA INNER JOIN returns companyB ON companyA.identifier = companyB.identifier WHERE companyA.purchasedate BETWEEN '2022-01-01' AND '2022-12-31' AND TRUNC(companyB.returndate) > companyA.purchasedate GROUP BY companyB.state;
  10. A 社と B 社がクエリログを確認します。B 社は、クエリがコラボレーション契約で合意された内容と一致していることを確認します。

集計分析ルールに関する問題のトラブルシューティング

集計分析ルール使用時の一般的な問題については、ここにある情報を使用して診断と修正を行ってください。

クエリから何も結果が返されない

この問題は、一致する結果がない場合や、一致する結果が最小数の集約しきい値を 1 つ以上満たしていない場合に発生する可能性があります。

最小数の集約しきい値の詳細については、「集計分析ルール - 例」を参照してください。