Regra de análise de agregação

Em AWS Clean Rooms, uma regra de análise de agregação gera estatísticas agregadas usando as funções COUNT, SUM e/ou AVG junto com dimensões opcionais. Quando a regra de análise de agregação é adicionada a uma tabela configurada, ela permite que o membro que pode consultar execute consultas na tabela configurada.

A regra de análise de agregação oferece suporte a casos de uso como planejamento de campanhas, alcance de mídia, medição de frequência e atribuição.

A estrutura e a sintaxe de consulta suportadas são definidas em Estrutura e sintaxe da consulta de agregação.

Os parâmetros da regra de análise, definidos em Regra de análise de agregação - controles de consulta, incluem controles de consulta e controles de resultados de consulta. Seus controles de consulta incluem a capacidade de exigir que uma tabela configurada seja unida a pelo menos uma tabela configurada de propriedade do membro que pode consultar, direta ou transitivamente. Esse requisito permite garantir que a consulta seja executada na interseção (INNER JOIN) da sua tabela com a deles.

Estrutura e sintaxe da consulta de agregação

As consultas em tabelas que têm uma regra de análise de agregação devem seguir a sintaxe a seguir.

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

A tabela a seguir explica cada expressão listada na sintaxe anterior.

Expressão	Definição	Exemplos
select_aggregate_function_expression	Uma lista separada por vírgulas contendo as seguintes expressões: select_aggregation_function_expression select_aggregate_expression nota Deve haver pelo menos um select_aggregation_function_expression no select_aggregate_expression.	SELECT SUM(PRICE), user_segment
select_aggregation_function_expression	Uma ou mais funções de agregação suportadas aplicadas a uma ou mais colunas. Somente colunas são permitidas como argumentos das funções de agregação. nota Deve haver pelo menos um select_aggregation_function_expression no select_aggregate_expression.	AVG(PRICE) COUNT(DISTINCT user_id)
select_grouping_column_expression	Uma expressão que pode conter qualquer expressão usando o seguinte: Nomes de colunas da tabela Funções escalares aceitas Literais de string Literais numéricos nota select_aggregate_expression pode criar um alias para colunas com ou sem o parâmetro AS. Para obter mais informações, consulte a SQL Reference AWS Clean Rooms.	TRUNC(timestampColumn) UPPER(campaignName)
table_expression	Uma tabela, ou junção de tabelas, conectando expressões condicionais de junção com join_condition. join_condition retorna um Booleano. O table_expression oferece suporte a: Um tipo específico JOIN (INNER JOIN) A condição de comparação de igualdade dentro de um 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	Uma expressão condicional que retorna um booliano. Pode ser composto do seguinte: Nomes de colunas da tabela Funções escalares aceitas Operadores matemáticos Literais de string Literais numéricos As condições de comparação suportadas são (=, >, <, <=, >=, <>, !=, NOT, IN, NOT IN, LIKE, IS NULL, IS NOT NULL). Os operadores lógicos suportados são (AND, OR). where_expression é opcional.	WHERE where_condition WHERE price > 100 WHERE TRUNC(timestampColumn) = '1/1/2022' WHERE timestampColumn = timestampColumn2 - 14
group_by_expression	Uma lista separada por vírgulas de expressões que atendem aos requisitos do select_grouping_column_expression.	GROUP BY TRUNC(timestampColumn), UPPER(campaignName), segment
having_expression	Uma expressão condicional que retorna um booleano. Eles têm uma função de agregação compatível aplicada a uma única coluna (por exemplo, SUM(price)) e são comparados a um literal numérico. As condições suportadas são (=, >, <, <=, >=, <>, !=). Os operadores lógicos suportados são (AND, OR). having_expression é opcional.	HAVING SUM(SALES) > 500
order_by_expression	Uma lista de expressões separadas por vírgulas que é compatível com os mesmos requisitos select_aggregate_expression definidos anteriormente. order_by_expression é opcional. nota order_by_expression permite os parâmetros ASC e DESC. Para obter mais informações, consulte Parâmetros ASC DESC na SQL Reference AWS Clean Rooms.	ORDER BY SUM(SALES), UPPER(campaignName)

Para a estrutura e a sintaxe da consulta de agregação, lembre-se de que:

• Comandos SQL diferentes SELECT dos não são suportados.

• Não há suporte para subconsultas e expressões de tabela comuns (por exemplo, WITH).

• Operadores que combinam várias consultas (por exemplo, UNION) não são compatíveis.

• Os parâmetros TOP, LIMIT e OFFSET não têm suporte.

Regra de análise de agregação - controles de consulta

Com os controles de consulta de agregação, você pode controlar como as colunas em sua tabela são usadas para consultar a tabela. Por exemplo, você pode controlar qual coluna é usada para unir, qual coluna pode ser contada ou qual coluna pode ser usada em declarações WHERE.

As seções a seguir explicam cada controle.

Controles de agregação

Ao usar controles de agregação, você pode definir quais funções de agregação permitir e em quais colunas elas devem ser aplicadas. As funções de agregação podem ser usadas nas expressões SELECT, HAVING, ORDER e BY.

Controle	Definição	Uso
aggregateColumns	Colunas de colunas de tabela configuradas que você permite usar nas funções de agregação.	aggregateColumns pode ser usado dentro de uma função de agregação nas expressões SELECT, HAVING, ORDER e BY. Alguns aggregateColumns também podem ser categorizados como joinColumn (definidos posteriormente). Considerando que aggregateColumn também não pode ser categorizado como um dimensionColumn (definido posteriormente).
function	As funções COUNT, SUM e AVG que você permite usar em cima do aggregateColumns.	function pode ser aplicado a um aggregateColumns que esteja associado a ele.

Controles de junção

Uma cláusula JOIN é usada para combinar linhas de duas ou mais tabelas, com base em uma coluna relacionada entre elas.

Você pode usar os controles de união para controlar como sua tabela pode ser unida a outras tabelas no table_expression. AWS Clean Rooms só suporta INNER JOIN. As instruções INNER JOIN só podem usar colunas que tenham sido explicitamente categorizadas como joinColumn em sua regra de análise, sujeitas aos controles que você define.

INNER JOIN devem operar em uma joinColumn da sua tabela configurada e em outra joinColumn tabela configurada na colaboração. Você decide quais colunas da sua tabela podem ser usadas como joinColumn.

Cada condição de correspondência dentro da cláusula ON deve usar a condição de comparação de igualdade (=) entre duas colunas.

Várias condições de correspondência dentro de qualquer ON cláusula podem ser:

• Combinado usando o operador lógico AND

• Separado usando o operador lógico OR

nota

Todas as condições de correspondência JOIN devem corresponder a uma linha de cada lado do JOIN. Todas as condicionais conectadas por um OR ou um operador lógico AND ou devem atender a este requisito também.

Veja a seguir um exemplo de uma consulta com um operador lógico AND.

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

Veja a seguir um exemplo de uma consulta com um operador lógico OR.

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

Controle	Definição	Uso
joinColumns	As colunas (se houver) que você deseja permitir que o membro que pode consultar use na instrução INNER JOIN.	Um joinColumn específico também pode ser classificado como aggregateColumn (consulte Controles de agregação). A mesma coluna não pode ser usada como joinColumn e dimensionColumns (confira mais adiante). A menos que também tenha sido categorizado como um aggregateColumn, um joinColumn não pode ser usado em nenhuma outra parte da consulta além de INNER JOIN.
joinRequired	Controle se você precisa de um INNER JOIN com uma tabela configurada do membro que pode consultar.	Se você ativar esse parâmetro, será necessário um INNER JOIN. Se você não habilitar esse parâmetro, an INNER JOIN é opcional. Supondo que você habilite esse parâmetro, o membro que pode consultar deverá incluir uma tabela de sua propriedade no INNER JOIN. Eles devem unir JOIN sua mesa à deles, direta ou transitivamente (ou seja, unir sua mesa a outra mesa, que por sua vez está unida à sua mesa).

A seguir está um exemplo de transitividade.

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

nota

O membro que pode consultar também pode usar o parâmetro joinRequired. Nesse caso, a consulta deve unir sua tabela a pelo menos uma outra tabela.

Controles de dimensão

Os controles de dimensão controlam a coluna na qual as colunas de agregação podem ser filtradas, agrupadas ou agregadas.

Controle	Definição	Uso
dimensionColumns	As colunas (se houver) que você permite que o membro que pode consultar use em SELECT, WHERE, GROUP BY e ORDER BY.	A dimensionColumn pode ser usado em SELECT (select_grouping_column_expression), WHERE, GROUP BY e ORDER BY. A mesma coluna não pode ser ao mesmo tempo um dimensionColumn, um joinColumn e/ou um aggregateColumn.

Funções escalares

As funções escalares controlam quais funções escalares podem ser usadas em colunas de dimensão.

Controle	Definição	Uso
scalarFunctions	As funções escalares que podem ser usadas em dimensionColumns na consulta.	Especifica as funções escalares (se houver) nas quais você permite (por exemplo, CAST) que sejam aplicadas a dimensionColumns. As funções escalares não podem ser usadas em cima de outras funções ou dentro de outras funções. Os argumentos das funções escalares podem ser colunas, literais de string ou literais numéricos.

As seguintes funções escalares são suportadas:

• Funções matemáticas — ABS, CEILING, FLOOR, LOG, LN, ROUND, SQRT

• Funções de formatação de tipo de dados – CAST, CONVERT, TO_CHAR, TO_DATE, TO_NUMBER, TO_TIMESTAMP

• Funções de string — LOWER, UPPER, TRIM, RTRIM, SUBSTRING

  • Para RTRIM, conjuntos de caracteres personalizados para cortar não são permitidos.

• Expressões condicionais – COALESCE

• Funções de data — EXTRACT, GETDATE, CURRENT_DATE, DATEADD

• Outras funções – TRUNC

Para obter mais detalhes, consulte a SQL Reference AWS Clean Rooms.

Regra de análise de agregação - controles de resultados da consulta

Com os controles de resultados da consulta de agregação, você pode controlar quais resultados são retornados especificando uma ou mais condições que cada linha de saída deve atender para que seja retornada. AWS Clean Rooms suporta restrições de agregação na forma de COUNT (DISTINCT column) >= X. Esse formulário exige que cada linha agregue pelo menos X valores distintos de uma escolha da tabela configurada (por exemplo, um número mínimo de user_id valores distintos). Esse limite mínimo é aplicado automaticamente, mesmo que a consulta enviada em si não use a coluna especificada. Elas são aplicadas coletivamente em cada tabela configurada na consulta a partir das tabelas configuradas de cada membro na colaboração.

Cada tabela configurada deve ter pelo menos uma restrição de agregação em sua regra de análise. Os proprietários de tabelas configuradas podem adicionar várias columnName e associadas minimum e elas são aplicadas coletivamente.

Restrições de agregação

As restrições de agregação controlam quais linhas nos resultados da consulta são retornadas. Para ser retornada, uma linha deve atender ao número mínimo especificado de valores distintos em cada coluna especificada na restrição de agregação. Esse requisito se aplica mesmo que a coluna não seja mencionada explicitamente na consulta ou em outras partes da regra de análise.

Controle	Definição	Uso
columnName	O aggregateColumn que é usado na condição que cada linha de saída deve atender.	Pode ser qualquer coluna na tabela configurada.
minimum	O número mínimo de valores distintos associados aggregateColumn que a linha de saída deve ter (por exemplo, COUNT DISTINCT) para que ela seja retornada nos resultados da consulta.	O minimum deve ter pelo menos um valor de 2.

Estrutura de regras de análise de agregação

O exemplo a seguir mostra uma estrutura predefinida para uma regra de análise de agregação.

No exemplo a seguir, MyTable refere-se à sua tabela de dados. Você pode substituir cada espaço reservado de entrada do usuário por suas próprias informações.

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

Regra de análise de agregação - exemplo

O exemplo a seguir demonstra como duas empresas podem colaborar em AWS Clean Rooms usando da análise de agregação.

A empresa A tem dados de clientes e vendas. A empresa A está interessada em entender a atividade de devolução de produtos. A empresa B é uma das varejistas da empresa A e tem dados de devoluções. A empresa B também tem atributos de segmento de clientes que são úteis para a empresa A (por exemplo, comprou produtos relacionados, usa o atendimento ao cliente do varejista). A empresa B não quer fornecer dados de retorno de clientes em nível de linha e informações de atributos. A empresa B deseja apenas habilitar um conjunto de consultas para que a empresa A obtenha estatísticas agregadas sobre clientes sobrepostos em um limite mínimo de agregação.

A empresa A e a empresa B decidem colaborar para que a empresa A possa entender a atividade de devolução de produtos e oferecer produtos melhores na empresa B e em outros canais.

Para criar a colaboração e executar uma análise de agregação, as empresas fazem o seguinte:

1. A empresa A cria uma colaboração e cria uma associação. A colaboração tem a Empresa B como outro membro da colaboração. A empresa A permite o registro de consultas na colaboração e permite o registro de consultas em sua conta.

2. A empresa B cria uma associação na colaboração. Ele permite o registro de consultas em sua conta.

3. A empresa A cria uma tabela configurada de vendas.

4. A empresa A adiciona a seguinte regra de análise de agregação à tabela configurada de vendas.

   {
     "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 empresa A quer contar o número de clientes únicos na sobreposição entre dados de vendas e dados de devoluções. A empresa A também deseja somar o número de purchases feitos para comparar com o número de returns.

   joinColumns – A empresa A deseja usar para combinar clientes identifier a partir de dados de vendas com clientes a partir de dados de devoluções. Isso ajudará a empresa A Match a retornar às compras certas. Também ajuda a Empresa A a segmentar clientes sobrepostos.

   dimensionColumns – A empresa A usa dimensionColumns para filtrar por produto específico, comparar compras e devoluções em um determinado período de tempo, garantir que a data de devolução seja posterior à data do produto e ajudar a segmentar clientes sobrepostos.

   scalarFunctions – A empresa A seleciona a função escalar CAST para ajudar a atualizar os formatos do tipo de dados, se necessário, com base na tabela configurada que a empresa A associa à colaboração. Ele também adiciona funções escalares para ajudar a formatar colunas, se necessário.

   outputConstraints – A empresa A define restrições mínimas de produção. Não é necessário restringir os resultados porque o analista pode ver dados em nível de linha em sua tabela de vendas

   nota

   A empresa A não inclui joinRequired na regra de análise. Ele fornece flexibilidade para o analista consultar a tabela de vendas sozinho.

5. A empresa B cria uma tabela configurada de devoluções.

6. A empresa B adiciona a seguinte regra de análise de agregação à tabela configurada de devoluções.

   {
     "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 – A empresa B permite que a empresa A faça uma soma returns para comparar com o número de compras. Eles têm pelo menos uma coluna agregada porque estão habilitando uma consulta agregada.

   joinColumns – A empresa B permite que a empresa A se junte identifier para combinar clientes a partir dos dados de devolução com os clientes a partir dos dados de vendas. Os dados identifier são particularmente confidenciais e tê-los como garantia joinColumn de que os dados nunca serão gerados em uma consulta.

   joinRequired – A empresa B exige que as consultas sobre os dados de devolução sejam sobrepostas aos dados de vendas. Eles não querem permitir que a Empresa A consulte todas as pessoas em seu conjunto de dados. Eles também concordaram com essa restrição em seu acordo de colaboração.

   dimensionColumns – A empresa B permite que a empresa A filtre e agrupe por state, popularpurchases e customerserviceuser que são atributos exclusivos que podem ajudar a fazer a análise para a empresa A. A empresa B permite que a empresa A use returndate para filtrar a saída returndate que ocorre depois de purchasedate. Com essa filtragem, a saída é mais precisa para avaliar o impacto da alteração do produto.

   scalarFunctions – A empresa B permite o seguinte:

   • TRUNC para datas

   • LOWER e UPPER, caso o producttype seja inserido em um formato diferente em seus dados

   • CAST se a empresa A precisar converter os tipos de dados em vendas para serem iguais aos tipos de dados em devoluções

   A empresa A não habilita outras funções escalares porque não acredita que sejam necessárias para consultas.

   outputConstraints – A empresa B define restrições mínimas de produção em hashedemail para ajudar a reduzir a capacidade de reidentificar clientes. Também adiciona uma restrição mínima de produção em producttype para reduzir a capacidade de reidentificar produtos específicos que foram devolvidos. Certos tipos de produtos podem ser mais dominantes com base nas dimensões da produção (por exemplo, state). Suas restrições de saída sempre serão aplicadas, independentemente das restrições de saída adicionadas pela Empresa A aos seus dados.

7. A empresa A cria uma associação de tabela de vendas à colaboração.

8. A empresa B cria uma associação de tabela de devoluções à colaboração.

9. A empresa A executa consultas, como o exemplo a seguir, para entender melhor a quantidade de devoluções na empresa B em comparação com o total de compras por local em 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. A empresa A e a empresa B revisam os logs de consulta. A empresa B verifica se a consulta está alinhada com o que foi acordado no contrato de colaboração.

Solução de problemas de regras de análise de agregação

Use as informações aqui para ajudá-lo a diagnosticar e corrigir problemas comuns ao trabalhar com regras de análise de agregação.

Problemas

• Minha consulta não retornou nenhum resultado

Minha consulta não retornou nenhum resultado

Isso pode acontecer quando não há resultados correspondentes ou quando os resultados correspondentes não atendem a um ou mais limites mínimos de agregação.

Para obter mais informações sobre limites mínimos de agregação, consulte Regra de análise de agregação - exemplo.