-- 预处理：解析 scoresmap + page 分类
WITH t_raw AS
(
    SELECT  *
            ,REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\","") AS scoresmap
            ,CASE   WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                    WHEN page IN ("回流页","其他") THEN "非推荐"
                    ELSE "其他"
            END AS page_type
    FROM    loghubods.dwd_recsys_alg_sample_all_20250212
    WHERE   dt = '${dt}'
    AND     apptype IN ("0","4")
    AND     abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9")
    AND     abcode NOT IN ("ab100")
    AND     extend_alg IS NOT NULL
    AND     GET_JSON_OBJECT(extend_alg,'$.scoresMap') IS NOT NULL
)
-- 过滤：只保留推荐页面
,t_filtered AS
(
    SELECT  *
    FROM    t_raw
    WHERE   page_type = "推荐"
)
-- 特征提取与维度映射
,t_base AS
(
    SELECT  dt
            ,apptype
            ,CASE   WHEN apptype IN ("4") AND abcode IN ("ab0","ab1") THEN "实验组-先验地域降权"
                    WHEN apptype IN ("4") AND abcode IN ("ab6","ab7") THEN "实验组-str+校准&ros-统计量"
                    WHEN apptype IN ("4") AND abcode IN ("ab8","ab9") THEN "实验组-str+校准"
                    WHEN apptype IN ("4") AND abcode IN ("ab2","ab3","ab4","ab5") THEN "对照组"
                    ELSE "其他"
            END AS abcode
            ,mid
            ,vid
            ,is_return_noself
            ,return_n_uv_noself
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) AS str_pred
            ,1.22 * pow(CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE), 1.15) AS ros_pred
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.hasReturnRovScore') AS DOUBLE) AS ros_stat
    FROM    t_filtered
)
-- 按 ros_pred 分桶
,t_bucket AS
(
    SELECT  *
            ,NTILE(10) OVER (PARTITION BY dt, apptype, abcode ORDER BY ros_pred) AS ros_pred_bucket
            ,NTILE(10) OVER (PARTITION BY dt, apptype, abcode ORDER BY ros_stat) AS ros_stat_bucket
    FROM    t_base
)
-- 按桶聚合
SELECT  dt
        ,apptype
        ,abcode
        ,ros_pred_bucket AS bucket
        ,'ros_pred' AS bucket_type
        ,COUNT(1) AS sample_cnt
        ,SUM(is_return_noself) AS return_cnt
        ,round(SUM(is_return_noself) / COUNT(1), 6) AS str_real
        ,round(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0), 4) AS ros_real
        ,round(AVG(ros_pred), 4) AS ros_pred_avg
        ,round(AVG(ros_stat), 4) AS ros_stat_avg
FROM    t_bucket
GROUP BY dt, apptype, abcode, ros_pred_bucket
UNION ALL
SELECT  dt
        ,apptype
        ,abcode
        ,ros_stat_bucket AS bucket
        ,'ros_stat' AS bucket_type
        ,COUNT(1) AS sample_cnt
        ,SUM(is_return_noself) AS return_cnt
        ,round(SUM(is_return_noself) / COUNT(1), 6) AS str_real
        ,round(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0), 4) AS ros_real
        ,round(AVG(ros_pred), 4) AS ros_pred_avg
        ,round(AVG(ros_stat), 4) AS ros_stat_avg
FROM    t_bucket
GROUP BY dt, apptype, abcode, ros_stat_bucket
ORDER BY dt DESC, apptype, abcode, bucket_type, bucket
;
