-- ════════════════════════════════════════════════════════════════════════════
-- exp_per_dau 拆解：人均session数 × 每session曝光数 × 按页面类型分布
-- 基于 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
                        ,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.subsessionid
                        ,page
                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
)
-- 按单尾号聚合：拆解 exp_per_dau
,t_bucket AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,suffix
            ,COALESCE(source_type,'总体') AS source_type
            ,COALESCE(layer_type,'总体') AS layer_type
            -- ── 总量 ──
            ,COUNT(1) AS exp
            ,COUNT(DISTINCT mid) AS dau
            ,COUNT(DISTINCT subsessionid) AS session_cnt
            -- ── 核心拆解：exp_per_dau = sessions_per_dau × exp_per_session ──
            ,COALESCE(COUNT(1) / COUNT(DISTINCT mid), 0) AS exp_per_dau
            ,COALESCE(COUNT(DISTINCT subsessionid) / COUNT(DISTINCT mid), 0) AS sessions_per_dau
            ,COALESCE(COUNT(1) / COUNT(DISTINCT subsessionid), 0) AS exp_per_session
            -- ── 按页面类型拆曝光占比 ──
            ,SUM(CASE WHEN page = '首页feed' THEN 1 ELSE 0 END) / COUNT(1) AS pct_homepage
            ,SUM(CASE WHEN page = '详情后沉浸页' THEN 1 ELSE 0 END) / COUNT(1) AS pct_detail_immerse
            ,SUM(CASE WHEN page = '回流后沉浸页&内页feed' THEN 1 ELSE 0 END) / COUNT(1) AS pct_return_immerse
            ,SUM(CASE WHEN page = '详情页' THEN 1 ELSE 0 END) / COUNT(1) AS pct_detail
            -- ── 按页面类型拆人均曝光 ──
            ,SUM(CASE WHEN page = '首页feed' THEN 1 ELSE 0 END) / COUNT(DISTINCT mid) AS exp_per_dau_homepage
            ,SUM(CASE WHEN page = '详情后沉浸页' THEN 1 ELSE 0 END) / COUNT(DISTINCT mid) AS exp_per_dau_detail_immerse
            ,SUM(CASE WHEN page = '回流后沉浸页&内页feed' THEN 1 ELSE 0 END) / COUNT(DISTINCT mid) AS exp_per_dau_return_immerse
            ,SUM(CASE WHEN page = '详情页' THEN 1 ELSE 0 END) / COUNT(DISTINCT mid) AS exp_per_dau_detail
    FROM    t_base
    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)
    )
)
-- 按实验组求尾号均值
,t_metrics AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,source_type
            ,layer_type
            ,WM_CONCAT(DISTINCT ',', suffix) AS suffix
            -- 总量
            ,AVG(exp) AS exp
            ,AVG(dau) AS dau
            ,AVG(session_cnt) AS session_cnt
            -- 核心拆解
            ,ROUND(AVG(exp_per_dau), 2) AS exp_per_dau
            ,ROUND(AVG(sessions_per_dau), 4) AS sessions_per_dau
            ,ROUND(AVG(exp_per_session), 2) AS exp_per_session
            -- 页面占比
            ,ROUND(AVG(pct_homepage), 6) AS pct_homepage
            ,ROUND(AVG(pct_detail_immerse), 6) AS pct_detail_immerse
            ,ROUND(AVG(pct_return_immerse), 6) AS pct_return_immerse
            ,ROUND(AVG(pct_detail), 6) AS pct_detail
            -- 按页面拆人均曝光
            ,ROUND(AVG(exp_per_dau_homepage), 2) AS exp_per_dau_homepage
            ,ROUND(AVG(exp_per_dau_detail_immerse), 2) AS exp_per_dau_detail_immerse
            ,ROUND(AVG(exp_per_dau_return_immerse), 2) AS exp_per_dau_return_immerse
            ,ROUND(AVG(exp_per_dau_detail), 2) AS exp_per_dau_detail
    FROM    t_bucket
    GROUP BY dt, apptype, abcode, suffix_group, source_type, layer_type
)
-- 基线桶（89）
,t_ctrl AS
(
    SELECT  dt, apptype, source_type, layer_type
            ,exp_per_dau AS ctrl_exp_per_dau
            ,sessions_per_dau AS ctrl_sessions_per_dau
            ,exp_per_session AS ctrl_exp_per_session
            ,exp_per_dau_homepage AS ctrl_exp_per_dau_homepage
            ,exp_per_dau_detail_immerse AS ctrl_exp_per_dau_detail_immerse
            ,exp_per_dau_return_immerse AS ctrl_exp_per_dau_return_immerse
            ,exp_per_dau_detail AS ctrl_exp_per_dau_detail
    FROM    t_metrics
    WHERE   suffix_group = '89'
)
-- 最终输出
SELECT  r.dt
        ,r.apptype
        ,r.abcode
        ,r.suffix_group
        ,r.source_type
        ,r.layer_type
        ,r.suffix
        -- ── 总量 ──
        ,r.exp
        ,r.dau
        ,r.session_cnt
        -- ── 核心拆解 ──
        ,r.exp_per_dau
        ,r.sessions_per_dau
        ,r.exp_per_session
        -- ── 页面占比 ──
        ,r.pct_homepage
        ,r.pct_detail_immerse
        ,r.pct_return_immerse
        ,r.pct_detail
        -- ── 按页面拆人均曝光 ──
        ,r.exp_per_dau_homepage
        ,r.exp_per_dau_detail_immerse
        ,r.exp_per_dau_return_immerse
        ,r.exp_per_dau_detail
        -- ── vs 对照组 89 diff ──
        ,ROUND(r.exp_per_dau / NULLIF(ctrl.ctrl_exp_per_dau, 0) - 1, 6) AS exp_per_dau_diff
        ,ROUND(r.sessions_per_dau / NULLIF(ctrl.ctrl_sessions_per_dau, 0) - 1, 6) AS sessions_per_dau_diff
        ,ROUND(r.exp_per_session / NULLIF(ctrl.ctrl_exp_per_session, 0) - 1, 6) AS exp_per_session_diff
        ,ROUND(r.exp_per_dau_homepage / NULLIF(ctrl.ctrl_exp_per_dau_homepage, 0) - 1, 6) AS exp_per_dau_homepage_diff
        ,ROUND(r.exp_per_dau_detail_immerse / NULLIF(ctrl.ctrl_exp_per_dau_detail_immerse, 0) - 1, 6) AS exp_per_dau_detail_immerse_diff
        ,ROUND(r.exp_per_dau_return_immerse / NULLIF(ctrl.ctrl_exp_per_dau_return_immerse, 0) - 1, 6) AS exp_per_dau_return_immerse_diff
        ,ROUND(r.exp_per_dau_detail / NULLIF(ctrl.ctrl_exp_per_dau_detail, 0) - 1, 6) AS exp_per_dau_detail_diff
FROM    t_metrics 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.abcode, r.suffix_group, r.source_type, r.layer_type
;
