data_analysis/tmp_sql/头部视频.sql

SELECT dt,
视频id,标题,merge二级品类,4,5,6,7,8,9,
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 流量池分发曝光,
        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 有效分享率,
        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(
          CASE
            WHEN 推荐天数间隔 in (0,1,2,3)
            THEN 当日分发曝光pv
          END
        ) as 新0_3VoV0,
        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,
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,
sum(0_1日分发回流uv)/sum(当日分发曝光pv) as rov1,
sum(0_7日分发回流uv)/sum(当日分发曝光pv) as rov7,
sum(0_30日分发回流uv)/sum(当日分发曝光pv) as rov30,
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曝光视频量,
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>=20251201 AND dt<=20270101 and 曝光rank <40 and 0 =0 and 0 =0 and 0 =0 AND ( 0 =0 OR  0 =0)
  group by dt,视频id,标题,merge二级品类,4,5,6,7,8,9
  order by dt desc,分发曝光pv desc
  LIMIT 50000;