Uso de matrizes para computar valores distintos para agregações hierárquicas¶

Introdução¶

Ao calcular o número de valores distintos para agregações hierárquicas (por exemplo, conjuntos de agrupamento múltiplos, valores acumulados ou cubos), você pode acelerar o cálculo chamando funções que produzam matrizes contendo os valores distintos. Você pode então chamar ARRAY_SIZE para calcular a contagem desses valores distintos.

Estas funções de agregação que produzem ARRAYs de valores distintos podem desempenhar melhor que COUNT(DISTINCT <expressão>) nas consultas das seguintes formas:

• Consultas agregadas GROUP BY ROLLUP

• consultas contendo múltiplos conjuntos de agrupamentos.

Ao contrário de COUNT(DISTINCT <expressão>) (que precisa ser executada para cada grupo), você pode compor e reutilizar ARRAYs que contenham os valores distintos. Para agregações hierárquicas, evita-se o cálculo repetido das contagens distintas, produzindo estes ARRAYs uma vez e reutilizando-os em níveis de agregação mais altos.

Além disso, para melhorar ainda mais o desempenho, você pode produzir estes ARRAYs antes do tempo (por exemplo, em uma exibição materializada), em vez de durante a consulta, e você pode usar estes ARRAYs pré-calculados em sua consulta.

Criando um ARRAY contendo valores distintos¶

Para criar um ARRAY que contenha os valores distintos em uma coluna, chame a função ARRAY_UNIQUE_AGG em uma instrução SELECT.

ARRAY_UNIQUE_AGG é uma função de agregação. A agregação neste contexto significa retornar apenas uma instância de um valor que aparece em várias linhas. Se várias linhas contêm o valor 3, ARRAY_UNIQUE_AGG apenas inclua 3 uma vez no ARRAY retornado.

Por exemplo, crie a seguinte tabela contendo uma coluna de valores numéricos e insira alguns valores nessa coluna.

CREATE OR REPLACE TABLE array_unique_agg_test (a INTEGER);
INSERT INTO array_unique_agg_test VALUES (5), (2), (1), (2), (1);

Execute o seguinte comando para produzir um ARRAY que contenha os valores distintos na coluna:

SELECT ARRAY_UNIQUE_AGG(a) AS distinct_values FROM array_unique_agg_test;

+-----------------+
| DISTINCT_VALUES |
|-----------------|
| [               |
|   5,            |
|   2,            |
|   1             |
| ]               |
+-----------------+

Cálculo do número de valores distintos a partir de ARRAYs¶

Para obter a contagem total dos valores distintos do ARRAY, chame ARRAY_SIZE, passando o ARRAY criado por ARRAY_UNIQUE_AGG.

Por exemplo:

SELECT ARRAY_SIZE(ARRAY_UNIQUE_AGG(a)) AS number_of_distinct_values FROM array_unique_agg_test;

+---------------------------+
| NUMBER_OF_DISTINCT_VALUES |
|---------------------------|
|                         3 |
+---------------------------+

Uso de matrizes para melhorar o desempenho das consultas¶

Os exemplos seguintes demonstram como utilizar as funções de agregação que produzem ARRAYs de valores distintos como alternativa a COUNT(DISTINCT <expressão>).

• Exemplo 1: Contar os valores distintos em uma única tabela

• Exemplo 2: Usar GROUP BY para calcular as contagens por grupo

• Exemplo 3: Usar GROUP BY ROLLUP para acumular contagens por grupo

SELECT
  COUNT(DISTINCT my_column_1),
  COUNT(DISTINCT my_column_2)
FROM my_table;

SELECT
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_1)),
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_2))
FROM my_table;

SELECT
  COUNT(DISTINCT my_column_1),
  COUNT(DISTINCT my_column_2)
FROM my_table
GROUP BY my_key_1, my_key_2;

SELECT
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_1)),
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_2))
FROM my_table
GROUP BY my_key_1, my_key_2;

SELECT
  COUNT(DISTINCT my_column)
FROM my_table
GROUP BY ROLLUP(my_key_1, my_key_2);

SELECT
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column))
FROM my_table
GROUP BY ROLLUP(my_key_1, my_key_2);

Pré-computação de ARRAYs¶

Para melhorar o desempenho, você pode pré-computar ARRAYs de valores distintos em uma tabela ou exibição materializada.

Por exemplo, suponha que seu data warehouse contenha uma tabela de fatos com múltiplas dimensões. Você pode definir uma exibição materializada que constrói ARRAYs para realizar uma pré-computação ou pré-agregação grosseira antes de computar os agregados ou cubos finais que requerem uma COUNT(DISTINCT <expressão>).

Para coletar os valores distintos de ARRAYs em cada linha, chame a função ARRAY_UNION_AGG.

O exemplo seguinte cria uma tabela contendo ARRAYs e utiliza esta tabela para calcular o número de valores distintos, agregados por diferentes dimensões.

A seguinte instrução cria uma tabela chamada precompute que contém ARRAYs:

CREATE TABLE precompute AS
SELECT
  my_dimension_1,
  my_dimension_2,
  ARRAY_UNIQUE_AGG(my_column) arr
FROM my_table
GROUP BY 1, 2;

A seguinte instrução calcula os agregados para my_dimension_1 e my_dimension_2:

SELECT
  my_dimension_1,
  my_dimension_2,
  ARRAY_SIZE(arr)
FROM precompute
GROUP BY 1, 2;

A seguinte instrução calcula o agregado somente para my_dimension_1:

SELECT
  my_dimension_1,
  ARRAY_SIZE(ARRAY_UNION_AGG(arr))
FROM precompute
GROUP BY 1;

A seguinte instrução calcula o agregado somente para my_dimension_2:

SELECT
  my_dimension_2,
  ARRAY_SIZE(ARRAY_UNION_AGG(arr))
FROM precompute
GROUP BY 1;

Limitações¶

No Snowflake, tipos de dados ARRAY são limitados a 16 MiB, o que significa que ARRAY_UNIQUE_AGG ou ARRAY_UNION_AGG gerará um erro se o tamanho físico da saída ARRAY exceder este tamanho.

Nestes casos, considere o uso de uma agregação de bitmap em vez disso. Como alternativa, você pode aplicar uma técnica de bucketização semelhante à utilizada para agregações de bitmap, mas com uma função de bucketização diferente da BITMAP_BUCKET_NUMBER.