WITH t_base AS 
(
    SELECT  apptype
            ,page
            ,dt
            ,hh
            ,mid
            ,vid
            ,share_cnt
            ,is_return_1
            ,is_return_n
            ,is_return_noself
            ,is_return_n_noself
            ,return_n_uv
            ,return_n_uv_noself
            ,"all" AS group_key
            ,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
)
,t_p_r AS 
(
    SELECT  apptype
            ,group_key
            ,return_n_uv_noself
            ,norXGBScore
            ,NTILE(100) OVER (PARTITION BY apptype ORDER BY norXGBScore ) AS bucket_id
    FROM    (
                SELECT  apptype
                        ,group_key
                        ,mid
                        ,vid
                        ,CAST(return_n_uv_noself AS BIGINT) AS return_n_uv_noself
                        ,CAST(GET_JSON_OBJECT(scoresmap,"$.NorXGBScore") AS DOUBLE) AS norXGBScore
                FROM    t_base
                WHERE   group_key = "all"
                AND     GET_JSON_OBJECT(scoresmap,"$.NorXGBScore") IS NOT NULL
                AND     return_n_uv_noself IS NOT NULL
                AND     CAST(return_n_uv_noself AS BIGINT) > 0
            ) 
    WHERE   norXGBScore > 0
)
,t_qq AS 
(
    SELECT  apptype
            ,group_key
            ,bucket_id
            ,ROUND(MIN(norXGBScore),6) AS range_begin
            ,ROUND(MAX(norXGBScore),6) AS range_end
            ,ROUND(AVG(norXGBScore),6) AS predict
            ,ROUND(AVG(return_n_uv_noself),6) AS label
            ,COUNT(1) AS cnt
    FROM    t_p_r
    GROUP BY apptype
             ,group_key
             ,bucket_id
)
SELECT  *
FROM    t_qq
ORDER BY apptype,group_key,bucket_id
LIMIT   1000
;