-- V3: 扩展特征分析
-- 增加page场景、曝光量分层等维度，支持更深入的归因分析

WITH sample_base AS (
    SELECT  vid
            ,page
            ,is_return_noself
            ,return_n_uv_noself
            ,new_exposure_cnt
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE) AS NorXGBScore
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) AS fmRov
    FROM    (
                SELECT  vid
                        ,page
                        ,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     apptype = '36'
                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")
            )
    WHERE   GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') IS NOT NULL
    AND     GET_JSON_OBJECT(scoresmap,'$.fmRov') IS NOT NULL
)
,vid_page_agg AS (
    -- 按视频+page聚合
    SELECT  vid
            ,page
            ,COUNT(1) AS sample_cnt
            ,SUM(new_exposure_cnt) AS total_exp
            -- 预估值
            ,AVG(1.22 * POW(NorXGBScore, 1.15)) AS pred_ros
            ,AVG(fmRov) AS pred_str
            -- 真实值
            ,SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0) AS real_ros
            ,SUM(is_return_noself) / NULLIF(SUM(new_exposure_cnt), 0) AS real_str
            -- 原始值用于后续计算
            ,SUM(return_n_uv_noself) AS total_return_uv
            ,SUM(is_return_noself) AS total_share_cnt
    FROM    sample_base
    GROUP BY vid, page
    HAVING  SUM(new_exposure_cnt) > 50  -- 至少50曝光
)
SELECT  vid
        ,page
        ,sample_cnt
        ,total_exp
        ,total_share_cnt
        ,total_return_uv
        -- 预估
        ,ROUND(pred_ros, 4) AS pred_ros
        ,ROUND(pred_str, 6) AS pred_str
        -- 真实
        ,ROUND(real_ros, 4) AS real_ros
        ,ROUND(real_str, 6) AS real_str
        -- 偏差
        ,ROUND((pred_ros - real_ros) / NULLIF(real_ros, 0) * 100, 2) AS ros_bias_pct
        ,ROUND((pred_str - real_str) / NULLIF(real_str, 0) * 100, 2) AS str_bias_pct
        -- 预估score和真实score
        ,ROUND(pred_ros * pred_str, 6) AS pred_score
        ,ROUND(real_ros * real_str, 6) AS real_score
FROM    vid_page_agg
WHERE   real_ros IS NOT NULL
AND     real_str IS NOT NULL
AND     real_ros > 0
AND     real_str > 0
ORDER BY total_exp DESC
LIMIT   2000
;
