-- 标题相关性分析（简化版）
-- 参数: ${dt} - 日期，格式 YYYYMMDD
-- 只关注：分享标题/文章标题 与 视频标题 的相关性

SELECT  dt
        ,channel
        ,hotsencetype
        ,合作方名
        ,公众号名
        -- 素材维度
        ,rootsourceid
        ,文章标题
        ,分享标题
        ,分享封面
        -- 视频维度
        ,videoid
        ,title
        ,merge一级品类
        ,merge二级品类
        -- 核心指标
        ,COUNT(DISTINCT mid) AS 点击uv
        -- 整体回流（原视频+推荐）
        ,(SUM(CASE WHEN 再分享群聊回流uv > 0 THEN 再分享群聊回流uv ELSE 0 END)
          + SUM(CASE WHEN 再分享单聊回流uv > 0 THEN 再分享单聊回流uv ELSE 0 END)
         ) / (COUNT(DISTINCT mid) + 10) AS 整体回流率
        ,SUM(CASE WHEN 再分享群聊回流uv > 0 THEN 再分享群聊回流uv ELSE 0 END)
         + SUM(CASE WHEN 再分享单聊回流uv > 0 THEN 再分享单聊回流uv ELSE 0 END) AS 整体回流uv
        -- 头部回流（原视频）
        ,(SUM(CASE WHEN 是否原视频 = '是' THEN 再分享群聊回流uv ELSE 0 END)
          + SUM(CASE WHEN 是否原视频 = '是' THEN 再分享单聊回流uv ELSE 0 END)
         ) / (COUNT(DISTINCT mid) + 10) AS 头部回流率
        ,SUM(CASE WHEN 是否原视频 = '是' THEN 再分享群聊回流uv ELSE 0 END)
         + SUM(CASE WHEN 是否原视频 = '是' THEN 再分享单聊回流uv ELSE 0 END) AS 头部回流uv
        -- 推荐回流（非原视频）
        ,(SUM(CASE WHEN 是否原视频 = '否' THEN 再分享群聊回流uv ELSE 0 END)
          + SUM(CASE WHEN 是否原视频 = '否' THEN 再分享单聊回流uv ELSE 0 END)
         ) / (COUNT(DISTINCT mid) + 10) AS 推荐回流率
        ,SUM(CASE WHEN 是否原视频 = '否' THEN 再分享群聊回流uv ELSE 0 END)
         + SUM(CASE WHEN 是否原视频 = '否' THEN 再分享单聊回流uv ELSE 0 END) AS 推荐回流uv
FROM    loghubods.opengid_base_data
WHERE   dt = '${dt}'
AND     usersharedepth = 0
AND     videoid IS NOT NULL
AND     (文章标题 IS NOT NULL AND 文章标题 != '' OR 分享标题 IS NOT NULL AND 分享标题 != '')
GROUP BY dt
         ,channel
         ,hotsencetype
         ,合作方名
         ,公众号名
         ,rootsourceid
         ,文章标题
         ,分享标题
         ,分享封面
         ,videoid
         ,title
         ,merge一级品类
         ,merge二级品类
ORDER BY 点击uv DESC
LIMIT   50000
;
