[SQL] Histogram of Tweets

오늘은 DataLemur의 Histogram of Tweets 문제를 풀어보겠습니다.

 

문제

트위터의 트윗 데이터가 담긴 `tweets` 테이블이 있다. 각 유저가 2022년에 작성한 트윗 개수를 기준으로 유저를 그룹화하여 출력하는 SQL 쿼리를 작성하시오.

`tweets` 테이블 구조

Column Name	Type
tweet_id	integer
user_id	integer
msg	string
tweet_date	timestamp

출력

• `tweet_bucket`: 트윗 개수
• `users_num`: 해당 트윗 개수를 가진 유저 수

 

풀이 1: 단순하게 풀기

가장 먼저 떠오르는 방법은 서브쿼리를 이용하는 것이다. 단순하게 "2022년 데이터 필터링 -> 유저별 개수 세기 -> 그 개수를 다시 그룹화"하는 방식을 이용하는 것으로, 아래와 같이 작성해볼 수 있다.

SELECT tweet_count AS tweet_bucket, COUNT(*) AS users_num
FROM (
  SELECT user_id, COUNT(*) AS tweet_count
  FROM tweets
  WHERE EXTRACT(YEAR FROM tweet_date) = 2022
  GROUP BY user_id
) t
GROUP BY tweet_count
ORDER BY tweet_count;

 

원하는 결과는 얻을 수 있지만, 다음과 같은 두 가지 단점이 있다.

 

첫째, 쿼리의 논리 흐름이 한 줄로 이어져 있어 가독성과 디버깅 측면에서 아쉽다. 로직을 단계별로 분리하지 않으면, 추후 조건이 추가되거나 집계 방식이 바뀔 때 쿼리를 수정하고 유지보수하는 데 어려움이 생길 수 있다.

 

둘째, 날짜를 필터링할 때 `EXTRACT(YEAR FROM tweet_date)`처럼 컬럼에 함수를 적용하면 인덱스를 제대로 활용하기 어렵다. 인덱스가 존재하더라도, 컬럼 값에 변환 함수가 적용되면 데이터베이스는 인덱스를 이용해 조건을 빠르게 판별하지 못하고 전체 테이블을 스캔하는 방식을 선택할 가능성이 높다. 작은 테이블에서는 문제가 되지 않지만, 데이터가 수백만 건 이상으로 커질 경우 풀 스캔이 발생하여 성능 병목으로 이어질 수 있다.

 

실제로 쿼리 플랜을 살펴보면 아래와 같이 나타난다.

QUERY PLAN
GroupAggregate (cost=1.11..1.13 rows=1 width=16)
  Group Key: t.tweet_count
  -> Sort (cost=1.11..1.11 rows=1 width=8)
       Sort Key: t.tweet_count
       -> Subquery Scan on t (cost=1.08..1.10 rows=1 width=8)
            -> HashAggregate (cost=1.08..1.09 rows=1 width=12)
                 Group Key: tweets_16.user_id
                 -> Seq Scan on tweets_16 (cost=0.00..1.07 rows=1 width=4)
                      Filter: (EXTRACT(year FROM tweet_date) = '2022'::numeric)

 

플랜의 마지막 부분을 보면 Seq Scan on tweets_16과 Filter: (EXTRACT(year FROM tweet_date) = '2022')라는 구문이 있다. 이는 데이터베이스가 테이블 전체를 처음부터 끝까지 순차적으로 읽으며 각 행마다 연도를 계산하고 비교한다는 의미다. 즉, 인덱스가 존재하더라도 이 조건에서는 활용되지 않는다.

 

풀이 2: 개선하기

첫번째 방식은 가독성과 성능 면에서 아쉬운 점이 있었다. 이를 개선하기 위해 다음 두 가지 전략을 사용할 수 있다.

 

첫째, CTE(Common Table Expression)를 활용해 로직을 단계별로 분리한다. "유저별 트윗 수 계산하는 과정"과 "그 결과를 그룹화하여 히스토그램을 만드는 부분"을 분리하면, 로직이 훨씬 명확해진다. 또한, 나중에 필터 조건이 추가되거나 집계 방식이 바뀌더라도 수정이 쉽고, 쿼리 자체의 가독성도 높아진다.

 

둘째, 날짜 필터링을 할 때 EXTRACT() 함수 대신 범위 조건을 사용한다. 원본 컬럼에 직접 조건을 걸면 데이터베이스가 인덱스를 효율적으로 활용할 수 있어, 대용량 데이터 환경에서도 성능 저하를 줄일 수 있다.

WITH tweets_count_list AS (
    SELECT
        user_id,
        COUNT(*) AS tweet_count
    FROM tweets
    WHERE tweet_date >= '2022-01-01'
      AND tweet_date < '2023-01-01'
    GROUP BY user_id
)

SELECT
    tweet_count AS tweet_bucket,
    COUNT(*) AS users_num
FROM tweets_count_list
GROUP BY tweet_bucket
ORDER BY tweet_bucket;