-- base_v5_v1：基于 base_v4_v1 结构（apptype=0），追加模型预估 / COPC / 特征统计
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
                        ,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
                        -- ===== 模型预估值 =====
                        ,CAST(GET_JSON_OBJECT(REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\",""),'$.fmRov') AS DOUBLE) AS a_str_pred
                        ,1.22 * pow(CAST(GET_JSON_OBJECT(REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\",""),'$.NorXGBScore') AS DOUBLE), 1.15) AS bn_ros_pred
                        -- ===== 模型打分 =====
                        ,CAST(GET_JSON_OBJECT(extend_alg,'$.sortScore') AS DOUBLE) AS sortScore
                        ,CAST(GET_JSON_OBJECT(extend_alg,'$.rovScore') AS DOUBLE) AS rovScore
                        -- ===== b1 视频全局历史 =====
                        ,CAST(GET_JSON_OBJECT(b1_feature,'$.exp_168h') AS DOUBLE) AS b1_exp_168h
                        ,CAST(GET_JSON_OBJECT(b1_feature,'$.return_1_uv_1h') AS DOUBLE)
                            / NULLIF(CAST(GET_JSON_OBJECT(b1_feature,'$.exp_1h') AS DOUBLE), 0) AS b1_rovn_1h
                        ,CAST(GET_JSON_OBJECT(b1_feature,'$.return_1_uv_24h') AS DOUBLE)
                            / NULLIF(CAST(GET_JSON_OBJECT(b1_feature,'$.exp_24h') AS DOUBLE), 0) AS b1_rovn_24h
                        ,CAST(GET_JSON_OBJECT(b1_feature,'$.rovn_168h') AS DOUBLE) AS b1_rovn_168h
                        ,CAST(GET_JSON_OBJECT(b1_feature,'$.is_share_168h') AS DOUBLE)
                            / NULLIF(CAST(GET_JSON_OBJECT(b1_feature,'$.exp_168h') AS DOUBLE), 0) AS b1_str_168h
                        -- ===== b2 推荐场景历史 =====
                        ,CAST(GET_JSON_OBJECT(b2_feature,'$.return_n_uv_168h') AS DOUBLE)
                            / NULLIF(CAST(GET_JSON_OBJECT(b2_feature,'$.exp_168h') AS DOUBLE), 0) AS b2_rovn_168h
                        ,CAST(GET_JSON_OBJECT(b2_feature,'$.exp_168h') AS DOUBLE) AS b2_exp_168h
                        -- ===== b12 超长期历史 =====
                        ,CAST(GET_JSON_OBJECT(b12_feature,'$.return_n_uv_30d') AS DOUBLE)
                            / NULLIF(CAST(GET_JSON_OBJECT(b12_feature,'$.exp_30d') AS DOUBLE), 0) AS b12_rovn_30d
                        ,CAST(GET_JSON_OBJECT(b12_feature,'$.exp_30d') AS DOUBLE) AS b12_exp_30d
                        -- ===== c1 用户全局特征 =====
                        ,CAST(GET_JSON_OBJECT(c1_feature,'$.rovn_168h') AS DOUBLE) AS c1_rovn_168h
                        ,CAST(GET_JSON_OBJECT(c1_feature,'$.exp_168h') AS DOUBLE) AS c1_exp_168h
                        -- ===== b8 类目基线 =====
                        ,CAST(GET_JSON_OBJECT(b8_feature,'$.return_n_uv_168h') AS DOUBLE)
                            / NULLIF(CAST(GET_JSON_OBJECT(b8_feature,'$.exp_168h') AS DOUBLE), 0) AS b8_rovn_168h
                        -- ===== v1 视频元信息 =====
                        ,GET_JSON_OBJECT(v1_feature,'$.channel') AS channel
                        ,GET_JSON_OBJECT(v1_feature,'$.merge_first_level_cate') AS merge_cate1
                        ,CAST(GET_JSON_OBJECT(v1_feature,'$.total_time') AS DOUBLE) AS total_time
                        -- ===== c1/cn 链路 =====
                        ,cc.cn
                        ,cc.c1
                        -- ===== d1/dn 链路 =====
                        ,dd.dn
                        ,dd.d1
                FROM    loghubods.dwd_recsys_alg_sample_all_20250212 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}"
                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
)
-- 按单尾号聚合（尾号内 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
            -- ===== 模型对应真实值 =====
            ,COALESCE(SUM(is_return_noself) / COUNT(1),0) AS a_strx
            ,COALESCE(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself),0),0) AS bn_rosx
            ,COALESCE(SUM(return_n_uv_noself) / COUNT(1),0) AS bn_rovx
            -- ===== 模型预估值 =====
            ,ROUND(AVG(a_str_pred),6) AS a_strx_pred
            ,ROUND(AVG(CASE WHEN is_return_noself = 1 THEN bn_ros_pred END),6) AS bn_rosx_pred
            ,ROUND(AVG(a_str_pred * bn_ros_pred),6) AS bn_rovx_pred
            -- ===== COPC =====
            ,ROUND((SUM(is_return_noself) / COUNT(1)) / NULLIF(AVG(a_str_pred), 0), 4) AS a_strx_copc
            ,ROUND(COALESCE(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself),0),0) / NULLIF(AVG(CASE WHEN is_return_noself = 1 THEN bn_ros_pred END), 0), 4) AS bn_rosx_copc
            ,ROUND((SUM(return_n_uv_noself) / COUNT(1)) / NULLIF(AVG(a_str_pred * bn_ros_pred), 0), 4) AS bn_rovx_copc
            -- ===== 模型打分 =====
            ,ROUND(AVG(sortScore),4) AS avg_sortScore
            ,ROUND(AVG(rovScore),4) AS avg_rovScore
            -- ===== 特征均值 =====
            ,ROUND(AVG(b1_exp_168h),0) AS b1_exp_168h
            ,ROUND(AVG(b1_rovn_1h),6) AS b1_rovn_1h
            ,ROUND(AVG(b1_rovn_24h),6) AS b1_rovn_24h
            ,ROUND(AVG(b1_rovn_168h),6) AS b1_rovn_168h
            ,ROUND(AVG(b1_str_168h),6) AS b1_str_168h
            ,ROUND(AVG(b2_rovn_168h),6) AS b2_rovn_168h
            ,ROUND(AVG(b2_exp_168h),0) AS b2_exp_168h
            ,ROUND(AVG(b12_rovn_30d),6) AS b12_rovn_30d
            ,ROUND(AVG(b12_exp_30d),0) AS b12_exp_30d
            ,ROUND(AVG(c1_rovn_168h),6) AS avg_c1_rovn_168h
            ,ROUND(AVG(c1_exp_168h),0) AS avg_c1_exp_168h
            ,ROUND(AVG(b8_rovn_168h),6) AS b8_rovn_168h
            ,ROUND(AVG(total_time),0) AS avg_total_time
            -- ===== 原有计数 =====
            ,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(is_return_noself),0) AS is_return_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
            -- ===== 模型对应真实值 =====
            ,ROUND(AVG(a_strx),6) AS a_strx
            ,ROUND(AVG(bn_rosx),6) AS bn_rosx
            ,ROUND(AVG(bn_rovx),6) AS bn_rovx
            -- ===== 模型预估值 =====
            ,ROUND(AVG(a_strx_pred),6) AS a_strx_pred
            ,ROUND(AVG(bn_rosx_pred),6) AS bn_rosx_pred
            ,ROUND(AVG(bn_rovx_pred),6) AS bn_rovx_pred
            -- ===== COPC =====
            ,ROUND(AVG(a_strx_copc),4) AS a_strx_copc
            ,ROUND(AVG(bn_rosx_copc),4) AS bn_rosx_copc
            ,ROUND(AVG(bn_rovx_copc),4) AS bn_rovx_copc
            -- ===== 模型打分 =====
            ,ROUND(AVG(avg_sortScore),4) AS avg_sortScore
            ,ROUND(AVG(avg_rovScore),4) AS avg_rovScore
            -- ===== 特征均值 =====
            ,ROUND(AVG(b1_exp_168h),0) AS b1_exp_168h
            ,ROUND(AVG(b1_rovn_1h),6) AS b1_rovn_1h
            ,ROUND(AVG(b1_rovn_24h),6) AS b1_rovn_24h
            ,ROUND(AVG(b1_rovn_168h),6) AS b1_rovn_168h
            ,ROUND(AVG(b1_str_168h),6) AS b1_str_168h
            ,ROUND(AVG(b2_rovn_168h),6) AS b2_rovn_168h
            ,ROUND(AVG(b2_exp_168h),0) AS b2_exp_168h
            ,ROUND(AVG(b12_rovn_30d),6) AS b12_rovn_30d
            ,ROUND(AVG(b12_exp_30d),0) AS b12_exp_30d
            ,ROUND(AVG(avg_c1_rovn_168h),6) AS avg_c1_rovn_168h
            ,ROUND(AVG(avg_c1_exp_168h),0) AS avg_c1_exp_168h
            ,ROUND(AVG(b8_rovn_168h),6) AS b8_rovn_168h
            ,ROUND(AVG(avg_total_time),0) AS avg_total_time
            -- ===== 原有计数 =====
            ,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(is_return_noself) AS is_return_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.a_strx
        ,a.bn_rosx
        ,a.bn_rovx
        -- 模型预估值
        ,a.a_strx_pred
        ,a.bn_rosx_pred
        ,a.bn_rovx_pred
        -- COPC
        ,a.a_strx_copc
        ,a.bn_rosx_copc
        ,a.bn_rovx_copc
        -- 模型打分
        ,a.avg_sortScore
        ,a.avg_rovScore
        -- 特征
        ,a.b1_exp_168h
        ,a.b1_rovn_1h
        ,a.b1_rovn_24h
        ,a.b1_rovn_168h
        ,a.b1_str_168h
        ,a.b2_rovn_168h
        ,a.b2_exp_168h
        ,a.b12_rovn_30d
        ,a.b12_exp_30d
        ,a.avg_c1_rovn_168h
        ,a.avg_c1_exp_168h
        ,a.b8_rovn_168h
        ,a.avg_total_time
        -- 计数
        ,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.is_return_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.apptype,a.abcode
;
