-- 按有/无预测值分组，分析覆盖率及各组指标
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
            ,page_type AS page
            ,mid
            ,vid
            ,is_share
            ,share_cnt
            ,is_return_1
            ,is_return_n
            ,is_return_noself
            ,return_1_uv
            ,return_n_uv
            ,return_n_uv_noself
            ,new_exposure_cnt
            ,flowpool
            ,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 ros_pred
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.hasReturnRovScore') AS DOUBLE) AS ros_stat
            -- 是否有预测值
            ,CASE WHEN GET_JSON_OBJECT(scoresmap,'$.fmRov') IS NOT NULL
                   AND GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') IS NOT NULL
                  THEN '有预测值'
                  ELSE '无预测值'
            END AS has_pred
    FROM    t_filtered
)
SELECT  dt
        ,apptype
        ,abcode
        ,has_pred
        -- 样本量与占比
        ,COUNT(1) AS sample_cnt
        ,round(COUNT(1) / SUM(COUNT(1)) OVER (PARTITION BY dt, apptype, abcode), 4) AS sample_ratio
        ,SUM(is_return_noself) AS return_sample_cnt
        ,round(SUM(is_return_noself) / COUNT(1), 6) AS return_sample_ratio
        -- 全量样本指标
        ,round(COALESCE(SUM(is_return_noself) / COUNT(1),0),6) AS str_real
        ,round(COALESCE(SUM(str_pred) / COUNT(1),0),6) AS str_pred
        ,round(COALESCE(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0),0),6) AS ros_real
        ,round(COALESCE(SUM(ros_pred) / COUNT(1),0),6) AS ros_pred
        ,round(COALESCE(SUM(ros_stat) / COUNT(1),0),6) AS ros_stat
        -- 仅回流样本的均值对比
        ,round(AVG(IF(is_return_noself=1, return_n_uv_noself, NULL)),6) AS ros_real_return
        ,round(AVG(IF(is_return_noself=1, ros_pred, NULL)),6) AS ros_pred_return
        ,round(AVG(IF(is_return_noself=1, ros_stat, NULL)),6) AS ros_stat_return
        -- 仅回流样本的偏差（正=高估，负=低估）
        ,round(AVG(IF(is_return_noself=1, ros_pred - return_n_uv_noself, NULL)),6) AS ros_pred_bias
        ,round(AVG(IF(is_return_noself=1, ros_stat - return_n_uv_noself, NULL)),6) AS ros_stat_bias
        -- 仅回流样本的误差
        ,round(AVG(IF(is_return_noself=1, ABS(ros_pred - return_n_uv_noself), NULL)),6) AS ros_pred_mae_return
        ,round(AVG(IF(is_return_noself=1, ABS(ros_stat - return_n_uv_noself), NULL)),6) AS ros_stat_mae_return
        -- 高估/低估分开的误差（仅回流样本）
        ,round(AVG(IF(is_return_noself=1 AND ros_pred > return_n_uv_noself, ros_pred - return_n_uv_noself, NULL)),6) AS ros_pred_mae_over
        ,round(AVG(IF(is_return_noself=1 AND ros_pred < return_n_uv_noself, return_n_uv_noself - ros_pred, NULL)),6) AS ros_pred_mae_under
        ,round(AVG(IF(is_return_noself=1 AND ros_stat > return_n_uv_noself, ros_stat - return_n_uv_noself, NULL)),6) AS ros_stat_mae_over
        ,round(AVG(IF(is_return_noself=1 AND ros_stat < return_n_uv_noself, return_n_uv_noself - ros_stat, NULL)),6) AS ros_stat_mae_under
        -- 非回流样本的误差（真实值=0）
        ,round(AVG(IF(is_return_noself=0, ABS(ros_pred), NULL)),6) AS ros_pred_mae_noreturn
        ,round(AVG(IF(is_return_noself=0, ABS(ros_stat), NULL)),6) AS ros_stat_mae_noreturn
        -- 业务指标
        ,round(COALESCE(COUNT(1) / COUNT(DISTINCT mid),0),2) AS exp_per_dau
        ,round(COALESCE(SUM(is_share) / COUNT(1),0),6) AS str_one
        ,round(COALESCE(SUM(return_n_uv) / SUM(is_share),0),6) AS ros_one
        ,round(COALESCE(SUM(share_cnt) / COUNT(1),0),6) AS str
        ,round(COALESCE(SUM(return_n_uv) / SUM(share_cnt),0),6) AS ros
        ,round(COALESCE(SUM(is_return_1) / COUNT(1),0),6) AS str_plus
        ,round(COALESCE(SUM(return_n_uv) / SUM(is_return_1),0),6) AS ros_minus
        ,round(COALESCE(SUM(return_n_uv) / COUNT(1),0),6) AS rovn
        ,round(COALESCE(SUM(new_exposure_cnt) / COUNT(1),0),6) AS vovh24
        ,COUNT(DISTINCT mid) AS dau
        ,COUNT(1) AS exp
        ,COALESCE(SUM(is_share),0) AS is_share
        ,COALESCE(SUM(share_cnt),0) AS share_cnt
        ,COALESCE(SUM(is_return_1),0) AS is_return_1
        ,COALESCE(SUM(return_n_uv),0) AS return_n_uv
        ,COALESCE(SUM(new_exposure_cnt),0) AS viewh24
        ,COALESCE(SUM(return_n_uv_noself),0) AS return_n_uv_noself
FROM    t_base
GROUP BY dt
         ,apptype
         ,abcode
         ,has_pred
ORDER BY dt DESC,apptype,abcode,has_pred DESC
;
