Regla de análisis de agregación

En AWS Clean Rooms, una regla de análisis de agregación genera estadísticas agregadas utilizando las funciones COUNT, SUM y/o AVG en dimensiones opcionales. 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) entre 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 Referencia de SQL de AWS Clean Rooms.	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 tipo JOIN específico (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 los parámetros ASC DESC en Referencia de SQL de AWS Clean Rooms.	ORDER BY SUM(SALES), UPPER(campaignName)

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

• No se admiten comandos SQL distintos de SELECT.

• No se admiten subconsultas ni expresiones de tabla comunes (por ejemplo, WITH).

• No se admiten operadores que combinen varias consultas (por ejemplo, UNION).

• No se admiten los parámetros TOP, LIMIT ni OFFSET.

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 combinar, qué columna se puede contar o qué columna se puede usar en instrucciones WHERE.

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 usar en las expresiones SELECT, HAVING y ORDER BY.

Control	Definición	Uso
aggregateColumns	Columnas de tablas configuradas que se permite utilizar en las funciones de agregación.	aggregateColumns se puede usar dentro de una función de agregación en las expresiones SELECT, HAVING y ORDER BY. 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 funciones COUNT, SUM y AVG que se permite utilizar además de aggregateColumns.	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 utilizar los controles de combinación para controlar cómo se puede combinar la tabla a otras tablas de la table_expression. AWS Clean Rooms solo admite INNER JOIN. Las instrucciones INNER JOIN las declaraciones solo pueden usar columnas que se hayan categorizado explícitamente como joinColumn en la regla de análisis, con sujeción a los controles que usted defina.

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

Cada condición de coincidencia de la cláusula ON debe utilizar la condición de comparación de igualdad (=) entre dos columnas.

Las condiciones de coincidencia múltiples dentro de una cláusula ON pueden ser:

• Combinación con el operador lógico AND

• Separación mediante el operador lógico OR

nota

Todas las condiciones de coincidencia JOIN deben coincidir con una fila de cada lado de JOIN. Todos los condicionales conectados por un operador lógico OR o AND también deben cumplir 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 se desea permitir que el miembro que puede realizar la consulta utilice en la instrucción INNER JOIN.	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 categorizado como aggregateColumn, una joinColumn no se puede usar en ninguna otra parte de la consulta que no sea INNER JOIN.
joinRequired	Controle si necesita una INNER JOIN con una tabla configurada del miembro que puede realizar la consulta.	Si habilita este parámetro, INNER JOIN es obligatorio. Si no habilita este parámetro, INNER JOIN es opcional. Presuponiendo que se habilite este parámetro, el miembro que puede realizar la consulta debe incluir una tabla de su propiedad en INNER JOIN. Debe combinar su tabla JOIN con la suya, ya sea de forma directa o transitiva (es decir, combinar su tabla con otra tabla que, a su vez, está combinada con 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 se permite que el miembro que puede realizar la consulta utilice en SELECT, WHERE, GROUP BY y ORDER BY.	Se puede usar una dimensionColumn 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 permite (por ejemplo, CAST) aplicar a 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, FLOOR, LOG, LN, ROUND, SQRT

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

• Funciones de cadena: LOWER, UPPER, TRIM, RTRIM, SUBSTRING

  • En el caso de RTRIM, 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 información, consulte Referencia de SQL de AWS Clean Rooms.

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 diferenciados de la aggregateColumn asociada que debe tener la fila de salida (por ejemplo, COUNT DISTINCT) para que se devuelva 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 marcador de posición de entrada del usuario con información propia.

{
  "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 AWS Clean Rooms utilizando el 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:

   • TRUNC para las fechas

   • LOWER y UPPER en caso de que producttype se introduzca en un formato distinto 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.