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

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

그래서 조회수 대신 성장률을 기준으로 트렌드를 판단하는 구조를 만들었습니다.
사례 기록에는 UGREEN DXP4800PLUS NAS에 영상 약 1,100개와 스냅샷 약 74,000개를 저장한 것으로 나옵니다. 아래 COUNT 결과는 당시 사례 기록이며, 700개 쿼터 사례와의 집계 시점·범위는 명시되지 않아 수치를 직접 비교할 수 없습니다.
이 글은 그 구조를 만든 이유와 실제 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는 시간별 수치를 쌓습니다.
사례에서 기록한 COUNT 결과(수집 시점 미기록):

SELECT COUNT(*) FROM videos;
-- 결과: 1,095

SELECT COUNT(*) FROM video_snapshots;
-- 결과: 73,883

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("""
            WITH trends AS (
                SELECT v.video_id, v.title, v.channel_name,
                       v.content_cluster, v.format_type,
                       latest.view_count,
                       ROUND((latest.view_count - older6.view_count)::numeric
                             / NULLIF(older6.view_count, 0) * 100, 2) AS growth_6h,
                       ROUND((latest.view_count - older24.view_count)::numeric
                             / NULLIF(older24.view_count, 0) * 100, 2) AS growth_24h
                FROM videos v
                JOIN LATERAL (
                    SELECT view_count, recorded_at
                    FROM video_snapshots
                    WHERE video_id = v.video_id
                    ORDER BY recorded_at DESC
                    LIMIT 1
                ) latest ON true
                LEFT JOIN LATERAL (
                    SELECT view_count
                    FROM video_snapshots
                    WHERE video_id = v.video_id
                      AND recorded_at <= latest.recorded_at - INTERVAL '6 hours'
                    ORDER BY recorded_at DESC
                    LIMIT 1
                ) older6 ON true
                LEFT JOIN LATERAL (
                    SELECT view_count
                    FROM video_snapshots
                    WHERE video_id = v.video_id
                      AND recorded_at <= latest.recorded_at - INTERVAL '24 hours'
                    ORDER BY recorded_at DESC
                    LIMIT 1
                ) older24 ON true
            )
            SELECT *
            FROM trends
            WHERE growth_6h IS NOT NULL
            ORDER BY growth_6h DESC
            LIMIT 50
        """)
    )
    rows = result.mappings().all()
    return [dict(row) for row in rows]

검증 범위: 이 쿼리는 각 영상의 최신 스냅샷 시각을 기준으로 6시간·24시간 이전의 가장 가까운 스냅샷을 찾도록 구성했습니다. 전체 테이블의 단일 MAX 시각을 사용하지 않으며, growth 컬럼이 테이블에 미리 저장돼 있다고 가정하지 않습니다.

사례에 기록된 운영 결과

PostgreSQL 쿼리로 YouTube 조회수 스냅샷을 비교한 실행 결과 화면

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

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

스냅샷 비교 판단 기준

판단 기준은 같은 영상의 최신 스냅샷과 기준 시각 이전의 기록을 짝짓는 것입니다. 이전 기록이 없거나 이전 조회수가 0이면 성장률을 계산할 수 없으므로 별도로 다뤄야 합니다.

비교 대상	확인할 값
최신 스냅샷	영상별 가장 최근 시각과 조회수
이전 스냅샷	기준 시각 이전에 기록된 조회수

조회 시각이 같은 행이 여럿이면 정렬 기준을 추가해 어느 기록을 선택할지 정해야 합니다. PostgreSQL 정렬 문서에서 ORDER BY 기준을 확인할 수 있습니다.

마무리

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

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