Règle d'analyse d'agrégation

DansAWS Clean Rooms, une règle d'analyse d'agrégation génère des statistiques agrégées à l'aide des fonctions COUNT, SUM et/ou AVG avec des dimensions facultatives. Lorsque la règle d'analyse d'agrégation est ajoutée à une table configurée, elle permet au membre habilité à effectuer des requêtes sur la table configurée.

La règle d'analyse d'agrégation prend en charge les cas d'utilisation tels que la planification de campagnes, la portée médiatique, la mesure de fréquence et l'attribution.

La structure et la syntaxe de requête prises en charge sont définies dansStructure et syntaxe des requêtes d'agrégation.

Les paramètres de la règle d'analyse, définis dansRègle d'analyse d'agrégation : contrôles des requêtes, incluent les contrôles de requête et les contrôles de résultats de requête. Ses contrôles de requête incluent la possibilité d'exiger qu'une table configurée soit jointe à au moins une table configurée appartenant au membre qui peut effectuer une requête, directement ou de manière transitive. Cette exigence vous permet de vous assurer que la requête est exécutée à l'intersection (INNERJOIN) de votre table et de la leur.

Structure et syntaxe des requêtes d'agrégation

Les requêtes sur les tables dotées d'une règle d'analyse d'agrégation doivent respecter la syntaxe suivante.

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

Le tableau suivant explique chaque expression répertoriée dans la syntaxe précédente.

Expression	Définition	Exemples
select_aggregate_function_expression	Une liste séparée par des virgules contenant les expressions suivantes : select_aggregation_function_expression select_aggregate_expression Note Il doit y en avoir au moins un select_aggregation_function_expression dans leselect_aggregate_expression.	SELECT SUM(PRICE), user_segment
select_aggregation_function_expression	Une ou plusieurs fonctions d'agrégation prises en charge sont appliquées à une ou plusieurs colonnes. Seules les colonnes sont autorisées comme arguments des fonctions d'agrégation. Note Il doit y en avoir au moins un select_aggregation_function_expression dans leselect_aggregate_expression.	AVG(PRICE) COUNT(DISTINCT user_id)
select_grouping_column_expression	Expression qui peut contenir n'importe quelle expression utilisant les éléments suivants : Nom des colonnes de la table Fonctions scalaires prises en charge Littéraux de chaîne Littéraux numériques Note select_aggregate_expressionpeut créer un alias pour les colonnes avec ou sans le AS paramètre. Pour plus d'informations, consultez la référence AWS Clean Rooms SQL.	TRUNC(timestampColumn) UPPER(campaignName)
table_expression	Table, ou jointure de tables, reliant des expressions conditionnelles de jointure àjoin_condition. join_conditionrenvoie une valeur booléenne. Les table_expression supports : Un JOIN type spécifique (INNERJOIN) La condition de comparaison de l'égalité au sein d'un join_condition (=) Opérateurs logiques (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	Expression conditionnelle qui renvoie une valeur booléenne. Il peut être composé des éléments suivants : Nom des colonnes de la table Fonctions scalaires prises en charge Operateurs mathématiques Littéraux de chaîne Littéraux numériques Les conditions de comparaison prises en charge sont (=, >, <, <=, >=, <>, !=, NOT, IN, NOT IN, LIKE, IS NULL, IS NOT NULL). Les opérateurs logiques pris en charge sont (AND, OR). where_expressionC'est facultatif.	WHERE where_condition WHERE price > 100 WHERE TRUNC(timestampColumn) = '1/1/2022' WHERE timestampColumn = timestampColumn2 - 14
group_by_expression	Liste d'expressions séparées par des virgules qui répondent aux exigences du. select_grouping_column_expression	GROUP BY TRUNC(timestampColumn), UPPER(campaignName), segment
having_expression	Expression conditionnelle qui renvoie une valeur booléenne. Ils disposent d'une fonction d'agrégation prise en charge appliquée à une seule colonne (par exemple,SUM(price)) et sont comparés à un littéral numérique. Les conditions prises en charge sont (=, >, <, <=, >=, <>, !=). Les opérateurs logiques pris en charge sont (AND, OR). having_expressionC'est facultatif.	HAVING SUM(SALES) > 500
order_by_expression	Liste d'expressions séparées par des virgules qui est compatible avec les mêmes exigences définies dans la section select_aggregate_expression définie précédemment. order_by_expressionC'est facultatif. Note order_by_expressionautorisations ASC et DESC paramètres. Pour plus d'informations, consultez la section Paramètres ASC DESC dans le manuel AWS Clean RoomsSQL Reference.	ORDER BY SUM(SALES), UPPER(campaignName)

En ce qui concerne la structure et la syntaxe des requêtes d'agrégation, tenez compte des points suivants :

• Les commandes SQL autres que ne SELECT sont pas prises en charge.

• Les sous-requêtes et les expressions de table communes (par exemple,WITH) ne sont pas prises en charge.

• Les opérateurs qui combinent plusieurs requêtes (par exemple,UNION) ne sont pas pris en charge.

• TOPLIMIT, et les OFFSET paramètres ne sont pas pris en charge.

Règle d'analyse d'agrégation : contrôles des requêtes

Grâce aux commandes de requête d'agrégation, vous pouvez contrôler la manière dont les colonnes de votre table sont utilisées pour interroger la table. Par exemple, vous pouvez contrôler quelle colonne est utilisée pour la jointure, quelle colonne peut être comptée ou quelle colonne peut être utilisée dans WHERE les instructions.

Les sections suivantes expliquent chaque contrôle.

Contrôles d'agrégation

À l'aide des contrôles d'agrégation, vous pouvez définir les fonctions d'agrégation à autoriser et les colonnes auxquelles elles doivent être appliquées. Les fonctions d'agrégation peuvent être utilisées dans les ORDER BY expressions SELECTHAVING, et.

Contrôle	Définition	Utilisation
aggregateColumns	Colonnes de colonnes de table configurées que vous autorisez à utiliser dans les fonctions d'agrégation.	aggregateColumnspeut être utilisé dans une fonction d'agrégation dans les ORDER BY expressions SELECTHAVING,, et. Certains aggregateColumns peuvent également être classés dans la catégorie « A » joinColumn (définis ultérieurement). Given ne aggregateColumn peut pas également être classé dans la catégorie dimensionColumn (défini ultérieurement).
function	Les fonctions COUNT, SUM et AVG que vous autorisez à utiliser en plus deaggregateColumns.	functionpeut être appliqué à un aggregateColumns objet qui lui est associé.

Commandes de jointure

Une JOIN clause est utilisée pour combiner les lignes de deux tables ou plus, sur la base d'une colonne associée entre elles.

Vous pouvez utiliser les commandes de jointure pour contrôler la manière dont votre table peut être jointe aux autres tables dutable_expression. AWS Clean Roomsuniquement des supports INNERJOIN. INNERJOINles instructions ne peuvent utiliser que des colonnes explicitement classées comme telles joinColumn dans votre règle d'analyse, sous réserve des contrôles que vous définissez.

Ils INNER JOIN doivent opérer à joinColumn partir d'une table configurée et joinColumn à partir d'une autre table configurée dans la collaboration. C'est vous qui décidez quelles colonnes de votre tableau peuvent être utiliséesjoinColumn.

Chaque condition de correspondance contenue dans la ON clause est requise pour utiliser la condition de comparaison d'égalité (=) entre deux colonnes.

Plusieurs conditions de correspondance au sein d'une même ON clause peuvent être les suivantes :

• Combiné à l'aide de l'opérateur AND logique

• Séparé à l'aide de l'opérateur OR logique

Note

Toutes les JOIN conditions de match doivent correspondre à une ligne de chaque côté duJOIN. Toutes les conditions connectées par un opérateur OR ou un opérateur AND logique doivent également respecter cette exigence.

Voici un exemple de requête avec un opérateur AND logique.

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

Voici un exemple de requête avec un opérateur OR logique.

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

Contrôle	Définition	Utilisation
joinColumns	Les colonnes (le cas échéant) que vous souhaitez autoriser le membre autorisé à effectuer une requête à utiliser dans la INNER JOIN déclaration.	Un spécifique joinColumn peut également être classé dans la catégorie aggregateColumn (voirContrôles d'agrégation). La même colonne ne peut pas être utilisée à la fois comme joinColumn et dimensionColumns (voir plus loin). À moins qu'il n'ait également été classé comme unaggregateColumn, a ne joinColumn peut être utilisé dans aucune autre partie de la requête autre que le INNERJOIN.
joinRequired	Déterminez si vous avez besoin INNER JOIN d'une table configurée de la part du membre qui peut effectuer la requête.	Si vous activez ce paramètre, un INNER JOIN est requis. Si vous n'activez pas ce paramètre, un INNER JOIN est facultatif. En supposant que vous activiez ce paramètre, le membre autorisé à effectuer une requête doit inclure une table qu'il possède dans le INNERJOIN. Ils doivent joindre JOIN votre table à la leur, soit directement, soit de manière transitive (c'est-à-dire joindre leur table à une autre table, elle-même jointe à la vôtre).

Voici un exemple de transitivité.

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

Note

Le membre qui peut effectuer une requête peut également utiliser le joinRequired paramètre. Dans ce cas, la requête doit joindre sa table à au moins une autre table.

Contrôles dimensionnels

Les contrôles de dimension contrôlent la colonne le long de laquelle les colonnes d'agrégation peuvent être filtrées, groupées ou agrégées.

Contrôle	Définition	Utilisation
dimensionColumns	Les colonnes (le cas échéant) que vous autorisez le membre autorisé à effectuer une requête à utiliser dans SELECTWHERE, GROUPBY, et ORDERBY.	A dimensionColumn peut être utilisé dans SELECT (select_grouping_column_expression)WHERE, GROUPBY, et ORDERBY. La même colonne ne peut pas être à la fois a dimensionColumnjoinColumn, a et/ou anaggregateColumn.

Fonctions scalaires

Les fonctions scalaires contrôlent les fonctions scalaires qui peuvent être utilisées sur les colonnes de dimension.

Contrôle	Définition	Utilisation
scalarFunctions	Les fonctions scalaires qui peuvent être utilisées dimensionColumns dans la requête.	Spécifie les fonctions scalaires (le cas échéant) auxquelles vous autorisez (par exempleCAST) l'dimensionColumnsapplication. Les fonctions scalaires ne peuvent pas être utilisées par-dessus d'autres fonctions ou au sein d'autres fonctions. Les arguments des fonctions scalaires peuvent être des colonnes, des chaînes littérales ou des littéraux numériques.

Les fonctions scalaires suivantes sont prises en charge :

• Fonctions mathématiques : ABS, PLAFOND, PLANCHER, BOIS, LN, ROND, SQRT

• Fonctions de formatage des types de données — CAST, CONVERT, TO_CHAR, TO_DATE, TO_NUMBER, TO_TIMESTAMP

• Fonctions de chaîne : LOWER, UPPER, TRIM, RTRIM, SUBSTRING

  • Pour RTRIM, les jeux de caractères personnalisés à découper ne sont pas autorisés.

• Expressions conditionnelles — COALESCE

• Fonctions de date : EXTRACT, GETDATE, CURRENT_DATE, DATEADD

• Autres fonctions — TRUNC

Pour plus de détails, consultez la référence AWS Clean Rooms SQL.

Règle d'analyse d'agrégation : contrôles des résultats des requêtes

Avec les contrôles des résultats des requêtes d'agrégation, vous pouvez contrôler les résultats renvoyés en spécifiant une ou plusieurs conditions que chaque ligne de sortie doit remplir pour être renvoyée. AWS Clean Roomsprend en charge les contraintes d'agrégation sous la forme deCOUNT (DISTINCT column) >= X. Ce formulaire exige que chaque ligne agrège au moins X valeurs distinctes d'un choix dans votre table configurée (par exemple, un nombre minimum de user_id valeurs distinctes). Ce seuil minimum est automatiquement appliqué, même si la requête soumise elle-même n'utilise pas la colonne spécifiée. Ils sont appliqués collectivement sur chaque table configurée dans la requête à partir des tables configurées de chaque membre de la collaboration.

Chaque table configurée doit comporter au moins une contrainte d'agrégation dans sa règle d'analyse. Les propriétaires de tables configurées peuvent en ajouter plusieurs columnName et minimum les associer, et elles sont appliquées collectivement.

Contraintes d'agrégation

Les contraintes d'agrégation contrôlent les lignes renvoyées dans les résultats de la requête. Pour être renvoyée, une ligne doit respecter le nombre minimum de valeurs distinctes spécifié dans chaque colonne spécifiée dans la contrainte d'agrégation. Cette exigence s'applique même si la colonne n'est pas explicitement mentionnée dans la requête ou dans d'autres parties de la règle d'analyse.

Contrôle	Définition	Utilisation
columnName	Le aggregateColumn qui est utilisé dans la condition que chaque ligne de sortie doit remplir.	Il peut s'agir de n'importe quelle colonne de la table configurée.
minimum	Le nombre minimum de valeurs distinctes associées aggregateColumn que la ligne de sortie doit avoir (par exemple, COUNT DISTINCT) pour qu'elle soit renvoyée dans les résultats de la requête.	La valeur minimum doit être au moins égale à 2.

Structure des règles d'analyse d'agrégation

L'exemple suivant montre une structure prédéfinie pour une règle d'analyse d'agrégation.

Dans l'exemple suivant, MyTablefait référence à votre table de données. Vous pouvez remplacer chaque espace réservé saisi par l'utilisateur par vos propres informations.

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

Règle d'analyse d'agrégation - exemple

L'exemple suivant montre comment deux entreprises peuvent collaborer en AWS Clean Rooms utilisant l'analyse d'agrégation.

L'entreprise A possède des données sur les clients et les ventes. L'entreprise A souhaite comprendre l'activité de retour de produits. L'entreprise B est l'un des détaillants de l'entreprise A et possède des données sur les retours. L'entreprise B possède également des attributs de segment relatifs aux clients qui sont utiles à l'entreprise A (par exemple, achat de produits connexes, utilisation du service client du détaillant). L'entreprise B ne souhaite pas fournir de données de retour client au niveau des lignes ni d'informations sur les attributs. L'entreprise B souhaite uniquement activer un ensemble de requêtes pour que l'entreprise A obtienne des statistiques agrégées sur les clients qui se chevauchent à un seuil d'agrégation minimum.

L'entreprise A et l'entreprise B décident de collaborer afin que l'entreprise A puisse comprendre l'activité de retour des produits et fournir de meilleurs produits à l'entreprise B et à d'autres canaux.

Pour créer la collaboration et exécuter une analyse d'agrégation, les entreprises procèdent comme suit :

1. L'entreprise A crée une collaboration et crée une adhésion. La collaboration a la société B comme autre membre de la collaboration. L'entreprise A active la journalisation des requêtes dans la collaboration, et elle permet la journalisation des requêtes dans son compte.

2. L'entreprise B crée une adhésion à la collaboration. Il permet la journalisation des requêtes dans son compte.

3. La société A crée une table configurée pour les ventes.

4. La société A ajoute la règle d'analyse d'agrégation suivante au tableau des ventes configuré.

   {
     "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— L'entreprise A souhaite compter le nombre de clients uniques entre les données de vente et les données de retours. L'entreprise A souhaite également additionner le nombre de purchases produits fabriqués pour le comparer au nombre dereturns.

   joinColumns— L'entreprise A souhaite utiliser pour faire correspondre identifier les clients à partir des données de vente aux clients à partir des données de retours. Cela aidera l'entreprise A à faire correspondre les retours aux bons achats. Cela aide également l'entreprise A à segmenter les clients qui se recoupent.

   dimensionColumns— L'entreprise A filtre en dimensionColumns fonction du produit spécifique, compare les achats et les retours sur une certaine période, s'assure que la date de retour est postérieure à la date du produit et aide à segmenter les clients qui se recoupent.

   scalarFunctions— L'entreprise A sélectionne une fonction CAST scalaire pour aider à mettre à jour les formats des types de données si nécessaire en fonction de la table configurée que l'entreprise A associe à la collaboration. Il ajoute également des fonctions scalaires pour aider à formater les colonnes si nécessaire.

   outputConstraints— L'entreprise A définit des contraintes de sortie minimales. Il n'est pas nécessaire de restreindre les résultats car l'analyste est autorisé à voir les données au niveau des lignes depuis son tableau des ventes

   Note

   L'entreprise A n'est pas incluse joinRequired dans la règle d'analyse. Cela permet à leur analyste d'interroger seul le tableau des ventes.

5. La société B crée une table de retours configurée.

6. La société B ajoute la règle d'analyse d'agrégation suivante à la table des retours configurés.

   {
     "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— L'entreprise B permet à l'entreprise A de returns faire la somme pour comparer le nombre d'achats. Ils ont au moins une colonne d'agrégation car ils activent une requête d'agrégation.

   joinColumns— L'entreprise B permet à l'entreprise A de se joindre à elle identifier pour faire correspondre les clients à partir des données de retour aux clients à partir des données de vente. identifierles données sont particulièrement sensibles et leur utilisation joinColumn garantit qu'elles ne seront jamais sorties dans une requête.

   joinRequired— L'entreprise B exige que les requêtes sur les données de retour soient recoupées avec les données de vente. Ils ne veulent pas permettre à l'entreprise A d'interroger tous les individus de leur ensemble de données. Ils ont également convenu de cette restriction dans leur accord de collaboration.

   dimensionColumns— L'entreprise B permet à l'entreprise A de filtrer et de regrouper par statepopularpurchases, et customerserviceuser qui sont des attributs uniques qui pourraient aider à effectuer l'analyse pour l'entreprise A. L'entreprise B permet à l'entreprise A d'utiliser returndate pour filtrer les résultats sur returndate ce qui se produit ensuitepurchasedate. Grâce à ce filtrage, le résultat est plus précis pour évaluer l'impact du changement de produit.

   scalarFunctions— La société B permet ce qui suit :

   • TRUNC pour les dates

   • INFÉRIEUR et SUPÉRIEUR au cas où ils producttype sont saisis dans un format différent dans leurs données

   • CASTsi l'entreprise A doit convertir les types de données des ventes pour qu'ils soient identiques aux types de données des retours

   La société A n'active pas d'autres fonctions scalaires car elle ne pense pas qu'elles soient nécessaires pour les requêtes.

   outputConstraints— L'entreprise B impose des contraintes de production minimales hashedemail afin de réduire la capacité à réidentifier les clients. Cela ajoute également une contrainte de sortie minimale afin producttype de réduire la capacité de réidentifier les produits spécifiques qui ont été renvoyés. Certains types de produits peuvent être plus dominants en fonction des dimensions de la sortie (par exemple,state). Leurs contraintes de sortie seront toujours appliquées, quelles que soient les contraintes de sortie ajoutées par l'entreprise A à ses données.

7. L'entreprise A crée une table de vente associée à la collaboration.

8. L'entreprise B crée une association de tables de retours à la collaboration.

9. L'entreprise A exécute des requêtes, comme dans l'exemple suivant, pour mieux comprendre le nombre de retours dans l'entreprise B par rapport au total des achats par site en 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. Les entreprises A et B examinent les journaux de requêtes. L'entreprise B vérifie que la requête est conforme à ce qui a été convenu dans l'accord de collaboration.

Résolution des problèmes liés aux règles d'analyse d'agrégation

Utilisez les informations présentées ici pour vous aider à diagnostiquer et à résoudre les problèmes courants liés à l'utilisation des règles d'analyse d'agrégation.

Problèmes

• Ma requête n'a renvoyé aucun résultat

Ma requête n'a renvoyé aucun résultat

Cela peut se produire lorsqu'aucun résultat ne correspond ou lorsque les résultats correspondants n'atteignent pas un ou plusieurs seuils d'agrégation minimaux.

Pour plus d'informations sur les seuils d'agrégation minimaux, consultezRègle d'analyse d'agrégation - exemple.