-- ════════════════════════════════════════════════════════════════════════════
-- 策略打分阈值分布 [简化版]
--   目的：为公式调权实验提供分数的"地形图"
--        - 每个分数的 min / max / 分位数（p50/p95）→ 回答"改权重后分数落在哪"
--        - 每个分数的 cov → 该分数对该策略实际贡献覆盖率
--        - avg / std → 中心趋势 + 离散度
--   行粒度：(dt, apptype, suffix_group) + GROUPING SETS 总体行
--
-- 与 base panel SQL 的差异：
--   - 数据源仍是 dwd_recsys_alg_sample_all_20250212
--   - 砍掉：dau2 / _diff / cc-dd / ECS-ARP / 分层（source_type/layer_type）
--   - 加上：min / max / p50 / p95（PERCENTILE_APPROX 近似分位数）
--
-- 调权时怎么读：
--   想调 ros_add（562→563 改 0.1→0.001）：
--     → 看 nor_dnn_score 的 p50（DNN 路径），ros_add 占乘子的比例 = ros_add / (ros_add + nor_dnn_p50)
--     → 0.1 / (0.1 + 0.65) ≈ 13%（562 默认）；0.001 / (0.001 + 0.65) ≈ 0.15%（563 上线后）
--   想调 vor_w：
--     → 看 vor 的 p50/p95，权重×p50 决定主流量级，权重×p95 决定尾部杠杆
--   想调 leave_w/leave_exp（仅 565/566）：
--     → 看 p_leave 的 min/max/p50，newPLeave = (1-w·pLeave)^exp 在该分布上落到哪
-- ════════════════════════════════════════════════════════════════════════════
WITH t_suffix_group AS
(
    SELECT 'a' AS suffix, 'ab' AS suffix_group
    UNION ALL SELECT 'b', 'ab'
    UNION ALL SELECT '0', '01'
    UNION ALL SELECT '1', '01'
    UNION ALL SELECT '2', '2c'
    UNION ALL SELECT 'c', '2c'
    UNION ALL SELECT '3', '34'
    UNION ALL SELECT '4', '34'
    UNION ALL SELECT '5', '5d'
    UNION ALL SELECT 'd', '5d'
    UNION ALL SELECT '6', '67'
    UNION ALL SELECT '7', '67'
    UNION ALL SELECT '8', '89'
    UNION ALL SELECT '9', '89'
    UNION ALL SELECT 'e', 'ef'
    UNION ALL SELECT 'f', 'ef'
)
,t_experiment_map AS
(
    -- ── apptype = 4 ──
    SELECT "4" AS apptype, "ab" AS suffix_group, "实验组：变更str*ros建模目标实验" AS abcode, "20260416" AS start_dt, "29991231" AS end_dt
    UNION ALL SELECT "4", "01", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "67", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "5d", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "34", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "ef", "实验组：DNN模型",                 "20260407", "29991231"
    UNION ALL SELECT "4", "2c", "实验组：DNN模型-调参",            "20260413", "29991231"
    UNION ALL SELECT "4", "89", "对照组",                          "20260301", "29991231"
    -- ── apptype = 0 ──
    UNION ALL SELECT "0", "ab", "实验组：变更str*ros建模目标实验", "20260417", "29991231"
    UNION ALL SELECT "0", "01", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验", "20260426", "29991231"
    UNION ALL SELECT "0", "67", "实验组：变更str*ros建模目标实验", "20260417", "29991231"
    UNION ALL SELECT "0", "5d", "实验组：曝光建模v1+DNN模型",      "20260425", "29991231"
    UNION ALL SELECT "0", "ef", "实验组：DNN模型-调参",            "20260410", "29991231"
    UNION ALL SELECT "0", "2c", "实验组：DNN模型",                 "20260413", "29991231"
    UNION ALL SELECT "0", "89", "对照组",                          "20260301", "29991231"
)
,t_exp_code_map AS
(
    -- 流量配置 20260425.json：(apptype, suffix_group) → expCode
    SELECT "0" AS apptype, "89" AS suffix_group, "839" AS expcode, "20260425" AS start_dt, "29991231" AS end_dt
    UNION ALL SELECT "4", "89", "839", "20260425", "29991231"
    UNION ALL SELECT "4", "ef", "562", "20260425", "29991231"
    UNION ALL SELECT "0", "2c", "562", "20260425", "29991231"
    UNION ALL SELECT "4", "2c", "563", "20260425", "29991231"
    UNION ALL SELECT "0", "ef", "563", "20260425", "29991231"
    UNION ALL SELECT "0", "5d", "565", "20260425", "29991231"
    UNION ALL SELECT "0", "34", "566", "20260425", "29991231"
)
-- 单条曝光 + 解 13 个分数（不要业务 label，不要 cc/dd）
,t_scored AS
(
    SELECT  a.dt
            ,a.apptype
            ,sg.suffix_group
            ,COALESCE(m.abcode, "对照组") AS abcode
            ,e.expcode
            -- ── 13 个分数 ──
            ,CAST(a.score AS DOUBLE)                                            AS final_score
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.fmRov')              AS DOUBLE) AS fmrov
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.fmRovOrigin')        AS DOUBLE) AS fmrov_origin
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.NorXGBScore')        AS DOUBLE) AS nor_xgb
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.NorDNNScore')        AS DOUBLE) AS nor_dnn
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.newNorDNNScore')     AS DOUBLE) AS new_nor_dnn
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.hasReturnRovScore')  AS DOUBLE) AS has_return_rov
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.vor')                AS DOUBLE) AS vor
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.c1RovnScore')        AS DOUBLE) AS c1_rovn
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.b0StrScore')         AS DOUBLE) AS b0_str
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.b0RorScore')         AS DOUBLE) AS b0_ror
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.cnRovnScore')        AS DOUBLE) AS cn_rovn
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.dnRovnScore')        AS DOUBLE) AS dn_rovn
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.pLeave')             AS DOUBLE) AS p_leave
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.newPLeave')          AS DOUBLE) AS new_p_leave
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.reduceCoefficient')  AS DOUBLE) AS reduce_coef
    FROM    (
                SELECT  dt
                        ,apptype
                        ,SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),
                                LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) AS suffix
                        ,score
                        ,REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\","") AS scoresmap
                FROM    loghubods.dwd_recsys_alg_sample_all_20250212
                WHERE   dt = '${dt}'
                AND     apptype IN ('0','4')
                AND     extend_alg IS NOT NULL
                AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页")
                AND     abcode NOT IN ("ab100")
                AND     (apptype = "4" OR abcode IN ("ab0","ab1","ab2","ab3","ab4","ab8","ab9"))
                AND     (apptype = "0" OR abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9"))
            ) a
    INNER JOIN t_suffix_group sg ON a.suffix = sg.suffix
    LEFT JOIN t_experiment_map m
        ON  a.apptype = m.apptype
        AND sg.suffix_group = m.suffix_group
        AND '${dt}' BETWEEN m.start_dt AND m.end_dt
    LEFT JOIN t_exp_code_map e
        ON  a.apptype = e.apptype
        AND sg.suffix_group = e.suffix_group
        AND '${dt}' BETWEEN e.start_dt AND e.end_dt
)
-- 每个分数：avg / std / min / max / p50 / p95 / cov（7 列）
SELECT  dt
        ,apptype
        ,COALESCE(suffix_group, '总体') AS suffix_group
        ,MAX(abcode) AS abcode
        ,MAX(expcode) AS expcode
        ,COUNT(1) AS exp
        -- ────── final_score（线上落盘 finalScore）──────
        ,ROUND(AVG(final_score), 6)                          AS finalscore_avg
        ,ROUND(STDDEV_POP(final_score), 6)                   AS finalscore_std
        ,ROUND(MIN(final_score), 6)                          AS finalscore_min
        ,ROUND(MAX(final_score), 6)                          AS finalscore_max
        ,ROUND(PERCENTILE_APPROX(final_score, 0.50), 6)      AS finalscore_p50
        ,ROUND(PERCENTILE_APPROX(final_score, 0.95), 6)      AS finalscore_p95
        ,ROUND(SUM(CASE WHEN final_score IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS finalscore_cov
        -- ────── fmRov（STR 预估，分享率）──────
        ,ROUND(AVG(fmrov), 6)                                AS fmrov_avg
        ,ROUND(STDDEV_POP(fmrov), 6)                         AS fmrov_std
        ,ROUND(MIN(fmrov), 6)                                AS fmrov_min
        ,ROUND(MAX(fmrov), 6)                                AS fmrov_max
        ,ROUND(PERCENTILE_APPROX(fmrov, 0.50), 6)            AS fmrov_p50
        ,ROUND(PERCENTILE_APPROX(fmrov, 0.95), 6)            AS fmrov_p95
        ,ROUND(SUM(CASE WHEN fmrov IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS fmrov_cov
        -- ────── fmRovOrigin（STR 反归一化前）──────
        ,ROUND(AVG(fmrov_origin), 6)                         AS fmrovorig_avg
        ,ROUND(STDDEV_POP(fmrov_origin), 6)                  AS fmrovorig_std
        ,ROUND(MIN(fmrov_origin), 6)                         AS fmrovorig_min
        ,ROUND(MAX(fmrov_origin), 6)                         AS fmrovorig_max
        ,ROUND(PERCENTILE_APPROX(fmrov_origin, 0.50), 6)     AS fmrovorig_p50
        ,ROUND(PERCENTILE_APPROX(fmrov_origin, 0.95), 6)     AS fmrovorig_p95
        ,ROUND(SUM(CASE WHEN fmrov_origin IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS fmrovorig_cov
        -- ────── NorXGBScore（XGB 路径 ROS 预估，839/566）──────
        ,ROUND(AVG(nor_xgb), 6)                              AS norxgb_avg
        ,ROUND(STDDEV_POP(nor_xgb), 6)                       AS norxgb_std
        ,ROUND(MIN(nor_xgb), 6)                              AS norxgb_min
        ,ROUND(MAX(nor_xgb), 6)                              AS norxgb_max
        ,ROUND(PERCENTILE_APPROX(nor_xgb, 0.50), 6)          AS norxgb_p50
        ,ROUND(PERCENTILE_APPROX(nor_xgb, 0.95), 6)          AS norxgb_p95
        ,ROUND(SUM(CASE WHEN nor_xgb IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS norxgb_cov
        -- ────── NorDNNScore（DNN 路径 ROS 预估，562/563/565）──────
        ,ROUND(AVG(nor_dnn), 6)                              AS nordnn_avg
        ,ROUND(STDDEV_POP(nor_dnn), 6)                       AS nordnn_std
        ,ROUND(MIN(nor_dnn), 6)                              AS nordnn_min
        ,ROUND(MAX(nor_dnn), 6)                              AS nordnn_max
        ,ROUND(PERCENTILE_APPROX(nor_dnn, 0.50), 6)          AS nordnn_p50
        ,ROUND(PERCENTILE_APPROX(nor_dnn, 0.95), 6)          AS nordnn_p95
        ,ROUND(SUM(CASE WHEN nor_dnn IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS nordnn_cov
        -- ────── newNorDNNScore（DNN 经 power 校准后）──────
        ,ROUND(AVG(new_nor_dnn), 6)                          AS newnordnn_avg
        ,ROUND(STDDEV_POP(new_nor_dnn), 6)                   AS newnordnn_std
        ,ROUND(MIN(new_nor_dnn), 6)                          AS newnordnn_min
        ,ROUND(MAX(new_nor_dnn), 6)                          AS newnordnn_max
        ,ROUND(PERCENTILE_APPROX(new_nor_dnn, 0.50), 6)      AS newnordnn_p50
        ,ROUND(PERCENTILE_APPROX(new_nor_dnn, 0.95), 6)      AS newnordnn_p95
        ,ROUND(SUM(CASE WHEN new_nor_dnn IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS newnordnn_cov
        -- ────── hasReturnRovScore（redis vid_hasreturn_vor:rov 历史回流率）──────
        ,ROUND(AVG(has_return_rov), 6)                       AS hasretrov_avg
        ,ROUND(STDDEV_POP(has_return_rov), 6)                AS hasretrov_std
        ,ROUND(MIN(has_return_rov), 6)                       AS hasretrov_min
        ,ROUND(MAX(has_return_rov), 6)                       AS hasretrov_max
        ,ROUND(PERCENTILE_APPROX(has_return_rov, 0.50), 6)   AS hasretrov_p50
        ,ROUND(PERCENTILE_APPROX(has_return_rov, 0.95), 6)   AS hasretrov_p95
        ,ROUND(SUM(CASE WHEN has_return_rov IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS hasretrov_cov
        -- ────── vor（redis vid_hasreturn_vor:vor 历史曝光裂变率）──────
        ,ROUND(AVG(vor), 6)                                  AS vor_avg
        ,ROUND(STDDEV_POP(vor), 6)                           AS vor_std
        ,ROUND(MIN(vor), 6)                                  AS vor_min
        ,ROUND(MAX(vor), 6)                                  AS vor_max
        ,ROUND(PERCENTILE_APPROX(vor, 0.50), 6)              AS vor_p50
        ,ROUND(PERCENTILE_APPROX(vor, 0.95), 6)              AS vor_p95
        ,ROUND(SUM(CASE WHEN vor IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS vor_cov
        -- ────── c1RovnScore（一级品类 1h+24h ROVN）──────
        ,ROUND(AVG(c1_rovn), 6)                              AS c1rovn_avg
        ,ROUND(STDDEV_POP(c1_rovn), 6)                       AS c1rovn_std
        ,ROUND(MIN(c1_rovn), 6)                              AS c1rovn_min
        ,ROUND(MAX(c1_rovn), 6)                              AS c1rovn_max
        ,ROUND(PERCENTILE_APPROX(c1_rovn, 0.50), 6)          AS c1rovn_p50
        ,ROUND(PERCENTILE_APPROX(c1_rovn, 0.95), 6)          AS c1rovn_p95
        ,ROUND(SUM(CASE WHEN c1_rovn IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS c1rovn_cov
        -- ────── b0StrScore（B0 流 1h+24h STR；当前权重=0 全为 0）──────
        ,ROUND(AVG(b0_str), 6)                               AS b0str_avg
        ,ROUND(STDDEV_POP(b0_str), 6)                        AS b0str_std
        ,ROUND(MIN(b0_str), 6)                               AS b0str_min
        ,ROUND(MAX(b0_str), 6)                               AS b0str_max
        ,ROUND(PERCENTILE_APPROX(b0_str, 0.50), 6)           AS b0str_p50
        ,ROUND(PERCENTILE_APPROX(b0_str, 0.95), 6)           AS b0str_p95
        ,ROUND(SUM(CASE WHEN b0_str IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS b0str_cov
        -- ────── b0RorScore（B0 流 1h+24h ROR；当前权重=0）──────
        ,ROUND(AVG(b0_ror), 6)                               AS b0ror_avg
        ,ROUND(STDDEV_POP(b0_ror), 6)                        AS b0ror_std
        ,ROUND(MIN(b0_ror), 6)                               AS b0ror_min
        ,ROUND(MAX(b0_ror), 6)                               AS b0ror_max
        ,ROUND(PERCENTILE_APPROX(b0_ror, 0.50), 6)           AS b0ror_p50
        ,ROUND(PERCENTILE_APPROX(b0_ror, 0.95), 6)           AS b0ror_p95
        ,ROUND(SUM(CASE WHEN b0_ror IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS b0ror_cov
        -- ────── cnRovnScore（CN 流 1h+24h ROVN；当前权重=0）──────
        ,ROUND(AVG(cn_rovn), 6)                              AS cnrovn_avg
        ,ROUND(STDDEV_POP(cn_rovn), 6)                       AS cnrovn_std
        ,ROUND(MIN(cn_rovn), 6)                              AS cnrovn_min
        ,ROUND(MAX(cn_rovn), 6)                              AS cnrovn_max
        ,ROUND(PERCENTILE_APPROX(cn_rovn, 0.50), 6)          AS cnrovn_p50
        ,ROUND(PERCENTILE_APPROX(cn_rovn, 0.95), 6)          AS cnrovn_p95
        ,ROUND(SUM(CASE WHEN cn_rovn IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS cnrovn_cov
        -- ────── dnRovnScore（DN 流 1h+24h ROVN；当前权重=0）──────
        ,ROUND(AVG(dn_rovn), 6)                              AS dnrovn_avg
        ,ROUND(STDDEV_POP(dn_rovn), 6)                       AS dnrovn_std
        ,ROUND(MIN(dn_rovn), 6)                              AS dnrovn_min
        ,ROUND(MAX(dn_rovn), 6)                              AS dnrovn_max
        ,ROUND(PERCENTILE_APPROX(dn_rovn, 0.50), 6)          AS dnrovn_p50
        ,ROUND(PERCENTILE_APPROX(dn_rovn, 0.95), 6)          AS dnrovn_p95
        ,ROUND(SUM(CASE WHEN dn_rovn IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS dnrovn_cov
        -- ────── pLeave（PAI 模型预测离开概率原值）──────
        ,ROUND(AVG(p_leave), 6)                              AS pleave_avg
        ,ROUND(STDDEV_POP(p_leave), 6)                       AS pleave_std
        ,ROUND(MIN(p_leave), 6)                              AS pleave_min
        ,ROUND(MAX(p_leave), 6)                              AS pleave_max
        ,ROUND(PERCENTILE_APPROX(p_leave, 0.50), 6)          AS pleave_p50
        ,ROUND(PERCENTILE_APPROX(p_leave, 0.95), 6)          AS pleave_p95
        ,ROUND(SUM(CASE WHEN p_leave IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS pleave_cov
        -- ────── newPLeave = (1-leaveW·pLeave)^leaveExp（仅 565/566 真用进 finalScore）──────
        ,ROUND(AVG(new_p_leave), 6)                          AS newpleave_avg
        ,ROUND(STDDEV_POP(new_p_leave), 6)                   AS newpleave_std
        ,ROUND(MIN(new_p_leave), 6)                          AS newpleave_min
        ,ROUND(MAX(new_p_leave), 6)                          AS newpleave_max
        ,ROUND(PERCENTILE_APPROX(new_p_leave, 0.50), 6)      AS newpleave_p50
        ,ROUND(PERCENTILE_APPROX(new_p_leave, 0.95), 6)      AS newpleave_p95
        ,ROUND(SUM(CASE WHEN new_p_leave IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS newpleave_cov
        -- ────── reduceCoefficient（重排品类/节日降权乘子）──────
        ,ROUND(AVG(reduce_coef), 6)                          AS reduce_avg
        ,ROUND(STDDEV_POP(reduce_coef), 6)                   AS reduce_std
        ,ROUND(MIN(reduce_coef), 6)                          AS reduce_min
        ,ROUND(MAX(reduce_coef), 6)                          AS reduce_max
        ,ROUND(PERCENTILE_APPROX(reduce_coef, 0.50), 6)      AS reduce_p50
        ,ROUND(PERCENTILE_APPROX(reduce_coef, 0.95), 6)      AS reduce_p95
        ,ROUND(SUM(CASE WHEN reduce_coef IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS reduce_cov
FROM    t_scored
GROUP BY dt, apptype, suffix_group
GROUPING SETS (
    (dt, apptype),                          -- 整体（1 行/apptype）
    (dt, apptype, suffix_group)             -- 分尾号桶（8 行/apptype）
)
ORDER BY dt DESC, apptype, suffix_group
;
