-- ════════════════════════════════════════════════════════════════════════════
-- ARP 按 flowpool 拆解：看各流量池的曝光占比和平均热度变化
-- 基于 base_all_new_v3_分层 精简
-- ════════════════════════════════════════════════════════════════════════════
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", "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", "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_base AS
(
    SELECT  sub.*
            ,sg.suffix_group
            ,COALESCE(m.abcode,"对照组") AS abcode
    FROM    (
                SELECT  dt
                        ,apptype
                        ,SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) AS suffix
                        ,a.mid
                        ,a.vid
                        ,flowpool
                FROM    loghubods.dwd_recsys_alg_exposure_base_20250108 a
                WHERE   dt="${dt}"
                AND     apptype IN ("0","4")
                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"))
            ) 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
)
-- 全局视频热度（不分桶，作为 popularity reference）
,t_vid_global_pop AS
(
    SELECT  dt
            ,apptype
            ,vid
            ,COUNT(1) AS vid_global_pop
    FROM    t_base
    GROUP BY dt, apptype, vid
)
-- 按桶×flowpool 聚合
,t_bucket_fp AS
(
    SELECT  b.dt
            ,b.apptype
            ,b.abcode
            ,b.suffix_group
            ,b.suffix
            ,COALESCE(b.flowpool, 'unknown') AS flowpool
            ,COUNT(1) AS exp_cnt
            ,COUNT(DISTINCT b.vid) AS vid_cnt
            ,SUM(g.vid_global_pop) / COUNT(1) AS avg_vid_pop
    FROM    t_base b
    LEFT JOIN t_vid_global_pop g
    ON      b.dt = g.dt
    AND     b.apptype = g.apptype
    AND     b.vid = g.vid
    GROUP BY b.dt, b.apptype, b.abcode, b.suffix_group, b.suffix, b.flowpool
)
-- 加上桶内总曝光，算曝光占比
,t_bucket_total AS
(
    SELECT  dt, apptype, abcode, suffix_group, suffix
            ,SUM(exp_cnt) AS total_exp
    FROM    t_bucket_fp
    GROUP BY dt, apptype, abcode, suffix_group, suffix
)
,t_bucket_with_pct AS
(
    SELECT  f.*
            ,ROUND(f.exp_cnt / t.total_exp, 6) AS exp_pct
    FROM    t_bucket_fp f
    JOIN    t_bucket_total t
    ON      f.dt = t.dt
    AND     f.apptype = t.apptype
    AND     f.abcode = t.abcode
    AND     f.suffix_group = t.suffix_group
    AND     f.suffix = t.suffix
)
-- 按 suffix_group 求尾号均值
,t_metrics AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,flowpool
            ,WM_CONCAT(DISTINCT ',', suffix) AS suffix
            ,AVG(exp_cnt) AS exp_cnt
            ,ROUND(AVG(exp_pct), 6) AS exp_pct
            ,ROUND(AVG(vid_cnt), 0) AS vid_cnt
            ,ROUND(AVG(avg_vid_pop), 0) AS avg_vid_pop
    FROM    t_bucket_with_pct
    GROUP BY dt, apptype, abcode, suffix_group, flowpool
)
-- 基线桶（89）
,t_ctrl AS
(
    SELECT  dt, apptype, flowpool
            ,exp_pct AS ctrl_exp_pct
            ,avg_vid_pop AS ctrl_avg_vid_pop
    FROM    t_metrics
    WHERE   suffix_group = '89'
)
SELECT  r.dt
        ,r.apptype
        ,r.abcode
        ,r.suffix_group
        ,r.suffix
        ,r.flowpool
        ,r.exp_cnt
        ,r.exp_pct
        ,r.vid_cnt
        ,r.avg_vid_pop
        -- vs 89
        ,ROUND(r.exp_pct - ctrl.ctrl_exp_pct, 6) AS exp_pct_diff
        ,ROUND(r.avg_vid_pop / NULLIF(ctrl.ctrl_avg_vid_pop, 0) - 1, 6) AS avg_vid_pop_diff
FROM    t_metrics r
LEFT JOIN t_ctrl ctrl
ON      r.dt = ctrl.dt
AND     r.apptype = ctrl.apptype
AND     r.flowpool = ctrl.flowpool
ORDER BY r.dt DESC, r.apptype, r.suffix_group, r.exp_pct DESC
;
