-- ROSN G-C-Index 指标计算（简化版）
-- 只计算实验组粒度的 GAUC 类指标：
--   str_gauc: 分享率预测的 GAUC（二分类，标签=is_return_noself>0）
--   rov_gauc: ROV 预测的 GAUC（二分类，预测=str*rosn）
--   rosn_gcindex: ROSN 预测的 G-C-Index（连续值，仅在有回流样本中计算）
--   rosn_stat_gcindex: ROSN 统计量的 G-C-Index（连续值，仅在有回流样本中计算）

-- ========== 预处理：解析 scoresmap ==========
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") THEN "对照组"
                    WHEN apptype IN ("4") AND abcode IN ("ab4","ab5") THEN "ab4-5"
                    ELSE "其他"
            END AS abcode
            ,mid
            ,vid
            ,is_return_noself
            ,return_n_uv_noself
            ,scoresmap
            ,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 rosn_pred
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.hasReturnRovScore') AS DOUBLE) AS rosn_stat
    FROM    t_filtered
)
,t_valid AS
(
    SELECT  *
    FROM    t_base
    WHERE   str_pred IS NOT NULL
    AND     rosn_pred IS NOT NULL
)

-- ========== str_gauc & rov_gauc（二分类 GAUC）==========
-- 1. 添加用户维度排名
,t_with_rank AS
(
    SELECT  *
            ,str_pred * rosn_pred AS rov_pred
            ,str_pred * rosn_stat AS rov_stat_pred
            ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, abcode, mid ORDER BY str_pred) AS str_rank_user
            ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, abcode, mid ORDER BY str_pred * rosn_pred) AS rov_rank_user
            ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, abcode, mid ORDER BY str_pred * rosn_stat) AS rov_stat_rank_user
    FROM    t_valid
)
-- 2. 用户维度 AUC（str + rov）
,t_user_auc AS
(
    SELECT  dt, apptype, abcode, mid
            ,COUNT(1) AS user_exp
            ,SUM(CASE WHEN is_return_noself > 0 THEN 1 ELSE 0 END) AS user_n_pos
            ,SUM(CASE WHEN is_return_noself > 0 THEN str_rank_user ELSE 0 END) AS user_sum_str_pos_rank
            ,SUM(CASE WHEN is_return_noself > 0 THEN rov_rank_user ELSE 0 END) AS user_sum_rov_pos_rank
            ,SUM(CASE WHEN is_return_noself > 0 THEN rov_stat_rank_user ELSE 0 END) AS user_sum_rov_stat_pos_rank
    FROM    t_with_rank
    GROUP BY dt, apptype, abcode, mid
)
,t_user_auc_valid AS
(
    SELECT  *
            ,CASE
                WHEN user_exp < 5 OR user_n_pos = 0 OR user_n_pos = user_exp THEN NULL
                ELSE GREATEST(0.0, LEAST(1.0,
                    (user_sum_str_pos_rank * 1.0 / user_n_pos - (user_n_pos + 1) / 2.0) / (user_exp - user_n_pos)
                ))
            END AS user_str_auc
            ,CASE
                WHEN user_exp < 5 OR user_n_pos = 0 OR user_n_pos = user_exp THEN NULL
                ELSE GREATEST(0.0, LEAST(1.0,
                    (user_sum_rov_pos_rank * 1.0 / user_n_pos - (user_n_pos + 1) / 2.0) / (user_exp - user_n_pos)
                ))
            END AS user_rov_auc
            ,CASE
                WHEN user_exp < 5 OR user_n_pos = 0 OR user_n_pos = user_exp THEN NULL
                ELSE GREATEST(0.0, LEAST(1.0,
                    (user_sum_rov_stat_pos_rank * 1.0 / user_n_pos - (user_n_pos + 1) / 2.0) / (user_exp - user_n_pos)
                ))
            END AS user_rov_stat_auc
    FROM    t_user_auc
)
,t_gauc AS
(
    SELECT  dt, apptype, abcode
            ,round(SUM(user_exp * user_str_auc) / NULLIF(SUM(CASE WHEN user_str_auc IS NOT NULL THEN user_exp END), 0), 6) AS str_gauc
            ,round(SUM(user_exp * user_rov_auc) / NULLIF(SUM(CASE WHEN user_rov_auc IS NOT NULL THEN user_exp END), 0), 6) AS rov_gauc
            ,round(SUM(user_exp * user_rov_stat_auc) / NULLIF(SUM(CASE WHEN user_rov_stat_auc IS NOT NULL THEN user_exp END), 0), 6) AS rov_stat_gauc
    FROM    t_user_auc_valid
    GROUP BY dt, apptype, abcode
)

-- ========== rosn_gcindex（连续值 C-Index，仅有回流样本）==========
-- 筛选有回流的样本
,t_has_return AS
(
    SELECT  *
    FROM    t_valid
    WHERE   is_return_noself > 0
)
-- 1. 用户维度 self-join 计算样本对
-- 只比较 return_n_uv_noself 不同的样本对（a > b）
,t_user_pairs AS
(
    SELECT  a.dt, a.apptype, a.abcode, a.mid
            ,CASE WHEN a.rosn_pred > b.rosn_pred THEN 1.0
                  WHEN a.rosn_pred = b.rosn_pred THEN 0.5
                  ELSE 0.0 END AS concordant_pred
            ,CASE WHEN a.rosn_stat > b.rosn_stat THEN 1.0
                  WHEN a.rosn_stat = b.rosn_stat THEN 0.5
                  ELSE 0.0 END AS concordant_stat
    FROM    t_has_return a
    JOIN    t_has_return b
    ON      a.dt = b.dt AND a.apptype = b.apptype AND a.abcode = b.abcode
    AND     a.mid = b.mid
    AND     a.return_n_uv_noself > b.return_n_uv_noself
)
-- 2. 用户维度 C-Index
,t_user_cindex AS
(
    SELECT  dt, apptype, abcode, mid
            ,COUNT(1) AS user_pairs
            ,AVG(concordant_pred) AS user_cindex_pred
            ,AVG(concordant_stat) AS user_cindex_stat
    FROM    t_user_pairs
    GROUP BY dt, apptype, abcode, mid
    HAVING  COUNT(1) >= 3
)
-- 3. 按样本对数加权平均
,t_rosn_gcindex AS
(
    SELECT  dt, apptype, abcode
            ,round(SUM(user_pairs * user_cindex_pred) / SUM(user_pairs), 6) AS rosn_gcindex
            ,round(SUM(user_pairs * user_cindex_stat) / SUM(user_pairs), 6) AS rosn_stat_gcindex
    FROM    t_user_cindex
    GROUP BY dt, apptype, abcode
)

-- ========== 最终输出 ==========
SELECT  a.dt
        ,a.apptype
        ,a.abcode
        ,a.str_gauc
        ,a.rov_gauc
        ,a.rov_stat_gauc
        ,b.rosn_gcindex
        ,b.rosn_stat_gcindex
FROM    t_gauc a
LEFT JOIN t_rosn_gcindex b
ON      a.dt = b.dt AND a.apptype = b.apptype AND a.abcode = b.abcode
ORDER BY dt DESC, apptype, abcode
;
