-- 品类再分享分析
-- 分析不同渠道下，头部视频品类 × 再分享视频品类的关系

SELECT  dt
        ,channel
        ,merge一级品类 AS 头部一级品类
        ,merge二级品类 AS 头部二级品类
        ,再分享merge一级品类 AS 再分享一级品类
        ,再分享merge二级品类 AS 再分享二级品类
        ,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 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二级品类, 再分享merge一级品类, 再分享merge二级品类
ORDER BY dt, channel, 点击uv DESC
;
