-- 视频维度分析
-- 按 dt、channel、videoid 等维度统计点击、再分享、回流等指标

SELECT  dt
        ,channel
        ,hotsencetype
        ,合作方名
        ,公众号名
        ,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 是否原视频 = '是' THEN 再分享群聊回流uv END)
          + SUM(CASE WHEN 是否原视频 = '是' THEN 再分享单聊回流uv END)
         ) / (COUNT(DISTINCT mid) + 10) AS 原视频再分享回流率
        ,COUNT(DISTINCT CASE WHEN 是否进入推荐 = '1' THEN mid END) AS 进入推荐人数
        ,COUNT(DISTINCT CASE WHEN 是否进入推荐 = '1' THEN mid END) / COUNT(DISTINCT mid) 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)
         ) / ((SUM(CASE WHEN 是否原视频 = '否' THEN 再分享群聊回流uv END)
               + SUM(CASE WHEN 是否原视频 = '否' THEN 再分享单聊回流uv END)
              ) / (COUNT(DISTINCT mid) + 10)
         ) AS 原视频质量
        ,AVG(粉丝数) AS 粉丝量avg
        ,AVG(新增粉丝数) AS 新增粉丝数avg
        ,AVG(阅读量) AS 阅读量avg
        ,100 * COUNT(DISTINCT mid) / (AVG(粉丝数) + 1) AS 阅读打开率
        ,100 * AVG(阅读量) / (AVG(粉丝数) + 1) AS 阅读率
        ,100 * COUNT(DISTINCT mid) / (AVG(阅读量) + 1) AS 打开率
        ,SUM(CASE WHEN 再分享群聊回流uv > 0 THEN 再分享群聊回流uv ELSE 0 END)
         + SUM(CASE WHEN 再分享单聊回流uv > 0 THEN 再分享单聊回流uv ELSE 0 END) AS 再分享回流uv
        ,COUNT(DISTINCT wx_sn) AS 阅读文章数
        ,COUNT(DISTINCT videoid) AS 点击视频数
        ,COUNT(DISTINCT 再分享videoid) AS 再分享视频数
        ,COUNT(DISTINCT opengid) - 1 AS 点击群数量
        ,COUNT(DISTINCT mid) / COUNT(DISTINCT shareid) AS 单分享卡片访问uv
        ,1000 * (COUNT(DISTINCT CASE WHEN 群类型 = '群聊' THEN mid END) + 1)
         / (COUNT(DISTINCT CASE WHEN 群类型 = '群聊' THEN opengid END) * 30 + 500)
         * (SUM(CASE WHEN 再分享群聊回流uv > 0 THEN 再分享群聊回流uv ELSE 0 END)
            + SUM(CASE WHEN 再分享单聊回流uv > 0 THEN 再分享单聊回流uv ELSE 0 END) + 1
           ) / (COUNT(DISTINCT mid) + 500) AS 群pushscore_ctr_ROR
        ,(COUNT(DISTINCT CASE WHEN 群类型 = '群聊' THEN mid END) + 1)
         / (COUNT(DISTINCT CASE WHEN 群类型 = '群聊' THEN opengid END) * 30 + 100) AS 群ctr
        ,COUNT(DISTINCT 再分享shareid) / 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 再分享shareid) AS 再分享ros
        ,SUM(CASE WHEN 再分享群聊回流uv > 0 THEN 再分享群聊回流uv ELSE 0 END)
         / (SUM(CASE WHEN 再分享群聊回流uv > 0 THEN 再分享群聊回流uv ELSE 0 END)
            + SUM(CASE WHEN 再分享单聊回流uv > 0 THEN 再分享单聊回流uv ELSE 0 END)
           ) AS 再分享群聊占比
        ,COUNT(DISTINCT CASE WHEN 是否原视频 = '是' THEN 再分享shareid END) / COUNT(DISTINCT mid) AS 原视频再分享率
        ,(SUM(CASE WHEN 是否原视频 = '是' THEN 再分享群聊回流uv END)
          + SUM(CASE WHEN 是否原视频 = '是' THEN 再分享单聊回流uv END)
         ) / COUNT(DISTINCT CASE WHEN 是否原视频 = '是' THEN 再分享shareid END) AS 原视频再分享ros
        ,SUM(CASE WHEN 是否原视频 = '是' THEN 再分享群聊回流uv END)
         / (SUM(CASE WHEN 是否原视频 = '是' THEN 再分享单聊回流uv END)
            + SUM(CASE WHEN 是否原视频 = '是' THEN 再分享群聊回流uv END)
           ) AS 原视频再分享群聊占比
        ,COUNT(DISTINCT CASE WHEN 是否原视频 = '否' THEN 再分享shareid END) / COUNT(DISTINCT mid) AS 推荐再分享率
        ,(SUM(CASE WHEN 是否原视频 = '否' THEN 再分享群聊回流uv END)
          + SUM(CASE WHEN 是否原视频 = '否' THEN 再分享单聊回流uv END)
         ) / COUNT(DISTINCT CASE WHEN 是否原视频 = '是' THEN 再分享shareid END) AS 推荐再分享ros
        ,SUM(CASE WHEN 是否原视频 = '否' THEN 再分享群聊回流uv END)
         / (SUM(CASE WHEN 是否原视频 = '否' THEN 再分享单聊回流uv END)
            + SUM(CASE WHEN 是否原视频 = '否' THEN 再分享群聊回流uv END)
           ) AS 推荐再分享群聊占比
        ,COUNT(DISTINCT 再分享shareid) AS 再分享次数
        ,SUM(CASE WHEN 再分享群聊回流uv > 0 THEN 再分享群聊回流uv END) AS 再分享群聊回流
        ,SUM(CASE WHEN 再分享单聊回流uv > 0 THEN 再分享单聊回流uv END) AS 再分享单聊回流
        ,COUNT(DISTINCT CASE WHEN 再分享点击场景 = '1008' THEN 再分享点击id END) AS 再分享回流群数
        ,COUNT(DISTINCT CASE WHEN 是否原视频 = '是' THEN 再分享shareid END) AS 再分享原视频次数
        ,SUM(CASE WHEN 是否原视频 = '是' THEN 再分享群聊回流uv END) AS 再分享原视频群聊回流
        ,SUM(CASE WHEN 是否原视频 = '是' THEN 再分享单聊回流uv END) AS 再分享原视频单聊回流
        ,COUNT(DISTINCT CASE WHEN 是否原视频 = '否' THEN 再分享shareid END) AS 再分享推荐视频次数
        ,SUM(CASE WHEN 是否原视频 = '否' THEN 再分享群聊回流uv END) AS 再分享推荐群聊回流
        ,SUM(CASE WHEN 是否原视频 = '否' THEN 再分享单聊回流uv END) AS 再分享推荐单聊回流
        ,COUNT(DISTINCT 再分享merge二级品类) AS 再分享二级品类数
        ,COUNT(DISTINCT shareid) AS 来源分享卡片数
        ,COUNT(DISTINCT 分享者mid) AS 来源分享uv
        ,COUNT(DISTINCT click_province) AS 点击地域省数
        ,COUNT(DISTINCT click_city) AS 点击地域市数
        ,SUM(群历史人数) AS 群历史总人数
        ,AVG(群当日活跃人数) AS 平均群当日活跃人数
        ,AVG(群历史人数) AS 平均群历史人数
        ,AVG(opengid_cnt) AS 平均人历史所在群数
        ,COUNT(DISTINCT mid) / COUNT(DISTINCT videoid) AS 单视频访问uv
        ,COUNT(DISTINCT mid) / COUNT(DISTINCT opengid) AS 单群访问uv
        ,COUNT(DISTINCT mid) / COUNT(DISTINCT 分享者mid) AS 单分享者访问uv
        ,COUNT(DISTINCT rootsourceid) AS 点击rootsourceid数
        ,COUNT(DISTINCT channel) AS 点击channel数
FROM    loghubods.opengid_base_data
WHERE   dt >= ${start}
AND     dt <= ${end}
AND     usersharedepth = 0
AND     videoid IS NOT NULL
GROUP BY dt
         ,channel
         ,hotsencetype
         ,合作方名
         ,公众号名
         ,videoid
         ,title
         ,推荐状态
         ,merge一级品类
         ,merge二级品类
ORDER BY 点击uv DESC
LIMIT   5000
;
