-- ════════════════════════════════════════════════════════════════════════════
-- 头部视频分析：按全局热度分档，看各档的曝光占比变化
-- 分档：top100 / top1000 / top5000 / 长尾
-- ════════════════════════════════════════════════════════════════════════════
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, "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"
    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
                        ,vid
                FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
                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
)
-- 全局视频热度排名
,t_vid_rank AS
(
    SELECT  dt
            ,apptype
            ,vid
            ,COUNT(1) AS vid_global_exp
            ,ROW_NUMBER() OVER (PARTITION BY dt, apptype ORDER BY COUNT(1) DESC) AS pop_rank
    FROM    t_base
    GROUP BY dt, apptype, vid
)
-- 给每个视频打热度分档标签
,t_vid_tier AS
(
    SELECT  *
            ,CASE
                WHEN pop_rank <= 100  THEN '1_top100'
                WHEN pop_rank <= 1000 THEN '2_top1000'
                WHEN pop_rank <= 5000 THEN '3_top5000'
                ELSE '4_长尾'
             END AS vid_tier
    FROM    t_vid_rank
)
-- 按桶×视频档位聚合曝光
,t_bucket AS
(
    SELECT  b.dt
            ,b.apptype
            ,b.abcode
            ,b.suffix_group
            ,b.suffix
            ,v.vid_tier
            ,COUNT(1) AS exp_cnt
    FROM    t_base b
    LEFT JOIN t_vid_tier v
    ON      b.dt = v.dt
    AND     b.apptype = v.apptype
    AND     b.vid = v.vid
    GROUP BY b.dt, b.apptype, b.abcode, b.suffix_group, b.suffix, v.vid_tier
)
-- 加上桶内总曝光算占比
,t_total AS
(
    SELECT  dt, apptype, abcode, suffix_group, suffix
            ,SUM(exp_cnt) AS total_exp
    FROM    t_bucket
    GROUP BY dt, apptype, abcode, suffix_group, suffix
)
,t_with_pct AS
(
    SELECT  b.*
            ,ROUND(b.exp_cnt / t.total_exp, 6) AS exp_pct
    FROM    t_bucket b
    JOIN    t_total t
    ON      b.dt = t.dt AND b.apptype = t.apptype
    AND     b.abcode = t.abcode AND b.suffix_group = t.suffix_group
    AND     b.suffix = t.suffix
)
-- 按 suffix_group 求尾号均值
,t_metrics AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,vid_tier
            ,WM_CONCAT(DISTINCT ',', suffix) AS suffix
            ,AVG(exp_cnt) AS exp_cnt
            ,ROUND(AVG(exp_pct), 6) AS exp_pct
    FROM    t_with_pct
    GROUP BY dt, apptype, abcode, suffix_group, vid_tier
)
-- 基线桶（89）
,t_ctrl AS
(
    SELECT  dt, apptype, vid_tier
            ,exp_pct AS ctrl_exp_pct
    FROM    t_metrics
    WHERE   suffix_group = '89'
)
SELECT  r.dt
        ,r.apptype
        ,r.abcode
        ,r.suffix_group
        ,r.suffix
        ,r.vid_tier
        ,r.exp_cnt
        ,r.exp_pct
        ,ROUND(r.exp_pct - ctrl.ctrl_exp_pct, 6) AS exp_pct_diff
FROM    t_metrics r
LEFT JOIN t_ctrl ctrl
ON      r.dt = ctrl.dt
AND     r.apptype = ctrl.apptype
AND     r.vid_tier = ctrl.vid_tier
ORDER BY r.dt DESC, r.apptype, r.suffix_group, r.vid_tier
;
