-- 头部视频模型指标分析（以头部视频表为主，增加模型预估字段）
-- 使用 fetch_daily.py 按天获取，变量: ${dt}

-- 模型分数表：按 vid 聚合
WITH tab_model AS (
    SELECT  dt
            ,vid
            ,COUNT(1) AS sample_cnt
            -- 模型预估指标
            ,AVG(CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE)) AS pred_str
            ,AVG(CAST(GET_JSON_OBJECT(scoresmap,'$.fmRovOrigin') AS DOUBLE)) AS pred_str_origin
            ,AVG((0.059 * CAST(GET_JSON_OBJECT(scoresmap,'$.fmRovOrigin') AS DOUBLE)) / (1 - (1 - 0.059) * CAST(GET_JSON_OBJECT(scoresmap,'$.fmRovOrigin') AS DOUBLE))) AS pred_str_online
            ,AVG((0.036 * CAST(GET_JSON_OBJECT(scoresmap,'$.fmRovOrigin') AS DOUBLE)) / (1 - (1 - 0.036) * CAST(GET_JSON_OBJECT(scoresmap,'$.fmRovOrigin') AS DOUBLE))) AS pred_str_real
            ,AVG(1.22 * POW(CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE), 1.15)) AS pred_ros
            ,AVG(CAST(GET_JSON_OBJECT(scoresmap,'$.vor') AS DOUBLE)) AS pred_vor
            ,AVG(CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) * 1.22 * POW(CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE), 1.15) * CAST(GET_JSON_OBJECT(scoresmap,'$.vor') AS DOUBLE)) AS pred_vov
            -- 样本级真实指标
            ,SUM(is_return_noself) / COUNT(1) AS real_str
            ,SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0) AS real_ros
            ,SUM(return_n_uv_noself) / COUNT(1) AS real_rov
            ,SUM(new_exposure_cnt) / COUNT(1) AS real_vov
            ,SUM(new_exposure_cnt) / NULLIF(SUM(return_n_uv_noself), 0) AS real_vor
    FROM    (
                SELECT  dt
                        ,vid
                        ,is_return_noself
                        ,return_n_uv_noself
                        ,new_exposure_cnt
                        ,REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),'\\','') AS scoresmap
                FROM    loghubods.dwd_recsys_alg_sample_all_20250212
                WHERE   dt = '${dt}'
                AND     extend_alg IS NOT NULL
                AND     GET_JSON_OBJECT(extend_alg,'$.scoresMap') IS NOT NULL
                AND     page IN ('回流后沉浸页&内页feed','详情后沉浸页','首页feed','详情页')
                AND     abcode IN ('ab0','ab1','ab2','ab3','ab4','ab5','ab6','ab7','ab8','ab9')
                AND     abcode NOT IN ('ab100')
            ) t
    WHERE   GET_JSON_OBJECT(scoresmap,'$.fmRov') IS NOT NULL
    AND     GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') IS NOT NULL
    AND     GET_JSON_OBJECT(scoresmap,'$.vor') IS NOT NULL
    GROUP BY dt, vid
)

-- 主查询：头部视频表 LEFT JOIN 模型字段
SELECT
v.dt,
v.视频id,v.标题,v.merge二级品类,
sum(v.当日分发曝光pv) as 分发曝光pv,
sum(v.当日分发拉回曝光pv) as 分发拉回曝光pv,
sum(v.当日分发回流uv) AS 分发回流_当日,
sum(v.累计分享回流uv) AS 总回流uv,
sum(v.当日分发回流uv)/sum(v.当日分发曝光pv) as rov_t0,
sum(v.当日分发回流uv)/sum(v.当日分发分享pv)  as ros_t0,
sum(v.当日分发拉回曝光pv)/sum(v.当日分发曝光pv)  as vov0,
sum(v.0_1日分发拉回曝光pv)/sum(v.当日分发曝光pv)  as vov1,
sum(v.当日分发拉回曝光pv)/sum(v.当日分发回流uv)  as vor_t0,
sum(v.当日分发分享pv)/sum(v.当日分发曝光pv)  as str_t0,
-- ========== 模型预估字段（新增）==========
m.sample_cnt,
m.pred_str,
m.pred_str_origin,
m.pred_str_online,
m.pred_str_real,
m.pred_ros,
m.pred_vor,
m.pred_vov,
m.real_str,
m.real_ros,
m.real_rov,
m.real_vov,
m.real_vor,
-- ========== 原始头部视频字段（续）==========
AVG(v.视频时长) as 视频时长,
count(DISTINCT v.视频id) as 分发视频量,
count(DISTINCT if(v.是否当日新推荐>0,v.视频id,null)) as 新推荐视频量,
SUM(v.1008回流人数) / SUM(v.总回流uv) AS 群聊占比,
SUM(v.头部分享pv)/SUM(v.总分享pv) AS 头部分享占比,
SUM(v.当日分发头部分享pv)/SUM(v.当日分发曝光pv) AS 头部str_t0,
SUM(v.当日分发头部分享pv)/SUM(v.当日分发头部分享pv+v.当日分发分享pv) AS 当日分发头部分享占比,
sum(v.推荐回流)/sum(v.流量池曝光) AS 流量池曝光roi,
sum(v.流量池曝光) AS 流量池分发曝光,
sum(CASE WHEN v.推荐天数间隔 in (0,1,2,3) THEN v.当日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (0,1,2,3) THEN v.当日分发曝光pv END) as 新0_3VoV0,
avg(v.曝光rank)-avg(v.回流rank) AS rankdiff,
avg(v.回流rank) as 回流rank_avg,
avg(v.曝光rank) as 曝光rank_avg,
sum(v.流量池回流)/sum(v.流量池曝光) AS 流量池rov,
sum(v.流量池回流)/sum(v.流量池分享) AS 流量池ros,
sum(v.流量池分享)/sum(v.流量池曝光) AS 流量池str,
sum(v.推荐回流)/sum(v.推荐曝光) AS 推荐rov,
sum(v.推荐回流)/sum(v.推荐分享) AS 推荐ros,
sum(v.推荐分享)/sum(v.推荐曝光) AS 推荐str,
(SUM(v.带来1007回流的分享数)+SUM(v.带来1008回流的分享数))/SUM(v.总分享pv) AS 有效分享率,
sum(CASE WHEN v.推荐天数间隔 in (0,1,2,3,4,5,6,7) THEN v.0_1日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (0,1,2,3,4,5,6,7) THEN v.当日分发曝光pv END) as 新0_7VoV1,
sum(CASE WHEN v.推荐天数间隔 in (0,1,2,3,4,5,6,7) THEN v.当日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (0,1,2,3,4,5,6,7) THEN v.当日分发曝光pv END) as 新0_7VoV0,
sum(CASE WHEN v.推荐天数间隔 in (0,1,2,3,4,5,6,7) THEN v.当日分发曝光pv END)/sum(v.当日分发曝光pv) as 新0_7曝光占比,
sum(CASE WHEN v.推荐天数间隔 in (0,1,2,3) THEN v.0_1日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (0,1,2,3) THEN v.当日分发曝光pv END) as 新0_3VoV1,
sum(CASE WHEN v.推荐天数间隔 in (0,1,2,3) THEN v.当日分发曝光pv END)/sum(v.当日分发曝光pv) as 新0_3曝光占比,
sum(CASE WHEN v.推荐天数间隔 in (1,2,3) THEN v.0_1日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (1,2,3) THEN v.当日分发曝光pv END) as 新1_3VoV1,
sum(CASE WHEN v.推荐天数间隔 in (1,2,3) THEN v.当日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (1,2,3) THEN v.当日分发曝光pv END) as 新1_3VoV0,
sum(CASE WHEN v.推荐天数间隔 in (0) THEN v.0_1日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (0) THEN v.当日分发曝光pv END) as 新0VoV1,
sum(CASE WHEN v.推荐天数间隔 in (0) THEN v.当日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (0) THEN v.当日分发曝光pv END) as 新0VoV0,
sum(CASE WHEN v.推荐天数间隔 in (0) THEN v.当日分发曝光pv END)/sum(v.当日分发曝光pv) as 新0曝光占比,
count(DISTINCT if(v.推荐天数间隔=1,v.视频id,null)) as 新1视频量,
sum(CASE WHEN v.推荐天数间隔 in (1) THEN v.0_1日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (1) THEN v.当日分发曝光pv END) as 新1VoV1,
sum(CASE WHEN v.推荐天数间隔 in (1) THEN v.当日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (1) THEN v.当日分发曝光pv END) as 新1VoV0,
sum(CASE WHEN v.推荐天数间隔 in (1) THEN v.当日分发曝光pv END)/sum(v.当日分发曝光pv) as 新1曝光占比,
count(DISTINCT if(v.推荐天数间隔=2,v.视频id,null)) as 新2视频量,
sum(CASE WHEN v.推荐天数间隔 in (2) THEN v.0_1日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (2) THEN v.当日分发曝光pv END) as 新2VoV1,
sum(CASE WHEN v.推荐天数间隔 in (2) THEN v.当日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (2) THEN v.当日分发曝光pv END) as 新2VoV0,
sum(CASE WHEN v.推荐天数间隔 in (2) THEN v.当日分发曝光pv END)/sum(v.当日分发曝光pv) as 新2曝光占比,
count(DISTINCT if(v.推荐天数间隔=3,v.视频id,null)) as 新3视频量,
sum(CASE WHEN v.推荐天数间隔 in (3) THEN v.0_1日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (3) THEN v.当日分发曝光pv END) as 新3VoV1,
sum(CASE WHEN v.推荐天数间隔 in (3) THEN v.当日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 in (3) THEN v.当日分发曝光pv END) as 新3VoV0,
sum(CASE WHEN v.推荐天数间隔 in (3) THEN v.当日分发曝光pv END)/sum(v.当日分发曝光pv) as 新3曝光占比,
sum(CASE WHEN v.推荐天数间隔 not in (0,1,2,3,4,5,6,7) THEN v.0_1日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 not in (0,1,2,3,4,5,6,7) THEN v.当日分发曝光pv END) as 非0_7_VoV1,
sum(CASE WHEN v.推荐天数间隔 not in (0,1,2,3,4,5,6,7) THEN v.当日分发拉回曝光pv END)/sum(CASE WHEN v.推荐天数间隔 not in (0,1,2,3,4,5,6,7) THEN v.当日分发曝光pv END) as 非0_7_VoV0,
sum(v.0_2日分发拉回曝光pv)/sum(v.当日分发曝光pv) as vov2,
sum(v.0_7日分发拉回曝光pv)/sum(v.当日分发曝光pv) as vov7,
sum(v.0_30日分发拉回曝光pv)/sum(v.当日分发曝光pv) as vov30,
(sum(v.0_1日分发拉回曝光pv)/sum(v.当日分发曝光pv))-(sum(v.当日分发拉回曝光pv)/sum(v.当日分发曝光pv)) as vov1减vov0,
sum(v.当日分发回流uv) as 分发回流uv,
sum(v.当日分发分享pv) as 分发分享pv,
SUM(v.1008回流人数)/ SUM(v.带来1008回流的分享数) AS 群聊ros,
SUM(v.1007回流人数)/ SUM(v.带来1007回流的分享数) AS 单聊ros,
SUM(v.1007进入分发曝光pv)/SUM(v.1007回流人数) AS 单聊vor,
SUM(v.1008进入分发曝光pv)/SUM(v.1008回流人数) AS 群聊vor,
(SUM(v.1007回流再分享pv)+ SUM(v.1008回流再分享pv))/(SUM(v.1007进入分发曝光pv)+ SUM(v.1008进入分发曝光pv)) AS 回流后str,
(SUM(v.1008回流再分享pv))/(SUM(v.1008进入分发曝光pv)) AS 群聊后str,
(SUM(v.1007回流再分享pv))/(SUM(v.1007进入分发曝光pv)) AS 单聊后str,
SUM(v.总回流uv)/SUM(v.累计分享回流uv) AS 当日分享回流占比,
SUM(v.当日分享当日回流首层uv)/SUM(v.当日分享当日回流uv) AS 当日分享当日回流首层比当日分享当日回流,
count(DISTINCT if(v.是否七日内创建>0,v.视频id,null)) as 七日内新视频量,
count(DISTINCT if(v.是否首发视频>0,v.视频id,null)) as 首发视频量,
count(DISTINCT if(v.是否首发视频>0,v.视频id,null))/count(DISTINCT v.视频id) as 首发视频比例,
count(DISTINCT v.站内uid) as 供给uid量,
AVG(v.首发距今时间) as 首发距今间隔avg,
AVG(v.推荐天数间隔) as 推荐距今间隔avg,
AVG(v.创建天数间隔) as 创建距今间隔avg,
sum(v.0_1日分发拉回曝光pv) as 0_1日分发拉回曝光pv,
sum(v.0_2日分发拉回曝光pv) as 0_2日分发拉回曝光pv,
sum(v.0_3日分发拉回曝光pv) as 0_3日分发拉回曝光pv,
sum(v.0_7日分发拉回曝光pv) as 0_7日分发拉回曝光pv,
sum(v.0_30日分发拉回曝光pv) as 0_30日分发拉回曝光pv,
sum(v.0_1日分发回流uv)/sum(v.当日分发曝光pv) as rov1,
sum(v.0_7日分发回流uv)/sum(v.当日分发曝光pv) as rov7,
sum(v.0_30日分发回流uv)/sum(v.当日分发曝光pv) as rov30,
sum(v.0_1日分发拉回曝光pv)/sum(v.0_1日分发回流uv) as vor1,
sum(v.0_7日分发拉回曝光pv)/sum(v.0_7日分发回流uv) as vor7,
sum(v.0_30日分发拉回曝光pv)/sum(v.0_30日分发回流uv) as vor30,
sum(v.流量池曝光) AS 流量池曝光,
sum(v.流量池播放) AS 流量池播放,
sum(v.流量池分享) AS 流量池分享,
sum(v.流量池回流) AS 流量池回流,
sum(v.推荐曝光) AS 推荐曝光,
sum(v.推荐播放) AS 推荐播放,
sum(v.推荐分享) AS 推荐分享,
sum(v.推荐回流) AS 推荐回流,
SUM(v.总分享pv) AS 总分享pv,
SUM(v.总回流uv) AS 总回流uv,
SUM(v.1007回流人数) AS 1007回流人数,
SUM(v.1008回流人数) AS 1008回流人数,
SUM(v.带来1007回流的分享数) AS 带来1007回流的分享数,
SUM(v.带来1008回流的分享数) AS 带来1008回流的分享数,
SUM(v.1007进入分发曝光pv) AS 1007进入分发曝光pv,
SUM(v.1008进入分发曝光pv) AS 1008进入分发曝光pv,
SUM(v.1007回流再分享pv) AS 1007回流再分享pv,
SUM(v.1008回流再分享pv) AS 1008回流再分享pv,
SUM(v.有回流分享pv) AS 有回流分享pv,
SUM(v.累计分享回流uv) AS 累计分享回流uv,
SUM(v.分发分享pv) AS 分发分享pv2,
SUM(v.头部分享pv) AS 头部分享pv,
SUM(v.当日分发头部分享pv) AS 当日分发头部分享pv,
SUM(v.当日分享当日回流uv) AS 当日分享当日回流uv,
SUM(v.当日分享当日回流首层uv) AS 当日分享当日回流首层uv,
SUM(v.当日分享当日回流非首层uv) AS 当日分享当日回流非首层uv,
SUM(v.非当日分享回流uv) AS 非当日分享回流uv,
SUM(v.n当日分发回流uv) AS n当日分发回流uv,
SUM(v.非当日分发回流uv) AS 非当日分发回流uv,
count(DISTINCT if(v.当日分发曝光pv>=100,v.视频id,null)) as t0_100曝光视频量,
count(DISTINCT if(v.当日分发曝光pv>=500,v.视频id,null)) as t0_500曝光视频量,
count(DISTINCT if(v.当日分发曝光pv>=1000,v.视频id,null)) as t0_1k曝光视频量,
count(DISTINCT if(v.当日分发曝光pv>=10000,v.视频id,null)) as t0_1w曝光视频量,
count(DISTINCT if((v.0_1日分发拉回曝光pv)/(v.当日分发曝光pv)-(v.当日分发拉回曝光pv)/(v.当日分发曝光pv)>=0.2 and v.当日分发曝光pv>=500,v.视频id,null)) as vov1_0_02_500视频量,
count(DISTINCT if((v.0_1日分发拉回曝光pv)/(v.当日分发曝光pv)-(v.当日分发拉回曝光pv)/(v.当日分发曝光pv)>=0.2 and v.当日分发曝光pv>=500,v.视频id,null))/count(DISTINCT if(v.当日分发曝光pv>=500,v.视频id,null)) as vov1_0_02_500视频占比,
count(DISTINCT if((v.当日分发拉回曝光pv)/(v.当日分发曝光pv)>=0.4 and v.当日分发曝光pv>=500,v.视频id,null)) as vov0_04_500视频量,
count(DISTINCT if((v.当日分发拉回曝光pv)/(v.当日分发曝光pv)>=0.4 and v.当日分发曝光pv>=500,v.视频id,null))/count(DISTINCT if(v.当日分发曝光pv>=500,v.视频id,null)) as vov0_04_500视频占比,
count(DISTINCT if((v.0_1日分发拉回曝光pv)/(v.当日分发曝光pv)>=0.7 and v.当日分发曝光pv>=500,v.视频id,null)) as vov1_07_500视频量,
count(DISTINCT if((v.0_1日分发拉回曝光pv)/(v.当日分发曝光pv)>=0.7 and v.当日分发曝光pv>=500,v.视频id,null))/count(DISTINCT if(v.当日分发曝光pv>=500,v.视频id,null)) as vov1_07_500视频占比,
count(DISTINCT if((v.0_1日分发拉回曝光pv)/(v.当日分发曝光pv)>=0.8 and v.当日分发曝光pv>=500,v.视频id,null)) as vov1_08_500视频量,
count(DISTINCT if((v.0_1日分发拉回曝光pv)/(v.当日分发曝光pv)>=0.8 and v.当日分发曝光pv>=500,v.视频id,null))/count(DISTINCT if(v.当日分发曝光pv>=500,v.视频id,null)) as vov1_08_500视频占比,
count(DISTINCT if(v.当日分发拉回曝光pv>=500,v.视频id,null)) as t0_500拉回曝光视频量,
count(DISTINCT if(v.0_1日分发拉回曝光pv>=500,v.视频id,null)) as t1_500拉回曝光视频量,
count(DISTINCT if(v.当日分发拉回曝光pv>=10000,v.视频id,null)) as t0_1w拉回曝光视频量,
count(DISTINCT if(v.0_1日分发拉回曝光pv>=10000,v.视频id,null)) as t1_1w拉回曝光视频量,
count(DISTINCT if(v.当日分发拉回曝光pv>=100000,v.视频id,null)) as t0_10w拉回曝光视频量,
count(DISTINCT if(v.0_1日分发拉回曝光pv>=100000,v.视频id,null)) as t1_10w拉回曝光视频量,
count(DISTINCT if(v.当日分发拉回曝光pv>=1000000,v.视频id,null)) as t0_100w拉回曝光视频量,
count(DISTINCT if(v.0_1日分发拉回曝光pv>=1000000,v.视频id,null)) as t1_100w拉回曝光视频量,
(SUM(v.带来流量池1007回流的分享数)+SUM(v.带来流量池1008回流的分享数))/SUM(v.带来流量池回流的分享数) AS 流量池有效分享率,
SUM(v.流量池1008回流人数) / SUM(v.流量池回流人数) AS 流量池群聊占比

FROM loghubods.video_dimension_detail_add_column v
LEFT JOIN tab_model m
ON v.dt = m.dt AND v.视频id = m.vid
WHERE v.dt = '${dt}' AND v.曝光rank < 40
GROUP BY m.sample_cnt, m.pred_str, m.pred_str_origin, m.pred_str_online, m.pred_str_real, m.pred_ros, m.pred_vor, m.pred_vov,
         m.real_str, m.real_ros, m.real_rov, m.real_vov, m.real_vor,
         v.dt, v.视频id, v.标题, v.merge二级品类
ORDER BY 分发曝光pv DESC
;
