-- 素材与头部视频相关性分析（单日查询）
-- 参数: ${dt} - 日期，格式 YYYYMMDD
-- JOIN 视频基础信息（关键词、口播等），分析素材与视频内容的匹配关系

SELECT  a.dt
        ,a.channel
        ,a.hotsencetype
        ,a.合作方名
        ,a.公众号名
        -- 素材维度
        ,a.rootsourceid
        ,a.文章标题
        ,a.分享标题
        ,a.分享封面
        -- 视频基础信息
        ,a.videoid
        ,a.title
        ,a.merge一级品类
        ,a.merge二级品类
        -- 视频内容信息（来自 video_dimension 表）
        ,b.视频关键词
        ,b.视频口播
        ,b.视频主题
        ,b.视频场景
        ,b.情感倾向
        ,b.视频风格
        ,b.传播性判断
        ,b.推测观众年龄段
        ,b.是否有片尾引导
        ,b.引导强度
        -- 核心指标
        ,COUNT(DISTINCT a.mid) AS 点击uv
        ,COUNT(DISTINCT CASE WHEN a.是否进入推荐 = '1' THEN a.mid END) / COUNT(DISTINCT a.mid) AS 进入推荐率
        ,(SUM(CASE WHEN a.再分享群聊回流uv > 0 THEN a.再分享群聊回流uv ELSE 0 END)
          + SUM(CASE WHEN a.再分享单聊回流uv > 0 THEN a.再分享单聊回流uv ELSE 0 END)
         ) / (COUNT(DISTINCT a.mid) + 10) AS 再分享回流率
        ,(SUM(CASE WHEN a.是否原视频 = '是' THEN a.再分享群聊回流uv END)
          + SUM(CASE WHEN a.是否原视频 = '是' THEN a.再分享单聊回流uv END)
         ) / (COUNT(DISTINCT a.mid) + 10) AS 原视频再分享回流率
        ,(SUM(CASE WHEN a.是否原视频 = '否' THEN a.再分享群聊回流uv END)
          + SUM(CASE WHEN a.是否原视频 = '否' THEN a.再分享单聊回流uv END)
         ) / (COUNT(DISTINCT a.mid) + 10) AS 推荐再分享回流率
        ,SUM(CASE WHEN a.再分享群聊回流uv > 0 THEN a.再分享群聊回流uv ELSE 0 END)
         + SUM(CASE WHEN a.再分享单聊回流uv > 0 THEN a.再分享单聊回流uv ELSE 0 END) AS 再分享回流uv
FROM    loghubods.opengid_base_data a
LEFT JOIN (
    SELECT  视频id
            ,视频关键词
            ,视频口播
            ,视频主题
            ,视频场景
            ,情感倾向
            ,视频风格
            ,传播性判断
            ,推测观众年龄段
            ,是否有片尾引导
            ,引导强度
    FROM    loghubods.video_dimension_detail_add_column
    WHERE   dt = '${dt}'
    GROUP BY 视频id
             ,视频关键词
             ,视频口播
             ,视频主题
             ,视频场景
             ,情感倾向
             ,视频风格
             ,传播性判断
             ,推测观众年龄段
             ,是否有片尾引导
             ,引导强度
) b ON a.videoid = b.视频id
WHERE   a.dt = '${dt}'
AND     a.usersharedepth = 0
AND     a.videoid IS NOT NULL
AND     (a.文章标题 IS NOT NULL AND a.文章标题 != '' OR a.分享标题 IS NOT NULL AND a.分享标题 != '')
GROUP BY a.dt
         ,a.channel
         ,a.hotsencetype
         ,a.合作方名
         ,a.公众号名
         ,a.rootsourceid
         ,a.文章标题
         ,a.分享标题
         ,a.分享封面
         ,a.videoid
         ,a.title
         ,a.merge一级品类
         ,a.merge二级品类
         ,b.视频关键词
         ,b.视频口播
         ,b.视频主题
         ,b.视频场景
         ,b.情感倾向
         ,b.视频风格
         ,b.传播性判断
         ,b.推测观众年龄段
         ,b.是否有片尾引导
         ,b.引导强度
ORDER BY 点击uv DESC
LIMIT   50000
;
