-- 全量基线 · 召回策略效果
-- 口径：abcode IN (ab0..ab9) 且 NOT IN (ab100) → 团队 canonical 基线流量定义
-- 维度：apptype × 页面分类 × 召回源（push_from）；不区分实验组/对照组
-- apptype: 0=票圈推荐, 4=票圈视频；GROUPING SETS 同时输出每个 apptype 和"all"合并行
-- 归因：EXPLODE pushFromIndexList → 一条曝光被多个召回源同时认领（各召回源 rate 之和 >100%）
-- 跑批：python fetch_daily.py tasks/召回策略效果/base_baseline.sql --days 30

WITH base_tab AS
(
    SELECT  dt
            ,apptype
            ,vid
            ,mid
            ,is_share
            ,share_pv
            ,is_return_1
            ,return_n_uv
            ,return_n_uv_noself
            ,new_exposure_cnt
            ,CASE   WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                    WHEN page IN ("回流页","其他") THEN "非推荐"
                    ELSE "其他"
            END AS page
            ,push_from_array
    FROM    (
                SELECT  dt
                        ,apptype
                        ,vid
                        ,mid
                        ,is_share
                        ,COALESCE(share_cnt,0) AS share_pv
                        ,is_return_1
                        ,COALESCE(return_n_uv,0) AS return_n_uv
                        ,COALESCE(return_n_uv_noself,0) AS return_n_uv_noself
                        ,COALESCE(new_exposure_cnt,0) AS new_exposure_cnt
                        ,SPLIT(
                              REGEXP_REPLACE(GET_JSON_OBJECT(recommendlogvo,'$.pushFromIndexList[*].pushFrom'),'(\\[|\\]|")','')
                        ,',') AS push_from_array
                        ,page
                        ,extend
                        ,rootsourceid
                        ,subsessionid
                        ,sessionid
                FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
                WHERE   dt = '${dt}'
                AND     apptype IN ('0','4')
                AND     recommendlogvo IS NOT NULL
                AND     GET_JSON_OBJECT(recommendlogvo,'$.pushFromIndexList') IS NOT NULL
                AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页")
                AND     abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9")
                AND     abcode NOT IN ("ab100")
            )
)
,total_tab AS
(
    SELECT  dt
            ,COALESCE(apptype,"all") AS apptype
            ,page
            ,COUNT(DISTINCT mid) AS total_exp_uv
            ,COUNT(1) AS total_exp_pv
            ,SUM(share_pv) AS total_share_pv
            ,SUM(return_n_uv) AS total_return_n_uv
            ,SUM(return_n_uv_noself) AS total_return_n_uv_noself
            ,SUM(new_exposure_cnt) AS total_new_exposure_cnt
            ,COUNT(DISTINCT vid) AS total_vid_cnt
    FROM    base_tab
    GROUP BY dt
             ,apptype
             ,page
    GROUPING SETS ((dt,apptype,page)
                  ,(dt,page))
)
,detail_table AS
(
    SELECT  dt
            ,apptype
            ,page
            ,single_push_from
            ,COALESCE(exp_uv,0) AS exp_uv
            ,COALESCE(exp_pv,0) AS exp_pv
            ,COALESCE(share_pv,0) AS share_pv
            ,COALESCE(return_n_uv,0) AS return_n_uv
            ,COALESCE(return_n_uv_noself,0) AS return_n_uv_noself
            ,COALESCE(new_exposure_cnt,0) AS new_exposure_cnt
            ,COALESCE(vid_cnt,0) AS vid_cnt
            ,ROUND(COALESCE(视频平均曝光次数,0),4) AS 视频平均曝光次数
            ,ROUND(COALESCE(share_pv / exp_pv,0),4) AS str
            ,ROUND(COALESCE(return_n_uv / share_pv,0),4) AS rosn
            ,ROUND(COALESCE(return_n_uv / exp_pv,0),4) AS rovn
            ,ROUND(COALESCE(return_n_uv_noself / share_pv,0),4) AS rosn_noself
            ,ROUND(COALESCE(return_n_uv_noself / exp_pv,0),4) AS rovn_noself
            ,ROUND(COALESCE(new_exposure_cnt / exp_pv,0),4) AS vovh24
            ,max_return_n_uv
    FROM    (
                SELECT  dt
                        ,COALESCE(apptype,"all") AS apptype
                        ,page
                        ,single_push_from
                        ,COUNT(DISTINCT mid) AS exp_uv
                        ,COUNT(1) AS exp_pv
                        ,SUM(share_pv) AS share_pv
                        ,SUM(return_n_uv) AS return_n_uv
                        ,SUM(return_n_uv_noself) AS return_n_uv_noself
                        ,SUM(new_exposure_cnt) AS new_exposure_cnt
                        ,COUNT(DISTINCT vid) AS vid_cnt
                        ,COUNT(1) / COUNT(DISTINCT vid) AS 视频平均曝光次数
                        ,MAX(CAST(return_n_uv AS BIGINT)) AS max_return_n_uv
                FROM    (
                            SELECT  dt
                                    ,apptype
                                    ,vid
                                    ,mid
                                    ,is_share
                                    ,share_pv
                                    ,is_return_1
                                    ,return_n_uv
                                    ,return_n_uv_noself
                                    ,new_exposure_cnt
                                    ,page
                                    ,single_push_from
                            FROM    base_tab
                            LATERAL VIEW EXPLODE(push_from_array) t AS single_push_from
                        )
                GROUP BY dt
                         ,apptype
                         ,page
                         ,single_push_from
                GROUPING SETS ((dt,apptype,page,single_push_from)
                              ,(dt,page,single_push_from))
            )
)
SELECT  t1.dt AS 日期
        ,t1.apptype AS apptype
        ,t1.page AS 页面
        ,t1.single_push_from AS 召回源
        ,t1.str
        ,t1.rosn
        ,t1.rovn
        ,t1.rosn_noself
        ,t1.rovn_noself
        ,t1.vovh24
        ,t1.exp_uv
        ,t2.total_exp_uv
        ,ROUND(t1.exp_uv / t2.total_exp_uv,4) AS exp_uv_rate
        ,t1.exp_pv
        ,t2.total_exp_pv
        ,ROUND(t1.exp_pv / t2.total_exp_pv,4) AS exp_pv_rate
        ,t1.share_pv
        ,t2.total_share_pv
        ,ROUND(t1.share_pv / t2.total_share_pv,4) AS share_pv_rate
        ,t1.return_n_uv
        ,t2.total_return_n_uv
        ,ROUND(t1.return_n_uv / t2.total_return_n_uv,4) AS return_n_uv_rate
        ,t1.return_n_uv_noself
        ,t2.total_return_n_uv_noself
        ,ROUND(t1.return_n_uv_noself / t2.total_return_n_uv_noself,4) AS return_n_uv_rate_noself
        ,t1.new_exposure_cnt
        ,t2.total_new_exposure_cnt
        ,ROUND(t1.new_exposure_cnt / t2.total_new_exposure_cnt,4) AS new_exposure_rate
        ,t1.vid_cnt
        ,t2.total_vid_cnt
        ,ROUND(t1.vid_cnt / t2.total_vid_cnt,4) AS vid_cnt_rate
        ,ROUND(t1.exp_pv / t1.exp_uv,4) AS 人均曝光pv
        ,ROUND(t1.share_pv / t1.exp_uv,4) AS 人均分享pv
        ,t1.视频平均曝光次数
        ,t1.max_return_n_uv
FROM    detail_table t1
JOIN    total_tab t2
ON      t1.dt = t2.dt
AND     t1.apptype = t2.apptype
AND     t1.page = t2.page
WHERE   t1.page IN ("推荐")
AND     t1.exp_pv > 100
ORDER BY t1.dt DESC,t1.apptype,t1.page,t1.single_push_from
LIMIT   2000
;
