-- 视频二级品类分析
-- 按 merge二级品类+视频 维度聚合，分析视频效果指标
-- 包含：分发效果、流量池效果、推荐效果、新视频表现、群聊/单聊分析等

SELECT  0
        ,merge二级品类
        ,视频id
        ,标题
        ,类型
        ,上传渠道
        ,推荐状态
        ,首次推荐时间
        ,视频关键词
        ,视频口播
        -- 核心分发指标
        ,SUM(当日分发曝光pv) AS 分发曝光pv
        ,SUM(当日分发拉回曝光pv) AS 分发拉回曝光pv
        ,SUM(当日分发回流uv) AS 分发回流_当日
        ,SUM(累计分享回流uv) AS 总回流uv
        -- 效率指标
        ,SUM(当日分发回流uv) / SUM(当日分发曝光pv) AS rov_t0
        ,SUM(当日分发回流uv) / SUM(当日分发分享pv) AS ros_t0
        ,SUM(当日分发拉回曝光pv) / SUM(当日分发曝光pv) AS vov0
        ,SUM(0_1日分发拉回曝光pv) / SUM(当日分发曝光pv) AS vov1
        ,SUM(当日分发拉回曝光pv) / SUM(当日分发回流uv) AS vor_t0
        ,SUM(当日分发分享pv) / SUM(当日分发曝光pv) AS str_t0
        ,AVG(视频时长) AS 视频时长
        -- 视频量统计
        ,COUNT(DISTINCT 视频id) AS 分发视频量
        ,COUNT(DISTINCT IF(是否当日新推荐 > 0, 视频id, NULL)) AS 新推荐视频量
        -- 群聊/头部分享
        ,SUM(1008回流人数) / SUM(总回流uv) AS 群聊占比
        ,SUM(头部分享pv) / SUM(总分享pv) AS 头部分享占比
        ,SUM(当日分发头部分享pv) / SUM(当日分发曝光pv) AS 头部str_t0
        ,SUM(当日分发头部分享pv) / SUM(当日分发头部分享pv + 当日分发分享pv) AS 当日分发头部分享占比
        -- 流量池相关
        ,SUM(推荐回流) / SUM(流量池曝光) AS 流量池曝光roi
        ,SUM(流量池曝光) AS 流量池分发曝光
        -- 新视频 VoV 分析
        ,SUM(CASE WHEN 推荐天数间隔 IN (0,1,2,3) THEN 当日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (0,1,2,3) THEN 当日分发曝光pv END) AS 新0_3VoV0
        -- 排名分析
        ,AVG(曝光rank) - AVG(回流rank) AS rankdiff
        ,AVG(回流rank) AS 回流rank_avg
        ,AVG(曝光rank) AS 曝光rank_avg
        -- 流量池效率
        ,SUM(流量池回流) / SUM(流量池曝光) AS 流量池rov
        ,SUM(流量池回流) / SUM(流量池分享) AS 流量池ros
        ,SUM(流量池分享) / SUM(流量池曝光) AS 流量池str
        -- 推荐效率
        ,SUM(推荐回流) / SUM(推荐曝光) AS 推荐rov
        ,SUM(推荐回流) / SUM(推荐分享) AS 推荐ros
        ,SUM(推荐分享) / SUM(推荐曝光) AS 推荐str
        -- 有效分享率
        ,(SUM(带来1007回流的分享数) + SUM(带来1008回流的分享数)) / SUM(总分享pv) AS 有效分享率
        -- 新视频各时间窗口 VoV
        ,SUM(CASE WHEN 推荐天数间隔 IN (0,1,2,3,4,5,6,7) THEN 0_1日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (0,1,2,3,4,5,6,7) THEN 当日分发曝光pv END) AS 新0_7VoV1
        ,SUM(CASE WHEN 推荐天数间隔 IN (0,1,2,3,4,5,6,7) THEN 当日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (0,1,2,3,4,5,6,7) THEN 当日分发曝光pv END) AS 新0_7VoV0
        ,SUM(CASE WHEN 推荐天数间隔 IN (0,1,2,3,4,5,6,7) THEN 当日分发曝光pv END)
         / SUM(当日分发曝光pv) AS 新0_7曝光占比
        ,SUM(CASE WHEN 推荐天数间隔 IN (0,1,2,3) THEN 0_1日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (0,1,2,3) THEN 当日分发曝光pv END) AS 新0_3VoV1
        ,SUM(CASE WHEN 推荐天数间隔 IN (0,1,2,3) THEN 当日分发曝光pv END)
         / SUM(当日分发曝光pv) AS 新0_3曝光占比
        ,SUM(CASE WHEN 推荐天数间隔 IN (1,2,3) THEN 0_1日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (1,2,3) THEN 当日分发曝光pv END) AS 新1_3VoV1
        ,SUM(CASE WHEN 推荐天数间隔 IN (1,2,3) THEN 当日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (1,2,3) THEN 当日分发曝光pv END) AS 新1_3VoV0
        ,SUM(CASE WHEN 推荐天数间隔 IN (0) THEN 0_1日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (0) THEN 当日分发曝光pv END) AS 新0VoV1
        ,SUM(CASE WHEN 推荐天数间隔 IN (0) THEN 当日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (0) THEN 当日分发曝光pv END) AS 新0VoV0
        ,SUM(CASE WHEN 推荐天数间隔 IN (0) THEN 当日分发曝光pv END)
         / SUM(当日分发曝光pv) AS 新0曝光占比
        -- 各天新视频量
        ,COUNT(DISTINCT IF(推荐天数间隔 = 1, 视频id, NULL)) AS 新1视频量
        ,SUM(CASE WHEN 推荐天数间隔 IN (1) THEN 0_1日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (1) THEN 当日分发曝光pv END) AS 新1VoV1
        ,SUM(CASE WHEN 推荐天数间隔 IN (1) THEN 当日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (1) THEN 当日分发曝光pv END) AS 新1VoV0
        ,SUM(CASE WHEN 推荐天数间隔 IN (1) THEN 当日分发曝光pv END)
         / SUM(当日分发曝光pv) AS 新1曝光占比
        ,COUNT(DISTINCT IF(推荐天数间隔 = 2, 视频id, NULL)) AS 新2视频量
        ,SUM(CASE WHEN 推荐天数间隔 IN (2) THEN 0_1日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (2) THEN 当日分发曝光pv END) AS 新2VoV1
        ,SUM(CASE WHEN 推荐天数间隔 IN (2) THEN 当日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (2) THEN 当日分发曝光pv END) AS 新2VoV0
        ,SUM(CASE WHEN 推荐天数间隔 IN (2) THEN 当日分发曝光pv END)
         / SUM(当日分发曝光pv) AS 新2曝光占比
        ,COUNT(DISTINCT IF(推荐天数间隔 = 3, 视频id, NULL)) AS 新3视频量
        ,SUM(CASE WHEN 推荐天数间隔 IN (3) THEN 0_1日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (3) THEN 当日分发曝光pv END) AS 新3VoV1
        ,SUM(CASE WHEN 推荐天数间隔 IN (3) THEN 当日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 IN (3) THEN 当日分发曝光pv END) AS 新3VoV0
        ,SUM(CASE WHEN 推荐天数间隔 IN (3) THEN 当日分发曝光pv END)
         / SUM(当日分发曝光pv) AS 新3曝光占比
        -- 非新视频
        ,SUM(CASE WHEN 推荐天数间隔 NOT IN (0,1,2,3,4,5,6,7) THEN 0_1日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 NOT IN (0,1,2,3,4,5,6,7) THEN 当日分发曝光pv END) AS 非0_7_VoV1
        ,SUM(CASE WHEN 推荐天数间隔 NOT IN (0,1,2,3,4,5,6,7) THEN 当日分发拉回曝光pv END)
         / SUM(CASE WHEN 推荐天数间隔 NOT IN (0,1,2,3,4,5,6,7) THEN 当日分发曝光pv END) AS 非0_7_VoV0
        -- VoV 时间序列
        ,SUM(0_2日分发拉回曝光pv) / SUM(当日分发曝光pv) AS vov2
        ,SUM(0_7日分发拉回曝光pv) / SUM(当日分发曝光pv) AS vov7
        ,SUM(0_30日分发拉回曝光pv) / SUM(当日分发曝光pv) AS vov30
        ,(SUM(0_1日分发拉回曝光pv) / SUM(当日分发曝光pv)) - (SUM(当日分发拉回曝光pv) / SUM(当日分发曝光pv)) AS vov1减vov0
        -- 分发数据
        ,SUM(当日分发回流uv) AS 分发回流uv
        ,SUM(当日分发分享pv) AS 分发分享pv
        -- 群聊/单聊效率
        ,SUM(1008回流人数) / SUM(带来1008回流的分享数) AS 群聊ros
        ,SUM(1007回流人数) / SUM(带来1007回流的分享数) AS 单聊ros
        ,SUM(1007进入分发曝光pv) / SUM(1007回流人数) AS 单聊vor
        ,SUM(1008进入分发曝光pv) / SUM(1008回流人数) AS 群聊vor
        -- 回流后行为
        ,(SUM(1007回流再分享pv) + SUM(1008回流再分享pv)) / (SUM(1007进入分发曝光pv) + SUM(1008进入分发曝光pv)) AS 回流后str
        ,SUM(1008回流再分享pv) / SUM(1008进入分发曝光pv) AS 群聊后str
        ,SUM(1007回流再分享pv) / SUM(1007进入分发曝光pv) AS 单聊后str
        ,SUM(总回流uv) / SUM(累计分享回流uv) AS 当日分享回流占比
        ,SUM(当日分享当日回流首层uv) / SUM(当日分享当日回流uv) AS 当日分享当日回流首层比当日分享当日回流
        -- 供给统计
        ,COUNT(DISTINCT IF(是否七日内创建 > 0, 视频id, NULL)) AS 七日内新视频量
        ,COUNT(DISTINCT IF(是否首发视频 > 0, 视频id, NULL)) AS 首发视频量
        ,COUNT(DISTINCT IF(是否首发视频 > 0, 视频id, NULL)) / COUNT(DISTINCT 视频id) AS 首发视频比例
        ,COUNT(DISTINCT 站内uid) AS 供给uid量
        ,AVG(首发距今时间) AS 首发距今间隔avg
        ,AVG(推荐天数间隔) AS 推荐距今间隔avg
        ,AVG(创建天数间隔) AS 创建距今间隔avg
        -- 累计拉回曝光
        ,SUM(0_1日分发拉回曝光pv) AS 0_1日分发拉回曝光pv
        ,SUM(0_2日分发拉回曝光pv) AS 0_2日分发拉回曝光pv
        ,SUM(0_3日分发拉回曝光pv) AS 0_3日分发拉回曝光pv
        ,SUM(0_7日分发拉回曝光pv) AS 0_7日分发拉回曝光pv
        ,SUM(0_30日分发拉回曝光pv) AS 0_30日分发拉回曝光pv
        -- ROV 时间序列
        ,SUM(0_1日分发回流uv) / SUM(当日分发曝光pv) AS rov1
        ,SUM(0_7日分发回流uv) / SUM(当日分发曝光pv) AS rov7
        ,SUM(0_30日分发回流uv) / SUM(当日分发曝光pv) AS rov30
        -- VOR 时间序列
        ,SUM(0_1日分发拉回曝光pv) / SUM(0_1日分发回流uv) AS vor1
        ,SUM(0_7日分发拉回曝光pv) / SUM(0_7日分发回流uv) AS vor7
        ,SUM(0_30日分发拉回曝光pv) / SUM(0_30日分发回流uv) AS vor30
        -- 流量池明细
        ,SUM(流量池曝光) AS 流量池曝光
        ,SUM(流量池播放) AS 流量池播放
        ,SUM(流量池分享) AS 流量池分享
        ,SUM(流量池回流) AS 流量池回流
        -- 推荐明细
        ,SUM(推荐曝光) AS 推荐曝光
        ,SUM(推荐播放) AS 推荐播放
        ,SUM(推荐分享) AS 推荐分享
        ,SUM(推荐回流) AS 推荐回流
        -- 分享/回流明细
        ,SUM(总分享pv) AS 总分享pv
        ,SUM(总回流uv) AS 总回流uv
        ,SUM(1007回流人数) AS 1007回流人数
        ,SUM(1008回流人数) AS 1008回流人数
        ,SUM(带来1007回流的分享数) AS 带来1007回流的分享数
        ,SUM(带来1008回流的分享数) AS 带来1008回流的分享数
        ,SUM(1007进入分发曝光pv) AS 1007进入分发曝光pv
        ,SUM(1008进入分发曝光pv) AS 1008进入分发曝光pv
        ,SUM(1007回流再分享pv) AS 1007回流再分享pv
        ,SUM(1008回流再分享pv) AS 1008回流再分享pv
        ,SUM(有回流分享pv) AS 有回流分享pv
        ,SUM(累计分享回流uv) AS 累计分享回流uv
        ,SUM(分发分享pv) AS 分发分享pv
        ,SUM(头部分享pv) AS 头部分享pv
        ,SUM(当日分发头部分享pv) AS 当日分发头部分享pv
        ,SUM(当日分享当日回流uv) AS 当日分享当日回流uv
        ,SUM(当日分享当日回流首层uv) AS 当日分享当日回流首层uv
        ,SUM(当日分享当日回流非首层uv) AS 当日分享当日回流非首层uv
        ,SUM(非当日分享回流uv) AS 非当日分享回流uv
        ,SUM(n当日分发回流uv) AS n当日分发回流uv
        ,SUM(非当日分发回流uv) AS 非当日分发回流uv
        -- 视频量级分布
        ,COUNT(DISTINCT IF(当日分发曝光pv >= 100, 视频id, NULL)) AS t0_100曝光视频量
        ,COUNT(DISTINCT IF(当日分发曝光pv >= 500, 视频id, NULL)) AS t0_500曝光视频量
        ,COUNT(DISTINCT IF(当日分发曝光pv >= 1000, 视频id, NULL)) AS t0_1k曝光视频量
        ,COUNT(DISTINCT IF(当日分发曝光pv >= 10000, 视频id, NULL)) AS t0_1w曝光视频量
        -- VoV 高质量视频
        ,COUNT(DISTINCT IF((0_1日分发拉回曝光pv) / (当日分发曝光pv) - (当日分发拉回曝光pv) / (当日分发曝光pv) >= 0.2 AND 当日分发曝光pv >= 500, 视频id, NULL)) AS vov1_0_02_500视频量
        ,COUNT(DISTINCT IF((0_1日分发拉回曝光pv) / (当日分发曝光pv) - (当日分发拉回曝光pv) / (当日分发曝光pv) >= 0.2 AND 当日分发曝光pv >= 500, 视频id, NULL)) / COUNT(DISTINCT IF(当日分发曝光pv >= 500, 视频id, NULL)) AS vov1_0_02_500视频占比
        ,COUNT(DISTINCT IF((当日分发拉回曝光pv) / (当日分发曝光pv) >= 0.4 AND 当日分发曝光pv >= 500, 视频id, NULL)) AS vov0_04_500视频量
        ,COUNT(DISTINCT IF((当日分发拉回曝光pv) / (当日分发曝光pv) >= 0.4 AND 当日分发曝光pv >= 500, 视频id, NULL)) / COUNT(DISTINCT IF(当日分发曝光pv >= 500, 视频id, NULL)) AS vov0_04_500视频占比
        ,COUNT(DISTINCT IF((0_1日分发拉回曝光pv) / (当日分发曝光pv) >= 0.7 AND 当日分发曝光pv >= 500, 视频id, NULL)) AS vov1_07_500视频量
        ,COUNT(DISTINCT IF((0_1日分发拉回曝光pv) / (当日分发曝光pv) >= 0.7 AND 当日分发曝光pv >= 500, 视频id, NULL)) / COUNT(DISTINCT IF(当日分发曝光pv >= 500, 视频id, NULL)) AS vov1_07_500视频占比
        ,COUNT(DISTINCT IF((0_1日分发拉回曝光pv) / (当日分发曝光pv) >= 0.8 AND 当日分发曝光pv >= 500, 视频id, NULL)) AS vov1_08_500视频量
        ,COUNT(DISTINCT IF((0_1日分发拉回曝光pv) / (当日分发曝光pv) >= 0.8 AND 当日分发曝光pv >= 500, 视频id, NULL)) / COUNT(DISTINCT IF(当日分发曝光pv >= 500, 视频id, NULL)) AS vov1_08_500视频占比
        -- 拉回曝光量级分布
        ,COUNT(DISTINCT IF(当日分发拉回曝光pv >= 500, 视频id, NULL)) AS t0_500拉回曝光视频量
        ,COUNT(DISTINCT IF(0_1日分发拉回曝光pv >= 500, 视频id, NULL)) AS t1_500拉回曝光视频量
        ,COUNT(DISTINCT IF(当日分发拉回曝光pv >= 10000, 视频id, NULL)) AS t0_1w拉回曝光视频量
        ,COUNT(DISTINCT IF(0_1日分发拉回曝光pv >= 10000, 视频id, NULL)) AS t1_1w拉回曝光视频量
        ,COUNT(DISTINCT IF(当日分发拉回曝光pv >= 100000, 视频id, NULL)) AS t0_10w拉回曝光视频量
        ,COUNT(DISTINCT IF(0_1日分发拉回曝光pv >= 100000, 视频id, NULL)) AS t1_10w拉回曝光视频量
        ,COUNT(DISTINCT IF(当日分发拉回曝光pv >= 1000000, 视频id, NULL)) AS t0_100w拉回曝光视频量
        ,COUNT(DISTINCT IF(0_1日分发拉回曝光pv >= 1000000, 视频id, NULL)) AS t1_100w拉回曝光视频量
        -- 流量池指标
        ,(SUM(带来流量池1007回流的分享数) + SUM(带来流量池1008回流的分享数)) / SUM(带来流量池回流的分享数) AS 流量池有效分享率
        ,SUM(流量池1008回流人数) / SUM(流量池回流人数) AS 流量池群聊占比
FROM    loghubods.video_dimension_detail_add_column
WHERE   dt >= '${start}'
AND     dt <= '${end}'
AND     推荐状态 = '推荐'
GROUP BY 0
         ,merge二级品类
         ,视频id
         ,标题
         ,类型
         ,上传渠道
         ,推荐状态
         ,首次推荐时间
         ,视频关键词
         ,视频口播
ORDER BY 0 DESC
         ,分发曝光pv DESC
LIMIT   50000
;
