-- Step3: 分析原因
-- 对比模型预估值（str/ros/vor）与真实值，找出偏差来源
-- 使用 ${dt} 变量，配合 fetch_daily.py 并发获取

WITH tab_base AS
(
    SELECT  dt
            ,vid
            ,is_return_noself
            ,return_n_uv_noself
            ,new_exposure_cnt
            ,CAST(score AS DOUBLE) AS score
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) AS fmRov
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE) AS NorXGBScore
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.vor') AS DOUBLE) AS vor
    FROM    (
                SELECT  dt
                        ,vid
                        ,is_return_noself
                        ,return_n_uv_noself
                        ,new_exposure_cnt
                        ,score
                        ,REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\","") AS scoresmap
                FROM    loghubods.dwd_recsys_alg_sample_all_20250212
                WHERE   dt = '${dt}'
                AND     apptype = '36'
                -- 选取问题视频
                AND     vid IN ('62421458','63497994','63444428','62955809','55931081','63535473','63214561','63378562','60237250','62875331')
                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,'$.fmRov') IS NOT NULL
    AND     GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') IS NOT NULL
    AND     GET_JSON_OBJECT(scoresmap,'$.vor') IS NOT NULL
)
SELECT  vid
        ,dt
        ,COUNT(1) AS sample_cnt
        -- 预估值
        ,ROUND(AVG(score), 6) AS avg_score
        ,ROUND(AVG(fmRov), 6) AS pred_str
        ,ROUND(AVG(1.22 * POW(NorXGBScore, 1.15)), 6) AS pred_ros
        ,ROUND(AVG(vor), 6) AS pred_vor
        -- 真实值
        ,ROUND(SUM(is_return_noself) / COUNT(1), 6) AS real_str
        ,ROUND(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0), 6) AS real_ros
        ,ROUND(SUM(new_exposure_cnt) / NULLIF(SUM(return_n_uv_noself), 0), 6) AS real_vor
        ,ROUND(SUM(new_exposure_cnt) / COUNT(1), 6) AS real_vov
FROM    tab_base
GROUP BY vid, dt
ORDER BY vid, dt
;
