本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。 # 聚合分析规则 在中 AWS Clean Rooms，*聚合分析规则*使用可选维度的 COUNT、SUM、 and/or AVG 函数生成聚合统计数据。将聚合分析规则添加到配置表后，可以查询的成员就能在配置表上运行查询。 聚合分析规则支持活动规划、媒体覆盖率、频率测量和归因等使用案例。 支持的查询结构和语法在 [聚合查询结构和语法](#agg-query-structure-syntax) 中定义。 [聚合分析规则 — 查询控制](#agg-query-controls) 中定义的分析规则的参数包括查询控制和查询结果控制。其查询控制包括要求一个配置表至少联接到一个可直接或临时查询的成员所拥有的配置表。此要求可使您确保在您的表及其他人的表的交叉点 (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}} | 包含以下表达式的逗号分隔列表：[See the AWS documentation website for more details](http://docs.aws.amazon.com/zh_cn/clean-rooms/latest/userguide/analysis-rules-aggregation.html) `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}} | 可以包含任何使用以下元素的表达式的表达式：[See the AWS documentation website for more details](http://docs.aws.amazon.com/zh_cn/clean-rooms/latest/userguide/analysis-rules-aggregation.html) `select_aggregate_expression` 可以带或不带 `AS` 参数对列设置别名。有关更多信息，请参阅 [AWS Clean Rooms SQL 参考](https://docs.aws.amazon.com/clean-rooms/latest/sql-reference/sql-reference.html)。 | `TRUNC(timestampColumn)`
`UPPER(campaignName)` | | {{table\_expression}} | 使用 `join_condition` 连接联接条件表达式的表或表的联接。
`join_condition` 返回布尔值。
`table_expression` 支持：[See the AWS documentation website for more details](http://docs.aws.amazon.com/zh_cn/clean-rooms/latest/userguide/analysis-rules-aggregation.html) | 
FROM consumer_table 
INNER JOIN provider_table
ON
consumer_table.identifier1 = provider_table.identifier1
AND
consumer_table.identifier2 = provider_table.identifier2
| | {{where\_expression}} | 返回布尔值的条件表达式。它可能包括以下内容：[See the AWS documentation website for more details](http://docs.aws.amazon.com/zh_cn/clean-rooms/latest/userguide/analysis-rules-aggregation.html)
支持的比较条件是 (`=, >, <, <=, >=, <>, !=, 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 参考](https://docs.aws.amazon.com/clean-rooms/latest/sql-reference/sql-reference.html)中的 ASC DESC 参数。 | `ORDER BY SUM(SALES), UPPER(campaignName)` | 关于聚合查询的结构和语法，请注意以下几点： + 不支持除 SELECT 之外的 SQL 命令。 + 不支持子查询和通用表格表达式（例如 WITH）。 + 不支持组合多个查询的运算符（例如 UNION）。 + TOP、LIMIT 和 OFFSET 参数不受支持。 ## 聚合分析规则 — 查询控制 使用聚合查询控制，您可以控制如何使用表中的列来查询表。例如，您可以控制哪一列用于联接，哪一列可以计数，或者 WHERE 语句中可以使用哪一列。 下面几节解释每种控制。 **Topics** + [聚合控制](#agg-functions) + [联接控制](#join-controls) + [维度控制](#dimension-controls) + [标量函数](#scalar-functions) ### 聚合控制 通过使用*聚合控制*，您可以定义允许哪些聚合函数以及必须将其应用于哪些列。聚合函数可以在 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 仅支持INNERJOIN。 INNERJOIN语句只能使用在分析规则`joinColumn`中明确归类为 a 的列，但要遵守您定义的控件。 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`（参阅[聚合控制](#agg-functions)）。
同一列不能同时用作 `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 中使用。
同一列不能同时是 a `dimensionColumn`、a `joinColumn`、 and/or a `aggregateColumn`。 | ### 标量函数 *标量函数*控制哪些标量函数可以在维度列上使用。 | 控件 | 定义 | 用法 | | --- | --- | --- | | scalarFunctions | 可在查询的 `dimensionColumns` 上使用的标量函数。 | 指定允许在 `dimensionColumns` 上应用的标量函数（如果有）（例如 CAST）。
标量函数不能在其他函数之上使用，也不能在其他函数中使用。标量函数的参数可以是列、字符串文本或数字文本。 | 支持以下标量函数： + 数学函数 - ABS、CEILING、FLOOR、LOG、LN、ROUND、SQRT + 数据类型格式设置函数 — CAST, CONVERT, TO\_CHAR, TO\_DATE, TO\_NUMBER, TO\_TIMESTAMP + 字符串函数 - LOWER、UPPER、RTRIM、SUBSTRING + 对于 RTRIM，不允许使用自定义字符集进行修剪。 + 条件表达式 — COALESCE + 日期函数 - EXTRACT、GETDATE、CURRENT\_DATE、DATEADD + 其他函数 — TRUNC 有关详细信息，请参阅 [AWS Clean Rooms SQL 参考](https://docs.aws.amazon.com/clean-rooms/latest/sql-reference/sql-reference.html)。 ## 聚合分析规则 — 查询结果控制 使用聚合查询结果控制，可以通过指定每个输出行必须满足的一个或多个条件来控制返回哪些结果。 AWS Clean Rooms 支持 `COUNT (DISTINCT column) >= X` 形式的聚合约束。此形式要求每行至少聚合从配置表中选择的 X 个不同值（例如，不同 `user_id` 值的最少个数）。即使提交的查询本身不使用指定的列，也会自动强制执行此最低阈值。它们是在来自协作中每个成员的已配置表的查询中的每个已配置表中共同强制执行的。 每个配置表的分析规则中必须有至少一个聚合约束。配置表所有者可以添加多个 `columnName` 和关联的 `minimum`，这些表将共同强制执行。 ### 聚合约束 *聚合约束* 控制返回查询结果中的哪些行。要返回，行必须满足聚合约束中指定的每列中指定的最小不同值数。即使在查询或分析规则的其他部分中未明确提及该列，此要求也适用。 | 控件 | 定义 | 用法 | | --- | --- | --- | | columnName | 在每个输出行必须满足的条件中使用的 `aggregateColumn`。 | 可以是已配置表中的任何列。 | | minimum | 要在查询结果中返回输出行（例如 COUNT DISTINCT），关联 `aggregateColumn` 必须具有的最小不同值个数。 | `minimum` 的值必须至少为 2。 | ## 聚合分析规则结构 以下示例显示了聚合分析规则的预定义结构。 在以下示例中，{{`MyTable`}} 指您的数据表。你可以用自己的信息替换每个{{user input placeholder}}信息。 ``` { "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 公司在协作中启用查询日志记录，并在其账户中启用查询日志记录。 1. B 公司在协作中创建成员身份。它在其账户中启用查询日志记录。 1. A 公司创建销售配置表。 1. 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`。它为他们的分析师提供了单独查询销售表的灵活性。 1. B 公司创建退货配置表。 1. 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 公司在其数据中添加了什么输出约束，他们的输出约束都将始终得到执行。 1. A 公司创建了与协作的销售表关联。 1. B 公司创建了与协作的退货表关联。 1. 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; ``` 1. A 公司和 B 公司查看查询日志。B 公司验证查询是否符合协作协议中上商定的内容。 ## 聚合分析规则问题疑难解答 使用此处的信息可帮助您诊断和修复在使用聚合分析规则时出现的常见问题。 **Topics** + [我的查询没有返回任何结果](#query-no-results) ### 我的查询没有返回任何结果 当没有匹配结果或匹配结果不符合一个或多个最低聚合阈值时，就会发生这种情况。 有关最低聚合阈值的更多信息，请参阅[聚合分析规则 — 示例](#agg-analysis-rule-example)。