15_SQL SubQuery 활용 실습

1. 인트로

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

---

2. 실습 DB 준비

import sqlite3
import pandas as pd

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

# 사용자 테이블
cur.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
        name     TEXT    NOT NULL,
        plan     TEXT    DEFAULT 'basic',
        join_dt  TEXT    DEFAULT (date('now'))
    )
""")

# 콘텐츠 테이블
cur.execute("""
    CREATE TABLE IF NOT EXISTS contents (
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
        title    TEXT    NOT NULL,
        genre    TEXT,
        rating   REAL
    )
""")

# 시청 기록 테이블
cur.execute("""
    CREATE TABLE IF NOT EXISTS watches (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id    INTEGER,
        content_id INTEGER,
        watch_dt   TEXT DEFAULT (date('now')),
        FOREIGN KEY (user_id)    REFERENCES users(id),
        FOREIGN KEY (content_id) REFERENCES contents(id)
    )
""")

conn.commit()

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

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

watches_data = [
    (1,1),(1,2),(1,3),(1,5),
    (2,1),(2,4),
    (3,1),(3,2),(3,3),(3,4),(3,5),(3,6),
    (4,2),
    (5,1),(5,3),
    (6,1),(6,2),(6,5),(6,6),
]
cur.executemany("INSERT INTO watches (user_id,content_id) VALUES (?,?)", watches_data)

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

---

3. WHERE 절에서의 SubQuery 활용법

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

df = pd.read_sql("""
    SELECT title, rating
    FROM contents
    WHERE rating > (SELECT AVG(rating) FROM contents)
    ORDER BY rating DESC
""", conn)
print("=== 평균 이상 평점 콘텐츠 ===")
print(df)

코드 설명:

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

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

cur.execute("""
    SELECT title, rating
    FROM contents
    WHERE id = (
        SELECT content_id
        FROM watches
        GROUP BY content_id
        ORDER BY COUNT(*) DESC
        LIMIT 1
    )
""")
top = cur.fetchone()
print(f"최다 시청 콘텐츠: {top[0]} (평점: {top[1]})")

코드 설명:

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

3.3. IN + SubQuery 조합

# premium 사용자가 시청한 콘텐츠 목록
df = pd.read_sql("""
    SELECT DISTINCT c.title, c.genre
    FROM contents AS c
    WHERE c.id IN (
        SELECT w.content_id
        FROM watches AS w
        INNER JOIN users AS u ON w.user_id = u.id
        WHERE u.plan = 'premium'
    )
    ORDER BY c.title
""", conn)
print("=== 프리미엄 사용자 시청 콘텐츠 ===")
print(df)

코드 설명:

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

---

4. FROM 절에서의 SubQuery 활용법

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

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

df = pd.read_sql("""
    SELECT name, plan, watch_cnt
    FROM (
        SELECT u.name, u.plan, COUNT(w.id) AS watch_cnt
        FROM users AS u
        LEFT JOIN watches AS w ON u.id = w.user_id
        GROUP BY u.id, u.name, u.plan
    ) AS user_stats
    WHERE watch_cnt >= 3
    ORDER BY watch_cnt DESC
""", conn)
print("=== 시청 3개 이상 사용자 ===")
print(df)

코드 설명:

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

---

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

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

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

# 각 장르의 평균보다 높은 평점의 콘텐츠
df = pd.read_sql("""
    SELECT c1.title, c1.genre, c1.rating
    FROM contents AS c1
    WHERE c1.rating > (
        SELECT AVG(c2.rating)
        FROM contents AS c2
        WHERE c2.genre = c1.genre
    )
    ORDER BY c1.genre, c1.rating DESC
""", conn)
print("=== 장르 내 평균 초과 콘텐츠 ===")
print(df)

코드 설명:

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

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

# 시청 기록이 있는 사용자만 조회
df = pd.read_sql("""
    SELECT u.name, u.plan
    FROM users AS u
    WHERE EXISTS (
        SELECT 1
        FROM watches AS w
        WHERE w.user_id = u.id
    )
""", conn)
print("=== 시청 기록 있는 사용자 ===")
print(df)

코드 설명:

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

---

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

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

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

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

코드 설명:

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

---

7. 문제풀기