本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
彙總分析規則
在中AWS Clean Rooms,彙總分析規則會使用 COUNT、SUM 和/或 AVG 函數沿選用維度來產生彙總統計資料。將彙總分析規則新增至已設定的資料表時,可讓查詢的成員在已設定的資料表上執行查詢。
彙總分析規則支援宣傳活動規劃、媒體觸及率、頻率測量和歸因等使用案例。
支援的查詢結構和語法在中定義聚合查詢結構和語法。
中彙總分析規則-查詢控制項定義的分析規則參數包括查詢控制項和查詢結果控制項。其查詢控制項包括要求將已設定的資料表連結至少一個由成員擁有的已設定資料表,這些資料表可以直接或傳輸進行查詢。此要求允許您確保查詢在您的表和他們的交叉點 (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的其他 SQL 指令。
-
不支援子查詢和一般資料表運算式 (例如WITH)。
-
不支援合併多個查詢的運算子 (例如,UNION)。
-
TOPLIMIT、和OFFSET參數不受支援。
彙總分析規則-查詢控制項
使用彙總查詢控制項,您可以控制如何使用資料表中的資料行來查詢資料表。例如,您可以控制哪個資料行用於聯結、哪個資料行可以計算,或是哪個資料行可用於WHERE陳述式。
以下各節將說明每個控制項。
彙總控制項
透過使用彙總控制項,您可以定義要允許哪些彙總函數,以及必須套用到哪些資料行。聚合函數可以在SELECT、HAVING和ORDERBY運算式中使用。
控制項 | 定義 | 用量 |
---|---|---|
aggregateColumns |
您允許在彙總函數中使用的已設定表格資料欄的資料欄。 |
有些也 鑑於不 |
function |
您允許在上方使用的計數、總和和和 AVG 函數aggregateColumns 。 |
|
加入控制
一個JOIN
子句用於從兩個或多個表,基於它們之間的相關列合併行。
您可以使用聯結控制項來控制如何將表格連接至中的其他表格table_expression
。 AWS Clean Rooms僅支持INNERJOIN。 INNERJOIN陳述式只能使用已在分析規則joinColumn
中明確分類為的欄 (視您定義的控制項而定)。
INNERJOIN必須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 |
您要允許查詢的成員在INNERJOIN陳述式中使用的資料欄 (如果有的話)。 |
特定的也 同一列不能同時用作 除非它也被歸類為 |
joinRequired |
控制您是否需要INNERJOIN來自可查詢之成員的已設定資料表。 |
如果啟用此參數,INNERJOIN則需要。如果您未啟用這個參數,則 a INNER JOIN 是選擇性的。 假設您啟用此參數,則可以查詢的成員必須在中包含他們擁有的資料表INNERJOIN。他們必須將JOIN您的桌子與他們的桌子一起直接或傳遞(即將其表連接到另一個表格,該表本身已連接到您的桌子)。 |
以下是傳遞性的一個例子。
ON my_table.identifer = third_party_table.identifier .... ON third_party_table.identifier = member_who_can_query_table.id
注意
可以查詢的成員也可以使用joinRequired
參數。在這種情況下,查詢必須將其資料表與至少一個其他資料表聯結。
標註控制
維度控制項會控制可篩選、分組或彙總彙總資料欄的資料欄。
控制項 | 定義 | 用量 |
---|---|---|
dimensionColumns |
您允許查詢的成員在SELECT、WHERE、和中使用的欄 (如果有的話) ORDER BY。GROUP BY |
A 相同的資料行不能同時是 a |
純量函數
純量函數控制哪些純量函數可以在維度列上使用。
控制項 | 定義 | 用量 |
---|---|---|
scalarFunctions |
可 |
指定您允許 (例如) 套用的純量函數 (如果有的話CAST)。 標量函數不能在其他函數之上或其他函數中使用。標量函數的參數可以是列,字符串文字或數字文字。 |
支援下列純量函數:
-
數學功能-ABS,天花板,地板,日誌,LN,圓形,SQRT
-
資料類型格式化函數 — CAST, CONVERT, TO_CHAR, TO_DATE, TO_NUMBER, TO_TIMESTAMP
-
字符串函數-下,上,修剪,RTRIM,子字符串
-
對於 RTRIM,不允許要修剪的自定義字符集。
-
-
條件運算式 — 合併
-
日期函數-提取,獲取日期,當前日期,日期添加
-
其他功能 — TRUNC
如需詳細資訊,請參閱 AWS Clean RoomsSQL 參考。
彙總分析規則-查詢結果控制項
使用彙總查詢結果控制項,您可以指定每個輸出資料列必須符合才能傳回的一或多個條件,來控制要傳回的結果。 AWS Clean Rooms支援的形式的彙總限制COUNT (DISTINCT column) >=
X
。此表單要求每個資料列從已設定的資料表彙總至少 X 個不同選擇值 (例如,不同user_id
值的最小數目)。即使提交的查詢本身並未使用指定的資料行,這個最小臨界值也會自動強制執行。它們會從協同合作中每個成員的已配置表格中,在查詢中的每個已配置表格中集體強制執行。
每個已配置的表格在其分析規則中必須至少有一個彙總條件約束。已配置的資料表擁有者可以新增多個columnName
minimum
與相關聯的資料表擁有者,並
彙總約束
彙總條件約束可控制傳回查詢結果中的哪些資料列。若要傳回,資料列必須符合彙總條件約束中指定之每個資料行中不同值的指定最小數目。即使查詢或分析規則的其他部分未明確提及資料行,此需求也適用。
控制項 | 定義 | 用量 |
---|---|---|
columnName |
在每個輸出行必須滿足的條件下使用的。 |
可以是已配置表格中的任何欄。 |
minimum |
要在查詢結果中傳回的輸出資料列必須具有的相關聯 |
必 |
彙總分析規則結構
下列範例顯示彙總分析規則的預先定義結構。
在下面的例子中,
是指您的數據表。您可以使用自己的資訊取代每個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 公司和其他渠道提供更好的產品。
若要建立協同作業並執行彙總分析,公司會執行下列動作:
-
公司 A 會建立協同合作並建立成員資格。該合作將 B 公司作為合作中的另一個成員。公司 A 可在協同作業中啟用查詢記錄,並在其帳戶中啟用查詢記錄。
-
B 公司會在協同合作中建立成員資格。它會在其帳戶中啟用查詢記錄。
-
A 公司創建一個銷售配置表。
-
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
在分析規則中。它提供了靈活性,為他們的分析師單獨查詢銷售表。 -
B 公司會建立退貨組態表格。
-
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
來篩選之後發生returndate
的輸出purchasedate
。透過此篩選,輸出可以更精確地評估產品變更的影響。scalarFunctions
— B 公司啟用以下項目:-
主線日期
-
如果在其數據中以不同的格式輸入較低和 UPPER
producttype
-
CAST如果 A 公司需要將銷售中的數據類型轉換為與回報中的數據類型相同
A 公司不啟用其他標量函數,因為他們不認為查詢需要它們。
outputConstraints
— B 公司設定最小輸出限制,hashedemail
以協助降低重新識別客戶的能力。它還增加了最小輸出限制,producttype
以減少重新識別退回的特定產品的能力。根據輸出的尺寸,某些產品類型可能更具主導地位(例如,state
)。無論公司 A 向其數據添加的輸出約束如何,它們的輸出約束都將始終強制執行。 -
-
公司 A 創建一個銷售表關聯協作。
-
B 公司會建立與協同合作的退貨表關聯。
-
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;
-
A 公司和 B 公司會複查查詢記錄。B 公司會驗證查詢是否符合合作協議中同意的內容。
疑難排解彙總分析規則問
使用此處的資訊可協助您診斷並修正使用彙總分析規則時的常見問題。
我的查詢沒有返回任何結果
當沒有相符結果,或符合結果不符合一或多個最小彙總閾值時,就會發生這種情況。
如需最小聚總臨界值的詳細資訊,請參閱彙總分析規則-範例。