WITH t_abmap AS
(
    SELECT "c" AS suffix, "前基线" AS abcode
    UNION ALL SELECT "e", "实验组：解构特征排序str模型&召回"
    UNION ALL SELECT "f", "实验组：解构特征排序str模型&召回"
    UNION ALL SELECT "5", "实验组：解构特征排序str模型"
    UNION ALL SELECT "d", "实验组：解构特征排序str模型"
    UNION ALL SELECT "3", "实验组：bn_ros新损失函数"
    UNION ALL SELECT "4", "实验组：bn_ros新损失函数"
    UNION ALL SELECT "6", "实验组：cn_rov"
    UNION ALL SELECT "7", "实验组：cn_rov"
    UNION ALL SELECT "a", "对照组"
    UNION ALL SELECT "b", "对照组"
)
,t_base AS
(
    SELECT  sub.*
            ,COALESCE(m.abcode,"other") AS abcode
    FROM    (
                SELECT  dt
                        ,apptype
                        ,SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) AS suffix
                        ,CASE   WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                                WHEN page IN ("回流页","其他") THEN "非推荐"
                                ELSE "其他"
                        END AS page
                        ,page AS page_raw
                        ,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","ab8","ab9")
                AND     abcode NOT IN ("ab100")
            ) sub
    LEFT JOIN t_abmap m
    ON      sub.apptype = "0"
    AND     sub.suffix = m.suffix
)
-- dau2（总）：按单尾号聚合
,t_dau2_bucket AS
(
    SELECT  SUBSTR(sub.dt,1,8) AS dt
            ,sub.apptype
            ,COALESCE(m.abcode,"other") AS abcode
            ,sub.suffix
            ,COUNT(DISTINCT sub.machinecode) AS dau2
    FROM    (
                SELECT  dt
                        ,apptype
                        ,machinecode
                        ,SUBSTR(GET_JSON_OBJECT(extparams,'$.rootSessionId'),LENGTH(GET_JSON_OBJECT(extparams,'$.rootSessionId')),1) AS suffix
                FROM    loghubods.useractive_log
                WHERE   dt="${dt}"
                -- FROM    loghubods.useractive_log_per5min
                -- WHERE   dt BETWEEN CONCAT("${dt}","000000") AND CONCAT("${dt}","235500")
                AND     apptype IN ("0")
                AND     GET_JSON_OBJECT(extparams,'$.eventInfos.ab_test003') IN ("ab0","ab1","ab2","ab3","ab4","ab5", "ab6", "ab7", "ab8","ab9")
                AND     GET_JSON_OBJECT(extparams,'$.eventInfos.ab_test003') NOT IN ("ab100")
            ) sub
    LEFT JOIN t_abmap m
    ON      sub.apptype = "0"
    AND     sub.suffix = m.suffix
    GROUP BY SUBSTR(sub.dt,1,8)
             ,sub.apptype
             ,COALESCE(m.abcode,"other")
             ,sub.suffix
)
-- dau2（总）：按实验组求尾号均值
,t_dau2 AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,AVG(dau2) AS dau2
    FROM    t_dau2_bucket
    GROUP BY dt
             ,apptype
             ,abcode
)
-- dau2（按页面）：按单尾号聚合，从曝光表按page分类统计
,t_dau2_page_bucket AS
(
    SELECT  dt
            ,abcode
            ,suffix
            ,COUNT(DISTINCT CASE WHEN page = "推荐" THEN mid END) AS dau2_recommend
            ,COUNT(DISTINCT CASE WHEN page = "非推荐" THEN mid END) AS dau2_non_recommend
            ,COUNT(DISTINCT CASE WHEN page_raw = "回流后沉浸页&内页feed" THEN mid END) AS dau2_return_immerse
    FROM    t_base
    GROUP BY dt
             ,abcode
             ,suffix
)
-- dau2（按页面）：按实验组求尾号均值
,t_dau2_page AS
(
    SELECT  dt
            ,abcode
            ,AVG(dau2_recommend) AS dau2_recommend
            ,AVG(dau2_non_recommend) AS dau2_non_recommend
            ,AVG(dau2_return_immerse) AS dau2_return_immerse
    FROM    t_dau2_page_bucket
    GROUP BY dt
             ,abcode
)
-- 按单尾号聚合（尾号内 UV 去重）
,t_bucket AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix
            ,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(new_exposure_cnt) / COUNT(1),0) 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 = "推荐"
    GROUP BY dt
             ,apptype
             ,abcode
             ,suffix
)
-- 按实验组求尾号均值
,t_metrics AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,ROUND(AVG(exp_per_dau),2) AS exp_per_dau
            ,ROUND(AVG(str_one),6) AS str_one
            ,ROUND(AVG(ros_one),6) AS ros_one
            ,ROUND(AVG(str),6) AS str
            ,ROUND(AVG(ros),6) AS ros
            ,ROUND(AVG(str_plus),6) AS str_plus
            ,ROUND(AVG(ros_minus),6) AS ros_minus
            ,ROUND(AVG(bn_rov),6) AS bn_rov
            ,ROUND(AVG(c1_rov),6) AS c1_rov
            ,ROUND(AVG(cn_rov),6) AS cn_rov
            ,ROUND(AVG(d1_rov),6) AS d1_rov
            ,ROUND(AVG(dn_rov),6) AS dn_rov
            ,ROUND(AVG(vovh24),6) 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
            ,WM_CONCAT(DISTINCT ',',suffix) AS suffix
    FROM    t_bucket
    GROUP BY dt
             ,apptype
             ,abcode
)
SELECT  a.dt
        ,a.apptype
        ,a.abcode
        ,a.suffix
        ,a.exp_per_dau
        ,a.str_one
        ,a.ros_one
        ,a.str
        ,a.ros
        ,a.str_plus
        ,a.ros_minus
        ,a.bn_rov
        ,a.c1_rov
        ,a.cn_rov
        ,a.d1_rov
        ,a.dn_rov
        ,a.vovh24
        ,a.dau
        ,a.exp
        ,a.is_share
        ,a.share_cnt
        ,a.is_return_1
        ,a.return_n_uv
        ,a.viewh24
        ,a.return_n_uv_noself
        ,a.cn
        ,a.c1
        ,a.dn
        ,a.d1
        ,b.dau2
        ,c.dau2_recommend
        ,c.dau2_non_recommend
        ,c.dau2_return_immerse
FROM    t_metrics a
LEFT JOIN t_dau2 b
ON      a.dt = b.dt
AND     a.apptype = b.apptype
AND     a.abcode = b.abcode
LEFT JOIN t_dau2_page c
ON      a.dt = c.dt
AND     a.abcode = c.abcode
ORDER BY a.dt DESC,a.apptype,a.abcode
;
