-- ROSN G-C-Index 指标计算（v2）
--
-- 过滤条件：
--   原始版本：user_exp >= 5 且正负样本都存在
--   新版本：user_pairs >= 3（样本对数 = n_pos × n_neg）
--
-- 加权方式后缀：
--   无后缀: 原始版本（exp>=5 + 曝光量加权）
--   _exp: 曝光量加权（业务影响）
--   _pairs: 样本对数加权（统计最优）
--   _equal: 等权（用户公平）
--
-- 指标说明：
--   str_gauc: 原始版本（与 03_metrics 一致）
--   str_gauc_*: 分享率预测的 GAUC（全量样本，标签=is_return_noself>0）
--   str_gcindex: str_gauc_pairs 的 self-join 验证版本
--   rosn_gcindex: ROSN 预测的 G-C-Index（仅有回流样本，标签=return_n_uv_noself）
--   rosn_stat_gcindex: ROSN 统计量的 G-C-Index
--   rov_gauc_*: ROV 预测的 GAUC（全量样本，预测=str×rosn）
--   rov_stat_gauc_*: ROV 统计量预测的 GAUC

-- ========== 预处理：解析 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  *
            ,str_pred * rosn_pred AS rov_pred
            ,str_pred * rosn_stat AS rov_stat_pred
    FROM    t_base
    WHERE   str_pred IS NOT NULL
    AND     rosn_pred IS NOT NULL
)

-- ========== 1. str_gauc（秩和公式）==========
,t_with_rank AS
(
    SELECT  *
            ,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 rov_pred) AS rov_rank_user
            ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, abcode, mid ORDER BY rov_stat_pred) AS rov_stat_rank_user
    FROM    t_valid
)
,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  *
            ,user_n_pos * (user_exp - user_n_pos) AS user_pairs  -- 样本对数 = n_pos * n_neg
            -- 原始过滤条件：user_exp >= 5 且正负样本都存在
            ,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_orig
            -- 统一过滤条件：样本对数 >= 3
            ,CASE
                WHEN user_n_pos * (user_exp - user_n_pos) < 3 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_n_pos * (user_exp - user_n_pos) < 3 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_n_pos * (user_exp - user_n_pos) < 3 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
            -- str_gauc 原始版本（与 03_metrics 一致）
            ,round(SUM(user_exp * user_str_auc_orig) / NULLIF(SUM(CASE WHEN user_str_auc_orig IS NOT NULL THEN user_exp END), 0), 6) AS str_gauc
            -- str: 三种加权方式
            ,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_exp
            ,round(SUM(user_pairs * user_str_auc) / NULLIF(SUM(CASE WHEN user_str_auc IS NOT NULL THEN user_pairs END), 0), 6) AS str_gauc_pairs
            ,round(AVG(user_str_auc), 6) AS str_gauc_equal
            -- rov: 三种加权方式
            ,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_exp
            ,round(SUM(user_pairs * user_rov_auc) / NULLIF(SUM(CASE WHEN user_rov_auc IS NOT NULL THEN user_pairs END), 0), 6) AS rov_gauc_pairs
            ,round(AVG(user_rov_auc), 6) AS rov_gauc_equal
            -- rov_stat: 三种加权方式
            ,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_exp
            ,round(SUM(user_pairs * user_rov_stat_auc) / NULLIF(SUM(CASE WHEN user_rov_stat_auc IS NOT NULL THEN user_pairs END), 0), 6) AS rov_stat_gauc_pairs
            ,round(AVG(user_rov_stat_auc), 6) AS rov_stat_gauc_equal
    FROM    t_user_auc_valid
    GROUP BY dt, apptype, abcode
)

-- ========== 2. rosn_gauc（仅有回流样本，连续值标签）==========
,t_has_return AS
(
    SELECT  *
    FROM    t_valid
    WHERE   is_return_noself > 0
)
,t_rosn_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
)
,t_user_rosn_exp AS
(
    SELECT  dt, apptype, abcode, mid
            ,COUNT(1) AS user_exp  -- 有回流的样本数
    FROM    t_has_return
    GROUP BY dt, apptype, abcode, mid
)
,t_user_rosn_cindex AS
(
    SELECT  a.dt, a.apptype, a.abcode, a.mid
            ,b.user_exp
            ,COUNT(1) AS user_pairs
            ,AVG(concordant_pred) AS user_cindex_pred
            ,AVG(concordant_stat) AS user_cindex_stat
    FROM    t_rosn_pairs a
    JOIN    t_user_rosn_exp b
    ON      a.dt = b.dt AND a.apptype = b.apptype AND a.abcode = b.abcode AND a.mid = b.mid
    GROUP BY a.dt, a.apptype, a.abcode, a.mid, b.user_exp
    HAVING  COUNT(1) >= 3
)
,t_rosn_gcindex AS
(
    SELECT  dt, apptype, abcode
            -- rosn: 三种加权方式
            ,round(SUM(user_exp * user_cindex_pred) / NULLIF(SUM(CASE WHEN user_cindex_pred IS NOT NULL THEN user_exp END), 0), 6) AS `rosn_gauc_曝光`
            ,round(SUM(user_pairs * user_cindex_pred) / SUM(user_pairs), 6) AS `rosn_gauc_样本对`
            ,round(AVG(user_cindex_pred), 6) AS `rosn_gauc_等权`
            -- rosn_stat: 三种加权方式
            ,round(SUM(user_exp * user_cindex_stat) / NULLIF(SUM(CASE WHEN user_cindex_stat IS NOT NULL THEN user_exp END), 0), 6) AS `rosn_stat_gauc_曝光`
            ,round(SUM(user_pairs * user_cindex_stat) / SUM(user_pairs), 6) AS `rosn_stat_gauc_样本对`
            ,round(AVG(user_cindex_stat), 6) AS `rosn_stat_gauc_等权`
    FROM    t_user_rosn_cindex
    GROUP BY dt, apptype, abcode
)

-- ========== 最终输出 ==========
SELECT  a.dt
        ,a.apptype
        ,a.abcode
        -- str gauc（三种加权方式）
        ,a.str_gauc AS str_gauc_曝光
        ,a.str_gauc_pairs AS str_gauc_样本对
        ,a.str_gauc_equal AS str_gauc_等权
        -- rosn gauc（仅有回流样本，三种加权方式）
        ,c.`rosn_gauc_曝光`
        ,c.`rosn_gauc_样本对`
        ,c.`rosn_gauc_等权`
        ,c.`rosn_stat_gauc_曝光`
        ,c.`rosn_stat_gauc_样本对`
        ,c.`rosn_stat_gauc_等权`
        -- rov gauc（三种加权方式）
        ,a.rov_gauc_exp AS rov_gauc_曝光
        ,a.rov_gauc_pairs AS rov_gauc_样本对
        ,a.rov_gauc_equal AS rov_gauc_等权
        -- rov_stat gauc（三种加权方式）
        ,a.rov_stat_gauc_exp AS rov_stat_gauc_曝光
        ,a.rov_stat_gauc_pairs AS rov_stat_gauc_样本对
        ,a.rov_stat_gauc_equal AS rov_stat_gauc_等权
FROM    t_gauc a
LEFT JOIN t_rosn_gcindex c
ON      a.dt = c.dt AND a.apptype = c.apptype AND a.abcode = c.abcode
ORDER BY dt DESC, apptype, abcode
;
