[SQL] 빅데이터 집계시 높은 카디널리티 해결하기

최근 빅데이터를 지탱하는 기술이라는 책을 정말 재밌게 읽고 있다.
해당 책에서 나왔던 쿼리를 기록하고 싶어서 작성한다.

흔히 데이터 마트를 구축할 때 특정 컬럼, 데이터를 기준으로 집계를 하는 경우가 많다. 하지만 이 기준 데이터의 카디널리티가 매우 크다면 문제가 된다.

'글에 포함되는 단어별 횟수 카운트 하기'라는 목표를 설정해 보자. 우리는 이 정보를 가지고 시각화를 하려고 한다.

hi - 12002319
hello - 1293129419

위와 같이 각 단어가 몇번 나왔는지 세는 것이 목표이다.

그런데 생각을 해보자. 위와 같은 집계결과를 만들면, 1번만 등장한 단어도 모두 집계 되게 된다.

즉 카디널리티(단어 종류)는 매우 커지는데에 비해 의미있는 정보의 비율이 줄어든다.

또 데이터 마트의 크기가 의미없이 매우 커지게 될 것이다. (데이터의 크기가 매우 커질 것이다.)

어떻게 처리하는게 좋을까?

hi - 12002319
hello - 1293129419
...
COUNT=4 - 420
COUNT=1 - 33993224

이렇게 처리하면 어떨까? 그니까 1000번 이하 등장한 단어에 대해서는 COUNT=x로 묶어서 처리를 하는 것이다. 이렇게 처리하면, 시각화 대시보드를 구성할 때 '아 1번만 등장한 단어들이 엄청 많구나..'라는 정보로 간추려 질 것이다.

이를 어떻게 구성할까?

우선 간추리지 않은 쿼리를 짜보자

SELECT word, COUNT(*) count
FROM tweet
GROUP BY 1

이렇게 하면 맨 처음의 결과가 나온다. 의미없는 결과로 인해 데이터마트의 크기가 매우 커질 것이다.

SELECT t.category word, SUM(t.count)
FROM (
	SELECT 
    	word,
        COUNT(*) count,
        IF(count > 1000, word, CONCAT("COUNT=",count)) category
    FROM tweet
    GROUP BY 1
)
GROUP BY 1

그렇다 IF(조건, 참일때 값, 거짓일때 값) 을 사용하면, 조건문을 사용할 수 있는데 이 값을 기준으로 그룹바이를 한번 더 하면, 쓸모 없는 값들을 뭉개버릴 수 있다.

결과는 다음과 같을 것이다.

hi - 12002319
hello - 1293129419
...
COUNT=4 - 420
COUNT=1 - 33993224