用 DuckDB 搭建短视频脚本分析器——批量拆解爆款内容的底层逻辑

video_id,title,transcript,likes,comments,shares,duration_sec,platform,publish_time
v001,3个让你月入过万的AI工具,"0:03|大家好，今天给大家分享3个副业项目
0:15|第一个工具完全免费
0:30|第二个工具适合零基础"... ,50000,3200,1800,62,youtube,2026-06-01 19:30:00
v002,AI写作教程,"0:05|很多人问我怎么写文章
0:20|今天分享一个AI技巧
0:45|这个方法让我效率翻倍"... ,32000,2100,900,45,youtube,2026-06-02 20:15:00

WITH 
    -- 将 transcript 拆分成独立行
    transcript_lines AS (
        SELECT 
            video_id,
            line_number,
            line
        FROM videos,
             unnest(split(transcript, E'\n')) WITH ORDINALITY AS t(line, line_number)
    ),
    -- 提取前 30 秒的内容
    first30 AS (
        SELECT 
            video_id,
            line
        FROM transcript_lines
        WHERE line_number <= 30
    ),
    -- 提取中文关键词（2-4 个汉字）
    keywords AS (
        SELECT 
            video_id,
            regexp_extract(line, '[\u4e00-\u9fa5]{2,4}', 1) AS kw
        FROM first30
        WHERE regexp_like(line, '[\u4e00-\u9fa5]')
    )
SELECT 
    kw AS opening_keyword,
    COUNT(*) AS frequency,
    COUNT(DISTINCT video_id) AS video_count
FROM keywords
WHERE kw IS NOT NULL
GROUP BY kw
ORDER BY frequency DESC
LIMIT 30;

SELECT 
    CASE 
        WHEN CHAR_LENGTH(title) <= 10 THEN '超短（10字内）'
        WHEN CHAR_LENGTH(title) <= 20 THEN '短（11-20字）'
        WHEN CHAR_LENGTH(title) <= 30 THEN '中（21-30字）'
        ELSE '长（30字以上）'
    END AS title_len_category,
    COUNT(*) AS video_count,
    ROUND(AVG(likes), 0) AS avg_likes,
    ROUND(AVG(likes / NULLIF(duration_sec, 0)), 2) AS likes_per_second,
    ROUND(100.0 * SUM(CASE WHEN likes > 10000 THEN 1 ELSE 0 END) / COUNT(*), 1) AS viral_rate_pct
FROM videos
GROUP BY 1
ORDER BY likes_per_second DESC;

SELECT 
    CASE 
        WHEN EXTRACT(HOUR FROM publish_time) BETWEEN 6 AND 11 THEN '早晨 (6-11点)'
        WHEN EXTRACT(HOUR FROM publish_time) BETWEEN 12 AND 14 THEN '中午 (12-14点)'
        WHEN EXTRACT(HOUR FROM publish_time) BETWEEN 15 AND 17 THEN '下午 (15-17点)'
        WHEN EXTRACT(HOUR FROM publish_time) BETWEEN 18 AND 21 THEN '晚间 (18-21点)'
        ELSE '深夜 (21-6点)'
    END AS time_slot,
    COUNT(*) AS video_count,
    ROUND(AVG(likes), 0) AS avg_likes,
    ROUND(AVG(comments) / NULLIF(AVG(likes), 0) * 100, 2) AS comment_ratio
FROM videos
GROUP BY 1
ORDER BY avg_likes DESC;

SELECT 
    video_id,
    ROUND(AVG(sentiment_score), 3) AS avg_sentiment,
    STDDEV(sentiment_score) AS sentiment_spread,
    COUNT(*) AS comment_count
FROM (
    SELECT 
        video_id,
        CASE 
            WHEN comment_text ILIKE '%好%' OR comment_text ILIKE '%棒%' THEN 0.8
            WHEN comment_text ILIKE '%赞%' OR comment_text ILIKE '%学到了%' THEN 0.7
            WHEN comment_text ILIKE '%差%' OR comment_text ILIKE '%垃圾%' THEN -0.6
            WHEN comment_text ILIKE '%没用%' OR comment_text ILIKE '%骗人%' THEN -0.8
            ELSE 0.0
        END AS sentiment_score
    FROM comments
)
GROUP BY video_id
ORDER BY avg_sentiment DESC;

WITH 
    video_features AS (
        SELECT 
            video_id,
            title,
            likes,
            comments,
            shares,
            duration_sec,
            CASE WHEN title SIMILAR TO '%[0-9]+个%' THEN 1 ELSE 0 END AS has_number,
            CASE WHEN title SIMILAR TO '%AI%' THEN 1 ELSE 0 END AS has_ai_tag
        FROM videos
    )
SELECT 
    video_id,
    title,
    CASE 
        WHEN likes > 50000 AND shares > 1000 THEN '传播型爆款'
        WHEN likes > 30000 AND comments > 1500 THEN '互动型爆款'
        WHEN likes > 20000 AND duration_sec > 300 THEN '深度型爆款'
        ELSE '普通视频'
    END AS video_type,
    ROUND(100.0 * COUNT(*) OVER (PARTITION BY 
        CASE 
            WHEN likes > 50000 AND shares > 1000 THEN '传播型爆款'
            WHEN likes > 30000 AND comments > 1500 THEN '互动型爆款'
            WHEN likes > 20000 AND duration_sec > 300 THEN '深度型爆款'
            ELSE '普通视频'
        END
    ) / COUNT(*) OVER (), 1) AS type_pct
FROM video_features
ORDER BY likes DESC;