-- 素材视频匹配分析
-- 分析素材与视频的搭配效果
-- 只取有素材信息的记录（文章标题或卡片标题非空）

SELECT  dt
        ,channel
        ,公众号名
        -- 素材维度
        ,rootsourceid
        ,文章标题
        ,分享标题
        -- 视频维度
        ,videoid
        ,title
        ,merge一级品类
        ,merge二级品类
        -- 核心指标
        ,COUNT(DISTINCT mid) AS 点击uv
        ,COUNT(DISTINCT CASE WHEN 是否进入推荐 = '1' THEN mid END) / COUNT(DISTINCT mid) AS 进入推荐率
        ,(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 是否原视频 = '是' THEN 再分享群聊回流uv END)
          + SUM(CASE WHEN 是否原视频 = '是' THEN 再分享单聊回流uv END)
         ) / (COUNT(DISTINCT mid) + 10) AS 原视频再分享回流率
        ,(SUM(CASE WHEN 是否原视频 = '否' THEN 再分享群聊回流uv END)
          + SUM(CASE WHEN 是否原视频 = '否' THEN 再分享单聊回流uv 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
FROM    loghubods.opengid_base_data
WHERE   dt >= ${start}
AND     dt <= ${end}
AND     usersharedepth = 0
AND     videoid IS NOT NULL
AND     (文章标题 IS NOT NULL AND 文章标题 != '' OR 分享标题 IS NOT NULL AND 分享标题 != '')
GROUP BY dt
         ,channel
         ,公众号名
         ,rootsourceid
         ,文章标题
         ,分享标题
         ,videoid
         ,title
         ,merge一级品类
         ,merge二级品类
ORDER BY 点击uv DESC
;
