PostgreSQL로 YouTube 조회수 성장률 계산하기 (스냅샷 비교 방식 실전 예제)

6월 20, 2026

YouTube 쇼츠 트렌드를 분석하면서 깨달은 게 있다. 조회수 자체는 별로 의미가 없다는 것이다. 조회수 100만짜리 영상도 6개월 전에 터진 거라면 지금 트렌드가 아니다. 반면 조회수 5,000인 영상이 어제 올라와서 24시간 만에 5,000이 찍혔다면 지금 뜨는 영상이다.

그래서 조회수 대신 성장률을 기준으로 트렌드를 판단하는 구조를 만들었다. 현재 UGREEN DXP4800PLUS NAS에서 운영 중인 시스템에는 영상 약 1,100개, 스냅샷 약 74,000개가 쌓여 있다. 이 글은 그 구조를 만든 이유와 실제 PostgreSQL 쿼리를 정리한다.

왜 YouTube 조회수 스냅샷 방식이 필요한가

처음에는 단순하게 접근했다. 영상 정보를 수집할 때 현재 조회수를 같이 저장하면 된다고 생각했다.

문제는 바로 드러났다.

오늘 조회수: 12,000
어제 조회수: 알 수 없음 (덮어쓰여서 사라짐)

현재 값만 저장하면 변화량을 계산할 수 없다. 성장률은 이전 값과 현재 값의 차이에서 나온다. 그래서 조회수를 덮어쓰는 대신 시간마다 찍어서 쌓는 스냅샷 방식을 선택했다.

PostgreSQL 테이블 구조 설계

핵심 테이블은 두 개다.

-- 영상 기본 정보
CREATE TABLE videos (
    id SERIAL PRIMARY KEY,
    video_id VARCHAR UNIQUE NOT NULL,
    title VARCHAR,
    channel_name VARCHAR,
    tags TEXT,
    content_cluster VARCHAR,
    format_type VARCHAR,
    collected_at TIMESTAMP DEFAULT NOW()
);

-- 시간별 조회수 스냅샷
CREATE TABLE video_snapshots (
    id SERIAL PRIMARY KEY,
    video_id VARCHAR REFERENCES videos(video_id),
    view_count INTEGER,
    like_count INTEGER,
    comment_count INTEGER,
    recorded_at TIMESTAMP DEFAULT NOW()
);

videos는 영상 메타데이터, video_snapshots는 시간별 수치를 쌓는다. 현재 실제 운영 수치:

SELECT COUNT(*) FROM videos;
-- 결과: 676

SELECT COUNT(*) FROM video_snapshots;
-- 결과: 38,134

PostgreSQL로 6시간·24시간 성장률 계산하기

성장률 계산의 핵심은 현재 스냅샷과 N시간 전 스냅샷을 비교하는 것이다.

-- 6시간 성장률
SELECT
    v.video_id,
    v.title,
    latest.view_count AS current_views,
    older.view_count AS old_views,
    ROUND(
        (latest.view_count - older.view_count)::numeric
        / NULLIF(older.view_count, 0) * 100, 2
    ) AS growth_6h
FROM videos v
JOIN LATERAL (
    SELECT view_count FROM video_snapshots
    WHERE video_id = v.video_id
    ORDER BY recorded_at DESC
    LIMIT 1
) latest ON true
JOIN LATERAL (
    SELECT view_count FROM video_snapshots
    WHERE video_id = v.video_id
      AND recorded_at <= NOW() - INTERVAL '6 hours'
    ORDER BY recorded_at DESC
    LIMIT 1
) older ON true
WHERE older.view_count > 100
ORDER BY growth_6h DESC;

NULLIF(older.view_count, 0)는 0으로 나누는 오류를 방지한다. WHERE older.view_count > 100은 신규 영상 왜곡을 막는다.

실제 운영 중 발견한 성장률 계산 문제

문제 1: old_views = 0인 경우

신규 수집 영상은 이전 스냅샷이 없다. 조회수 0에서 1,000이 되면 성장률이 무한대로 계산된다. WHERE older.view_count > 100 조건으로 걸러냈다.

문제 2: 500% 클램프 제거

처음에는 성장률 상한을 500%로 제한했다. 실제 데이터를 보니 쇼츠는 하루 만에 수천 퍼센트 성장하는 경우가 흔했다. 클램프를 걸면 진짜 트렌드 영상이 걸러지는 문제가 생겨서 제거했다. 현재는 raw 값을 그대로 저장하고, 점수 계산 시 min(growth, 100)을 적용한다.

문제 3: 조회수 적은 영상 왜곡

조회수 10짜리 영상이 조회수 20이 되면 성장률 100%다. 조회수 100만짜리 영상이 10% 성장하는 것보다 숫자가 크게 나온다. 최소 조회수 기준(100 이상)을 적용해서 노이즈를 줄였다.

FastAPI API 엔드포인트 연동

# 상위 성장 영상 조회
@app.get("/api/trends")
async def get_trends(db: AsyncSession = Depends(get_db)):
    result = await db.execute(
        text("""
            SELECT v.video_id, v.title, v.channel_name,
                   v.content_cluster, v.format_type,
                   s.view_count,
                   s.growth_6h, s.growth_24h
            FROM videos v
            JOIN video_snapshots s ON v.video_id = s.video_id
            WHERE s.recorded_at = (
                SELECT MAX(recorded_at) FROM video_snapshots
            )
            AND s.growth_6h IS NOT NULL
            ORDER BY s.growth_6h DESC
            LIMIT 50
        """)
    )
    rows = result.mappings().all()
    return [dict(row) for row in rows]

실제 운영 결과

수집 영상: 약 1,100개
저장 스냅샷: 약 74,000개
성장률 계산 성공률: 95%이상
트렌드 클러스터: 10종 (IT_DEVICE, AI/자동화, 연예/엔터 등)
포맷 분류: 6종 (TIP, COMPARISON, NEWS, HOOK, STORY, RANKING)

성공률 95.1%는 이전 스냅샷이 없는 신규 영상(4.9%)을 제외한 수치다. 30분 주기로 수집하고 있어서 하루 48번 스냅샷이 쌓인다.

마무리

조회수는 과거의 누적이다. 성장률은 지금 이 순간의 속도다. 트렌드를 잡으려면 조회수가 아닌 성장률을 봐야 한다.

PostgreSQL의 LATERAL JOIN은 이런 시계열 비교 쿼리에 특히 유용하다. 각 영상별로 "가장 최근 스냅샷"과 "N시간 전 스냅샷"을 동시에 가져오는 작업을 깔끔하게 처리해준다.

이 블로그 검색

AI 자동화 연구소