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

Se você estiver contando valores distintos para agregações hierárquicas (por exemplo, conjuntos de agrupamento múltiplos, valores acumulados ou cubos), pode melhorar o desempenho produzindo ARRAYs que contenham os valores distintos e calculando o número de valores distintos destes ARRAYs. Usar esta abordagem pode ser mais rápido do que usar COUNT(DISTINCT <expr>).

Este tópico explica como usar ARRAYs para contar valores distintos.

Para outras técnicas de contagem de valores distintos, consulte Cálculo do número de valores distintos.

Neste tópico:

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);
Copy

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;
Copy
+-----------------+
| DISTINCT_VALUES |
|-----------------|
| [               |
|   5,            |
|   2,            |
|   1             |
| ]               |
+-----------------+
Copy

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;
Copy
+---------------------------+
| NUMBER_OF_DISTINCT_VALUES |
|---------------------------|
|                         3 |
+---------------------------+
Copy

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

Suponha que você queira contar o número de valores distintos em my_column. A tabela a seguir compara as instruções SQL para realizar esta tarefa com COUNT(DISTINCT expression) e ARRAY_UNIQUE_AGG(expression).

Exemplo com COUNT(DISTINCT <expressão>)

Exemplo com ARRAY_UNIQUE_AGG(<expressão>)

SELECT
  COUNT(DISTINCT my_column_1),
  COUNT(DISTINCT my_column_2)
FROM my_table;
Copy
SELECT
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_1)),
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_2))
FROM my_table;
Copy

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

Suponha que você queira contar o número de valores distintos em my_column por my_key_1 e my_key_2. A tabela a seguir compara as instruções SQL para realizar esta tarefa com COUNT(DISTINCT expression) e ARRAY_UNIQUE_AGG(expression).

Exemplo com COUNT(DISTINCT <expressão>)

Exemplo com ARRAY_UNIQUE_AGG(<expressão>)

SELECT
  COUNT(DISTINCT my_column_1),
  COUNT(DISTINCT my_column_2)
FROM my_table
GROUP BY my_key_1, my_key_2;
Copy
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;
Copy

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

ARRAY_UNIQUE_AGG funciona ainda mais eficientemente para consultas agregadas GROUP BY ROLLUP. ARRAYs podem ser compostos (em contraste com COUNT(DISTINCT <expressão>)), o que resulta em menos trabalho de computação e tempos de execução menores.

Suponha que você queira acumular o número de valores distintos em my_column por my_key_1 e my_key_2. A tabela a seguir compara as instruções SQL para realizar esta tarefa com COUNT(DISTINCT expression) e ARRAY_UNIQUE_AGG(expression).

Exemplo com COUNT(DISTINCT <expressão>)

Exemplo com ARRAY_UNIQUE_AGG(<expressão>)

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

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;
Copy

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;
Copy

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;
Copy

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;
Copy

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.