HASH_AGG
HASH_AGG
(정렬되지 않은) 입력 행 집합에 대한 집계 부호 있는 64비트 해시 값을 돌려주는 집계·윈도우 함수예요. 값 집합의 변경을 개별 값을 비교하지 않고 감지할 때 유용해요.
출처: 공식 문서
본문
(정렬되지 않은) 입력 행 집합에 대한 집계 부호 있는 64비트 해시 값을 반환합니다. HASH_AGG는 입력이 제공되지 않아도 절대 NULL을 반환하지 않습니다. 빈 입력은 0으로 "해시"됩니다.
집계 해시 함수의 한 용도는 개별 이전·새 값을 비교하지 않고 값 집합의 변경을 감지하는 것입니다. HASH_AGG는 많은 입력에 기반해 단일 해시 값을 계산할 수 있습니다. 입력 중 하나에 대한 거의 모든 변경은 HASH_AGG 함수 출력의 변경으로 이어질 가능성이 높습니다. 두 값 목록을 비교하려면 보통 두 목록을 모두 정렬해야 하지만, HASH_AGG는 입력 순서와 무관하게 같은 값을 생성합니다. HASH_AGG는 값들을 정렬할 필요가 없기 때문에 성능이 일반적으로 훨씬 빠릅니다.
참고 HASH_AGG는 암호화 해시 함수가 아니며 그렇게 사용해서는 안 됩니다.
암호화 목적에는 SHA 함수 계열(String & binary functions 에 있음)을 사용하세요.
참고 (See also): HASH
구문 (Syntax)
집계 함수 (Aggregate function)
HASH_AGG( [ DISTINCT ] <expr> [ , <expr2> ... ] )
HASH_AGG(*)
윈도우 함수 (Window function)
HASH_AGG( [ DISTINCT ] <expr> [ , <expr2> ... ] ) OVER ( [ PARTITION BY <expr3> ] )
HASH_AGG(*) OVER ( [ PARTITION BY <expr3> ] )
인자 (Arguments)
(mytable.*)
(* ILIKE 'col1%')
(* EXCLUDE col1)
(* EXCLUDE (col1, col2))
(mytable.* ILIKE 'col1%')
exprN
표현식은 GEOGRAPHY와 GEOMETRY를 제외한 모든 Snowflake 데이터 타입의 일반 표현식일 수 있습니다.
expr2
추가 표현식을 포함할 수 있습니다.
expr3
결과를 여러 윈도우로 나누고 싶다면 분할할 컬럼입니다.
NULL 값을 가진 레코드를 포함한 모든 레코드의 모든 컬럼에 대한 집계 해시 값을 반환합니다. 집계 함수와 윈도우 함수 양쪽 모두에 와일드카드를 지정할 수 있습니다. 함수에 와일드카드를 전달할 때는 테이블의 이름이나 별칭으로 와일드카드를 한정할 수 있습니다. 예를 들어 mytable 테이블의 모든 컬럼을 전달하려면 (mytable.*)을 지정하세요. 필터링을 위해 ILIKE와 EXCLUDE 키워드를 사용할 수도 있습니다:
- ILIKE — 지정된 패턴과 일치하는 컬럼 이름을 필터링합니다. 패턴은 하나만 허용됩니다.
- EXCLUDE — 지정된 컬럼(또는 컬럼들)과 일치하지 않는 컬럼 이름을 필터링합니다.
이 키워드들을 사용할 때 한정자는 유효합니다. 다음 예제는 mytable 테이블에서 패턴 col1%과 일치하는 모든 컬럼을 필터링하기 위해 ILIKE 키워드를 사용합니다. ILIKE와 EXCLUDE 키워드는 단일 함수 호출에서 결합할 수 없습니다. 이 함수에서 ILIKE와 EXCLUDE 키워드는 SELECT 목록이나 GROUP BY 절에서만 유효합니다. ILIKE와 EXCLUDE 키워드에 대한 자세한 내용은 SELECT의 "Parameters" 섹션을 참고하세요.
반환값 (Returns)
부호 있는 64비트 값을 NUMBER(19,0)으로 반환합니다.
HASH_AGG는 NULL 입력에 대해서도 절대 NULL을 반환하지 않습니다.
사용 참고 (Usage notes)
- HASH_AGG는 전체 테이블, 쿼리 결과 또는 윈도우에 대한 "지문(fingerprint)"을 계산합니다. 입력에 대한 어떤 변경이든 압도적인 확률로 HASH_AGG 결과에 영향을 줍니다. 이는 테이블 내용이나 쿼리 결과의 변경을 빠르게 감지하는 데 사용할 수 있습니다.
두 개의 서로 다른 입력 테이블이 HASH_AGG에 대해 같은 결과를 만들 가능성은 매우 낮지만 가능합니다. 같은 HASH_AGG 결과를 만드는 두 테이블이나 쿼리 결과가 정말로 같은 데이터를 포함하는지 확인해야 한다면, 데이터를 동등성 비교해야 합니다(예: MINUS 연산자 사용). 자세한 내용은 Set operators를 참고하세요.
- HASH_AGG는 순서에 민감하지 않습니다(즉 입력 테이블이나 쿼리 결과의 행 순서는 HASH_AGG 결과에 영향을 주지 않습니다). 그러나 입력 컬럼의 순서를 바꾸는 것은 결과를 바꿉니다.
- HASH_AGG는 HASH 함수를 사용해 개별 입력 행을 해시합니다. 이 함수의 두드러진 특징은 HASH_AGG로 이어집니다. 특히 HASH_AGG는 같게 비교되고 호환 타입을 가진 두 행이 같은 값으로 해시되는 것이 보장된다는 점에서 "안정적"입니다(즉 같은 방식으로 HASH_AGG 결과에 영향을 줍니다). 예를 들어 어떤 테이블의 일부인 컬럼의 scale과 precision을 바꿔도 그 테이블에 대한 HASH_AGG 결과는 바뀌지 않습니다. 자세한 내용은 HASH를 참고하세요.
- 대부분의 다른 집계 함수와 달리 HASH_AGG는 NULL 입력을 무시하지 않습니다(즉 NULL 입력은 HASH_AGG 결과에 영향을 줍니다).
- 집계 함수와 윈도우 함수 양쪽에서, 중복 행(중복된 전부 NULL 행 포함)은 결과에 영향을 줍니다. DISTINCT 키워드를 사용해 중복 행의 효과를 억제할 수 있습니다.
- 이 함수를 윈도우 함수로 호출하면 다음을 지원하지 않습니다:
- OVER 절 안의 ORDER BY 절.
- 명시적 윈도우 프레임.
콜레이션 세부 사항 (Collation details)
- 동일하지만 콜레이션 지정이 다른 두 문자열은 같은 해시 값을 가집니다. 즉 해시 값에는 콜레이션 지정이 아니라 문자열만이 영향을 줍니다.
- 다르지만 콜레이션에 따라 동등하게 비교되는 두 문자열은 다른 해시 값을 가질 수 있습니다. 예를 들어 구두점 비구분 콜레이션으로 동일한 두 문자열은 보통 다른 해시 값을 가지는데, 그 이유는 해시 값에 콜레이션 지정이 아니라 문자열만이 영향을 주기 때문입니다.
예제 (Examples)
이 예제는 NULL이 무시되지 않음을 보여줍니다:
SELECT HASH_AGG(NULL), HASH_AGG(NULL, NULL), HASH_AGG(NULL, NULL, NULL);
+----------------------+----------------------+----------------------------+
| HASH_AGG(NULL) | HASH_AGG(NULL, NULL) | HASH_AGG(NULL, NULL, NULL) |
|----------------------+----------------------+----------------------------|
| -5089618745711334219 | 2405106413361157177 | -5970411136727777524 |
+----------------------+----------------------+----------------------------+
이 예제는 빈 입력이 0으로 해시됨을 보여줍니다:
SELECT HASH_AGG(NULL) WHERE 0 = 1;
+----------------+
| HASH_AGG(NULL) |
|----------------|
| 0 |
+----------------+
HASH_AGG(*)를 사용해 모든 입력 컬럼을 편리하게 집계합니다:
SELECT HASH_AGG(*) FROM orders;
+---------------------+
| HASH_AGG(*) |
|---------------------|
| 1830986524994392080 |
+---------------------+
이 예제는 그룹화된 집계가 지원됨을 보여줍니다:
SELECT YEAR(o_orderdate), HASH_AGG(*)
FROM ORDERS GROUP BY 1 ORDER BY 1;
+-------------------+----------------------+
| YEAR(O_ORDERDATE) | HASH_AGG(*) |
|-------------------+----------------------|
| 1992 | 4367993187952496263 |
| 1993 | 7016955727568565995 |
| 1994 | -2863786208045652463 |
| 1995 | 1815619282444629659 |
| 1996 | -4747088155740927035 |
| 1997 | 7576942849071284554 |
| 1998 | 4299551551435117762 |
+-------------------+----------------------+
이 예제는 DISTINCT를 사용해 중복 행을 억제합니다(중복 행은 HASH_AGG 결과에 영향을 줍니다):
SELECT YEAR(o_orderdate), HASH_AGG(o_custkey, o_orderdate)
FROM orders GROUP BY 1 ORDER BY 1;
+-------------------+----------------------------------+
| YEAR(O_ORDERDATE) | HASH_AGG(O_CUSTKEY, O_ORDERDATE) |
|-------------------+----------------------------------|
| 1992 | 5686635209456450692 |
| 1993 | -6250299655507324093 |
| 1994 | 6630860688638434134 |
| 1995 | 6010861038251393829 |
| 1996 | -767358262659738284 |
| 1997 | 6531729365592695532 |
| 1998 | 2105989674377706522 |
+-------------------+----------------------------------+
SELECT YEAR(o_orderdate), HASH_AGG(DISTINCT o_custkey, o_orderdate)
FROM orders GROUP BY 1 ORDER BY 1;
+-------------------+-------------------------------------------+
| YEAR(O_ORDERDATE) | HASH_AGG(DISTINCT O_CUSTKEY, O_ORDERDATE) |
|-------------------+-------------------------------------------|
| 1992 | -8416988862307613925 |
| 1993 | 3646533426281691479 |
| 1994 | -7562910554240209297 |
| 1995 | 6413920023502140932 |
| 1996 | -3176203653000722750 |
| 1997 | 4811642075915950332 |
| 1998 | 1919999828838507836 |
+-------------------+-------------------------------------------+
이 예제는 상태가 'F'가 아닌 주문과 'P'가 아닌 주문을 가진 고객의 해당 집합이 각각 동일한 날의 수를 계산합니다:
SELECT COUNT(DISTINCT o_orderdate) FROM orders;
+-----------------------------+
| COUNT(DISTINCT O_ORDERDATE) |
|-----------------------------|
| 2406 |
+-----------------------------+
SELECT COUNT(o_orderdate)
FROM (SELECT o_orderdate, HASH_AGG(DISTINCT o_custkey)
FROM orders
WHERE o_orderstatus <> 'F'
GROUP BY 1
INTERSECT
SELECT o_orderdate, HASH_AGG(DISTINCT o_custkey)
FROM orders
WHERE o_orderstatus <> 'P'
GROUP BY 1);
+--------------------+
| COUNT(O_ORDERDATE) |
|--------------------|
| 1143 |
+--------------------+
이 쿼리는 해시 충돌의 가능성을 고려하지 않으므로 실제 일 수는 약간 더 낮을 수 있습니다.
더 알아보기 (Learn more)
- HASH — 단일 값 해시
- Set operators — 집합 연산자
- 집계 함수