-- 标题相关性分析 v3
-- 参数: ${dt} - 日期，格式 YYYYMMDD
-- 增加裂变率指标和更多维度

SELECT  dt
        ,channel
        ,进入小程序场景
        ,人群包
        ,人群_创意名称
        ,hotsencetype
        ,合作方名
        ,公众号名
        -- 素材维度
        ,rootsourceid
        ,文章标题
        ,文章品类
        ,长文封面
        ,分享标题
        ,分享封面
        -- 视频维度
        ,videoid
        ,title
        ,merge一级品类
        ,merge二级品类
        -- 核心指标
        ,COUNT(DISTINCT mid) AS 点击uv
        -- 裂变指标（UV + 率）
        ,SUM(t0裂变人数) AS 裂变uv
        ,SUM(t0头部裂变人数) AS 头部裂变uv
        ,(SUM(CASE WHEN 是否原视频 = '是' THEN 再分享群聊回流uv ELSE 0 END)
          + SUM(CASE WHEN 是否原视频 = '是' THEN 再分享单聊回流uv ELSE 0 END)
         ) AS 头部原视频裂变uv
        ,SUM(t0分发裂变人数) AS 推荐裂变uv
        ,COUNT(DISTINCT CASE WHEN 是否进入推荐 = '1' THEN mid 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
;
