WITH t_abmap AS
(
    -- apptype = "4"
    SELECT "4" AS apptype, "c" AS suffix, "前基线" AS abcode
    UNION ALL SELECT "4", "e", "实验组：解构特征排序str模型&召回"
    UNION ALL SELECT "4", "f", "实验组：解构特征排序str模型&召回"
    UNION ALL SELECT "4", "5", "实验组：解构特征排序str模型"
    UNION ALL SELECT "4", "d", "实验组：解构特征排序str模型"
    UNION ALL SELECT "4", "6", "实验组：bn_ros新损失函数"
    UNION ALL SELECT "4", "7", "实验组：bn_ros新损失函数"
    UNION ALL SELECT "4", "a", "对照组"
    UNION ALL SELECT "4", "b", "对照组"
    -- apptype = "0"
    UNION ALL SELECT "0", "c", "前基线"
    UNION ALL SELECT "0", "e", "实验组：解构特征排序str模型&召回"
    UNION ALL SELECT "0", "f", "实验组：解构特征排序str模型&召回"
    UNION ALL SELECT "0", "5", "实验组：解构特征排序str模型"
    UNION ALL SELECT "0", "d", "实验组：解构特征排序str模型"
    UNION ALL SELECT "0", "3", "实验组：bn_ros新损失函数"
    UNION ALL SELECT "0", "4", "实验组：bn_ros新损失函数"
    UNION ALL SELECT "0", "6", "实验组：cn_rov"
    UNION ALL SELECT "0", "7", "实验组：cn_rov"
    UNION ALL SELECT "0", "a", "对照组"
    UNION ALL SELECT "0", "b", "对照组"
)
,t_base AS
(
    SELECT  sub.*
            ,COALESCE(m.abcode,"other") AS abcode
    FROM    (
                SELECT  dt
                        ,hh
                        ,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
                        ,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 IN ("0","4")
                AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页","回流页","其他")
                AND     (
                            (apptype = "4" AND abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9"))
                         OR (apptype = "0" AND abcode IN ("ab0","ab1","ab2","ab3","ab4","ab8","ab9"))
                        )
                AND     abcode NOT IN ("ab100")
            ) sub
    LEFT JOIN t_abmap m
    ON      sub.apptype = m.apptype
    AND     sub.suffix = m.suffix
)
-- top10 视频排名（跨尾号统计曝光量排序，按 page 分开排）
,t_vid_rank AS
(
    SELECT * FROM (
        SELECT  dt, hh, apptype, abcode, page, vid
                ,ROW_NUMBER() OVER (PARTITION BY dt, hh, apptype, abcode, page ORDER BY cnt DESC) AS vid_rank
        FROM (
            SELECT  dt, hh, apptype, abcode, page, vid, COUNT(1) AS cnt
            FROM    t_base
            WHERE   abcode != "other"
            GROUP BY dt, hh, apptype, abcode, page, vid
        )
    ) WHERE vid_rank <= 10
)
-- 视频标题
,t_vid_title AS
(
    SELECT  CAST(id AS STRING) AS vid
            ,title
    FROM    videoods.wx_video
    WHERE   id IN (SELECT DISTINCT CAST(vid AS BIGINT) FROM t_vid_rank)
)
-- dau2：按单尾号聚合（UNION ALL 合并两个 apptype）
,t_dau2_bucket AS
(
    -- apptype = "4"：useractive_log_per5min
    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_per5min
                WHERE   dt BETWEEN CONCAT("${dt}","000000") AND CONCAT("${dt}","235500")
                AND     apptype IN ("4")
            ) sub
    LEFT JOIN t_abmap m
    ON      sub.apptype = m.apptype
    AND     sub.suffix = m.suffix
    GROUP BY SUBSTR(sub.dt,1,8)
             ,sub.apptype
             ,COALESCE(m.abcode,"other")
             ,sub.suffix
    UNION ALL
    -- apptype = "0"：useractive_log + ab_test003 过滤
    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}"
                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 = m.apptype
    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
)
-- 按单尾号聚合（INNER JOIN t_vid_rank 只留 top10 视频，按 page 分组）
,t_bucket AS
(
    SELECT  b.dt
            ,b.hh
            ,b.apptype
            ,b.abcode
            ,b.page
            ,b.suffix
            ,b.vid
            ,COALESCE(v.title,'') AS title
            ,r.vid_rank
            ,COALESCE(COUNT(1) / COUNT(DISTINCT b.mid),0) AS exp_per_dau
            ,COALESCE(SUM(b.is_share) / COUNT(1),0) AS str_one
            ,COALESCE(SUM(b.return_n_uv) / SUM(b.is_share),0) AS ros_one
            ,COALESCE(SUM(b.share_cnt) / COUNT(1),0) AS str
            ,COALESCE(SUM(b.return_n_uv) / SUM(b.share_cnt),0) AS ros
            ,COALESCE(SUM(b.is_return_1) / COUNT(1),0) AS str_plus
            ,COALESCE(SUM(b.return_n_uv) / SUM(b.is_return_1),0) AS ros_minus
            ,COALESCE(SUM(b.return_n_uv) / COUNT(1),0) AS bn_rov
            ,COALESCE(SUM(b.c1) / COUNT(1),0) AS c1_rov
            ,COALESCE(SUM(b.cn) / COUNT(1),0) AS cn_rov
            ,COALESCE(SUM(b.d1) / COUNT(1),0) AS d1_rov
            ,COALESCE(SUM(b.dn) / COUNT(1),0) AS dn_rov
            ,COALESCE(SUM(b.new_exposure_cnt) / COUNT(1),0) AS vovh24
            ,COUNT(DISTINCT b.mid) AS dau
            ,COUNT(1) AS exp
            ,COALESCE(SUM(b.is_share),0) AS is_share
            ,COALESCE(SUM(b.share_cnt),0) AS share_cnt
            ,COALESCE(SUM(b.is_return_1),0) AS is_return_1
            ,COALESCE(SUM(b.return_n_uv),0) AS return_n_uv
            ,COALESCE(SUM(b.new_exposure_cnt),0) AS viewh24
            ,COALESCE(SUM(b.return_n_uv_noself),0) AS return_n_uv_noself
            ,COALESCE(SUM(b.cn),0) AS cn
            ,COALESCE(SUM(b.c1),0) AS c1
            ,COALESCE(SUM(b.dn),0) AS dn
            ,COALESCE(SUM(b.d1),0) AS d1
    FROM    t_base b
    INNER JOIN t_vid_rank r
    ON      b.dt = r.dt
    AND     b.hh = r.hh
    AND     b.apptype = r.apptype
    AND     b.abcode = r.abcode
    AND     b.page = r.page
    AND     b.vid = r.vid
    LEFT JOIN t_vid_title v
    ON      b.vid = v.vid
    WHERE   b.abcode != "other"
    GROUP BY b.dt
             ,b.hh
             ,b.apptype
             ,b.abcode
             ,b.page
             ,b.suffix
             ,b.vid
             ,v.title
             ,r.vid_rank
)
-- 按实验组求尾号均值
,t_metrics AS
(
    SELECT  dt
            ,hh
            ,apptype
            ,abcode
            ,page
            ,vid
            ,title
            ,vid_rank
            ,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
             ,hh
             ,apptype
             ,abcode
             ,page
             ,vid
             ,title
             ,vid_rank
)
SELECT  a.dt
        ,a.hh
        ,a.apptype
        ,a.abcode
        ,a.page
        ,a.vid
        ,a.title
        ,a.vid_rank
        ,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
FROM    t_metrics a
LEFT JOIN t_dau2 b
ON      a.dt = b.dt
AND     a.apptype = b.apptype
AND     a.abcode = b.abcode
ORDER BY a.dt DESC,a.hh,a.apptype,a.abcode,a.page,a.vid_rank
;
