-- 推荐AB天级效果 - 含对照组对比
-- 新增列：各指标相对对照组的变化率（lift）
WITH t_base AS
(
    SELECT  dt
            ,apptype
            ,abcode as abcode_raw
            ,CASE
                    WHEN apptype IN ("0") AND abcode IN ("ab5") THEN "rovn新损失函数实验"
                    WHEN apptype IN ("0") AND abcode IN ("ab6") THEN "c1_rovn&去掉vor实验"
                    WHEN apptype IN ("0") AND abcode IN ("ab7") THEN "c1_rovn实验"
                    WHEN apptype IN ("0") AND abcode IN ("ab8", "ab9") THEN "对照组"
                    ELSE "other"
            END AS abcode
            ,CASE   WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                    WHEN page IN ("回流页","其他") THEN "非推荐"
                    ELSE "其他"
            END AS page
            ,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   (
                    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   (
                    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 IN ("0")
    AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页","回流页","其他")
    AND     abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9")
),
-- 按单桶聚合（桶内 UV 去重）
t_bucket AS (
    SELECT  dt
            ,apptype
            ,abcode
            ,abcode_raw
            ,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 bn_rov
            ,SUM(c1) / COUNT(1) AS c1_rov
            ,SUM(cn) / COUNT(1) AS cn_rov
            ,SUM(d1) / COUNT(1) AS d1_rov
            ,SUM(dn) / COUNT(1) AS dn_rov
            ,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
            ,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 IN ("推荐")
    AND     abcode != "other"
    GROUP BY dt
             ,apptype
             ,abcode
             ,abcode_raw
             ,page
),
-- 按实验组求桶均值
t_metrics AS (
    SELECT  dt
            ,apptype
            ,abcode
            ,page
            ,AVG(exp_per_dau) AS exp_per_dau
            ,AVG(str_one) AS str_one
            ,AVG(ros_one) AS ros_one
            ,AVG(str) AS str
            ,AVG(ros) AS ros
            ,AVG(str_plus) AS str_plus
            ,AVG(ros_minus) AS ros_minus
            ,AVG(bn_rov) AS bn_rov
            ,AVG(c1_rov) AS c1_rov
            ,AVG(cn_rov) AS cn_rov
            ,AVG(d1_rov) AS d1_rov
            ,AVG(dn_rov) AS dn_rov
            ,AVG(vovh24) AS vovh24
            ,AVG(dau) AS dau
            ,AVG(exp) AS exp
            ,AVG(is_share) AS is_share
            ,AVG(share_cnt) AS share_cnt
            ,AVG(is_return_1) AS is_return_1
            ,AVG(return_n_uv) AS return_n_uv
            ,AVG(viewh24) AS viewh24
            ,AVG(return_n_uv_noself) AS return_n_uv_noself
            ,AVG(cn) AS cn
            ,AVG(c1) AS c1
            ,AVG(dn) AS dn
            ,AVG(d1) AS d1
    FROM    t_bucket
    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
            ,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
            ,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
            ,cn AS ctrl_cn
            ,c1 AS ctrl_c1
            ,dn AS ctrl_dn
            ,d1 AS ctrl_d1
    FROM    t_metrics
    WHERE   abcode IN ("对照组")
)
-- 关联对照组，计算变化率
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.bn_rov
        ,m.c1_rov
        ,m.cn_rov
        ,m.d1_rov
        ,m.dn_rov
        ,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.cn
        ,m.c1
        ,m.dn
        ,m.d1
        -- 相对对照组变化率
        ,(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.bn_rov - c.ctrl_bn_rov) / c.ctrl_bn_rov AS bn_rov_lift
        ,(m.c1_rov - c.ctrl_c1_rov) / c.ctrl_c1_rov AS c1_rov_lift
        ,(m.cn_rov - c.ctrl_cn_rov) / c.ctrl_cn_rov AS cn_rov_lift
        ,(m.d1_rov - c.ctrl_d1_rov) / c.ctrl_d1_rov AS d1_rov_lift
        ,(m.dn_rov - c.ctrl_dn_rov) / c.ctrl_dn_rov AS dn_rov_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
        ,(m.cn - c.ctrl_cn) / c.ctrl_cn AS cn_lift
        ,(m.c1 - c.ctrl_c1) / c.ctrl_c1 AS c1_lift
        ,(m.dn - c.ctrl_dn) / c.ctrl_dn AS dn_lift
        ,(m.d1 - c.ctrl_d1) / c.ctrl_d1 AS d1_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
;
