-- 渠道效果分析
-- 分析不同渠道的整体表现、渠道×品类表现

SELECT  dt
        ,channel
        ,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 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)
         ) / (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
GROUP BY dt, channel, merge一级品类, merge二级品类
ORDER BY dt, channel, 点击uv DESC
;
