-- Step5: 分析问题从什么时候开始
-- 拉更长时间的数据，看 ROS 偏差的时间趋势

WITH tab_base AS
(
    SELECT  dt
            ,vid
            ,is_return_noself
            ,return_n_uv_noself
            ,new_exposure_cnt
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE) AS NorXGBScore
    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     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
)
SELECT  dt
        ,COUNT(DISTINCT vid) AS vid_cnt
        ,COUNT(1) AS sample_cnt
        -- 预估 vs 真实
        ,ROUND(AVG(1.22 * POW(NorXGBScore, 1.15)), 4) AS pred_ros
        ,ROUND(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0), 4) AS real_ros
        -- COPC
        ,ROUND(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0) / AVG(1.22 * POW(NorXGBScore, 1.15)), 4) AS ros_copc
FROM    tab_base
GROUP BY dt
;
