-- 推荐AB天级效果 - 含对照组对比
-- 新增列：各指标相对对照组的变化率（lift）
WITH t_base AS
(
    SELECT  dt
            ,apptype
            ,concat(abcode, "-", CASE
                    WHEN apptype IN ("4") AND abcode IN ("ab0", "ab1") THEN "实验组-先验地域降权"
                    WHEN apptype IN ("4") AND abcode IN ("ab6") THEN "实验组-str校准&ros-统计量"
                    WHEN apptype IN ("4") AND abcode IN ("ab8") THEN "实验组-str校准&ros损失函数优化"
                    WHEN apptype IN ("4") AND abcode IN ("ab4") THEN "实验组-str校准&ros天级更新"
                    WHEN apptype IN ("4") AND abcode IN ("ab2", "ab3") THEN "对照组"
                    ELSE "对照组"
            END) AS abcode
            ,CASE   WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                    WHEN page IN ("回流页","其他") THEN "非推荐"
                    ELSE "其他"
            END AS page
            ,mid
            ,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
    FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
    WHERE   dt = '${dt}'
    AND     apptype IN ("4")
    AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页","回流页","其他")
    AND     abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9")
    AND     abcode NOT IN ("ab100")
),
-- 计算各组基础指标
t_metrics AS (
    SELECT  dt
            ,apptype
            ,abcode
            ,page
            ,COUNT(1) / COUNT(DISTINCT mid) AS exp_per_dau
            ,SUM(is_share) / COUNT(1) AS str_one
            ,SUM(return_n_uv) / SUM(is_share) AS ros_one
            ,SUM(share_cnt) / COUNT(1) AS str
            ,SUM(return_n_uv) / SUM(share_cnt) AS ros
            ,SUM(is_return_1) / COUNT(1) AS str_plus
            ,SUM(return_n_uv) / SUM(is_return_1) AS ros_minus
            ,SUM(return_n_uv) / COUNT(1) AS rovn
            ,SUM(new_exposure_cnt) / COUNT(1) AS vovh24
            ,COUNT(DISTINCT mid) AS dau
            ,COUNT(1) AS exp
            ,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
    FROM    t_base
    WHERE   page IN ("推荐")
    GROUP BY dt
             ,apptype
             ,abcode
             ,page
),
-- 获取对照组数据
t_control AS (
    SELECT  dt
            ,apptype
            ,page
            ,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
            ,str_plus AS ctrl_str_plus
            ,ros_minus AS ctrl_ros_minus
            ,rovn AS ctrl_rovn
            ,vovh24 AS ctrl_vovh24
            ,dau AS ctrl_dau
            ,exp AS ctrl_exp
            ,is_share AS ctrl_is_share
            ,share_cnt AS ctrl_share_cnt
            ,is_return_1 AS ctrl_is_return_1
            ,return_n_uv AS ctrl_return_n_uv
            ,viewh24 AS ctrl_viewh24
            ,return_n_uv_noself AS ctrl_return_n_uv_noself
    FROM    t_metrics
    WHERE   abcode = "ab2-对照组"
)
-- 关联对照组，计算变化率
SELECT  m.dt
        ,m.apptype
        ,m.abcode
        ,m.page
        -- 原始指标
        ,m.exp_per_dau
        ,m.str_one
        ,m.ros_one
        ,m.str
        ,m.ros
        ,m.str_plus
        ,m.ros_minus
        ,m.rovn
        ,m.vovh24
        ,m.dau
        ,m.exp
        ,m.is_share
        ,m.share_cnt
        ,m.is_return_1
        ,m.return_n_uv
        ,m.viewh24
        ,m.return_n_uv_noself
        -- 相对对照组变化率
        ,(m.exp_per_dau - c.ctrl_exp_per_dau) / c.ctrl_exp_per_dau AS exp_per_dau_lift
        ,(m.str_one - c.ctrl_str_one) / c.ctrl_str_one AS str_one_lift
        ,(m.ros_one - c.ctrl_ros_one) / c.ctrl_ros_one AS ros_one_lift
        ,(m.str - c.ctrl_str) / c.ctrl_str AS str_lift
        ,(m.ros - c.ctrl_ros) / c.ctrl_ros AS ros_lift
        ,(m.str_plus - c.ctrl_str_plus) / c.ctrl_str_plus AS str_plus_lift
        ,(m.ros_minus - c.ctrl_ros_minus) / c.ctrl_ros_minus AS ros_minus_lift
        ,(m.rovn - c.ctrl_rovn) / c.ctrl_rovn AS rovn_lift
        ,(m.vovh24 - c.ctrl_vovh24) / c.ctrl_vovh24 AS vovh24_lift
        ,(m.dau - c.ctrl_dau) / c.ctrl_dau AS dau_lift
        ,(m.exp - c.ctrl_exp) / c.ctrl_exp AS exp_lift
        ,(m.is_share - c.ctrl_is_share) / c.ctrl_is_share AS is_share_lift
        ,(m.share_cnt - c.ctrl_share_cnt) / c.ctrl_share_cnt AS share_cnt_lift
        ,(m.is_return_1 - c.ctrl_is_return_1) / c.ctrl_is_return_1 AS is_return_1_lift
        ,(m.return_n_uv - c.ctrl_return_n_uv) / c.ctrl_return_n_uv AS return_n_uv_lift
        ,(m.viewh24 - c.ctrl_viewh24) / c.ctrl_viewh24 AS viewh24_lift
        ,(m.return_n_uv_noself - c.ctrl_return_n_uv_noself) / c.ctrl_return_n_uv_noself AS return_n_uv_noself_lift
FROM    t_metrics m
LEFT JOIN t_control c
ON      m.dt = c.dt
AND     m.apptype = c.apptype
AND     m.page = c.page
ORDER BY m.dt DESC, m.apptype, m.page, m.abcode
;
