Regla de análisis de agregación

nota

Se aplica a: motor AWS Clean Rooms SQL de análisis

En AWS Clean Rooms, una regla de análisis de agregación genera estadísticas agregadas utilizando COUNT y/o AVG funciona a lo largo de dimensiones opcionales. SUM Cuando la regla de análisis de agregación se agrega a una tabla configurada, permite al miembro que puede realizar la consulta ejecutar consultas en la tabla configurada.

La regla de análisis de agregación admite casos de uso tales como la planificación de campañas, el alcance mediático, la medición de la frecuencia y la atribución.

La estructura y sintaxis de consulta admitidas se definen en Estructura y sintaxis de consultas de agregación.

Los parámetros de la regla de análisis, definidos en Regla de análisis de agregación: controles de consulta, incluyen los controles de consulta y los controles de resultados de las consultas. Sus controles de consulta incluyen la posibilidad de imponer como requisito que una tabla configurada se una a al menos una tabla configurada propiedad del miembro que puede realizar la consulta, ya sea de forma directa o transitiva. Este requisito le permite asegurarse de que la consulta se ejecute en la intersección (INNER JOIN) de su tabla y la de ellos.

Estructura y sintaxis de consultas de agregación

Las consultas en tablas que tienen una regla de análisis de agregación deben respetar la siguiente sintaxis.

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

En la siguiente tabla se explica cada una de las expresiones enumeradas en la sintaxis anterior.

Expression	Definición	Ejemplos
select_aggregate_function_expression	Una lista separada por comas que contiene las siguientes expresiones: select_aggregation_function_expression select_aggregate_expression nota Debe haber al menos una select_aggregation_function_expression en la select_aggregate_expression.	SELECT SUM(PRICE), user_segment
select_aggregation_function_expression	Una o más funciones de agregación admitidas aplicadas a una o más columnas. Solo se permiten columnas como argumentos de las funciones de agregación. nota Debe haber al menos una select_aggregation_function_expression en la select_aggregate_expression.	AVG(PRICE) COUNT(DISTINCT user_id)
select_grouping_column_expression	Expresión que puede contener cualquier expresión que utilice lo siguiente: Nombres de columna de la tabla Funciones escalares admitidas Literales de cadena Literales numéricos nota select_aggregate_expression puede asignar un alias a las columnas con o sin el parámetro AS. Para obtener más información, consulte la AWS Clean Rooms SQLReferencia.	TRUNC(timestampColumn) UPPER(campaignName)
table_expression	Una tabla o combinación de tablas que conecta expresiones condicionales de unión con join_condition. join_condition devuelve un valor booleano. La table_expression admite: Un específico JOIN tipo (INNER JOIN) La condición de comparación de igualdad dentro de una join_condition (=) Operadores lógicos (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	Una expresión condicional que devuelve un valor booleano. Puede constar de lo siguiente: Nombres de columna de la tabla Funciones escalares admitidas Operadores matemáticos Literales de cadena Literales numéricos Las condiciones de comparación admitidas son (=, >, <, <=, >=, <>, !=, NOT, IN, NOT IN, LIKE, IS NULL, IS NOT NULL). Los operadores lógicos admitidos son (AND, OR). La where_expression es opcional.	WHERE where_condition WHERE price > 100 WHERE TRUNC(timestampColumn) = '1/1/2022' WHERE timestampColumn = timestampColumn2 - 14
group_by_expression	Lista separada por comas de expresiones que cumplen con los requisitos de select_grouping_column_expression.	GROUP BY TRUNC(timestampColumn), UPPER(campaignName), segment
having_expression	Una expresión condicional que devuelve un valor booleano. Tienen una función de agregación admitida aplicada a una sola columna (por ejemplo, SUM(price)) y se comparan con un literal numérico. Las condiciones admitidas son (=, >, <, <=, >=, <>, !=). Los operadores lógicos admitidos son (AND, OR). La having_expression es opcional.	HAVING SUM(SALES) > 500
order_by_expression	Lista de expresiones separadas por comas que es compatible con los mismos requisitos definidos anteriormente en select_aggregate_expression. La order_by_expression es opcional. nota order_by_expression admite los parámetros ASC y DESC. Para obtener más información, consulte ASC DESC los parámetros en la AWS Clean Rooms SQLReferencia.	ORDER BY SUM(SALES), UPPER(campaignName)

En cuanto a la estructura y sintaxis de las consultas de agregación, tenga en cuenta lo siguiente:

• SQLcomandos distintos de SELECT no son compatibles.

• Subconsultas y expresiones de tabla comunes (por ejemplo, WITH) no se admiten.

• Operadores que combinan varias consultas (por ejemplo, UNION) no se admiten.

• TOP, LIMIT, y OFFSET no se admiten los parámetros.

Regla de análisis de agregación: controles de consulta

Los controles de consulta de agregación permiten controlar cómo se utilizan las columnas de la tabla para consultarla. Por ejemplo, puede controlar qué columna se usa para unir, qué columna se puede contar o en qué columna se puede usar WHERE declaraciones.

En las secciones siguientes se explica cada uno de los controles.

Controles de agregación

El uso de controles de agregación permite definir qué funciones de agregación se van a permitir y a qué columnas se deben aplicar. Las funciones de agregación se pueden utilizar en SELECT, HAVING, y ORDER BY expresiones.

Control	Definición	Uso
aggregateColumns	Columnas de tablas configuradas que se permite utilizar en las funciones de agregación.	aggregateColumnsse puede utilizar dentro de una función de agregación en SELECT, HAVING, y ORDER BY expresiones. Algunas aggregateColumns también se pueden categorizar como joinColumn (definición disponible más adelante). La aggregateColumn dada no se puede categorizar también como dimensionColumn (definición disponible más adelante).
function	Las AVG funciones COUNTSUM, y que permite utilizar además deaggregateColumns.	La function se puede aplicar a una aggregateColumns que esté asociada a ella.

Controles de combinación

Se utiliza una cláusula JOIN para combinar filas de dos o más tablas en función de una columna relacionada entre ellas.

Puede usar los controles de unión para controlar cómo se puede unir su tabla a otras tablas deltable_expression. AWS Clean Rooms solo admite INNER JOIN. INNER JOIN las declaraciones solo pueden usar columnas que se hayan clasificado explícitamente como una joinColumn en su regla de análisis, sujetas a los controles que usted defina.

La INNER JOIN deben funcionar en una joinColumn tabla configurada y en otra tabla configurada joinColumn de la colaboración. Usted decide qué columnas de la tabla se pueden usar como joinColumn.

Cada condición de coincidencia dentro del ON la cláusula es necesaria para utilizar la condición de comparación de igualdad (=) entre dos columnas.

Varias condiciones de coincidencia dentro de un ON las cláusulas pueden ser:

• Combinación con el operador lógico AND

• Separación mediante el operador lógico OR

nota

Todos JOIN las condiciones de coincidencia deben coincidir con una fila de cada lado del JOIN. Todos los condicionales conectados por un operador AND lógico OR o uno lógico también deben cumplir con este requisito.

A continuación se muestra un ejemplo de consulta con un operador lógico AND.

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

A continuación se muestra un ejemplo de consulta con un operador lógico OR.

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

Control	Definición	Uso
joinColumns	Las columnas (si las hay) que desea permitir que el miembro que puede realizar la consulta las utilice en INNER JOIN statement.	También se puede categorizar una joinColumn como aggregateColumn (consulteControles de agregación). La misma columna no se puede usar como joinColumn y dimensionColumns a la vez (consulte más adelante). A menos que también se haya clasificado como unaaggregateColumn, a no se joinColumn puede usar en ninguna otra parte de la consulta que no sea la INNER JOIN.
joinRequired	Controle si necesita un INNER JOIN con una tabla configurada por el miembro que puede realizar la consulta.	Si habilita este parámetro, un INNER JOIN es obligatorio. Si no habilita este parámetro, un INNER JOIN es opcional. Si se habilita este parámetro, el miembro que puede realizar la consulta debe incluir una tabla de su propiedad en el INNER JOIN. Deben JOIN su mesa con la de ellos, ya sea de forma directa o transitiva (es decir, unir su mesa a otra mesa, que a su vez está unida a la suya).

A continuación se muestra un ejemplo de transitividad.

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

nota

El miembro que puede realizar la consulta también puede usar el parámetro joinRequired. En ese caso, la consulta debe combinar su tabla con al menos otra tabla.

Controles de dimensión

Los controles de dimensión controlan la columna en la cual se pueden filtrar, agrupar o agregar las columnas de agregación.

Control	Definición	Uso
dimensionColumns	Las columnas (si las hay) que permites que utilice el miembro que puede realizar la consulta SELECT, WHERE, GROUP BY, y ORDER BY.	A dimensionColumn se puede usar en SELECT (select_grouping_column_expression), WHERE, GROUP BY, y ORDER BY. Una misma columna no puede ser a la vez dimensionColumn, joinColumn y/o aggregateColumn.

Funciones escalares

Las funciones escalares controlan qué funciones escalares se pueden usar en las columnas de dimensión.

Control	Definición	Uso
scalarFunctions	Las funciones escalares que se pueden utilizar en dimensionColumns en la consulta.	Especifica las funciones escalares (si las hay) que se permiten (por ejemplo, CAST) a las que se aplicará. dimensionColumns Las funciones escalares no se pueden usar además de otras funciones ni dentro de otras funciones. Los argumentos de las funciones escalares pueden ser columnas, literales de cadena o literales numéricos.

Se admiten las siguientes funciones escalares:

• Funciones matemáticas:ABS,CEILING,, FLOORLOG, LN,ROUND, SQRT

• Funciones de formato de tipos de datos: CAST, CONVERT, TO_CHAR, TO_DATE, TO_NUMBER, TO_TIMESTAMP

• Funciones de cadena — LOWERUPPER,TRIM,RTRIM, SUBSTRING

  • ParaRTRIM, no se permiten conjuntos de caracteres personalizados para recortar.

• Expresiones condicionales: COALESCE

• Funciones de fecha —EXTRACT,GETDATE, CURRENT _DATE, DATEADD

• Otras funciones — TRUNC

Para obtener más detalles, consulte la AWS Clean Rooms SQLReferencia.

Regla de análisis de agregación: controles de resultados de consulta

Los controles de resultados de consulta de agregación le permiten controlar qué resultados se devuelven especificando una o más condiciones que debe cumplir cada fila de salida. AWS Clean Rooms admite restricciones de agregación en forma de COUNT (DISTINCT column) >= X. Este formato requiere que cada fila agregue al menos X valores diferenciados de una selección de la tabla configurada (por ejemplo, un número mínimo de valores user_id diferenciados). Este umbral mínimo se aplica automáticamente, incluso si la consulta enviada en sí misma no utiliza la columna especificada. Se aplican de manera conjunta en cada tabla configurada de la consulta desde las tablas configuradas de cada miembro de la colaboración.

Cada tabla configurada debe tener al menos una restricción de agregación en su regla de análisis. Los propietarios de las tablas configuradas pueden añadir varios columnName y su minimum asociado y estos se aplicarán conjuntamente.

Restricciones de agregación

Las restricciones de agregación controlan qué filas de los resultados de la consulta se devuelven. Para incluirse en los resultados devueltos, una fila debe cumplir con el número mínimo especificado de valores diferenciados en cada columna especificada en la restricción de agregación. Este requisito se aplica incluso si la columna no se menciona explícitamente en la consulta o en otras partes de la regla de análisis.

Control	Definición	Uso
columnName	La aggregateColumn que se usa en la condición que debe cumplir cada fila de salida.	Puede tratarse de cualquier columna de la tabla configurada.
minimum	El número mínimo de valores distintos asociados aggregateColumn que debe tener la fila de salida (por ejemplo COUNTDISTINCT) para que aparezca en los resultados de la consulta.	El minimum debe tener al menos un valor de 2.

Estructura de la regla de análisis de agregación

El siguiente ejemplo muestra una estructura predefinida para una regla de análisis de agregación.

En el siguiente ejemplo, MyTable hace referencia a nuestra tabla de datos. Puede reemplazar cada uno user input placeholder por su propia información.

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

Regla de análisis de agregación: ejemplo

El siguiente ejemplo demuestra cómo dos empresas pueden colaborar en el AWS Clean Rooms uso del análisis de agregación.

La empresa A tiene datos de clientes y de ventas. La empresa A está interesada en conocer la actividad de devolución de productos. La empresa B es uno de los minoristas de la empresa A y dispone de datos sobre devoluciones. La empresa B también tiene atributos de segmento sobre los clientes que son útiles para la empresa A (por ejemplo, compra de productos relacionados o uso del servicio de atención al cliente del minorista). La empresa B no quiere proporcionar información sobre atributos ni datos sobre devoluciones de los clientes por fila. La empresa B solo quiere habilitar un conjunto de consultas para que la empresa A obtenga estadísticas agregadas sobre los clientes que se superponen dentro de un umbral de agregación mínimo.

La empresa A y la empresa B deciden colaborar para que la empresa A pueda entender la actividad de devolución de productos y ofrecer mejores productos en la empresa B y en otros canales.

Para crear la colaboración y realizar un análisis de agregación, las empresas hacen lo siguiente:

1. La empresa A crea una colaboración y crea una pertenencia. La colaboración tiene a la empresa B como otro miembro de la colaboración. La empresa A habilita el registro de consultas en la colaboración y habilita el registro de consultas en su cuenta.

2. La empresa B crea una pertenencia en la colaboración. Habilita el registro de consultas en su cuenta.

3. La empresa A crea una tabla configurada de ventas.

4. La empresa A añade la siguiente regla de análisis de agregación a la tabla configurada de ventas.

   {
     "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: la empresa A quiere contar el número de clientes únicos que se superponen entre los datos de ventas y los datos de devoluciones. La empresa A también quiere sumar el número de purchases fabricados para compararlo con el número de returns.

   joinColumns: la empresa A desea usar identifier para cotejar los clientes de los datos de ventas con los clientes de los datos de devoluciones. Esto ayudará a la empresa A a relacionar las devoluciones con las compras correctas. También ayudará a la empresa A a segmentar los clientes superpuestos.

   dimensionColumns: la empresa A usa dimensionColumns para filtrar por un producto específico, comparar las compras y las devoluciones correspondientes a un periodo de tiempo determinado, asegurarse de que la fecha de devolución sea posterior a la fecha del producto y ayudar a segmentar los clientes superpuestos.

   scalarFunctions: la empresa A selecciona la función escalar CAST para ayudar a actualizar los formatos de tipo de datos si es necesario, basándose en la tabla configurada que la empresa A ha asociado a la colaboración. También añade funciones escalares para ayudar a dar formato a las columnas si es necesario.

   outputConstraints: la empresa A establece restricciones de salida mínimas. No necesita restringir los resultados, ya que el analista puede ver los datos de la tabla de ventas por fila.

   nota

   La empresa A no incluye joinRequired en la regla de análisis. Ofrece flexibilidad para que el analista consulte solo la tabla de ventas.

5. La empresa B crea una tabla configurada de devoluciones.

6. La empresa B añade la siguiente regla de análisis de agregación a la tabla configurada de devoluciones.

   {
     "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: la empresa B permite a la empresa A sumar las returns para compararlas con el número de compras. Tienen al menos una columna agregada porque han habilitado una consulta agregada.

   joinColumns: la empresa B permite a la empresa A combinar por identifier para cotejar los clientes de los datos de devoluciones con los clientes de los datos de ventas. Los datos de identifier son especialmente sensibles, y tenerlos como joinColumn garantiza que los datos no se incluyan nunca en una consulta.

   joinRequired: la empresa B impone como requisito que las consultas sobre los datos de devoluciones se superpongan con los datos de ventas. No quieren permitir que la empresa A consulte todas las personas de su conjunto de datos. También acordaron esa restricción en su contrato de colaboración.

   dimensionColumns: la empresa B permite a la empresa A filtrar y agrupar por state, popularpurchases y customerserviceuser, que son atributos únicos que podrían ayudar a realizar el análisis para la empresa A. La empresa B permite a la empresa A usar returndate para filtrar la salida por returndate que sea posterior a purchasedate. Con este filtrado, la salida es más precisa a la hora de evaluar el impacto del cambio de producto.

   scalarFunctions: la empresa B habilita lo siguiente:

   • TRUNCpara fechas

   • LOWERy UPPER en caso de producttype que se introduzca en un formato diferente en sus datos

   • CAST si la empresa A necesita convertir los tipos de datos de las ventas para que sean iguales a los tipos de datos de las devoluciones

   La empresa A no habilita otras funciones escalares porque no cree que sean necesarias para las consultas.

   outputConstraints: la empresa B establece restricciones de salida mínimas en hashedemail para reducir en mayor medida la posibilidad de volver a identificar a los clientes. También añade una restricción de salida mínima en producttype para reducir en mayor medida la posibilidad de volver a identificar los productos específicos devueltos. Determinados tipos de productos podrían ser más dominantes en función de las dimensiones de la salida (por ejemplo, state). Sus restricciones de salida se aplicarán siempre, independientemente de las restricciones de salida que añada la empresa A a sus datos.

7. La empresa A crea una asociación a la tabla de ventas en la colaboración.

8. La empresa B crea una asociación a la tabla de devoluciones en la colaboración.

9. La empresa A realiza consultas, como las del ejemplo siguiente, para entender mejor la cantidad de devoluciones de la empresa B en comparación con el total de compras por ubicación 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. La empresa A y la empresa B revisan los registros de consultas. La empresa B comprueba que la consulta se ajusta a lo acordado en el contrato de colaboración.

Solución de problemas relacionados con reglas de análisis de agregación

Utilice la información que se incluye aquí para diagnosticar y solucionar problemas frecuentes cuando utilice reglas de análisis de agregación.

Problemas

• Mi consulta no ha devuelto ningún resultado

Mi consulta no ha devuelto ningún resultado

Esto puede ocurrir cuando no hay resultados coincidentes o cuando los resultados coincidentes no cumplen uno o más umbrales de agregación mínimos.

Para obtener más información sobre los umbrales de agregación mínimos, consulte Regla de análisis de agregación: ejemplo.