Snowflake SQL Tips

이번 블로그에서는 Snowflake에서 제공하는 유용한 SQL 기능과 함수에 대해서 간략하게 살펴보고자 합니다.

1. Exclude 키워드

컬럼 수가 많은 경우 일부 컬럼을 제외한 다른 컬럼의 모든 값을 조회하고자 하는 경우 조회 컬럼을 SQL 문에 일일이 타이핑하는 번거로움이 존재했습니다.

예) customer 테이블에 일부 컬럼을 제외하고 조회하고자 하는 요구사항이 존재하는 경우:

select * from customer limit 1000;

• Customer 테이블에서 1) C_CUSTOMER_SK 컬럼을 제외한 모든 컬럼의 값을 조회하고 싶은 경우, 2) C_CUSTOMER_SK와 C_CUSTOMER_ID 컬럼 값들을 제한 모든 컬럼의 값을 조회하고 싶은 경우

-- C_CUSTOMER_SK 컬럼을 제외한 CUSTOMER 테이블의 모든 컬럼을 조회하고 싶은 경우
select * EXCLUDE c_customer_sk from customer limit 1000;

-- C_CUSTOMER_SK 및 C_CUSTOMER_ID 컬럼 들을 제외한 CUSTOMER 테이블의 모든 컬럼을 조회하고 싶은 경우
select * EXCLUDE (c_customer_sk, c_customer_id) from customer limit 1000;

2. 자로 윙클러 문자열 유사도 알고리즘(Jaro Winkler)

두 입력 문자열 사이의 Jaro-Winkler 유사성을 계산합니다. 이 함수는 0과 100 사이의 정수를 반환하는데, 여기서 0은 유사성이 없음을 나타내고 100은 정확히 일치함을 나타냅니다.

-- 두 개의 컬럼에 저장된 String 값의 유사도 평가
select string_1 as input_1, string_2 as input_2, jarowinkler_similarity(string_1, string_2) from string_similarity;

3. SOUNDEX:

• 입력 문자열의 음성 표현을 포함하는 문자열을 반환하는 함수로써, 두 문자열의 유사 발음 여부를 확인하는 위한 용도로 활용
• 예: Customer 테이블에 ’S’로 시작되는 다양한 성이 존재하는 경우, 쓰미스와 유사한 발음의 성을 가진 문자열 반환

-- Customer 테이블에서 'S' 문자열로 시작되는 성을 갖는 데이터 확인
select C_LAST_NAME, C_FIRST_NAME FROM customer where c_last_name like 'S%';

-- Customer 테이블에서 "SMYTHE" 발음과 유사한 Last_name을 갖는 데이터 추출
SELECT C_LAST_NAME, C_FIRST_NAME FROM   customer
    WHERE  SOUNDEX(C_LAST_NAME) = SOUNDEX('SMYTHE'); 

• SOUNDEX_P123: // 첫 번째 문자와 두 번째 문자가 동일한 Soundex 코드 번호를 사용하는 경우를 제외하고 SOUNDEX 기능과 유사

SELECT C_LAST_NAME, C_FIRST_NAME FROM   customer
    WHERE  SOUNDEX_P123(C_LAST_NAME) = SOUNDEX_P123('SMYTHE'); 

아래 결과에서 확인해 보면, SOUNDEX() 함수의 결과와 다르게 —’ Schmidt’ 성을 갖는 사용자는 SOUNDEX_P123() 함수 결과에서 추출되지 않음을 확인

4. KURTOSIS()

: 첨도 — 확률 분포의 뾰족한 정도를 나타내는 척도: 데이터 들이 얼마만큼 평균에 몰려있는지 여부를 확인.

• 함수의 값이 0보다 크면 정규분포보다 긴 꼬리를 갖고, 분포가 중앙부분에 덜 집중되어 뾰족한 모양을 가짐을 의미
• 함수의 값이 0보다 작으면 정규분포보다 짧은 꼬리를 갖고 분포가 중앙 부분에 더 집중되어 중앙 부분이 보다 완만한 모양을 가짐을 의미

예) 다음 데이터-셋을 샘플로 활용

select kurtosis(k), kurtosis(v), kurtosis(v2) from aggr_test;

5. skew()

데이터 분포의 왜도를 반환 — 즉, 데이터의 정규분포 여부를 확인하고 범위 내 이상값 존재 여부를 확인하는 지표로 활용 비대칭도를 나타내는 통계량

• 오른쪽만 긴 꼬리를 가지는 경우 양수
• 왼쪽으로 긴 꼬리를 가지는 경우 음수

예) KURTOSIS()에서 사용한 동일 데이터로 테스트

select skew(k), skew(v), skew(v2) from aggr_test;

6. mode()

: 데이터의 값 들 중에 빈도수가 가장 높은 값을 반환

예) KURTOSIS()에서 사용한 동일 데이터로 테스트

select mode(k), mode(v), mode(v2) from aggr_test;

7. MEDIAN()

: 값의 중간 값은 반환

예) KURTOSIS()에서 사용한 동일 데이터로 테스트

select k, median(v) from aggr_test group by k order by k;

8. COVAR_POP

: 공분산 — 두 값의 모집단 모공분산을 반환 / 두 함수 모두 두 개 이상의 독립 변수들간의 관계를 분석하는 용도로 활용

• 공분산의 값이 양수이며 두 변수가 같은 방향으로 변화(증가나 감소)
• 공분산의 값이 음수이면 두 변수가 반대 방향으로 변화(하나가 증가하면 하나가 감소, 하나가 감소하면 하나가 증가)

예) KURTOSIS()에서 사용한 동일 데이터로 테스트

select k, covar_pop(v, v2) from aggr_test group by k;

9. COVAR_SAMP

: 두 값의 표본 공분산 값을 반환

예) KURTOSIS()에서 사용한 동일 데이터로 테스트

select k, covar_samp(v, v2) from aggr_test group by k;

10. CORR()

: 상관 계수: 두 변수에 대해서 한 변수의 변화 정도가 다른 변수에 미치는 영향도를 나타내는 지표. -1 ~ 1의 범위의 값으로 두 변수가 동일한 특성이 강하고 높을 수록 1로 두 변수가 상관 특성이 약할 수롤 -1의 값을 나타냄

예) KURTOSIS()에서 사용한 동일 데이터로 테스트

select k, corr(v, v2) from aggr_test group by k;

11. RANK() / DENSE_RANK()

• 다음 테스트-셋을 통해 테스트 진행

select ename, sal,
        rank() over (order by sal desc),
        dense_rank() over (order by sal desc)
    from salary;

12. 둘 이상 집합의 유사성 추정

MinHash: 두 개 이상의 데이터 세트에 대한 유사성 추정을 위한 함수 — MinHash는 두 데이터 세트간의 교집합 및 합집합을 계산하지 않고 집합 자체를 비교하기 때문에 더 효율적으로 유사성을 추정할 수 있음. 일반적으로 두 데이터 세트간의 유사성은 자카드 유사도/계수/지수(Jaccard Similarity/Coefficient/Index) — 내부적으로 교집합 및 합집합을 활용하여 데이터 세트의 유사성을 검출하기 때뭉네 상당한 리소스와 시간이 소요됨. 하지만 MinHas는 자카드 알고리즘에서 내부적으로 수행하는 합집합 및 교집합을 활용하기 않기 때문에 대용량 데이터-셋에 대한 유사성을 효율적으로 검출 할 수 있음.
1) MinHash: 입력 인수(K)의 MinHash 배열을 포함하는 MinHash 상태를 반환
2) MinHash_Combine: 두 개 이상의 입력 MinHash 상태를 단일 출력 MinHash 상태로 결합
3) Approximate_similarity(Approximate_jaccard_index): MinHash 상태를 기반으로 입력 세트의 유사성(Jaccard 인덱스) 추정치를 반환

• 테스트-셋

create or replace table mhtab1(c1 number,c2 double,c3 text,c4 date);
create or replace table mhtab2(c1 number,c2 double,c3 text,c4 date);

insert into mhtab1 values
    (1, 1.1, 'item 1', to_date('2016-11-30')),
    (2, 2.31, 'item 2', to_date('2016-11-30')),
    (3, 1.1, 'item 3', to_date('2016-11-29')),
    (4, 44.4, 'item 4', to_date('2016-11-30'));

insert into mhtab2 values
    (1, 1.1, 'item 1', to_date('2016-11-30')),
    (2, 2.31, 'item 2', to_date('2016-11-30')),
    (3, 1.1, 'item 3', to_date('2016-11-29')),
    (4, 44.4, 'item 4', to_date('2016-11-30')),
    (6, 34.23, 'item 6', to_date('2016-11-29'));

1. approximate_similarity():

select approximate_similarity(mh) from
    ((select minhash(100, *) as mh from mhtab1)
    union all
    (select minhash(100, *) as mh from mhtab2));

2. approximate_jaccard_index(): MinHash 상태를 기반으로 입력값의 유사성(Jaccard 인덱스) 추정치를 반환

select approximate_jaccard_index(mh) from
    ((select minhash(100, *) as mh from mhtab1)
    union all
    (select minhash(100, *) as mh from mhtab2));

13. 빈번한 값 추정

: Snowflake는 데이터-셋에서 가장 빈도가 많은 상위 N개의 데이터-셋을 효율적으로 계산하기 위한 Metwally, Agrawal 및 Abbadi 알고리즘을 활용한 개별 함수를 제공하고 있습니다.

with bth_years as (
    select approx_top_k(c_birth_year, 5, 1000) as year from customer
)
select value[0]::int as year, value[1]::int as frequency from bth_years, lateral flatten(year);