-- ════════════════════════════════════════════════════════════════════════════
-- 策略调权实验底层表
--   目的：为公式调权实验提供"参数化"的底层数据
--        - 真实值（label 聚合）+ 模型原始/校准预估 + 其他分数分布
--        - apollo 配置作为 CTE 维护，SQL 公式参数化（不写死数字）
--        - 提供 str_pred_recompute 校验列（SQL 重算 vs 线上 fmRov 应几乎相等）
--
--   ┌─ 字段命名约定 ────────────────────────────────────────────────────┐
--   │  真实值（无后缀）：            str / strx / ros / rosx / rov / vovh24 │
--   │  模型校准后预估（_pred）：     str_pred / ros_pred / rov_pred       │
--   │  模型原始预估（_origin）：     str_origin / ros_origin / rov_origin │
--   │  不需校准的字段（无后缀）：    vor / ros_stat / c1_rovn / b0_str /    │
--   │                              b0_ror / cn_rovn / dn_rovn / p_leave / │
--   │                              new_p_leave / reduce_coef / final_score │
--   │  校验列：str_pred_recompute（SQL 用 apollo 重算 fmRov，对比 scoresMap）│
--   │  标识列：expcode_known（apollo 配置是否覆盖该桶；0 = fallback 用代码默认）│
--   └────────────────────────────────────────────────────────────────────┘
--
--   ┌─ 业务对照（调权时按 expCode 配 pair）──────────────────────────────┐
--   │  expCode    str_pair        ros/rov_pair                            │
--   │  839（基线）strx ↔ str_pred  rosx ↔ ros_pred                          │
--   │  566（新建模）str ↔ str_pred  ros ↔ ros_pred                          │
--   │  562/563/565（DNN）str ↔ str_pred  rov ↔ rov_pred                     │
--   └────────────────────────────────────────────────────────────────────┘
--
--   ┌─ 调权 what-if 流程 ────────────────────────────────────────────────┐
--   │  1. 改 t_apollo_weight 的某个值（如 ros_add 从 0.1 → 0.05）         │
--   │  2. 重跑 SQL → 看 ros_pred 分布变化                                  │
--   │  3. 如果新增 finalScore 重算列（todo），可对比"假设 finalScore"vs真实  │
--   │  4. 上线前先确认 str_pred_recompute ≈ scoresMap.fmRov（公式对齐）    │
--   └────────────────────────────────────────────────────────────────────┘
--
--   每个分数字段输出 7 列：avg / std / min / max / p50 / p95 / cov
-- ════════════════════════════════════════════════════════════════════════════
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
(
    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"
    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
(
    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"
)
-- ┌─ apollo 配置（来源：tasks/00_尾号实验/线上实验配置/策略权重-apollo配置/）─┐
-- │  apollo 改了 → 只更新这个 CTE，其他 SQL 不动                              │
-- │  缺失字段：未在 apollo 显式配的 → 走代码默认值                            │
-- └────────────────────────────────────────────────────────────────────────┘
,t_apollo_weight AS
(
    -- expcode  | xgb_neg_rate | power_w | power_exp | ros_add | ros_w | vor_add | vor_w | leave_w | leave_exp | c1_1h_w | c1_24h_w
    SELECT '839' AS expcode, 0.055 AS xgb_neg_rate, 1.22 AS power_w, 1.15 AS power_exp,
           0.1 AS ros_add, 1.0 AS ros_w, 0.1 AS vor_add, 1.0 AS vor_w,
           CAST(NULL AS DOUBLE) AS leave_w, CAST(NULL AS DOUBLE) AS leave_exp,
           0.5 AS c1_1h_w, 0.5 AS c1_24h_w
    UNION ALL SELECT '562', 0.036, 0,    1,    0.1,   1.0, 0.1, 1.0, NULL, NULL, 0.5, 0.5
    UNION ALL SELECT '563', 0.036, 0,    1,    0.001, 1.0, 0.1, 1.0, NULL, NULL, 0.5, 0.5
    UNION ALL SELECT '565', 0.036, 0,    1,    0.1,   1.0, 0.1, 1.0, 1.0,  1.0,  0.5, 0.5
    UNION ALL SELECT '566', 0.036, 1,    1,    0.1,   1.0, 0.1, 1.0, 1.0,  1.0,  0.5, 0.5
)
-- ════════════════════════════════════════════════════════════════════════════
-- 第一步：单条曝光 + 解 scoresmap + JOIN apollo + SQL 校验列
-- ════════════════════════════════════════════════════════════════════════════
,t_scored AS
(
    SELECT  a.dt
            ,a.apptype
            ,a.mid
            ,a.vid
            ,sg.suffix_group
            ,COALESCE(m.abcode, "对照组") AS abcode
            ,e.expcode
            ,CASE WHEN e.expcode IS NOT NULL THEN 1 ELSE 0 END AS expcode_known
            -- ── 业务 label 透传 ──
            ,a.is_share
            ,a.share_cnt
            ,a.is_return_1
            ,a.is_return_n
            ,a.is_return_noself
            ,a.return_n_uv
            ,a.return_n_uv_noself
            ,a.new_exposure_cnt
            -- ── 模型原始预估（_origin = 模型直接输出）──
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRovOrigin')      AS DOUBLE) AS str_origin
            ,COALESCE(
                CAST(GET_JSON_OBJECT(a.scoresmap, '$.NorXGBScore') AS DOUBLE)
              )                                                                  AS ros_origin     -- 仅 XGB 路径有值（839/566）
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.NorDNNScore')      AS DOUBLE) AS rov_origin     -- 仅 DNN 路径有值（562/563/565）
            -- ── 模型校准后预估（_pred = 经线上代码任何处理后的"用值"）──
            -- str_pred = fmRov（线上已 restoreScore，从 scoresMap 直接拿）
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRov')            AS DOUBLE) AS str_pred
            -- ros_pred = newNorXGBScore（SQL 用 apollo power 校准重算；线上未 put 到 scoresMap）
            -- 公式：max(score, w · score^exp), 上限 100, 下限 = score
            ,CASE WHEN CAST(GET_JSON_OBJECT(a.scoresmap, '$.NorXGBScore') AS DOUBLE) IS NOT NULL THEN
                LEAST(100.0,
                      GREATEST(
                          CAST(GET_JSON_OBJECT(a.scoresmap, '$.NorXGBScore') AS DOUBLE),
                          COALESCE(w.power_w, 1.22) * POW(
                              CAST(GET_JSON_OBJECT(a.scoresmap, '$.NorXGBScore') AS DOUBLE),
                              COALESCE(w.power_exp, 1.15)
                          )
                      ))
             END                                                                 AS ros_pred
            -- rov_pred = newNorDNNScore（线上 562/563/565 已 put 到 scoresMap）
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.newNorDNNScore')   AS DOUBLE) AS rov_pred
            -- ── 校验列：SQL 用 apollo 重算 str_pred，对比线上 scoresMap.fmRov 应几乎相等 ──
            -- 公式：str_pred_recompute = (w · str_origin) / (1 - (1-w) · str_origin)
            ,CASE WHEN CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRovOrigin') AS DOUBLE) IS NOT NULL
                  AND CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRovOrigin') AS DOUBLE) < 1
                  AND COALESCE(w.xgb_neg_rate, 0.036) > 0 THEN
                (COALESCE(w.xgb_neg_rate, 0.036) * CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRovOrigin') AS DOUBLE))
                / NULLIF(1 - (1 - COALESCE(w.xgb_neg_rate, 0.036)) * CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRovOrigin') AS DOUBLE), 0)
             END                                                                 AS str_pred_recompute
            -- ── 不需校准的字段 ──
            ,CAST(a.score                                            AS DOUBLE) AS final_score
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.vor')              AS DOUBLE) AS vor
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.hasReturnRovScore') AS DOUBLE) AS ros_stat
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.c1RovnScore')      AS DOUBLE) AS c1_rovn
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.b0StrScore')       AS DOUBLE) AS b0_str
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.b0RorScore')       AS DOUBLE) AS b0_ror
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.cnRovnScore')      AS DOUBLE) AS cn_rovn
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.dnRovnScore')      AS DOUBLE) AS dn_rovn
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.pLeave')           AS DOUBLE) AS p_leave
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.newPLeave')        AS DOUBLE) AS new_p_leave
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.reduceCoefficient') AS DOUBLE) AS reduce_coef
    FROM    (
                SELECT  dt
                        ,apptype
                        ,mid
                        ,vid
                        ,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
                        ,is_share, share_cnt, is_return_1, is_return_n, is_return_noself
                        ,return_n_uv, return_n_uv_noself, new_exposure_cnt
                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
    LEFT JOIN t_apollo_weight w
        ON  e.expcode = w.expcode
)
-- ════════════════════════════════════════════════════════════════════════════
-- 第二步：聚合到 (dt, apptype, suffix_group) 输出
-- ════════════════════════════════════════════════════════════════════════════
SELECT  dt
        ,apptype
        ,COALESCE(suffix_group, '总体') AS suffix_group
        ,MAX(abcode) AS abcode
        ,MAX(expcode) AS expcode
        ,MAX(expcode_known) AS expcode_known
        -- ────── 样本量 ──────
        ,COUNT(1) AS exp
        ,COUNT(DISTINCT mid) AS dau
        ,COUNT(DISTINCT vid) AS distinct_vid
        -- ────── 业务真实计数 ──────
        ,COALESCE(SUM(is_share),0) AS share_uv
        ,COALESCE(SUM(share_cnt),0) AS share_cnt
        ,COALESCE(SUM(is_return_1),0) AS return_1_cnt
        ,COALESCE(SUM(is_return_n),0) AS return_n_cnt
        ,COALESCE(SUM(is_return_noself),0) AS return_noself_cnt
        ,COALESCE(SUM(return_n_uv),0) AS return_n_uv_sum
        ,COALESCE(SUM(return_n_uv_noself),0) AS return_n_uv_noself_sum
        ,COALESCE(SUM(new_exposure_cnt),0) AS viewh24
        -- ────── 业务真实率（无 _real 后缀，按命名约定）──────
        ,ROUND(COALESCE(SUM(is_share) / COUNT(1), 0), 6) AS str                                       -- 新建模/DNN 真实
        ,ROUND(COALESCE(SUM(is_return_noself) / COUNT(1), 0), 6) AS strx                              -- 基线 839 真实
        ,ROUND(COALESCE(SUM(return_n_uv_noself) / NULLIF(SUM(is_share), 0), 0), 6) AS ros             -- 新建模 566 真实
        ,ROUND(COALESCE(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0), 0), 6) AS rosx    -- 基线 839 真实
        ,ROUND(COALESCE(SUM(return_n_uv_noself) / COUNT(1), 0), 6) AS rov                             -- DNN 562/563/565 真实
        ,ROUND(COALESCE(SUM(new_exposure_cnt) / COUNT(1), 0), 6) AS vovh24
        -- ────── final_score（线上最终分）──────
        ,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
        -- ────── str_origin（STR 模型原始预估，fmRovOrigin）──────
        ,ROUND(AVG(str_origin), 6)                           AS strorigin_avg
        ,ROUND(STDDEV_POP(str_origin), 6)                    AS strorigin_std
        ,ROUND(MIN(str_origin), 6)                           AS strorigin_min
        ,ROUND(MAX(str_origin), 6)                           AS strorigin_max
        ,ROUND(PERCENTILE_APPROX(str_origin, 0.50), 6)       AS strorigin_p50
        ,ROUND(PERCENTILE_APPROX(str_origin, 0.95), 6)       AS strorigin_p95
        ,ROUND(SUM(CASE WHEN str_origin IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS strorigin_cov
        -- ────── str_pred（STR 反归一化校准，fmRov 线上值）──────
        ,ROUND(AVG(str_pred), 6)                             AS strpred_avg
        ,ROUND(STDDEV_POP(str_pred), 6)                      AS strpred_std
        ,ROUND(MIN(str_pred), 6)                             AS strpred_min
        ,ROUND(MAX(str_pred), 6)                             AS strpred_max
        ,ROUND(PERCENTILE_APPROX(str_pred, 0.50), 6)         AS strpred_p50
        ,ROUND(PERCENTILE_APPROX(str_pred, 0.95), 6)         AS strpred_p95
        ,ROUND(SUM(CASE WHEN str_pred IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS strpred_cov
        -- ────── str_pred 校验（SQL 用 apollo 重算 vs 线上 fmRov 的 AVG 比值差）──────
        -- restoreScore 是确定性公式：相同 fmRovOrigin + 相同 neg_rate → 每条曝光都等
        -- 整体偏移仅可能因 apollo neg_rate 错配或线上动态推送漂移；diff_pct 一列足够
        -- 解读：≈ 0 → 公式对齐 ✓；显著 ≠ 0 → 检查 t_apollo_weight 的 xgb_neg_rate
        ,ROUND(AVG(str_pred_recompute) / NULLIF(AVG(str_pred), 0) - 1, 6) AS strpredre_diff_pct
        -- ────── ros_origin（XGB 模型原始预估，839+566）──────
        ,ROUND(AVG(ros_origin), 6)                           AS rosorigin_avg
        ,ROUND(STDDEV_POP(ros_origin), 6)                    AS rosorigin_std
        ,ROUND(MIN(ros_origin), 6)                           AS rosorigin_min
        ,ROUND(MAX(ros_origin), 6)                           AS rosorigin_max
        ,ROUND(PERCENTILE_APPROX(ros_origin, 0.50), 6)       AS rosorigin_p50
        ,ROUND(PERCENTILE_APPROX(ros_origin, 0.95), 6)       AS rosorigin_p95
        ,ROUND(SUM(CASE WHEN ros_origin IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS rosorigin_cov
        -- ────── ros_pred（XGB power 校准后；SQL 重算）──────
        ,ROUND(AVG(ros_pred), 6)                             AS rospred_avg
        ,ROUND(STDDEV_POP(ros_pred), 6)                      AS rospred_std
        ,ROUND(MIN(ros_pred), 6)                             AS rospred_min
        ,ROUND(MAX(ros_pred), 6)                             AS rospred_max
        ,ROUND(PERCENTILE_APPROX(ros_pred, 0.50), 6)         AS rospred_p50
        ,ROUND(PERCENTILE_APPROX(ros_pred, 0.95), 6)         AS rospred_p95
        ,ROUND(SUM(CASE WHEN ros_pred IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS rospred_cov
        -- ────── rov_origin（DNN 模型原始预估，562/563/565）──────
        ,ROUND(AVG(rov_origin), 6)                           AS rovorigin_avg
        ,ROUND(STDDEV_POP(rov_origin), 6)                    AS rovorigin_std
        ,ROUND(MIN(rov_origin), 6)                           AS rovorigin_min
        ,ROUND(MAX(rov_origin), 6)                           AS rovorigin_max
        ,ROUND(PERCENTILE_APPROX(rov_origin, 0.50), 6)       AS rovorigin_p50
        ,ROUND(PERCENTILE_APPROX(rov_origin, 0.95), 6)       AS rovorigin_p95
        ,ROUND(SUM(CASE WHEN rov_origin IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS rovorigin_cov
        -- ────── rov_pred（DNN power 校准后，newNorDNNScore）──────
        ,ROUND(AVG(rov_pred), 6)                             AS rovpred_avg
        ,ROUND(STDDEV_POP(rov_pred), 6)                      AS rovpred_std
        ,ROUND(MIN(rov_pred), 6)                             AS rovpred_min
        ,ROUND(MAX(rov_pred), 6)                             AS rovpred_max
        ,ROUND(PERCENTILE_APPROX(rov_pred, 0.50), 6)         AS rovpred_p50
        ,ROUND(PERCENTILE_APPROX(rov_pred, 0.95), 6)         AS rovpred_p95
        ,ROUND(SUM(CASE WHEN rov_pred IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS rovpred_cov
        -- ────── vor（视频历史曝光裂变率，redis）──────
        ,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
        -- ────── ros_stat（视频历史回流率，redis hasReturnRovScore）──────
        ,ROUND(AVG(ros_stat), 6)                             AS rosstat_avg
        ,ROUND(STDDEV_POP(ros_stat), 6)                      AS rosstat_std
        ,ROUND(MIN(ros_stat), 6)                             AS rosstat_min
        ,ROUND(MAX(ros_stat), 6)                             AS rosstat_max
        ,ROUND(PERCENTILE_APPROX(ros_stat, 0.50), 6)         AS rosstat_p50
        ,ROUND(PERCENTILE_APPROX(ros_stat, 0.95), 6)         AS rosstat_p95
        ,ROUND(SUM(CASE WHEN ros_stat IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS rosstat_cov
        -- ────── c1_rovn（一级品类 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
        -- ────── b0_str（B0 流 STR；当前权重=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
        -- ────── b0_ror（B0 流 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
        -- ────── cn_rovn（CN 流 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
        -- ────── dn_rovn（DN 流 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
        -- ────── p_leave（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
        -- ────── new_p_leave = (1-leaveW·pLeave)^leaveExp ──────
        ,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
        -- ────── reduce_coef（重排品类/节日降权）──────
        ,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
;
