15 SQL SubQuery 활용 실습

1. 인트로

SubQuery(서브쿼리)는 SQL 안에 또 다른 SQL을 넣는 기술이다. 단일 SELECT로 처리하기 어려운 복잡한 조건을 하나의 쿼리로 해결할 수 있다. 이번 차시에서는 WHERE, FROM, SELECT 절에서의 서브쿼리 사용법을 모두 익힌다.

2. 실습 DB 준비

1
import sqlite3
2
import pandas as pd
3

4
conn = sqlite3.connect("streaming.db")
5
cur  = conn.cursor()
6

7
# 사용자 테이블
8
cur.execute("""
9
    CREATE TABLE IF NOT EXISTS users (
10
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
11
        name     TEXT    NOT NULL,
12
        plan     TEXT    DEFAULT 'basic',
13
        join_dt  TEXT    DEFAULT (date('now'))
14
    )
15
""")
16

17
# 콘텐츠 테이블
18
cur.execute("""
19
    CREATE TABLE IF NOT EXISTS contents (
20
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
21
        title    TEXT    NOT NULL,
22
        genre    TEXT,
23
        rating   REAL
24
    )
25
""")
26

27
# 시청 기록 테이블
28
cur.execute("""
29
    CREATE TABLE IF NOT EXISTS watches (
30
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
31
        user_id    INTEGER,
32
        content_id INTEGER,
33
        watch_dt   TEXT DEFAULT (date('now')),
34
        FOREIGN KEY (user_id)    REFERENCES users(id),
35
        FOREIGN KEY (content_id) REFERENCES contents(id)
36
    )
37
""")
38

39
conn.commit()
40

41
# 데이터 삽입
42
users_data = [
43
    ("김철수", "premium"), ("이영희", "basic"),
44
    ("박지민", "premium"), ("최수진", "basic"),
45
    ("홍길동", "standard"),("강민준", "premium"),
46
]
47
cur.executemany("INSERT INTO users (name,plan) VALUES (?,?)", users_data)
48

49
contents_data = [
50
    ("오징어게임",    "드라마",  9.5),
51
    ("더글로리",     "드라마",  9.2),
52
    ("이상한변호사우영우", "드라마", 9.0),
53
    ("정이",        "SF",    7.8),
54
    ("수리남",       "스릴러", 8.5),
55
    ("D.P.",       "드라마",  8.8),
56
]
57
cur.executemany("INSERT INTO contents (title,genre,rating) VALUES (?,?,?)", contents_data)
58

59
watches_data = [
60
    (1,1),(1,2),(1,3),(1,5),
61
    (2,1),(2,4),
62
    (3,1),(3,2),(3,3),(3,4),(3,5),(3,6),
63
    (4,2),
64
    (5,1),(5,3),
65
    (6,1),(6,2),(6,5),(6,6),
66
]
67
cur.executemany("INSERT INTO watches (user_id,content_id) VALUES (?,?)", watches_data)
68

69
conn.commit()
70
print("스트리밍 DB 준비 완료")

3. WHERE 절에서의 SubQuery 활용법

3.1. 평균보다 높은 평점의 콘텐츠

1
df = pd.read_sql("""
2
    SELECT title, rating
3
    FROM contents
4
    WHERE rating > (SELECT AVG(rating) FROM contents)
5
    ORDER BY rating DESC
6
""", conn)
7
print("=== 평균 이상 평점 콘텐츠 ===")
8
print(df)

코드 설명:

4행: (SELECT AVG(rating) FROM contents) — 전체 콘텐츠의 평균 평점을 구한다.
바깥 WHERE에서 그 값보다 높은 콘텐츠만 필터링한다.

3.2. 가장 많이 시청된 콘텐츠 조회

1
cur.execute("""
2
    SELECT title, rating
3
    FROM contents
4
    WHERE id = (
5
        SELECT content_id
6
        FROM watches
7
        GROUP BY content_id
8
        ORDER BY COUNT(*) DESC
9
        LIMIT 1
10
    )
11
""")
12
top = cur.fetchone()
13
print(f"최다 시청 콘텐츠: {top[0]} (평점: {top[1]})")

코드 설명:

5~9행: 서브쿼리에서 watches 테이블을 content_id로 그룹화하여 가장 많이 시청된 content_id를 구한다.
바깥 쿼리에서 그 id와 일치하는 콘텐츠 정보를 가져온다.

3.3. IN + SubQuery 조합

1
# premium 사용자가 시청한 콘텐츠 목록
2
df = pd.read_sql("""
3
    SELECT DISTINCT c.title, c.genre
4
    FROM contents AS c
5
    WHERE c.id IN (
6
        SELECT w.content_id
7
        FROM watches AS w
8
        INNER JOIN users AS u ON w.user_id = u.id
9
        WHERE u.plan = 'premium'
10
    )
11
    ORDER BY c.title
12
""", conn)
13
print("=== 프리미엄 사용자 시청 콘텐츠 ===")
14
print(df)

코드 설명:

5~10행: 서브쿼리에서 premium 사용자가 시청한 content_id 목록을 구한다.
3행: DISTINCT — 중복 없이 고유한 콘텐츠만 가져온다.
바깥 WHERE IN에서 그 목록에 포함된 콘텐츠만 가져온다.

4. FROM 절에서의 SubQuery 활용법

FROM 절에 서브쿼리를 넣으면 결과를 임시 테이블(Derived Table(더라이브드 테이블))처럼 사용할 수 있다.

4.1. 사용자별 시청 수 집계 후 필터링

1
df = pd.read_sql("""
2
    SELECT name, plan, watch_cnt
3
    FROM (
4
        SELECT u.name, u.plan, COUNT(w.id) AS watch_cnt
5
        FROM users AS u
6
        LEFT JOIN watches AS w ON u.id = w.user_id
7
        GROUP BY u.id, u.name, u.plan
8
    ) AS user_stats
9
    WHERE watch_cnt >= 3
10
    ORDER BY watch_cnt DESC
11
""", conn)
12
print("=== 시청 3개 이상 사용자 ===")
13
print(df)

코드 설명:

3~8행: FROM 절 안의 서브쿼리로 사용자별 시청 수를 집계한다.
8행: AS user_stats — 서브쿼리 결과에 이름을 붙인다. (필수)
9행: 집계 결과인 watch_cnt로 필터링한다. HAVING 대신 WHERE를 사용할 수 있다.

5. SubQuery(서브쿼리) 심화 실습

5.1. 상관 서브쿼리(Correlated SubQuery(코릴레이티드 서브쿼리))

바깥 쿼리의 각 행마다 서브쿼리가 실행되는 패턴이다.

1
# 각 장르의 평균보다 높은 평점의 콘텐츠
2
df = pd.read_sql("""
3
    SELECT c1.title, c1.genre, c1.rating
4
    FROM contents AS c1
5
    WHERE c1.rating > (
6
        SELECT AVG(c2.rating)
7
        FROM contents AS c2
8
        WHERE c2.genre = c1.genre
9
    )
10
    ORDER BY c1.genre, c1.rating DESC
11
""", conn)
12
print("=== 장르 내 평균 초과 콘텐츠 ===")
13
print(df)

코드 설명:

5~8행: 서브쿼리 안에서 c2.genre = c1.genre로 바깥 쿼리의 행(c1)을 참조한다.
바깥 쿼리의 각 행(c1)마다 서브쿼리가 실행되어 해당 장르의 평균을 계산한다.
c1의 평점이 그 장르 평균보다 높은 경우만 결과에 포함한다.

5.2. EXISTS(이그지스츠)를 사용한 서브쿼리

1
# 시청 기록이 있는 사용자만 조회
2
df = pd.read_sql("""
3
    SELECT u.name, u.plan
4
    FROM users AS u
5
    WHERE EXISTS (
6
        SELECT 1
7
        FROM watches AS w
8
        WHERE w.user_id = u.id
9
    )
10
""", conn)
11
print("=== 시청 기록 있는 사용자 ===")
12
print(df)

코드 설명:

5행: EXISTS — 서브쿼리의 결과가 한 행 이상 존재하면 TRUE를 반환한다.
6행: SELECT 1 — 실제 값보다 존재 여부만 확인하므로 1을 사용한다. 성능상 효율적이다.
NOT EXISTS — 반대로 결과가 없는 경우에 TRUE를 반환한다.

6. 활용 Plus — 리액트 추천 시스템 연동

리액트 스트리밍 앱의 “내가 아직 안 본 콘텐츠 추천” 기능 API 예시이다.

1
def get_recommendations(user_id):
2
    # 사용자가 아직 시청하지 않은 콘텐츠 중 평점 상위 3개 추천
3
    cur.execute("""
4
        SELECT title, genre, rating
5
        FROM contents
6
        WHERE id NOT IN (
7
            SELECT content_id
8
            FROM watches
9
            WHERE user_id = ?
10
        )
11
        ORDER BY rating DESC
12
        LIMIT 3
13
    """, (user_id,))
14
    rows = cur.fetchall()
15
    return [{"title": r[0], "genre": r[1], "rating": r[2]} for r in rows]
16

17
recs = get_recommendations(user_id=2)
18
print("=== user_id=2 추천 콘텐츠 ===")
19
for r in recs:
20
    print(r)

코드 설명:

6~9행: 서브쿼리로 user_id=2가 이미 시청한 content_id 목록을 구한다.
5행: NOT IN — 그 목록에 없는 콘텐츠만 가져온다. 즉, 아직 안 본 콘텐츠이다.
11행: LIMIT 3 — 평점 높은 순으로 3개만 추천한다.

7. 문제풀기

WHERE 절 서브쿼리: 조건값으로 사용된다. 단일 값 또는 값의 목록을 반환한다. FROM 절 서브쿼리: 임시 테이블처럼 사용된다. 반드시 별칭(AS)을 붙여야 한다.

다른 테이블의 조건을 만족하는 id 목록을 구하고, 그 목록 안의 데이터를 찾을 때 유용하다. 예: “특정 조건의 사용자가 시청한 콘텐츠 목록 조회”

IN: 서브쿼리가 반환하는 값의 목록과 비교한다. EXISTS: 서브쿼리의 결과가 존재하는지(한 행이라도 있는지)만 확인한다. 대용량 데이터에서는 EXISTS가 더 빠른 경우가 많다.

일반 서브쿼리: 한 번만 실행되고 결과를 바깥 쿼리에 전달한다. 상관 서브쿼리: 바깥 쿼리의 각 행마다 서브쿼리가 한 번씩 실행된다. 바깥 쿼리의 컬럼을 서브쿼리 안에서 참조한다.