-- ════════════════════════════════════════════════════════════════════════════
-- 策略阶段分数 panel（底层宽表）
--   目的：分析各个 expCode 策略的「最终打分 + 阶段子分数」
--        - 是否正常（AVG/STDDEV 时序，分布稳定性）
--        - 策略间差异（横向对比 fmRov / NorXXScore / vor / c1Rovn / b0Str / ... 各阶段）
--
--   行粒度沿用 base_all_new_v3_分层.sql：
--     dt × apptype × abcode × suffix_group × source_type × layer_type
--   分数 _diff 不在底层做（diff 由上层分析视图按需即席算）；业务 _diff 保留
--
-- ┌─ 数据源切换 ────────────────────────────────────────────────────────┐
-- │  原 base SQL：dwd_recsys_alg_exposure_base_20250108                 │
-- │  本   SQL：dwd_recsys_alg_sample_all_20250212                      │
-- │  二者关系：sample_all = base + LEFT JOIN statistics_log + 各特征表 │
-- │           曝光行集合一致；scoresMap 装在 sample_all.extend_alg     │
-- │           finalScore 装在 sample_all.score（STRING，CAST DOUBLE）  │
-- │  scoresMap=NULL（埋点丢失）的曝光不过滤：                           │
-- │    - 业务指标（dau/exp/str/ros/...）和 base SQL 完全对齐            │
-- │    - 分数列 AVG/STDDEV 自动 IGNORE NULLS                            │
-- │    - 单列 scoresmap_coverage 监控覆盖率                             │
-- └────────────────────────────────────────────────────────────────────┘
--
-- ┌─ scoresMap 字段口径（直读 RankStrategy*.java + raw scoresmap 验证）──┐
-- │  expCode             839   562   563   565   566   ★备注              │
-- │  ── 召回分 ──                                                         │
-- │  fmRovOrigin         ✅    ✅    ✅    ✅    ✅   FM 反归一化前       │
-- │  fmRov               ✅    ✅    ✅    ✅    ✅   = restoreScore(...)   │
-- │  ── 排序模型分（XGB 与 DNN 互斥；Scorer pipeline 自动写入）──         │
-- │  NorXGBScore         ✅(*1) -     -     -    ✅(*1) Scorer 写         │
-- │  NorDNNScore         -    ✅    ✅    ✅    -    Scorer 写            │
-- │  newNorDNNScore      -    ✅    ✅    ✅    -    norPower 校准后       │
-- │  ── 业务先验 ──                                                       │
-- │  hasReturnRovScore   ✅    ✅    ✅    ✅    ✅   redis vid:rov         │
-- │  vor                 ✅    ✅    ✅    ✅    ✅   redis vid:vor         │
-- │  ── 加性项 (1h+24h 加权和) ──                                         │
-- │  c1RovnScore         ✅    ✅    ✅    ✅    ✅                        │
-- │  b0StrScore          ✅    ✅    ✅    ✅    ✅   线上权重当前=0       │
-- │  b0RorScore          ✅    ✅    ✅    ✅    ✅   线上权重当前=0       │
-- │  cnRovnScore         -    ✅    ✅    ✅    ✅   线上权重当前=0       │
-- │  dnRovnScore         -    ✅    ✅    ✅    ✅   线上权重当前=0       │
-- │  ── 离开概率 ──                                                       │
-- │  pLeave              ⚠️(*2) -     -    ✅    ✅   原始离开概率         │
-- │  newPLeave           -     -     -    ✅    ✅   = (1-w·pL)^exp      │
-- │  ── 重排阶段降权 (RankProcessorBoost) ──                              │
-- │  reduceCoefficient   ✅    ✅    ✅    ✅    ✅   ★乘进 finalScore     │
-- │  ── 最终分（独立列 sample_all.score）──                               │
-- │  finalScore          ✅    ✅    ✅    ✅    ✅                        │
-- │                                                                       │
-- │  (*1) newNorXGBScore 在 839/566 中算了但**未写回 scoresMap**         │
-- │       想用必须 SQL 重算 weight × pow(NorXGBScore, exp)（含钳位）      │
-- │  (*2) apptype=4 的 839 的 pLeave≈0.52 是 Scorer fallback 值，**不是   │
-- │       真实离开概率模型输出**——跨 expCode 比较时要排除该行            │
-- │                                                                       │
-- │  线上 finalScore 计算（按 expCode 分）：                              │
-- │    score_pre =                                                        │
-- │      839: fmRov × (rosAdd+rosW·newNorXGBScore)                       │
-- │           × (vorAdd+vorW·vor)                                         │
-- │           + c1+b0Str+b0Ror                                            │
-- │      562/563: fmRov × (rosAdd+rosW·newNorDNNScore)                   │
-- │               × (vorAdd+vorW·vor)                                     │
-- │               + c1+b0Str+b0Ror+cn+dn                                  │
-- │      565: fmRov × (rosAdd+rosW·newNorDNNScore)                        │
-- │           × (vorAdd+vorW·vor) × newPLeave                             │
-- │           + c1+b0Str+b0Ror+cn+dn                                      │
-- │      566: fmRov × (rosAdd+rosW·newNorXGBScore)                        │
-- │           × (vorAdd+vorW·vor) × newPLeave                             │
-- │           + c1+b0Str+b0Ror+cn+dn                                      │
-- │    finalScore = score_pre × reduceCoefficient  ← 重排阶段最后乘       │
-- └────────────────────────────────────────────────────────────────────┘
--
-- ┌─ 流量配置维护 ──────────────────────────────────────────────────────┐
-- │  t_exp_code_map 仅录入 20260425 当前生效配置                         │
-- │  来源：tasks/00_尾号实验/线上实验配置/流量配置/{date}.json           │
-- │  实验切流时追加新行（同 t_experiment_map 的 SCD Type 2 模式）       │
-- └────────────────────────────────────────────────────────────────────┘
-- ════════════════════════════════════════════════════════════════════════════
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, "20260413" AS start_dt, "20260415" AS end_dt
    UNION ALL SELECT "4", "ab", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "01", "实验组：变更str*ros建模目标实验", "20260320", "20260410"
    UNION ALL SELECT "4", "01", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "01", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "67", "实验组：变更str*ros建模目标实验", "20260330", "20260410"
    UNION ALL SELECT "4", "67", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "67", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "5d", "实验组：变更str*ros建模目标实验", "20260407", "20260410"
    UNION ALL SELECT "4", "5d", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "5d", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "34", "实验组：变更str*ros建模目标实验", "20260407", "20260410"
    UNION ALL SELECT "4", "34", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "34", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "67", "实验组：bn_ros新损失函数",        "20260311", "20260319"
    UNION ALL SELECT "4", "5d", "实验组：解构特征排序str模型",     "20260314", "20260406"
    UNION ALL SELECT "4", "ef", "实验组：解构特征排序str模型&召回", "20260314", "20260320"
    UNION ALL SELECT "4", "ef", "实验组：DNN模型",                 "20260407", "29991231"
    UNION ALL SELECT "4", "2c", "实验组：DNN模型-调参",            "20260413", "29991231"
    UNION ALL SELECT "4", "89", "对照组",                          "20260301", "299991231"

    -- ── apptype = 0 ──
    UNION ALL SELECT "0", "ab", "实验组：变更str*ros建模目标实验 有问题", "20260413", "20260415"
    UNION ALL SELECT "0", "ab", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "ab", "实验组：变更str*ros建模目标实验", "20260417", "29991231"
    UNION ALL SELECT "0", "01", "实验组：变更str*ros建模目标实验", "20260320", "20260410"
    UNION ALL SELECT "0", "01", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "01", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验", "20260330", "20260410"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "34", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验", "20260417", "20260424"
    UNION ALL SELECT "0", "34", "实验组：曝光建模v1+变更str*ros建模目标实验", "20260425", "20260425"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验", "20260426", "29991231"
    UNION ALL SELECT "0", "67", "实验组：变更str*ros建模目标实验", "20260330", "20260410"
    UNION ALL SELECT "0", "67", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "67", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "67", "实验组：变更str*ros建模目标实验", "20260417", "29991231"
    UNION ALL SELECT "0", "5d", "实验组：变更str*ros建模目标实验", "20260407", "20260410"
    UNION ALL SELECT "0", "5d", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "5d", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "5d", "实验组：变更str*ros建模目标实验", "20260417", "20260424"
    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"
)
-- ┌─ 流量配置 → expCode 映射 ──────────────────────────────────────────┐
-- │  来源：tasks/00_尾号实验/线上实验配置/流量配置/20260425.json        │
-- │  仅录入 20260425 起当前生效；流量切流时按 SCD Type 2 追加新行       │
-- └────────────────────────────────────────────────────────────────────┘
,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"
)
,t_base AS
(
    SELECT  sub.*
            ,sg.suffix_group
            ,COALESCE(m.abcode,"对照组") AS abcode
    FROM    (
                -- 第二层：从 scoresmap 解 12 个子分数（scoresmap 已在内层解出来一次，避免重复 GET_JSON_OBJECT）
                SELECT  inner_sub.*
                        ,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_score
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.NorDNNScore')        AS DOUBLE) AS nor_dnn_score
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.newNorDNNScore')     AS DOUBLE) AS new_nor_dnn_score
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.hasReturnRovScore')  AS DOUBLE) AS has_return_rov_score
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.vor')                AS DOUBLE) AS vor_score
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.c1RovnScore')        AS DOUBLE) AS c1_rovn_score
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.b0StrScore')         AS DOUBLE) AS b0_str_score
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.b0RorScore')         AS DOUBLE) AS b0_ror_score
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.cnRovnScore')        AS DOUBLE) AS cn_rovn_score
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.dnRovnScore')        AS DOUBLE) AS dn_rovn_score
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.pLeave')             AS DOUBLE) AS p_leave
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.newPLeave')          AS DOUBLE) AS new_p_leave
                        -- reduceCoefficient: 重排阶段的视频降权系数（品类降权/节日降权），
                        -- 直接乘进 sample_all.score 列。<1 表示该曝光被降权
                        ,CAST(GET_JSON_OBJECT(scoresmap, '$.reduceCoefficient')  AS DOUBLE) AS reduce_coefficient
                FROM    (
                            -- 第一层：从 sample_all 拉曝光 + label + cc/dd + 解 final_score / scoresmap raw
                            SELECT  dt
                                    ,apptype
                                    ,SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) AS suffix
                                    ,CASE   WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                                            WHEN page IN ("回流页","其他") THEN "非推荐"
                                            ELSE "其他"
                                    END AS page
                                    ,CASE WHEN a.rootsourceid = '' OR a.rootsourceid IS NULL THEN '内部' ELSE '外部' END AS source_type
                                    ,CASE WHEN GET_JSON_OBJECT(a.extend,'$.rootsessionid') = a.subsessionid THEN '首层' ELSE '裂变层' END AS layer_type
                                    ,a.mid
                                    ,a.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
                                    ,cc.cn
                                    ,cc.c1
                                    ,dd.dn
                                    ,dd.d1
                                    -- 新增：finalScore + scoresMap raw（scoresMap 在 extend_alg 里）
                                    ,CAST(a.score AS DOUBLE) AS final_score
                                    ,REPLACE(GET_JSON_OBJECT(a.extend_alg,'$.scoresMap'),"\\","") AS scoresmap
                            FROM    loghubods.dwd_recsys_alg_sample_all_20250212 a
                            LEFT JOIN   (
                                            -- c1/cn：分享后被点击的回流 UV（同 base SQL）
                                            SELECT  a.machinecode AS mid
                                                    ,a.subsessionid
                                                    ,a.videoid AS vid
                                                    ,COUNT(DISTINCT CASE WHEN b1.machinecode <> b2.machinecode THEN b2.machinecode END) AS cn
                                                    ,COUNT(DISTINCT CASE WHEN b2.sharedepth = 1 AND b1.machinecode <> b2.machinecode THEN b2.machinecode END) AS c1
                                            FROM    (
                                                        SELECT  DISTINCT machinecode
                                                                ,shareobjectid AS videoid
                                                                ,recomTraceId
                                                                ,subsessionid
                                                                ,sharedepth
                                                                ,shareid
                                                        FROM    loghubods.user_share_log
                                                        WHERE   dt = '${dt}'
                                                        AND     topic = 'share'
                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                    ) a
                                            LEFT JOIN   (
                                                            SELECT  DISTINCT machinecode
                                                                    ,clickobjectid
                                                                    ,recomTraceId
                                                                    ,subsessionid
                                                                    ,sharedepth
                                                                    ,rootshareid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${dt}'
                                                            AND     topic = 'click'
                                                        ) b
                                            ON      a.shareid = b.rootshareid
                                            LEFT JOIN   (
                                                            SELECT  DISTINCT machinecode
                                                                    ,shareobjectid
                                                                    ,recomTraceId
                                                                    ,subsessionid
                                                                    ,sharedepth
                                                                    ,shareid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${dt}'
                                                            AND     topic = 'share'
                                                            AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                        ) b1
                                            ON      b.machinecode = b1.machinecode
                                            AND     b.subsessionid = b1.subsessionid
                                            LEFT JOIN   (
                                                            SELECT  DISTINCT machinecode
                                                                    ,clickobjectid
                                                                    ,recomTraceId
                                                                    ,subsessionid
                                                                    ,sharedepth
                                                                    ,shareid
                                                                    ,rootshareid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${dt}'
                                                            AND     topic = 'click'
                                                        ) b2
                                            ON      b1.shareid = b2.rootshareid
                                            GROUP BY a.machinecode
                                                     ,a.subsessionid
                                                     ,a.videoid
                                        ) cc
                            ON      a.mid = cc.mid
                            AND     a.subsessionid = cc.subsessionid
                            AND     a.vid = cc.vid
                            LEFT JOIN   (
                                            -- d1/dn：下一条视频带来的回流（同 base SQL）
                                            SELECT  *
                                                    ,LAG(回流,1,0) OVER (PARTITION BY mid,subsessionid ORDER BY rn DESC) AS dn
                                                    ,LAG(回流1,1,0) OVER (PARTITION BY mid,subsessionid ORDER BY rn DESC) AS d1
                                            FROM    (
                                                        SELECT  a.mid AS mid
                                                                ,a.subsessionid
                                                                ,a.videoid AS vid
                                                                ,COUNT(DISTINCT b.shareid) AS 分享次数
                                                                ,COUNT(DISTINCT CASE WHEN c.machinecode <> b.machinecode THEN c.machinecode END) AS 回流
                                                                ,COUNT(DISTINCT CASE WHEN c.machinecode <> b.machinecode AND c.sharedepth = 1 THEN c.machinecode END) AS 回流1
                                                                ,ROW_NUMBER() OVER (PARTITION BY a.subsessionid ORDER BY a.logtimestamp ASC) AS rn
                                                        FROM    (
                                                                    SELECT  *
                                                                    FROM    (
                                                                                SELECT  DISTINCT mid
                                                                                        ,subsessionid
                                                                                        ,videoid
                                                                                        ,logtimestamp
                                                                                        ,ROW_NUMBER() OVER (PARTITION BY mid,subsessionid,videoid ORDER BY logtimestamp ASC) AS rn
                                                                                FROM    loghubods.video_action_log_rp
                                                                                WHERE   dt = '${dt}'
                                                                                AND     businesstype = 'videoView'
                                                                                AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                                            )
                                                                    WHERE   rn = 1
                                                                ) a
                                                        LEFT JOIN   (
                                                                        SELECT  DISTINCT machinecode
                                                                                ,shareobjectid AS videoid
                                                                                ,recomTraceId
                                                                                ,subsessionid
                                                                                ,sharedepth
                                                                                ,shareid
                                                                                ,clienttimestamp
                                                                        FROM    loghubods.user_share_log
                                                                        WHERE   dt = '${dt}'
                                                                        AND     topic = 'share'
                                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                                    ) b
                                                        ON      a.mid = b.machinecode
                                                        AND     a.subsessionid = b.subsessionid
                                                        AND     a.videoid = b.videoid
                                                        LEFT JOIN   (
                                                                        SELECT  DISTINCT machinecode
                                                                                ,clickobjectid
                                                                                ,recomTraceId
                                                                                ,subsessionid
                                                                                ,sharedepth
                                                                                ,rootshareid
                                                                        FROM    loghubods.user_share_log
                                                                        WHERE   dt = '${dt}'
                                                                        AND     topic = 'click'
                                                                    ) c
                                                        ON      b.shareid = c.rootshareid
                                                        GROUP BY a.mid
                                                                 ,a.subsessionid
                                                                 ,a.videoid
                                                                 ,a.logtimestamp
                                                    )
                                        ) dd
                            ON      a.mid = dd.mid
                            AND     a.subsessionid = dd.subsessionid
                            AND     a.vid = dd.vid
                            WHERE   dt="${dt}"
                            AND     apptype IN ("0","4")
                            AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页","回流页","其他")
                            -- apptype=4: ab0-ab9; apptype=0: ab0-ab4,ab8,ab9（无 ab5/ab6/ab7）
                            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"))
                        ) inner_sub
            ) sub
    -- INNER JOIN: 合法尾号才进分析
    INNER JOIN t_suffix_group sg
    ON      sub.suffix = sg.suffix
    -- LEFT JOIN: 无实验匹配 → COALESCE 为"对照组"
    LEFT JOIN t_experiment_map m
    ON      sub.apptype = m.apptype
    AND     sg.suffix_group = m.suffix_group
    AND     '${dt}' BETWEEN m.start_dt AND m.end_dt
)
-- 桶内每个 vid 的曝光数（ECS / ARP 共同中间件）
,t_vid_exp AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix
            ,COALESCE(source_type,'总体') AS source_type
            ,COALESCE(layer_type,'总体') AS layer_type
            ,vid
            ,COUNT(1) AS vid_exp_cnt
    FROM    t_base
    WHERE   page = "推荐"
    GROUP BY dt, apptype, abcode, suffix, vid, source_type, layer_type
    GROUPING SETS (
        (dt, apptype, abcode, suffix, vid, source_type, layer_type),
        (dt, apptype, abcode, suffix, vid)
    )
)
,t_bucket_ecs AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix
            ,source_type
            ,layer_type
            ,2 * SUM(p * rn) - 1 AS ecs
    FROM    (
                SELECT  dt
                        ,apptype
                        ,abcode
                        ,suffix
                        ,source_type
                        ,layer_type
                        ,vid_exp_cnt / SUM(vid_exp_cnt) OVER (
                            PARTITION BY dt, apptype, abcode, suffix, source_type, layer_type
                        ) AS p
                        ,ROW_NUMBER() OVER (
                            PARTITION BY dt, apptype, abcode, suffix, source_type, layer_type
                            ORDER BY vid_exp_cnt DESC
                        ) AS rn
                FROM    t_vid_exp
            ) t
    GROUP BY dt, apptype, abcode, suffix, source_type, layer_type
)
,t_vid_global_pop AS
(
    SELECT  dt
            ,apptype
            ,COALESCE(source_type,'总体') AS source_type
            ,COALESCE(layer_type,'总体') AS layer_type
            ,vid
            ,COUNT(1) AS vid_global_pop
    FROM    t_base
    WHERE   page = "推荐"
    GROUP BY dt, apptype, vid, source_type, layer_type
    GROUPING SETS (
        (dt, apptype, vid, source_type, layer_type),
        (dt, apptype, vid)
    )
)
,t_bucket_arp AS
(
    SELECT  v.dt
            ,v.apptype
            ,v.abcode
            ,v.suffix
            ,v.source_type
            ,v.layer_type
            ,SUM(v.vid_exp_cnt * g.vid_global_pop) / SUM(v.vid_exp_cnt) AS arp
    FROM    t_vid_exp v
    LEFT JOIN t_vid_global_pop g
    ON      v.dt = g.dt
    AND     v.apptype = g.apptype
    AND     v.source_type = g.source_type
    AND     v.layer_type = g.layer_type
    AND     v.vid = g.vid
    GROUP BY v.dt, v.apptype, v.abcode, v.suffix, v.source_type, v.layer_type
)
-- dau2：按单尾号聚合
,t_dau2_bucket AS
(
    SELECT  SUBSTR(sub.dt,1,8) AS dt
            ,sub.apptype
            ,COALESCE(m.abcode,"对照组") AS abcode
            ,sg.suffix_group
            ,sub.suffix
            ,COALESCE(sub.source_type,'总体') AS source_type
            ,COALESCE(sub.layer_type,'总体') AS layer_type
            ,COUNT(DISTINCT sub.machinecode) AS dau2
    FROM    (
                SELECT  dt
                        ,apptype
                        ,machinecode
                        ,SUBSTR(GET_JSON_OBJECT(extparams,'$.rootSessionId'),LENGTH(GET_JSON_OBJECT(extparams,'$.rootSessionId')),1) AS suffix
                        ,CASE WHEN GET_JSON_OBJECT(extparams,'$.rootSourceId') != '' AND GET_JSON_OBJECT(extparams,'$.rootSourceId') IS NOT NULL THEN '外部' ELSE '内部' END AS source_type
                        ,CASE WHEN GET_JSON_OBJECT(extparams,'$.rootSessionId') = subsessionid
                                OR GET_JSON_OBJECT(extparams,'$.rootSessionId') = sessionid THEN '首层' ELSE '裂变层' END AS layer_type
                FROM    loghubods.useractive_log
                WHERE   dt="${dt}"
                AND     apptype IN ("0","4")
                AND     (apptype = "4"
                         OR GET_JSON_OBJECT(extparams,'$.eventInfos.ab_test003') IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9"))
                AND     (apptype = "4"
                         OR GET_JSON_OBJECT(extparams,'$.eventInfos.ab_test003') NOT IN ("ab100"))
            ) sub
    INNER JOIN t_suffix_group sg
    ON      sub.suffix = sg.suffix
    LEFT JOIN t_experiment_map m
    ON      sub.apptype = m.apptype
    AND     sg.suffix_group = m.suffix_group
    AND     '${dt}' BETWEEN m.start_dt AND m.end_dt
    GROUP BY SUBSTR(sub.dt,1,8), sub.apptype, COALESCE(m.abcode,"对照组"), sg.suffix_group, sub.suffix
             ,sub.source_type, sub.layer_type
    GROUPING SETS (
        (SUBSTR(sub.dt,1,8), sub.apptype, COALESCE(m.abcode,"对照组"), sg.suffix_group, sub.suffix, sub.source_type, sub.layer_type),
        (SUBSTR(sub.dt,1,8), sub.apptype, COALESCE(m.abcode,"对照组"), sg.suffix_group, sub.suffix)
    )
)
,t_dau2 AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,source_type
            ,layer_type
            ,AVG(dau2) AS dau2
    FROM    t_dau2_bucket
    GROUP BY dt, apptype, abcode, suffix_group, source_type, layer_type
)
-- 桶级聚合：业务指标 + 分数 AVG/STDDEV + 埋点覆盖率
,t_bucket AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,suffix
            ,COALESCE(source_type,'总体') AS source_type
            ,COALESCE(layer_type,'总体') AS layer_type
            -- 业务指标（同 base SQL）
            ,COALESCE(COUNT(1) / COUNT(DISTINCT mid),0) AS exp_per_dau
            ,COALESCE(SUM(is_share) / COUNT(1),0) AS str_one
            ,COALESCE(SUM(return_n_uv) / SUM(is_share),0) AS ros_one
            ,COALESCE(SUM(share_cnt) / COUNT(1),0) AS str
            ,COALESCE(SUM(return_n_uv) / SUM(share_cnt),0) AS ros
            ,COALESCE(SUM(is_return_1) / COUNT(1),0) AS str_plus
            ,COALESCE(SUM(return_n_uv) / SUM(is_return_1),0) AS ros_minus
            ,COALESCE(SUM(return_n_uv) / COUNT(1),0) AS bn_rov
            ,COALESCE(SUM(c1) / COUNT(1),0) AS c1_rov
            ,COALESCE(SUM(cn) / COUNT(1),0) AS cn_rov
            ,COALESCE(SUM(d1) / COUNT(1),0) AS d1_rov
            ,COALESCE(SUM(dn) / COUNT(1),0) AS dn_rov
            ,COALESCE((SUM(return_n_uv) + SUM(cn) + SUM(dn)) / COUNT(1),0) AS total_rov
            ,COALESCE(SUM(new_exposure_cnt) / COUNT(1),0) AS vovh24
            ,COUNT(DISTINCT mid) AS dau
            ,COUNT(1) AS exp
            ,COUNT(DISTINCT vid) AS distinct_vid_cnt
            ,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
            ,COALESCE(SUM(cn),0) AS cn
            ,COALESCE(SUM(c1),0) AS c1
            ,COALESCE(SUM(dn),0) AS dn
            ,COALESCE(SUM(d1),0) AS d1
            -- ── 新增：分数 AVG / STDDEV / COVERAGE（NULLable 字段 cov<<1 时 _avg 是统计幻象） ──
            ,AVG(final_score)            AS final_score_avg
            ,STDDEV_POP(final_score)     AS final_score_std
            ,AVG(fmrov)                  AS fmrov_avg
            ,STDDEV_POP(fmrov)           AS fmrov_std
            ,AVG(fmrov_origin)           AS fmrov_origin_avg
            ,STDDEV_POP(fmrov_origin)    AS fmrov_origin_std
            ,SUM(CASE WHEN fmrov_origin IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1) AS fmrov_origin_cov
            ,AVG(nor_xgb_score)          AS nor_xgb_score_avg
            ,STDDEV_POP(nor_xgb_score)   AS nor_xgb_score_std
            ,SUM(CASE WHEN nor_xgb_score IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1) AS nor_xgb_score_cov
            ,AVG(nor_dnn_score)          AS nor_dnn_score_avg
            ,STDDEV_POP(nor_dnn_score)   AS nor_dnn_score_std
            ,SUM(CASE WHEN nor_dnn_score IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1) AS nor_dnn_score_cov
            ,AVG(new_nor_dnn_score)      AS new_nor_dnn_score_avg
            ,STDDEV_POP(new_nor_dnn_score) AS new_nor_dnn_score_std
            ,SUM(CASE WHEN new_nor_dnn_score IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1) AS new_nor_dnn_score_cov
            ,AVG(has_return_rov_score)   AS has_return_rov_score_avg
            ,STDDEV_POP(has_return_rov_score) AS has_return_rov_score_std
            ,AVG(vor_score)              AS vor_score_avg
            ,STDDEV_POP(vor_score)       AS vor_score_std
            ,AVG(c1_rovn_score)          AS c1_rovn_score_avg
            ,STDDEV_POP(c1_rovn_score)   AS c1_rovn_score_std
            ,AVG(b0_str_score)           AS b0_str_score_avg
            ,STDDEV_POP(b0_str_score)    AS b0_str_score_std
            ,AVG(b0_ror_score)           AS b0_ror_score_avg
            ,STDDEV_POP(b0_ror_score)    AS b0_ror_score_std
            ,AVG(cn_rovn_score)          AS cn_rovn_score_avg
            ,STDDEV_POP(cn_rovn_score)   AS cn_rovn_score_std
            ,SUM(CASE WHEN cn_rovn_score IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1) AS cn_rovn_score_cov
            ,AVG(dn_rovn_score)          AS dn_rovn_score_avg
            ,STDDEV_POP(dn_rovn_score)   AS dn_rovn_score_std
            ,SUM(CASE WHEN dn_rovn_score IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1) AS dn_rovn_score_cov
            ,AVG(p_leave)                AS p_leave_avg
            ,STDDEV_POP(p_leave)         AS p_leave_std
            ,SUM(CASE WHEN p_leave IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1) AS p_leave_cov
            ,AVG(new_p_leave)            AS new_p_leave_avg
            ,STDDEV_POP(new_p_leave)     AS new_p_leave_std
            ,SUM(CASE WHEN new_p_leave IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1) AS new_p_leave_cov
            ,AVG(reduce_coefficient)     AS reduce_coefficient_avg
            ,STDDEV_POP(reduce_coefficient) AS reduce_coefficient_std
            ,SUM(CASE WHEN reduce_coefficient IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1) AS reduce_coefficient_cov
            -- 埋点覆盖率（监控 sample_all LEFT JOIN statistics_log 的丢失程度）
            ,SUM(CASE WHEN scoresmap IS NOT NULL AND scoresmap <> '' THEN 1 ELSE 0 END) / COUNT(1) AS scoresmap_coverage
    FROM    t_base
    WHERE   page = "推荐"
    GROUP BY dt, apptype, abcode, suffix_group, suffix, source_type, layer_type
    GROUPING SETS (
        (dt, apptype, abcode, suffix_group, suffix, source_type, layer_type),
        (dt, apptype, abcode, suffix_group, suffix)
    )
)
-- 实验组级 metrics：从尾号桶 AVG（业务指标） + 桶内分数 AVG 再求平均
,t_metrics AS
(
    SELECT  b.dt
            ,b.apptype
            ,b.abcode
            ,b.suffix_group
            ,b.source_type
            ,b.layer_type
            -- 业务指标（同 base SQL）
            ,ROUND(AVG(b.exp_per_dau),2) AS exp_per_dau
            ,ROUND(AVG(b.str_one),6) AS str_one
            ,ROUND(AVG(b.ros_one),6) AS ros_one
            ,ROUND(AVG(b.str),6) AS str
            ,ROUND(AVG(b.ros),6) AS ros
            ,ROUND(AVG(b.str_plus),6) AS str_plus
            ,ROUND(AVG(b.ros_minus),6) AS ros_minus
            ,ROUND(AVG(b.bn_rov),6) AS bn_rov
            ,ROUND(AVG(b.c1_rov),6) AS c1_rov
            ,ROUND(AVG(b.cn_rov),6) AS cn_rov
            ,ROUND(AVG(b.d1_rov),6) AS d1_rov
            ,ROUND(AVG(b.dn_rov),6) AS dn_rov
            ,ROUND(AVG(b.total_rov),6) AS total_rov
            ,ROUND(AVG(b.vovh24),6) AS vovh24
            ,AVG(b.dau) AS dau
            ,AVG(b.exp) AS exp
            ,ROUND(AVG(b.distinct_vid_cnt),0) AS distinct_vid_cnt
            ,ROUND(AVG(e.ecs),1) AS ecs
            ,ROUND(AVG(e.ecs) / NULLIF(AVG(b.distinct_vid_cnt),0),6) AS ecs_ratio
            ,ROUND(1 - AVG(e.ecs) / NULLIF(AVG(b.distinct_vid_cnt),0),6) AS gini
            ,ROUND(AVG(a.arp),0) AS arp
            ,AVG(b.is_share) AS is_share
            ,AVG(b.share_cnt) AS share_cnt
            ,AVG(b.is_return_1) AS is_return_1
            ,AVG(b.return_n_uv) AS return_n_uv
            ,AVG(b.viewh24) AS viewh24
            ,AVG(b.return_n_uv_noself) AS return_n_uv_noself
            ,AVG(b.cn) AS cn
            ,AVG(b.c1) AS c1
            ,AVG(b.dn) AS dn
            ,AVG(b.d1) AS d1
            ,WM_CONCAT(DISTINCT ',',b.suffix) AS suffix
            -- ── 新增：分数 AVG / STDDEV（桶内 AVG/STDDEV 再求实验组平均）──
            -- AVG 含义：实验组内分数均值
            -- STDDEV 含义：实验组内"桶内分数离散度"的均值（监控分布坍塌的核心信号）
            ,ROUND(AVG(b.final_score_avg),6) AS final_score_avg
            ,ROUND(AVG(b.final_score_std),6) AS final_score_std
            ,ROUND(AVG(b.fmrov_avg),6) AS fmrov_avg
            ,ROUND(AVG(b.fmrov_std),6) AS fmrov_std
            ,ROUND(AVG(b.fmrov_origin_avg),6) AS fmrov_origin_avg
            ,ROUND(AVG(b.fmrov_origin_std),6) AS fmrov_origin_std
            ,ROUND(AVG(b.fmrov_origin_cov),4) AS fmrov_origin_cov
            ,ROUND(AVG(b.nor_xgb_score_avg),6) AS nor_xgb_score_avg
            ,ROUND(AVG(b.nor_xgb_score_std),6) AS nor_xgb_score_std
            ,ROUND(AVG(b.nor_xgb_score_cov),4) AS nor_xgb_score_cov
            ,ROUND(AVG(b.nor_dnn_score_avg),6) AS nor_dnn_score_avg
            ,ROUND(AVG(b.nor_dnn_score_std),6) AS nor_dnn_score_std
            ,ROUND(AVG(b.nor_dnn_score_cov),4) AS nor_dnn_score_cov
            ,ROUND(AVG(b.new_nor_dnn_score_avg),6) AS new_nor_dnn_score_avg
            ,ROUND(AVG(b.new_nor_dnn_score_std),6) AS new_nor_dnn_score_std
            ,ROUND(AVG(b.new_nor_dnn_score_cov),4) AS new_nor_dnn_score_cov
            ,ROUND(AVG(b.has_return_rov_score_avg),6) AS has_return_rov_score_avg
            ,ROUND(AVG(b.has_return_rov_score_std),6) AS has_return_rov_score_std
            ,ROUND(AVG(b.vor_score_avg),6) AS vor_score_avg
            ,ROUND(AVG(b.vor_score_std),6) AS vor_score_std
            ,ROUND(AVG(b.c1_rovn_score_avg),6) AS c1_rovn_score_avg
            ,ROUND(AVG(b.c1_rovn_score_std),6) AS c1_rovn_score_std
            ,ROUND(AVG(b.b0_str_score_avg),6) AS b0_str_score_avg
            ,ROUND(AVG(b.b0_str_score_std),6) AS b0_str_score_std
            ,ROUND(AVG(b.b0_ror_score_avg),6) AS b0_ror_score_avg
            ,ROUND(AVG(b.b0_ror_score_std),6) AS b0_ror_score_std
            ,ROUND(AVG(b.cn_rovn_score_avg),6) AS cn_rovn_score_avg
            ,ROUND(AVG(b.cn_rovn_score_std),6) AS cn_rovn_score_std
            ,ROUND(AVG(b.cn_rovn_score_cov),4) AS cn_rovn_score_cov
            ,ROUND(AVG(b.dn_rovn_score_avg),6) AS dn_rovn_score_avg
            ,ROUND(AVG(b.dn_rovn_score_std),6) AS dn_rovn_score_std
            ,ROUND(AVG(b.dn_rovn_score_cov),4) AS dn_rovn_score_cov
            ,ROUND(AVG(b.p_leave_avg),6) AS p_leave_avg
            ,ROUND(AVG(b.p_leave_std),6) AS p_leave_std
            ,ROUND(AVG(b.p_leave_cov),4) AS p_leave_cov
            ,ROUND(AVG(b.new_p_leave_avg),6) AS new_p_leave_avg
            ,ROUND(AVG(b.new_p_leave_std),6) AS new_p_leave_std
            ,ROUND(AVG(b.new_p_leave_cov),4) AS new_p_leave_cov
            ,ROUND(AVG(b.reduce_coefficient_avg),6) AS reduce_coefficient_avg
            ,ROUND(AVG(b.reduce_coefficient_std),6) AS reduce_coefficient_std
            ,ROUND(AVG(b.reduce_coefficient_cov),4) AS reduce_coefficient_cov
            ,ROUND(AVG(b.scoresmap_coverage),4) AS scoresmap_coverage
    FROM    t_bucket b
    LEFT JOIN t_bucket_ecs e
    ON      b.dt = e.dt
    AND     b.apptype = e.apptype
    AND     b.abcode = e.abcode
    AND     b.suffix = e.suffix
    AND     b.source_type = e.source_type
    AND     b.layer_type = e.layer_type
    LEFT JOIN t_bucket_arp a
    ON      b.dt = a.dt
    AND     b.apptype = a.apptype
    AND     b.abcode = a.abcode
    AND     b.suffix = a.suffix
    AND     b.source_type = a.source_type
    AND     b.layer_type = a.layer_type
    GROUP BY b.dt, b.apptype, b.abcode, b.suffix_group, b.source_type, b.layer_type
)
-- ════════════════════════════════════════════════════════════════════════════
-- 基线对比层（业务指标的 _diff，分数列不参与）
-- ════════════════════════════════════════════════════════════════════════════
,t_dau2_base5 AS
(
    -- ── apptype = 0 ──
    SELECT "0" AS apptype, "01" AS suffix_group, "内部"  AS source_type, "裂变层" AS layer_type, 15293.4  AS dau2_base5
    UNION ALL SELECT "0", "01", "内部",  "首层",   5649.5
    UNION ALL SELECT "0", "01", "外部",  "裂变层", 187201.8
    UNION ALL SELECT "0", "01", "外部",  "首层",   44738.8
    UNION ALL SELECT "0", "01", "总体",  "总体",   250063.2
    UNION ALL SELECT "0", "2c", "内部",  "裂变层", 16274.4
    UNION ALL SELECT "0", "2c", "内部",  "首层",   5638.2
    UNION ALL SELECT "0", "2c", "外部",  "裂变层", 197492.2
    UNION ALL SELECT "0", "2c", "外部",  "首层",   44625.3
    UNION ALL SELECT "0", "2c", "总体",  "总体",   261125.5
    UNION ALL SELECT "0", "34", "内部",  "裂变层", 15779.9
    UNION ALL SELECT "0", "34", "内部",  "首层",   5646.3
    UNION ALL SELECT "0", "34", "外部",  "裂变层", 194802.8
    UNION ALL SELECT "0", "34", "外部",  "首层",   44646.0
    UNION ALL SELECT "0", "34", "总体",  "总体",   258002.4
    UNION ALL SELECT "0", "5d", "内部",  "裂变层", 15828.0
    UNION ALL SELECT "0", "5d", "内部",  "首层",   5639.7
    UNION ALL SELECT "0", "5d", "外部",  "裂变层", 190163.7
    UNION ALL SELECT "0", "5d", "外部",  "首层",   44828.3
    UNION ALL SELECT "0", "5d", "总体",  "总体",   253589.2
    UNION ALL SELECT "0", "67", "内部",  "裂变层", 15757.9
    UNION ALL SELECT "0", "67", "内部",  "首层",   5627.2
    UNION ALL SELECT "0", "67", "外部",  "裂变层", 195371.1
    UNION ALL SELECT "0", "67", "外部",  "首层",   44620.4
    UNION ALL SELECT "0", "67", "总体",  "总体",   258466.2
    UNION ALL SELECT "0", "89", "内部",  "裂变层", 15889.8
    UNION ALL SELECT "0", "89", "内部",  "首层",   5628.7
    UNION ALL SELECT "0", "89", "外部",  "裂变层", 187814.3
    UNION ALL SELECT "0", "89", "外部",  "首层",   44561.2
    UNION ALL SELECT "0", "89", "总体",  "总体",   251052.3
    UNION ALL SELECT "0", "ab", "内部",  "裂变层", 16179.7
    UNION ALL SELECT "0", "ab", "内部",  "首层",   5661.8
    UNION ALL SELECT "0", "ab", "外部",  "裂变层", 184483.2
    UNION ALL SELECT "0", "ab", "外部",  "首层",   44617.9
    UNION ALL SELECT "0", "ab", "总体",  "总体",   248110.7
    UNION ALL SELECT "0", "ef", "内部",  "裂变层", 15500.8
    UNION ALL SELECT "0", "ef", "内部",  "首层",   5664.6
    UNION ALL SELECT "0", "ef", "外部",  "裂变层", 184847.8
    UNION ALL SELECT "0", "ef", "外部",  "首层",   44637.9
    UNION ALL SELECT "0", "ef", "总体",  "总体",   247799.1
    -- ── apptype = 4 ──
    UNION ALL SELECT "4", "01", "内部",  "裂变层", 9358.8
    UNION ALL SELECT "4", "01", "内部",  "首层",   3953.4
    UNION ALL SELECT "4", "01", "外部",  "裂变层", 129526.8
    UNION ALL SELECT "4", "01", "外部",  "首层",   82839.8
    UNION ALL SELECT "4", "01", "总体",  "总体",   221923.1
    UNION ALL SELECT "4", "2c", "内部",  "裂变层", 9556.8
    UNION ALL SELECT "4", "2c", "内部",  "首层",   3935.1
    UNION ALL SELECT "4", "2c", "外部",  "裂变层", 131426.8
    UNION ALL SELECT "4", "2c", "外部",  "首层",   82767.1
    UNION ALL SELECT "4", "2c", "总体",  "总体",   223926.3
    UNION ALL SELECT "4", "34", "内部",  "裂变层", 11739.7
    UNION ALL SELECT "4", "34", "内部",  "首层",   3892.0
    UNION ALL SELECT "4", "34", "外部",  "裂变层", 126370.7
    UNION ALL SELECT "4", "34", "外部",  "首层",   82681.9
    UNION ALL SELECT "4", "34", "总体",  "总体",   220940.6
    UNION ALL SELECT "4", "5d", "内部",  "裂变层", 9999.3
    UNION ALL SELECT "4", "5d", "内部",  "首层",   3950.1
    UNION ALL SELECT "4", "5d", "外部",  "裂变层", 128746.6
    UNION ALL SELECT "4", "5d", "外部",  "首层",   82744.1
    UNION ALL SELECT "4", "5d", "总体",  "总体",   221669.4
    UNION ALL SELECT "4", "67", "内部",  "裂变层", 9685.2
    UNION ALL SELECT "4", "67", "内部",  "首层",   3942.3
    UNION ALL SELECT "4", "67", "外部",  "裂变层", 125356.4
    UNION ALL SELECT "4", "67", "外部",  "首层",   82720.8
    UNION ALL SELECT "4", "67", "总体",  "总体",   217974.6
    UNION ALL SELECT "4", "89", "内部",  "裂变层", 11370.5
    UNION ALL SELECT "4", "89", "内部",  "首层",   3964.6
    UNION ALL SELECT "4", "89", "外部",  "裂变层", 130128.5
    UNION ALL SELECT "4", "89", "外部",  "首层",   82614.5
    UNION ALL SELECT "4", "89", "总体",  "总体",   224279.4
    UNION ALL SELECT "4", "ab", "内部",  "裂变层", 9481.3
    UNION ALL SELECT "4", "ab", "内部",  "首层",   3933.9
    UNION ALL SELECT "4", "ab", "外部",  "裂变层", 129952.9
    UNION ALL SELECT "4", "ab", "外部",  "首层",   82784.3
    UNION ALL SELECT "4", "ab", "总体",  "总体",   222393.9
    UNION ALL SELECT "4", "ef", "内部",  "裂变层", 9533.7
    UNION ALL SELECT "4", "ef", "内部",  "首层",   3947.6
    UNION ALL SELECT "4", "ef", "外部",  "裂变层", 127244.0
    UNION ALL SELECT "4", "ef", "外部",  "首层",   82754.4
    UNION ALL SELECT "4", "ef", "总体",  "总体",   219735.4
)
-- 合并主表 + dau2 + dau_vs_5d + expCode
,t_combined AS
(
    SELECT  a.*
            ,b.dau2
            ,ROUND(b.dau2 / NULLIF(c.dau2_base5, 0), 6) AS dau_vs_5d
            ,e.expcode
    FROM    t_metrics a
    LEFT JOIN t_dau2 b
    ON      a.dt = b.dt
    AND     a.apptype = b.apptype
    AND     a.abcode = b.abcode
    AND     a.suffix_group = b.suffix_group
    AND     a.source_type = b.source_type
    AND     a.layer_type = b.layer_type
    LEFT JOIN t_dau2_base5 c
    ON      a.apptype = c.apptype
    AND     a.suffix_group = c.suffix_group
    AND     a.source_type = c.source_type
    AND     a.layer_type = c.layer_type
    LEFT JOIN t_exp_code_map e
    ON      a.apptype = e.apptype
    AND     a.suffix_group = e.suffix_group
    AND     a.dt BETWEEN e.start_dt AND e.end_dt
)
-- 基线桶（89）每日业务指标，作为横向对比基准（分数列不在此参与，留给上层视图）
,t_ctrl AS
(
    SELECT  dt
            ,apptype
            ,source_type
            ,layer_type
            ,dau_vs_5d  AS ctrl_dau_vs_5d
            ,exp        AS ctrl_exp
            ,exp_per_dau AS ctrl_exp_per_dau
            ,str_one    AS ctrl_str_one
            ,ros_one    AS ctrl_ros_one
            ,str        AS ctrl_str
            ,ros        AS ctrl_ros
            ,vovh24     AS ctrl_vovh24
            ,str_plus   AS ctrl_str_plus
            ,ros_minus  AS ctrl_ros_minus
            ,bn_rov     AS ctrl_bn_rov
            ,c1_rov     AS ctrl_c1_rov
            ,cn_rov     AS ctrl_cn_rov
            ,d1_rov     AS ctrl_d1_rov
            ,dn_rov     AS ctrl_dn_rov
            ,total_rov  AS ctrl_total_rov
            ,ecs        AS ctrl_ecs
            ,ecs_ratio  AS ctrl_ecs_ratio
            ,arp        AS ctrl_arp
    FROM    t_combined
    WHERE   suffix_group = '89'
)
-- ════════════════════════════════════════════════════════════════════════════
-- 最终输出
--   - 业务指标 + 业务 _diff（vs 89 桶；同 base SQL）
--   - 13 个分数 _avg / _std（IGNORE NULLS）
--   - scoresmap_coverage（埋点覆盖率监控）
--   - expcode（流量配置策略号）
-- ════════════════════════════════════════════════════════════════════════════
SELECT  r.dt
        ,r.apptype
        ,r.expcode
        ,r.abcode
        ,r.suffix_group
        ,r.source_type
        ,r.layer_type
        ,r.suffix
        -- ── 业务指标（同 base SQL）──
        ,r.exp_per_dau
        ,r.str_one
        ,r.ros_one
        ,r.str
        ,r.ros
        ,r.str_plus
        ,r.ros_minus
        ,r.bn_rov
        ,r.c1_rov
        ,r.cn_rov
        ,r.d1_rov
        ,r.dn_rov
        ,r.total_rov
        ,r.vovh24
        ,r.dau
        ,r.exp
        ,r.distinct_vid_cnt
        ,r.ecs
        ,r.ecs_ratio
        ,r.gini
        ,r.arp
        ,r.is_share
        ,r.share_cnt
        ,r.is_return_1
        ,r.return_n_uv
        ,r.viewh24
        ,r.return_n_uv_noself
        ,r.cn
        ,r.c1
        ,r.dn
        ,r.d1
        ,r.dau2
        -- ── 模型分数 panel（13 × 2 = 26 列）──
        ,r.final_score_avg
        ,r.final_score_std
        ,r.fmrov_avg
        ,r.fmrov_std
        ,r.fmrov_origin_avg
        ,r.fmrov_origin_std
        ,r.fmrov_origin_cov
        ,r.nor_xgb_score_avg
        ,r.nor_xgb_score_std
        ,r.nor_xgb_score_cov
        ,r.nor_dnn_score_avg
        ,r.nor_dnn_score_std
        ,r.nor_dnn_score_cov
        ,r.new_nor_dnn_score_avg
        ,r.new_nor_dnn_score_std
        ,r.new_nor_dnn_score_cov
        ,r.has_return_rov_score_avg
        ,r.has_return_rov_score_std
        ,r.vor_score_avg
        ,r.vor_score_std
        ,r.c1_rovn_score_avg
        ,r.c1_rovn_score_std
        ,r.b0_str_score_avg
        ,r.b0_str_score_std
        ,r.b0_ror_score_avg
        ,r.b0_ror_score_std
        ,r.cn_rovn_score_avg
        ,r.cn_rovn_score_std
        ,r.cn_rovn_score_cov
        ,r.dn_rovn_score_avg
        ,r.dn_rovn_score_std
        ,r.dn_rovn_score_cov
        ,r.p_leave_avg
        ,r.p_leave_std
        ,r.p_leave_cov
        ,r.new_p_leave_avg
        ,r.new_p_leave_std
        ,r.new_p_leave_cov
        ,r.reduce_coefficient_avg
        ,r.reduce_coefficient_std
        ,r.reduce_coefficient_cov
        -- ── 埋点覆盖率（监控 scoresMap=NULL 的曝光占比，越接近 0 越说明 sample_all LEFT JOIN 丢失严重）──
        ,r.scoresmap_coverage
        -- ── DAU2 纵向对比 ──
        ,r.dau_vs_5d
        -- ── 业务指标横向 _diff（vs 89 对照桶）──
        ,ROUND(r.dau_vs_5d / NULLIF(ctrl.ctrl_dau_vs_5d, 0) - 1, 6) AS dau_vs_5d_diff
        ,ROUND(r.exp / NULLIF(ctrl.ctrl_exp, 0) - 1, 6) AS exp_diff
        ,ROUND((1 + r.exp_per_dau / NULLIF(ctrl.ctrl_exp_per_dau, 0) - 1)
             * (1 + r.total_rov / NULLIF(ctrl.ctrl_total_rov, 0) - 1)
             - 1, 6) AS exp_rov_combined_diff
        ,ROUND(r.exp_per_dau / NULLIF(ctrl.ctrl_exp_per_dau, 0) - 1, 6) AS exp_per_dau_diff
        ,ROUND(r.str_one / NULLIF(ctrl.ctrl_str_one, 0) - 1, 6) AS str_one_diff
        ,ROUND(r.ros_one / NULLIF(ctrl.ctrl_ros_one, 0) - 1, 6) AS ros_one_diff
        ,ROUND(r.str / NULLIF(ctrl.ctrl_str, 0) - 1, 6) AS str_diff
        ,ROUND(r.ros / NULLIF(ctrl.ctrl_ros, 0) - 1, 6) AS ros_diff
        ,ROUND(r.vovh24 / NULLIF(ctrl.ctrl_vovh24, 0) - 1, 6) AS vovh24_diff
        ,ROUND(r.str_plus / NULLIF(ctrl.ctrl_str_plus, 0) - 1, 6) AS str_plus_diff
        ,ROUND(r.ros_minus / NULLIF(ctrl.ctrl_ros_minus, 0) - 1, 6) AS ros_minus_diff
        ,ROUND(r.bn_rov / NULLIF(ctrl.ctrl_bn_rov, 0) - 1, 6) AS bn_rov_diff
        ,ROUND(r.c1_rov / NULLIF(ctrl.ctrl_c1_rov, 0) - 1, 6) AS c1_rov_diff
        ,ROUND(r.cn_rov / NULLIF(ctrl.ctrl_cn_rov, 0) - 1, 6) AS cn_rov_diff
        ,ROUND(r.d1_rov / NULLIF(ctrl.ctrl_d1_rov, 0) - 1, 6) AS d1_rov_diff
        ,ROUND(r.dn_rov / NULLIF(ctrl.ctrl_dn_rov, 0) - 1, 6) AS dn_rov_diff
        ,ROUND(r.total_rov / NULLIF(ctrl.ctrl_total_rov, 0) - 1, 6) AS total_rov_diff
        ,ROUND(r.ecs / NULLIF(ctrl.ctrl_ecs, 0) - 1, 6) AS ecs_diff
        ,ROUND(r.ecs_ratio / NULLIF(ctrl.ctrl_ecs_ratio, 0) - 1, 6) AS ecs_ratio_diff
        ,ROUND(r.arp / NULLIF(ctrl.ctrl_arp, 0) - 1, 6) AS arp_diff
FROM    t_combined r
LEFT JOIN t_ctrl ctrl
ON      r.dt = ctrl.dt
AND     r.apptype = ctrl.apptype
AND     r.source_type = ctrl.source_type
AND     r.layer_type = ctrl.layer_type
ORDER BY r.dt DESC, r.apptype, r.expcode, r.abcode, r.suffix_group, r.source_type, r.layer_type
;
