-- 以 label(return_n_uv_noself) 作为分桶维度，观察不同实验组的 predict/stat 分布
WITH t_raw AS
(
    SELECT  apptype
            ,abcode
            ,dt
            ,mid
            ,vid
            ,CAST(return_n_uv_noself AS BIGINT) AS return_n_uv_noself
            ,REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),'\\','') AS scoresmap
    FROM    dwd_recsys_alg_sample_all_20250212
    WHERE   dt = '${dt}'
    AND     apptype IN ('0', '4')
    AND     abcode NOT IN ('ab100')
    AND     page IN ('回流后沉浸页&内页feed','详情后沉浸页','首页feed','详情页','回流页','其他')
    AND     extend_alg IS NOT NULL
    AND     GET_JSON_OBJECT(extend_alg,'$.scoresMap') IS NOT NULL
    AND     return_n_uv_noself IS NOT NULL
    AND     CAST(return_n_uv_noself AS BIGINT) > 0
)
,t_base AS
(
    SELECT  apptype
            ,abcode
            ,dt
            ,mid
            ,vid
            ,return_n_uv_noself
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE) AS norXGBScore
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.hasReturnRovScore') AS DOUBLE) AS rosn_stat
    FROM    t_raw
    WHERE   GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') IS NOT NULL
)
-- 展开 group_key: "all" + 实验组分组
,t_group AS
(
    SELECT  apptype, dt, mid, vid, return_n_uv_noself, norXGBScore, rosn_stat
            ,'all' AS group_key
    FROM    t_base
    UNION ALL
    SELECT  apptype, dt, mid, vid, return_n_uv_noself, norXGBScore, rosn_stat
            ,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') THEN '对照组'
                    WHEN apptype IN ('4') AND abcode IN ('ab4','ab5') THEN 'ab4-5'
                    ELSE '其他'
            END AS group_key
    FROM    t_base
)
-- 按 label 分桶
,t_bucket AS
(
    SELECT  apptype, dt, group_key, return_n_uv_noself, norXGBScore, rosn_stat
            ,NTILE(100) OVER (PARTITION BY apptype, group_key ORDER BY return_n_uv_noself) AS bucket_id
    FROM    t_group
)
SELECT  apptype
        ,dt
        ,group_key
        ,'label' AS bucket_type
        ,bucket_id
        ,ROUND(MIN(return_n_uv_noself), 6) AS range_begin
        ,ROUND(MAX(return_n_uv_noself), 6) AS range_end
        ,ROUND(AVG(norXGBScore), 6) AS predict
        ,ROUND(AVG(rosn_stat), 6) AS stat
        ,ROUND(AVG(return_n_uv_noself), 6) AS label
        ,COUNT(1) AS cnt
FROM    t_bucket
GROUP BY apptype
         ,dt
         ,group_key
         ,bucket_id
ORDER BY apptype, group_key, bucket_id
LIMIT   10000
;
