聚合分析规则 - AWS Clean Rooms
聚合查询结构和语法聚合分析规则 — 查询控制聚合分析规则 — 查询结果控制聚合分析规则结构聚合分析规则 — 示例聚合分析规则问题疑难解答

本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

聚合分析规则

在 AWS Clean Rooms 中，聚合分析规则使用 COUNT、SUM 和/或 AVG 函数按可选维度生成聚合统计数据。将聚合分析规则添加到配置表后，可以查询的成员就能在配置表上运行查询。

聚合分析规则支持活动规划、媒体覆盖率、频率测量和归因等使用案例。

支持的查询结构和语法在 聚合查询结构和语法 中定义。

中定义的分析规则的参数包括查询控制和查询结果控制。聚合分析规则 — 查询控制其查询控制包括要求一个配置表至少联接到一个可直接或临时查询的成员所拥有的配置表。此要求可使您确保在您的表及其他人的表的交叉点 (INNER JOIN) 上运行查询。

聚合查询结构和语法

对具有聚合分析规则的表的查询必须遵循以下语法。


    --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}]] [,...]]

下表解释前面语法中列出的每个表达式。

Expression 定义 示例
select_aggregate_function_expression

包含以下表达式的逗号分隔列表：

  • select_aggregation_function_expression

  • select_aggregate_expression

注意

select_aggregate_expression 中必须至少有一个 select_aggregation_function_expression。

SELECT SUM(PRICE), user_segment
select_aggregation_function_expression

应用于一个或多个列的一个或多个支持的聚合函数。只允许将列作为聚合函数的参数。

注意

select_aggregate_expression 中必须至少有一个 select_aggregation_function_expression。

AVG(PRICE)

COUNT(DISTINCT user_id)
select_grouping_column_expression

可以包含任何使用以下元素的表达式的表达式：

  • 表列名称

  • 支持的标量函数

  • 字符串文本

  • 数值文本

注意

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 中的相等比较条件 (=)

  • 逻辑运算符（AND、OR）。

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

返回布尔值的条件表达式。它可能包括以下内容：

  • 表列名称

  • 支持的标量函数

  • 数学运算符

  • 字符串文本

  • 数值文本

支持的比较条件是 (=, >, <, <=, >=, <>, !=, 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

返回布尔值的条件表达式。它们具有应用于单列（例如 SUM(price)）的支持聚合函数，并与数值文字进行比较。

支持的比较条件是 (=, >, <, <=, >=, <>, !=)。

支持的逻辑运算符是 (AND, OR)。

having_expression 是可选项。

HAVING SUM(SALES) > 500
order_by_expression

与前面定义的 select_aggregate_expression 中定义的要求一致的表达式的逗号分隔列表。

order_by_expression 是可选项。

注意

order_by_expression 允许 ASC 和 DESC 参数。有关更多信息，请参阅 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 表达式中的聚合函数中使用。

有些 aggregateColumns 也可以归类为 joinColumn（稍后定义）。

给定的 aggregateColumn 也不能归类为 dimensionColumn（稍后定义）。
function 允许在 aggregateColumns 上使用的 COUNT、SUM 和 AVG 函数。

function 可以应用于与之关联的 aggregateColumns。

联接控制

JOIN 子句用于根据两个或多个表中的相关列合并两个或多个表中的行。

您可以使用联接控制来控制如何将您的表联接到 table_expression 中的其他表。AWS Clean Rooms 仅支持 INNER JOIN。INNER JOIN 语句只能使用在分析规则中明确归类为 joinColumn 的列，但要遵守您定义的控制。

INNER JOIN 必须对您的已配置表中的 joinColumn 和协作中另一个已配置表中的 joinColumn 进行操作。您可以决定表中的哪些列可以用作 joinColumn。

ON 子句中的每个匹配条件都要求在两列之间使用相等比较条件 (=)。

ON 子句中的多个匹配条件可以是：

  • 使用 AND 逻辑运算符组合

  • 使用 OR 逻辑运算符分隔

注意

所有 JOIN 匹配条件都必须与 JOIN 两侧各一条记录匹配。所有由 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 语句中使用的列（如果有）。

特定的 joinColumn 也可以归类为 aggregateColumn（参阅聚合控制）。

同一列不能同时用作 joinColumn 和 dimensionColumns（见下文）。

除非它也被归类为 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 参数。在这种情况下，查询必须将其表与至少一个其他表联接。

维度控制

维度控制控制可以对聚合列进行筛选、分组或聚合的列。

控件 定义 使用量
dimensionColumns

您允许可以查询的成员在 SELECT、WHERE、GROUP、BY 和 ORDER BY 中使用的列（如果有）。

dimensionColumn 可以在 SELECT (select_grouping_column_expression)、WHERE、GROUP BY 和 ORDER BY 中使用。

同一列不能同时是 dimensionColumn、joinColumn 和/或 aggregateColumn。

标量函数

标量函数控制哪些标量函数可以在维度列上使用。

控件 定义 使用量
scalarFunctions

可在查询的 dimensionColumns 上使用的标量函数。

指定允许在 dimensionColumns 上应用的标量函数（如果有）（例如 CAST）。

标量函数不能在其他函数之上使用，也不能在其他函数中使用。标量函数的参数可以是列、字符串文本或数字文本。

支持以下标量函数：

  • 数学函数 — ABS、CEILOR、FLOOR、LN、ROUND、SQRT

  • 数据类型格式设置函数 — CAST, CONVERT, TO_CHAR, TO_DATE, TO_NUMBER, TO_TIMESTAMP

  • 字符串函数 — 下限、上限、TRIM、RTRIM、SUBSTRING

    • 对于 RTRIM，不允许使用自定义字符集进行修剪。

  • 条件表达式 — COALESCE

  • 日期函数 — 提取、获取日期、当前日期、日期添加

  • 其他函数 — TRUNC

有关详细信息，请参阅 AWS Clean Rooms SQL 参考。

聚合分析规则 — 查询结果控制

使用聚合查询结果控制，可以通过指定每个输出行必须满足的一个或多个条件来控制返回哪些结果。AWS Clean Rooms 支持 COUNT (DISTINCT column) >= X 形式的聚合约束。此形式要求每行至少聚合从配置表中选择的 X 个不同值（例如，不同 user_id 值的最少个数）。即使提交的查询本身不使用指定的列，也会自动强制执行此最低阈值。它们是在来自协作中每个成员的已配置表的查询中的每个已配置表中共同强制执行的。

每个配置表的分析规则中必须有至少一个聚合约束。配置表所有者可以添加多个 columnName 和关联的 minimum，这些表将共同强制执行。

聚合约束

聚合约束 控制返回查询结果中的哪些行。要返回，行必须满足聚合约束中指定的每列中指定的最小不同值数。即使在查询或分析规则的其他部分中未明确提及该列，此要求也适用。

控件 定义 使用量
columnName

在每个输出行必须满足的条件中使用的 aggregateColumn。

可以是已配置表中的任何列。
minimum

要在查询结果中返回输出行（例如 COUNT DISTINCT），关联 aggregateColumn 必须具有的最小不同值个数。

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] 
    },
  ]
}

聚合分析规则 — 示例

以下示例演示了两家公司如何使用聚合分析在 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 公司选择 CAST 标量函数，以便在需要时根据 A 公司关联到协作的配置表更新数据类型格式。它还添加了标量函数，以便在需要时帮助格式化列。

    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 以与购买数量进行比较。它们至少有一个聚合列，因为它们启用了聚合查询。

    joinColumns — B 公司让 A 公司在 identifier 上进行联接，以将退货数据中的客户与销售数据中的客户进行匹配。identifier 数据特别敏感，将其作为 joinColumn 可确保数据永远不会在查询中输出。

    joinRequired — B 公司要求对退货数据的查询必须与销售数据重叠。他们不想让 A 公司查询其数据集中的所有个人。他们还在协作协议中商定了这一限制。

    dimensionColumns — B 公司让 A 公司按 state、popularpurchases 和 customerserviceuser 进行筛选和分组，这些独特的属性有助于 A 公司进行分析。B 公司让 A 公司使用 returndate 筛选在 purchasedate 之后发生的 returndate 的输出。通过这种筛选，输出可以更准确地评估产品变更的影响。

    scalarFunctions — B 公司启用以下函数：

    • TRUNNC（表示日期）

    • LOWER 和 UPPER（如果 producttype 在数据中以不同的格式输入）

    • CAST（如果 A 公司需要将销售表中的数据类型转换为与退货中表的数据类型相同）

    A 公司不启用其他标量函数，因为他们认为查询不需要这些函数。

    outputConstraints — B 公司对 hashedemail 设定了最低输出约束，以帮助降低重新识别客户身份的能力。它还对 producttype 增加了最低输出约束，以降低重新识别退回的特定产品的能力。根据输出的维度（例如 state），某些产品类型可能更占优势。无论 A 公司在其数据中添加了什么输出约束，他们的输出约束都将始终得到执行。

  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 公司验证查询是否符合协作协议中上商定的内容。

聚合分析规则问题疑难解答

使用此处的信息可帮助您诊断和修复在使用聚合分析规则时出现的常见问题。

我的查询没有返回任何结果

当没有匹配结果或匹配结果不符合一个或多个最低聚合阈值时，就会发生这种情况。

有关最低聚合阈值的更多信息，请参阅聚合分析规则 — 示例。