-- ════════════════════════════════════════════════════════════════════════════
-- 策略调权简表（精简版）
--   设计：只保留每个字段的 AVG，**字段名直接用原物理量名**（不加 _avg 后缀）
--        cov 列保留（兄弟列，监控覆盖率防幻象均值），用 _cov 后缀
--
--   ┌─ 字段命名 ────────────────────────────────────────────────────────┐
--   │  真实值：str / strx / ros / rosx / rov / vovh24                    │
--   │  模型预估：str_origin / str_pred / ros_origin / ros_pred /         │
--   │           rov_origin / rov_pred                                    │
--   │  其他不校准：vor / ros_stat / c1_rovn / b0_str / b0_ror /          │
--   │             cn_rovn / dn_rovn / p_leave / new_p_leave /            │
--   │             reduce_coef / final_score                              │
--   │  覆盖率：每个分数列配 *_cov                                          │
--   │  校验：strpredre_diff_pct（SQL 重算 vs 线上 fmRov 偏差）            │
--   └────────────────────────────────────────────────────────────────────┘
--
--   ┌─ apollo 参数化 ───────────────────────────────────────────────────┐
--   │  t_apollo_weight CTE 维护每个 expCode 的权重                       │
--   │  apollo 改了 → 只更新 CTE，其他 SQL 不动                            │
--   │  expcode 缺失走代码默认值（1.22 / 1.15），expcode_known=0 标识      │
--   └────────────────────────────────────────────────────────────────────┘
--
--   ┌─ 调权 pair（按 expCode 对照真实/预估）────────────────────────────┐
--   │  839（XGB 基线）：strx ↔ str_pred ；rosx ↔ ros_pred                │
--   │  566（XGB 新建模）：str ↔ str_pred；ros ↔ ros_pred                  │
--   │  562/563/565（DNN）：str ↔ str_pred；rov ↔ rov_pred                 │
--   └────────────────────────────────────────────────────────────────────┘
-- ════════════════════════════════════════════════════════════════════════════
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配置/
,t_apollo_weight AS
(
    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 + 模型预估字段
,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
            -- ── 模型预估（按物理量拆字段，按 expCode 填值；没预估的 NULL）──
            -- raw 字段（先解出 scoresMap 原始值）
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRovOrigin')      AS DOUBLE) AS _fmrov_origin
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRov')            AS DOUBLE) AS _fmrov
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.NorXGBScore')      AS DOUBLE) AS _nor_xgb
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.NorDNNScore')      AS DOUBLE) AS _nor_dnn
            ,CAST(GET_JSON_OBJECT(a.scoresmap, '$.newNorDNNScore')   AS DOUBLE) AS _new_nor_dnn
            -- power 校准后的 XGB（SQL 重算 newNorXGBScore，apollo 参数化）
            ,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 _new_nor_xgb
            -- 按 expCode 分配字段（origin = 模型直出，pred = 校准后）
            ,CASE WHEN e.expcode = '839' THEN CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRovOrigin') AS DOUBLE) END AS strx_origin
            ,CASE WHEN e.expcode = '839' THEN CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRov')       AS DOUBLE) END AS strx_pred
            ,CASE WHEN e.expcode IN ('562','563','565','566') THEN CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRovOrigin') AS DOUBLE) END AS str_origin
            ,CASE WHEN e.expcode IN ('562','563','565','566') THEN CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRov')       AS DOUBLE) END AS str_pred
            ,CASE WHEN e.expcode = '839' THEN CAST(GET_JSON_OBJECT(a.scoresmap, '$.NorXGBScore') AS DOUBLE) END AS rosx_origin
            ,CASE WHEN e.expcode = '566' THEN CAST(GET_JSON_OBJECT(a.scoresmap, '$.NorXGBScore') AS DOUBLE) END AS ros_origin
            ,CASE WHEN e.expcode IN ('562','563','565') THEN CAST(GET_JSON_OBJECT(a.scoresmap, '$.NorDNNScore') AS DOUBLE) END AS rovx_origin
            -- _pred 用 power 校准后值
            ,CASE WHEN e.expcode = '839' 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 rosx_pred
            ,CASE WHEN e.expcode = '566' 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
            ,CASE WHEN e.expcode IN ('562','563','565') THEN
                CAST(GET_JSON_OBJECT(a.scoresmap, '$.newNorDNNScore') AS DOUBLE) END AS rovx_pred
            -- ── rov_pred 单条曝光级派生（统一物理量 = return_n_uv_noself / pv 预估）──
            -- 839: strx_pred × rosx_pred
            -- 566: str_pred × ros_pred
            -- DNN(562/563/565): str_pred × rovx_pred
            ,CASE
                WHEN e.expcode = '839' THEN
                    CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRov') AS DOUBLE) *
                    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))
                    ))
                WHEN e.expcode = '566' THEN
                    CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRov') AS DOUBLE) *
                    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))
                    ))
                WHEN e.expcode IN ('562','563','565') THEN
                    CAST(GET_JSON_OBJECT(a.scoresmap, '$.fmRov') AS DOUBLE) *
                    CAST(GET_JSON_OBJECT(a.scoresmap, '$.newNorDNNScore') AS DOUBLE)
             END                                                                  AS rov_pred
            -- ── 校验列：SQL 重算 str_pred ──
            ,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     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
)
-- 聚合输出：每个分数只 AVG（字段名 = 原物理量名，不加 _avg），配 *_cov
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
        -- ── 业务真实计数（字段名 = 原 label 名，聚合是 SUM）──
        ,COALESCE(SUM(is_share),0) AS is_share
        ,COALESCE(SUM(share_cnt),0) AS share_cnt
        ,COALESCE(SUM(is_return_noself),0) AS is_return_noself
        ,COALESCE(SUM(return_n_uv_noself),0) AS return_n_uv_noself
        ,COALESCE(SUM(new_exposure_cnt),0) AS new_exposure_cnt
        -- ── 业务真实率（无后缀）──
        ,ROUND(COALESCE(SUM(is_share) / COUNT(1), 0), 6) AS str
        ,ROUND(COALESCE(SUM(is_return_noself) / COUNT(1), 0), 6) AS strx
        ,ROUND(COALESCE(SUM(return_n_uv_noself) / NULLIF(SUM(is_share), 0), 0), 6) AS ros
        ,ROUND(COALESCE(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0), 0), 6) AS rosx
        ,ROUND(COALESCE(SUM(return_n_uv_noself) / COUNT(1), 0), 6) AS rov
        ,ROUND(COALESCE(SUM(new_exposure_cnt) / COUNT(1), 0), 6) AS vovh24
        -- ── 模型预估 AVG（按物理量拆，按 expCode 自动 NULL 不该填的）──
        -- 基线 839 路径
        ,ROUND(AVG(strx_origin), 6)  AS strx_origin
        ,ROUND(AVG(strx_pred), 6)    AS strx_pred
        ,ROUND(AVG(rosx_origin), 6)  AS rosx_origin
        ,ROUND(AVG(rosx_pred), 6)    AS rosx_pred
        -- 新建模 + DNN 路径（str 共享）
        ,ROUND(AVG(str_origin), 6)   AS str_origin
        ,ROUND(AVG(str_pred), 6)     AS str_pred
        -- 新建模 566 路径
        ,ROUND(AVG(ros_origin), 6)   AS ros_origin
        ,ROUND(AVG(ros_pred), 6)     AS ros_pred
        -- DNN 562/563/565 路径
        ,ROUND(AVG(rovx_origin), 6)  AS rovx_origin
        ,ROUND(AVG(rovx_pred), 6)    AS rovx_pred
        -- 派生：单条曝光级 str×ros（或 strx×rosx 或 str×rovx）求平均，跨 expCode 物理量统一
        ,ROUND(AVG(rov_pred), 6)     AS rov_pred
        -- ── 校验：SQL 重算 vs 线上 fmRov 偏差，≈0 = 公式对齐 ──
        ,ROUND(AVG(str_pred_recompute) / NULLIF(AVG(_fmrov), 0) - 1, 6) AS strpredre_diff_pct
        -- ── 其他不校准字段 AVG ──
        ,ROUND(AVG(final_score), 6)  AS final_score
        ,ROUND(AVG(vor), 6)          AS vor
        ,ROUND(AVG(ros_stat), 6)     AS ros_stat
        ,ROUND(AVG(c1_rovn), 6)      AS c1_rovn
        ,ROUND(AVG(b0_str), 6)       AS b0_str
        ,ROUND(AVG(b0_ror), 6)       AS b0_ror
        ,ROUND(AVG(cn_rovn), 6)      AS cn_rovn
        ,ROUND(AVG(dn_rovn), 6)      AS dn_rovn
        ,ROUND(AVG(p_leave), 6)      AS p_leave
        ,ROUND(AVG(new_p_leave), 6)  AS new_p_leave
        ,ROUND(AVG(reduce_coef), 6)  AS reduce_coef
        -- ── 全局数据质量监控 ──
        ,ROUND(SUM(CASE WHEN final_score IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS scoresmap_coverage
FROM    t_scored
GROUP BY dt, apptype, suffix_group
GROUPING SETS (
    (dt, apptype),
    (dt, apptype, suffix_group)
)
ORDER BY dt DESC, apptype, suffix_group
;
