배열을 사용하여 계층적 집계에 대한 고유 값 계산하기¶

소개¶

계층적 집계(예: 여러 그룹화 세트, 롤업 또는 큐브)에 대한 고유 값 개수를 계산할 때, 고유 값을 포함하는 배열을 생성하는 함수를 호출하여 계산 속도를 높일 수 있습니다. 그런 다음 ARRAY_SIZE 를 호출하여 고유 값의 수를 계산할 수 있습니다.

고유 값으로 구성된 ARRAY를 생성하는 이러한 집계 함수는 다음 형식의 쿼리에서 COUNT(DISTINCT <식>) 보다 나은 성능을 발휘할 수 있습니다.

• GROUP BY ROLLUP 집계 쿼리

• 여러 그룹화 세트를 포함하는 쿼리.

(각 그룹에 대해 실행해야 하는) COUNT(DISTINCT <식>) 과 달리, 고유 값을 포함하는 ARRAY를 구성하고 재사용할 수 있습니다. 계층적 집계의 경우, 이러한 ARRAY를 한 번 생성하고 더 높은 집계 수준에서 재사용하여 고유 값 개수의 반복적 계산을 방지합니다.

또한, 성능을 더욱 개선하려면, 쿼리를 수행하는 동안이 아닌 쿼리를 수행하기 전에 미리 이러한 ARRAY를 생성하여(예: 구체화된 뷰에서), 미리 계산된 ARRAY를 쿼리에 사용하면 됩니다.

고유 값을 포함하는 ARRAY 만들기¶

열에 고유 값을 포함하는 ARRAY를 만들려면 SELECT 문에서 ARRAY_UNIQUE_AGG 함수를 호출하십시오.

ARRAY_UNIQUE_AGG 는 집계 함수입니다. 이 컨텍스트에서 집계는 여러 행에 나타나는 값의 한 인스턴스만 반환하는 것을 의미합니다. 여러 행에 값 3이 포함된 경우 ARRAY_UNIQUE_AGG 는 반환된 ARRAY에 3을 한 번만 포함합니다.

예를 들어, 숫자 값의 열이 포함된 다음 테이블을 만들고 그 열에 몇 개의 값을 삽입합니다.

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

다음 명령을 실행하여 열에 고유 값이 포함된 ARRAY를 생성합니다.

SELECT ARRAY_UNIQUE_AGG(a) AS distinct_values FROM array_unique_agg_test;

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

ARRAY에서 고유 값 개수 계산하기¶

ARRAY에서 고유 값의 총 개수를 구하려면 ARRAY_SIZE 를 호출하여 ARRAY_UNIQUE_AGG 를 통해 생성된 ARRAY를 전달합니다.

예:

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

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

배열을 사용하여 쿼리 성능 향상하기¶

다음 예는 COUNT(DISTINCT <식>) 을 대신하여 고유 값의 ARRAY를 생성하는 집계 함수를 사용하는 방법을 보여줍니다.

• 예 1: 단일 테이블에서 고유 값 계산하기

• 예2: GROUP BY를 사용하여 그룹별로 개수 계산하기

• 예3: GROUP BY ROLLUP을 사용하여 그룹별로 개수 롤업하기

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

ARRAY 사전 계산하기¶

성능을 향상하기 위해, 테이블 또는 구체화된 뷰에서 고유 값의 ARRAY를 미리 계산할 수 있습니다.

예를 들어, 여러 차원이 있는 정보 테이블이 데이터 웨어하우스에 포함되어 있다고 가정해 보겠습니다. 코스 그레인드 사전 계산 또는 사전 집계를 실행하기 위해 ARRAY를 구성하는 구체화된 뷰를 정의한 후 COUNT(DISTINCT <식>) 이 필요한 최종 집계 또는 큐브를 계산할 수 있습니다.

각 행의 ARRAY에서 고유 값을 수집하려면 ARRAY_UNION_AGG 함수를 호출하십시오.

다음 예에서는 ARRAY가 포함된 테이블을 생성하고 이 테이블을 사용하여 다른 차원으로 집계된 고유 값 개수를 계산합니다.

다음 문을 실행하면 ARRAY를 포함하는 precompute 라는 테이블이 생성됩니다.

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

다음 문을 실행하면 my_dimension_1 및 my_dimension_2 의 집계가 계산됩니다.

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

다음 문을 실행하면 my_dimension_1 의 집계만 계산됩니다.

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

다음 문을 실행하면 my_dimension_2 의 집계만 계산됩니다.

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

제한 사항¶

Snowflake에서 ARRAY 데이터 타입은 16MiB로 제한되는데, 이는 출력 ARRAY의 실제 크기가 이 크기를 초과하면 ARRAY_UNIQUE_AGG 또는 ARRAY_UNION_AGG가 오류를 생성한다는 뜻입니다.

이러한 경우, 비트맵 집계 를 대신 사용해 보십시오. 또는 비트맵 집계에 사용되는 것과 유사한 버킷화 기술을 적용할 수 있지만, BITMAP_BUCKET_NUMBER와는 다른 버킷화 함수를 사용합니다.