5.1Common Table Expressions

아래의 데이터를 얻기 위한 쿼리를 작성해보자.

Show the username of users who were tagged in a caption or photo before January 7th, 2010. Also show the date they were tagged.

사용되는 테이블은 다음과 같다:

• users(id, username)
• caption_tags(id, user_id, created_at)
• photo_tags(id, user_id, created_at)

방법 1: UNION 키워드를 활용한 서브쿼리로 해결하기

UNION 키워드를 사용한 서브쿼리로 해결할 수 있다.

sql
SELECT users.username, tags.created_at 
FROM USERS
JOIN (
  SELECT user_id, created_at FROM caption_tags
  UNION ALL
  SELECT user_id, created_at FROM photo_tags
) AS tags ON tags.user_id = users.id
WHERE tags.created_at < '2010-01-07';

하지만, JOIN문 안에 상당히 긴 서브쿼리가 있어 가독성이 다소 떨어진다.

방법 2: CTE로 해결하기

Common Table Expression(CTE)는 메인 쿼리 위에 WITH 키워드로 테이블을 정의하는 것이다. 쿼리를 더 구조적으로 작성할 수 있어 가독성이 향상된다.

sql
WITH tags AS(
	SELECT user_id, created_at FROM caption_tags
  	UNION ALL
  	SELECT user_id, created_at FROM photo_tags
)

SELECT users.username, tags.created_at 
FROM USERS
JOIN tags ON tags.user_id = users.id
WHERE tags.created_at < '2010-01-07';

이렇게 기본적인 방식으로 CTE를 사용하면 쿼리 성능에는 개선이 없으나, 유지보수 및 협업 시 코드의 명료성이 높아진다.

재귀 CTE(Recursive Common Table Expressions)

재귀적으로 CTE를 활용하는 것은 앞서 살펴본 기본 방식과는 상당히 다르다. 재귀적인 CTE는 트리나 그래프 타입의 데이터 구조가 있을 때 유용하다. 또, 기본 방식과는 달리 반드시 UNION 키워드를 써야 한다.

예시: 카운트다운 테이블

예시로, 내림차순으로 행 데이터를 만드는 countdown 테이블을 만들고 데이터를 조회해보자.

sql
WITH RECURSIVE countdown(val) AS (
  SELECT 3 AS val -- Initial, Non-recursive query
  UNION
  SELECT val -1 FROM countdown WHERE val >1 -- Recursive query
)

SELECT *
FROM countdown;

재귀적 CTE는 이렇게 작동한다고 이해하면 편하다:

1. 먼저, Results 테이블과 Working 테이블을 만든다.
2. non-recursive 쿼리를 실행하고, 결과를 results 테이블과 working 테이블에 넣는다.
3. countdown이라는 테이블 이름을 working 테이블을 참조하는 테이블로 생각하고, working 테이블에서 recursive 쿼리를 실행한다.
4. recursive 쿼리가 만들어내는 결과를 results 테이블에 추가하고 다시 recursion 쿼리를 실행한다.
5. recursive 쿼리가 결과를 더 이상 반환하지 않으면 재귀를 멈춘다.

실제 활용 예: 유저 추천 시스템

개념을 이해했으니, 좀 더 현실적인 예시(우리의 Instagram 클로닝 프로젝트)로 Recursive CTE를 알아보자.

인스타그램엔 유저 추천 기능이 있는데, 주로 내가 어떤 유저들을 알면 그 유저들이 아는 또 다른 유저들을 알것이라고 생각해 추천하는 기능이다. 즉, 예를 들어 내가 The Rock과 Kevin Hart를 알고(팔로우하고), The Rock은 Justin Beiber를 알고 Kevin Hart는 Jennifer Lopez와 Snoop Dogg를 알면 내게 Justin Beiber와 Jennifer Lopez, Snoop Dogg를 추천하는 것이다.

하지만 여기서 끝이 아니다. Justin Beiber에는 관심 없어도 Justin Beiber가 알고 있는 Miley Cyrus를 추천해줄 수 있고, 마찬가지 원리로 Jennifer Lopez와 Snoop Dogg에게 관심이 없어도 그들이 알고 있는 Justin Timberlake와 Selena Gomez는 추천해줄 수 있다. 즉, 재귀적으로 유저들의 팔로우 관계를 검색해서 추천해줘야 한다.

그렇다면 이런 추천 기능 쿼리를 작성해보자.

sql
WITH RECURSIVE suggestions (leader_id, follower_id, depth) AS (
    SELECT leader_id, follower_id, 1 AS depth
    FROM followers
    WHERE follower_id = 1000 -- id = 1000인 사람에게 추천할 사람을 조회하는 상황을 가정
  UNION
    SELECT followers.leader_id, followers.follower_id, depth + 1
    FROM followers
    JOIN suggestions ON suggestions.leader_id = followers.follower_id
    WHERE depth < 3
)
SELECT DISTINCT users.id, users.username
FROM suggestions
JOIN users ON users.id = suggestions.leader_id
WHERE depth > 1
LIMIT 30;

이 쿼리가 어떻게 작동하는지 이해해보자.

1. 먼저, Results 테이블과 Working 테이블을 만든다. 각 테이블에 들어가는 데이터는 leader_id, follower_id와 depth다.
2. non-recursive query는 follower_id = 1000의 leader_id와 follower_id 그리고 1을 선택한다. 이 값을 양 테이블에 넣는다.
3. suggestions 테이블을 working 테이블을 참조하는 테이블이라고 생각하고, recursive 쿼리를 실행한다. followers 테이블에서 suggestions.leader_id = followers.follower_id인 데이터를 뽑고, depth에 1을 더한다.
4. 아직 depth가 2이므로 한번 더 재귀 쿼리를 실행한다.
5. 위의 과정을 반복하면, depth가 3이 되어 재귀가 멈춘다. 그렇게 나온 결과를 반환한다.