-- 全量基线 · 召回策略效果（中文名版 + 总计行 + 消耗比）
-- 口径：abcode IN (ab0..ab9) 且 NOT IN (ab100) → 团队 canonical 基线流量定义
-- 维度：apptype × 页面分类 × 召回源（push_from）
-- apptype: 0=票圈推荐, 4=票圈视频, all=合并
-- 召回源: 21 个明细 + 1 个总计/apptype（总计从 base_tab 直出，未经 EXPLODE 通胀）
-- 归因：EXPLODE pushFromIndexList → 明细行 rate 之和 >100%（每个召回源单独认领）
-- 消耗比 = return_n_uv_rate / exp_pv_rate - 1：>0 表示产出占比高于曝光占比（高效），<0 反之，总计行恒为 0
-- 跑批：python fetch_daily.py tasks/召回策略效果/base_baseline_named.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")
            )
)
,push_from_map AS
(
    -- 召回源标识 → 中文名 / 一级类目 / 二级类目
    -- 来源：~/Desktop/project/docs/召回策略梳理/recall_strategies_full.csv
              SELECT 'recall_strategy_region_1h'      AS push_from, '省份实时热门'              AS strategy_name, '场' AS l1, '地域'    AS l2
    UNION ALL SELECT 'recall_pool_region_h',           '区域小时热门',              '场', '地域'
    UNION ALL SELECT 'recall_pool_region_24h',         '区域24小时热门',            '场', '地域'
    UNION ALL SELECT 'recall_pool_24h',                '全局24小时兜底',            '场', '地域'
    UNION ALL SELECT 'rov_recall_24h_dup',             '全局24小时兜底dup',         '场', '地域'
    UNION ALL SELECT 'city_rovn',                      '城市热门',                  '场', '地域'
    UNION ALL SELECT 'priori_province_ros',            '省份日热门-分享回流率',     '场', '地域'
    UNION ALL SELECT 'priori_province_rovn',           '省份日热门-回流率',         '场', '地域'
    UNION ALL SELECT 'priori_province_str',            '省份日热门-分享率',         '场', '地域'
    UNION ALL SELECT 'recall_strategy_head_cate1',     '头部视频一级类目x省',       '场', '头部视频'
    UNION ALL SELECT 'recall_strategy_head_cate2',     '头部视频二级类目x省',       '场', '头部视频'
    UNION ALL SELECT 'recall_strategy_head_cate2_rov', '头部视频相似类目',          '场', '头部视频'
    UNION ALL SELECT 'recall_strategy_scene_cf_rovn',  '场景协同-回流率',           '场', '头部视频'
    UNION ALL SELECT 'recall_strategy_scene_cf_rosn',  '场景协同-分享回流率',       '场', '头部视频'
    UNION ALL SELECT 'return_video_recall',            '老版相似视频(老版i2i)',     '场', '头部视频'
    UNION ALL SELECT 'recall_strategy_user_cate1',     '用户一级类目偏好',          '人', '分享'
    UNION ALL SELECT 'recall_strategy_user_cate2',     '用户二级类目偏好',          '人', '分享'
    UNION ALL SELECT 'return_1_cate2_ros',             '回流类目ros',               '人', '回流'
    UNION ALL SELECT 'return_1_cate2_str',             '回流类目rov',               '人', '回流'
    UNION ALL SELECT 'flow_pool',                      '汤姆森冷启池',              '场', '流量池'
    UNION ALL SELECT 'recall_strategy_hotspot',        '抖音热点池',                '场', '流量池'
)
,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))
            )
)
-- ============ 明细行 + 总计行 UNION ============
SELECT  row_order
        ,日期
        ,apptype
        ,页面
        ,召回源
        ,召回策略名
        ,一级类目
        ,二级类目
        ,str
        ,rosn
        ,rovn
        ,rosn_noself
        ,rovn_noself
        ,vovh24
        ,exp_uv
        ,total_exp_uv
        ,exp_uv_rate
        ,exp_pv
        ,total_exp_pv
        ,exp_pv_rate
        ,share_pv
        ,total_share_pv
        ,share_pv_rate
        ,return_n_uv
        ,total_return_n_uv
        ,return_n_uv_rate
        ,消耗比
        ,return_n_uv_noself
        ,total_return_n_uv_noself
        ,return_n_uv_rate_noself
        ,消耗比_noself
        ,new_exposure_cnt
        ,total_new_exposure_cnt
        ,new_exposure_rate
        ,vid_cnt
        ,total_vid_cnt
        ,vid_cnt_rate
        ,人均曝光pv
        ,人均分享pv
        ,视频平均曝光次数
        ,max_return_n_uv
FROM    (
    -- 明细：每个召回源一行（带中文名 / 类目 / 占比）
    SELECT  1 AS row_order  -- 排序辅助：1=明细，0=总计（总计在前）
            ,t1.dt AS 日期
            ,t1.apptype AS apptype
            ,t1.page AS 页面
            ,t1.single_push_from AS 召回源
            ,COALESCE(m.strategy_name, t1.single_push_from) AS 召回策略名
            ,COALESCE(m.l1, '其他') AS 一级类目
            ,COALESCE(m.l2, '其他') 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
            ,ROUND(IF(t1.exp_pv / t2.total_exp_pv > 0
                      ,(t1.return_n_uv / t2.total_return_n_uv) / (t1.exp_pv / t2.total_exp_pv) - 1
                      ,NULL), 4) AS 消耗比
            ,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
            ,ROUND(IF(t1.exp_pv / t2.total_exp_pv > 0
                      ,(t1.return_n_uv_noself / t2.total_return_n_uv_noself) / (t1.exp_pv / t2.total_exp_pv) - 1
                      ,NULL), 4) AS 消耗比_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
    LEFT JOIN push_from_map m
    ON      t1.single_push_from = m.push_from
    WHERE   t1.page IN ("推荐")
    AND     t1.exp_pv > 100

    UNION ALL

    -- 总计：每 apptype 一行（汇总所有召回源，未经 EXPLODE 的真实大盘）
    SELECT  0 AS row_order
            ,dt AS 日期
            ,apptype AS apptype
            ,page AS 页面
            ,'__总计__' AS 召回源
            ,'总计' AS 召回策略名
            ,'-' AS 一级类目
            ,'-' AS 二级类目
            ,ROUND(COALESCE(total_share_pv / total_exp_pv,0),4) AS str
            ,ROUND(COALESCE(total_return_n_uv / total_share_pv,0),4) AS rosn
            ,ROUND(COALESCE(total_return_n_uv / total_exp_pv,0),4) AS rovn
            ,ROUND(COALESCE(total_return_n_uv_noself / total_share_pv,0),4) AS rosn_noself
            ,ROUND(COALESCE(total_return_n_uv_noself / total_exp_pv,0),4) AS rovn_noself
            ,ROUND(COALESCE(total_new_exposure_cnt / total_exp_pv,0),4) AS vovh24
            ,total_exp_uv AS exp_uv
            ,total_exp_uv AS total_exp_uv
            ,1.0 AS exp_uv_rate
            ,total_exp_pv AS exp_pv
            ,total_exp_pv AS total_exp_pv
            ,1.0 AS exp_pv_rate
            ,total_share_pv AS share_pv
            ,total_share_pv AS total_share_pv
            ,1.0 AS share_pv_rate
            ,total_return_n_uv AS return_n_uv
            ,total_return_n_uv AS total_return_n_uv
            ,1.0 AS return_n_uv_rate
            ,0.0 AS 消耗比                                    -- 总计自身 = 平均，恒为 0
            ,total_return_n_uv_noself AS return_n_uv_noself
            ,total_return_n_uv_noself AS total_return_n_uv_noself
            ,1.0 AS return_n_uv_rate_noself
            ,0.0 AS 消耗比_noself
            ,total_new_exposure_cnt AS new_exposure_cnt
            ,total_new_exposure_cnt AS total_new_exposure_cnt
            ,1.0 AS new_exposure_rate
            ,total_vid_cnt AS vid_cnt
            ,total_vid_cnt AS total_vid_cnt
            ,1.0 AS vid_cnt_rate
            ,ROUND(COALESCE(total_exp_pv / total_exp_uv,0),4) AS 人均曝光pv
            ,ROUND(COALESCE(total_share_pv / total_exp_uv,0),4) AS 人均分享pv
            ,ROUND(COALESCE(total_exp_pv / total_vid_cnt,0),4) AS 视频平均曝光次数
            ,CAST(NULL AS BIGINT) AS max_return_n_uv
    FROM    total_tab
    WHERE   page IN ("推荐")
) u
ORDER BY 日期 DESC, apptype, row_order, exp_pv_rate DESC
LIMIT   3000
;
