-- ════════════════════════════════════════════════════════════════════════════
-- 产品 32 · 前端保序实验 — 分层效果统计（倒数第 3 位尾号分流）
-- [基于 base_all_new_v3_分层.sql 改造]
--
-- ┌─ 实验配置 ──────────────────────────────────────────────────────────┐
-- │  apptype：32                                                          │
-- │  分流位：rootsessionid 倒数第 3 位（hex 0-f）                         │
-- │  实验组：[0,2,4,6,8,a,c,e]                                            │
-- │  对照组：[1,3,5,7,9,b,d,f]                                            │
-- │  实验名：实验组：前端保序实验                                          │
-- │  上线日：20260422                                                     │
-- │  基线期：20260417~20260421（上线日前 5 天均值）                       │
-- └─────────────────────────────────────────────────────────────────────┘
--
-- 分层维度：source_type(内部/外部) × layer_type(首层/裂变层)，GROUPING SETS 同时产出"总体"
-- 横向对比：每日 实验组 vs 对照组（基线桶 = '对照'）
-- 纵向对比：每日 dau2 vs 5 天基线均值
-- ════════════════════════════════════════════════════════════════════════════
WITH t_suffix_group AS
(
    -- 实验组：[0,2,4,6,8,a,c,e] → suffix_group = "02468ace"
    SELECT "0" AS suffix, "02468ace" AS suffix_group
    UNION ALL SELECT "2", "02468ace"
    UNION ALL SELECT "4", "02468ace"
    UNION ALL SELECT "6", "02468ace"
    UNION ALL SELECT "8", "02468ace"
    UNION ALL SELECT "a", "02468ace"
    UNION ALL SELECT "c", "02468ace"
    UNION ALL SELECT "e", "02468ace"
    -- 对照组：[1,3,5,7,9,b,d,f] → suffix_group = "13579bdf"
    UNION ALL SELECT "1", "13579bdf"
    UNION ALL SELECT "3", "13579bdf"
    UNION ALL SELECT "5", "13579bdf"
    UNION ALL SELECT "7", "13579bdf"
    UNION ALL SELECT "9", "13579bdf"
    UNION ALL SELECT "b", "13579bdf"
    UNION ALL SELECT "d", "13579bdf"
    UNION ALL SELECT "f", "13579bdf"
)
-- ┌─ 实验映射 ──────────────────────────────────────────────────────────┐
-- │  只列实验组那一行；对照组 + 上线前日期 → COALESCE 自动归为"对照组"     │
-- └─────────────────────────────────────────────────────────────────────┘
,t_experiment_map AS
(
    SELECT "32" AS apptype, "02468ace" AS suffix_group, "实验组：前端保序实验" AS abcode, "20260422" AS start_dt, "29991231" AS end_dt
)
,t_base AS
(
    SELECT  sub.*
            ,sg.suffix_group
            ,COALESCE(m.abcode,"对照组") AS abcode
    FROM    (
                SELECT  dt
                        ,apptype
                        ,SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'), -3, 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
                FROM    loghubods.dwd_recsys_alg_exposure_base_20250108 a
                LEFT JOIN   (
                                -- c1/cn：分享后被点击的回流 UV
                                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：下一条视频带来的回流
                                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 = "32"
                AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页","回流页","其他")
                AND     abcode 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
)
-- 桶内每个 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)
    )
)
-- 桶内 ECS：曝光实际"相当于推了多少条视频" = 2 * Σ(p_i * rank_i) - 1
,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
)
-- 全平台每个 vid 的曝光度（ARP popularity reference，不过滤 abcode）
,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)
    )
)
-- 桶内 ARP：推荐视频的平均热门度，按桶内曝光量加权
,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'), -3, 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 = "32"
            ) 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)
    )
)
-- dau2：按 suffix_group 求尾号均值
,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
)
-- 按单尾号聚合
,t_bucket AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,suffix
            ,COALESCE(source_type,'总体') AS source_type
            ,COALESCE(layer_type,'总体') AS layer_type
            ,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
    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)
    )
)
-- 按实验组求尾号均值
,t_metrics AS
(
    SELECT  b.dt
            ,b.apptype
            ,b.abcode
            ,b.suffix_group
            ,b.source_type
            ,b.layer_type
            ,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
    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
)
-- ════════════════════════════════════════════════════════════════════════════
-- 基线对比层：DAU2 历史 5 天均值（20260417~20260421）
-- ════════════════════════════════════════════════════════════════════════════
-- ┌─ 内联计算版：每次跑都会扫 5 天 useractive_log                        ┐
-- │  跑稳定后可改为硬编码版（拷出每组数值后粘回此处，省扫描）            │
-- └──────────────────────────────────────────────────────────────────────┘
,t_dau2_base5 AS
(
    SELECT  apptype
            ,suffix_group
            ,source_type
            ,layer_type
            ,AVG(dau2) AS dau2_base5
    FROM    (
                SELECT  sub.dt
                        ,sub.apptype
                        ,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'), -3, 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 BETWEEN '20260417' AND '20260421'
                            AND     apptype = "32"
                        ) sub
                INNER JOIN t_suffix_group sg
                ON      sub.suffix = sg.suffix
                GROUP BY sub.dt, sub.apptype, sg.suffix_group, sub.suffix, sub.source_type, sub.layer_type
                GROUPING SETS (
                    (sub.dt, sub.apptype, sg.suffix_group, sub.suffix, sub.source_type, sub.layer_type),
                    (sub.dt, sub.apptype, sg.suffix_group, sub.suffix)
                )
            )
    GROUP BY apptype, suffix_group, source_type, layer_type
)
-- 合并主表 + dau2 + dau_vs_5d
,t_combined AS
(
    SELECT  a.*
            ,b.dau2
            ,ROUND(b.dau2 / NULLIF(c.dau2_base5, 0), 6) AS dau_vs_5d
    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
)
-- 基线桶（对照组）每日指标，作为横向对比基准
,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 = '13579bdf'
)
-- 最终输出：原始指标 + 基线对比差值（_diff = 当前值 / 基线值 - 1）
SELECT  r.dt
        ,r.apptype
        ,r.abcode
        ,r.suffix_group
        ,r.source_type
        ,r.layer_type
        ,r.suffix
        -- ── 原始指标 ──
        ,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
        -- ── DAU2 纵向对比（vs 历史 5 天均值 20260417~20260421） ──
        ,r.dau_vs_5d
        -- ── 横向对比：实验组 vs 对照组 ──
        ,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.abcode, r.suffix_group, r.source_type, r.layer_type
;
