-- 全量基线 · 召回策略效果（实验分层版 · 中文名 + 总计行 + 消耗比 + Top20 vid + 视频元信息）
-- 基于 base_baseline_named_top60vid.sql + 引入尾号实验分组（apptype × abcode × suffix_group × suffix）
-- 实验映射参考 tasks/00_尾号实验/base_all_new_v3_分层.sql（保持配置 single-source-of-truth 概念）
--
-- 核心区别 vs 原 top60vid 版：
--   1. 增加 4 个实验维度：apptype, abcode, suffix_group, suffix
--   2. apptype 不再合并为 all：实验配置是 per-apptype，0/4 分别独立输出
--   3. abcode 过滤改为参考文件的 canonical 版：apptype=4 用 ab0-9；apptype=0 排除 ab5/6/7
--   4. exp_pv 阈值由 1000 调到 100：分到 ~32 个实验桶后单桶流量缩到 ~1/32
--
-- 输出层级（row_order）：
--   0 = (apptype, abcode, suffix_group, suffix) 总计（不分召回源）
--   1 = (apptype, abcode, suffix_group, suffix) × 召回源 汇总
--   2 = (apptype, abcode, suffix_group, suffix) × 召回源 × vid top20 明细 + 大盘 top20
--
-- 占比分母：三层均用对应 (apptype, abcode, suffix_group, suffix) 的 total_tab，桶内自洽
-- 消耗比 = return_n_uv_rate / exp_pv_rate - 1：>0 高效, <0 低效, 总计行恒为 0
-- 视频元信息：JOIN loghubods.video_dimension_detail_add_column（仅 vid 行）
-- 跑批：python fetch_daily.py tasks/召回策略效果/base_baseline_named_top20vid_分层.sql --days 30

WITH t_suffix_group AS
(
    SELECT "a" AS suffix, "ab" AS suffix_group
    UNION ALL SELECT "b", "ab"
    UNION ALL SELECT "0", "01"
    UNION ALL SELECT "1", "01"
    UNION ALL SELECT "2", "2c"
    UNION ALL SELECT "c", "2c"
    UNION ALL SELECT "3", "34"
    UNION ALL SELECT "4", "34"
    UNION ALL SELECT "5", "5d"
    UNION ALL SELECT "d", "5d"
    UNION ALL SELECT "6", "67"
    UNION ALL SELECT "7", "67"
    UNION ALL SELECT "8", "89"
    UNION ALL SELECT "9", "89"
    UNION ALL SELECT "e", "ef"
    UNION ALL SELECT "f", "ef"
)
-- 实验映射：未列出的桶 → 自动默认为"对照组"
-- 维护方式与 tasks/00_尾号实验/base_all_new_v3_分层.sql 同步
,t_experiment_map AS
(
    -- ── apptype = 4 ──────────────────────────────────────────────────
    SELECT "4" AS apptype, "ab" AS suffix_group, "实验组：变更str*ros建模目标实验 有问题" AS abcode, "20260413" AS start_dt, "20260415" AS end_dt
    UNION ALL SELECT "4", "ab", "实验组：变更str*ros建模目标实验", "20260416", "20260507"
    UNION ALL SELECT "4", "ab", "实验组：排序对高质召回提权实验", "20260508", "29991231"
    UNION ALL SELECT "4", "01", "实验组：变更str*ros建模目标实验", "20260320", "20260410"
    UNION ALL SELECT "4", "01", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "01", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "67", "实验组：变更str*ros建模目标实验", "20260330", "20260410"
    UNION ALL SELECT "4", "67", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "67", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "5d", "实验组：解构特征排序str模型",     "20260314", "20260406"
    UNION ALL SELECT "4", "5d", "实验组：变更str*ros建模目标实验", "20260407", "20260410"
    UNION ALL SELECT "4", "5d", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "5d", "实验组：变更str*ros建模目标实验", "20260416", "20260507"
    UNION ALL SELECT "4", "5d", "实验组：DNN模型-调参",     "20260508", "29991231"
    UNION ALL SELECT "4", "34", "实验组：变更str*ros建模目标实验", "20260407", "20260410"
    UNION ALL SELECT "4", "34", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "4", "34", "实验组：变更str*ros建模目标实验", "20260416", "20260507"
    UNION ALL SELECT "4", "34", "实验组：召回剔除低质召回实验", "20260508", "29991231"
    UNION ALL SELECT "4", "67", "实验组：bn_ros新损失函数",        "20260311", "20260319"
    UNION ALL SELECT "4", "ef", "实验组：解构特征排序str模型&召回", "20260314", "20260320"
    UNION ALL SELECT "4", "ef", "实验组：DNN模型",                 "20260407", "29991231"
    UNION ALL SELECT "4", "2c", "实验组：DNN模型-调参",            "20260413", "29991231"
    UNION ALL SELECT "4", "89", "对照组",                          "20260301", "299991231"

    -- ── apptype = 0 ──────────────────────────────────────────────────
    UNION ALL SELECT "0", "ab", "实验组：变更str*ros建模目标实验 有问题", "20260413", "20260415"
    UNION ALL SELECT "0", "ab", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "ab", "实验组：变更str*ros建模目标实验", "20260417", "20260429"
    UNION ALL SELECT "0", "ab", "实验组：DNN模型-调参", "20260430", "29991231"
    UNION ALL SELECT "0", "01", "实验组：变更str*ros建模目标实验", "20260320", "20260410"
    UNION ALL SELECT "0", "01", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "01", "实验组：变更str*ros建模目标实验", "20260416", "20260507"
    UNION ALL SELECT "0", "01", "对照组", "20260508", "29991231"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验", "20260330", "20260410"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "34", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验", "20260417", "20260424"
    UNION ALL SELECT "0", "34", "实验组：曝光建模v1+变更str*ros建模目标实验", "20260425", "20260425"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验", "20260426", "20260429"
    UNION ALL SELECT "0", "34", "实验组：DNN模型-调参", "20260430", "29991231"
    UNION ALL SELECT "0", "67", "实验组：变更str*ros建模目标实验", "20260330", "20260410"
    UNION ALL SELECT "0", "67", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "67", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "67", "实验组：变更str*ros建模目标实验", "20260417", "29991231"
    UNION ALL SELECT "0", "5d", "实验组：变更str*ros建模目标实验", "20260407", "20260410"
    UNION ALL SELECT "0", "5d", "实验组：变更str*ros建模目标实验 有问题", "20260411", "20260415"
    UNION ALL SELECT "0", "5d", "实验组：DNN模型-调参", "20260416", "20260416"
    UNION ALL SELECT "0", "5d", "实验组：变更str*ros建模目标实验", "20260417", "20260424"
    UNION ALL SELECT "0", "5d", "实验组：曝光建模v1+DNN模型", "20260425", "29991231"
    UNION ALL SELECT "0", "ef", "实验组：DNN模型-调参",            "20260410", "29991231"
    UNION ALL SELECT "0", "2c", "实验组：DNN模型",                 "20260413", "20260507"
    UNION ALL SELECT "0", "2c", "实验组：DNN模型-调参",                 "20260508", "29991231"
    UNION ALL SELECT "0", "89", "对照组",                          "20260301", "20260507"
    UNION ALL SELECT "0", "89", "实验组：DNN模型-调参",                          "20260508", "29991231"
)
,base_tab AS
(
    SELECT  sub.dt
            ,sub.apptype
            ,sg.suffix_group
            ,COALESCE(m.abcode,"对照组") AS abcode
            ,sub.suffix
            ,sub.vid
            ,sub.mid
            ,sub.is_share
            ,sub.share_pv
            ,sub.is_return_1
            ,sub.return_n_uv
            ,sub.return_n_uv_noself
            ,sub.new_exposure_cnt
            ,CASE   WHEN sub.page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                    WHEN sub.page IN ("回流页","其他") THEN "非推荐"
                    ELSE "其他"
            END AS page
            ,sub.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
                        ,SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) AS suffix
                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","详情页")
                -- canonical 过滤：apptype=4 用 ab0-9；apptype=0 排除 ab5/6/7
                AND     abcode NOT IN ("ab100")
                AND     (apptype = "4"
                         OR abcode IN ("ab0","ab1","ab2","ab3","ab4","ab8","ab9"))
                AND     (apptype = "0"
                         OR abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9"))
            ) sub
    -- INNER JOIN: 仅合法 hex 尾号（防御异常数据）
    INNER JOIN t_suffix_group sg
    ON      sub.suffix = sg.suffix
    -- LEFT JOIN: 未命中实验 → "对照组"
    LEFT JOIN t_experiment_map m
    ON      sub.apptype = m.apptype
    AND     sg.suffix_group = m.suffix_group
    AND     '${dt}' BETWEEN m.start_dt AND m.end_dt
)
,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
(
    -- 每 (apptype, abcode, suffix_group, suffix, page) 大盘汇总，作为占比分母
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,suffix
            ,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, abcode, suffix_group, suffix, page
)
,detail_table AS
(
    -- 每 (apptype, abcode, suffix_group, suffix, page, 召回源) 一行
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,suffix
            ,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
                        ,apptype
                        ,abcode
                        ,suffix_group
                        ,suffix
                        ,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
                                    ,abcode
                                    ,suffix_group
                                    ,suffix
                                    ,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, abcode, suffix_group, suffix, page, single_push_from
            )
)
,vid_detail_table AS
(
    -- 每 (apptype, abcode, suffix_group, suffix, 召回源, vid) 一行 + 按 exp_pv 排名
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,suffix
            ,page
            ,single_push_from
            ,vid
            ,exp_uv
            ,exp_pv
            ,share_pv
            ,return_n_uv
            ,return_n_uv_noself
            ,new_exposure_cnt
            ,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
            ,ROW_NUMBER() OVER (
                PARTITION BY dt, apptype, abcode, suffix_group, suffix, page, single_push_from
                ORDER BY exp_pv DESC
             ) AS vid_rank
    FROM    (
                SELECT  dt
                        ,apptype
                        ,abcode
                        ,suffix_group
                        ,suffix
                        ,page
                        ,single_push_from
                        ,vid
                        ,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
                FROM    (
                            SELECT  dt
                                    ,apptype
                                    ,abcode
                                    ,suffix_group
                                    ,suffix
                                    ,vid
                                    ,mid
                                    ,share_pv
                                    ,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, abcode, suffix_group, suffix, page, single_push_from, vid
            )
)
,vid_total_table AS
(
    -- 大盘层级（未经 EXPLODE）：每 (apptype, abcode, suffix_group, suffix, vid) 一行 + 按 exp_pv 排名
    SELECT  dt
            ,apptype
            ,abcode
            ,suffix_group
            ,suffix
            ,page
            ,vid
            ,exp_uv
            ,exp_pv
            ,share_pv
            ,return_n_uv
            ,return_n_uv_noself
            ,new_exposure_cnt
            ,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
            ,ROW_NUMBER() OVER (
                PARTITION BY dt, apptype, abcode, suffix_group, suffix, page
                ORDER BY exp_pv DESC
             ) AS vid_rank
    FROM    (
                SELECT  dt
                        ,apptype
                        ,abcode
                        ,suffix_group
                        ,suffix
                        ,page
                        ,vid
                        ,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
                FROM    base_tab
                GROUP BY dt, apptype, abcode, suffix_group, suffix, page, vid
            )
)
-- ============ 总计 + 召回源汇总 + Top20 vid + 总计 Top20 vid 四层 UNION ============
SELECT  row_order
        ,日期
        ,apptype
        ,abcode
        ,suffix_group
        ,suffix
        ,页面
        ,召回源
        ,召回策略名
        ,一级类目
        ,二级类目
        ,vid_rank
        ,vid
        ,视频标题
        ,视频上传时间
        ,视频地址
        ,首发日期
        ,首发播放量
        ,首发来源
        ,首发渠道
        ,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    (
    -- 召回源汇总：每 (apptype, abcode, suffix_group, suffix, 召回源) 一行（row_order=1）
    SELECT  1 AS row_order
            ,t1.dt AS 日期
            ,t1.apptype AS apptype
            ,t1.abcode AS abcode
            ,t1.suffix_group AS suffix_group
            ,t1.suffix AS suffix
            ,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 二级类目
            ,CAST(NULL AS BIGINT) AS vid_rank
            ,'__汇总__' AS vid
            ,CAST(NULL AS STRING) AS 视频标题
            ,CAST(NULL AS STRING) AS 视频上传时间
            ,CAST(NULL AS STRING) AS 视频地址
            ,CAST(NULL AS STRING) AS 首发日期
            ,CAST(NULL AS BIGINT) AS 首发播放量
            ,CAST(NULL AS STRING) AS 首发来源
            ,CAST(NULL AS STRING) 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.abcode = t2.abcode
    AND     t1.suffix_group = t2.suffix_group
    AND     t1.suffix = t2.suffix
    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, abcode, suffix_group, suffix) 一行（不分召回源，未经 EXPLODE 的真实大盘）row_order=0
    SELECT  0 AS row_order
            ,dt AS 日期
            ,apptype AS apptype
            ,abcode AS abcode
            ,suffix_group AS suffix_group
            ,suffix AS suffix
            ,page AS 页面
            ,'__总计__' AS 召回源
            ,'总计' AS 召回策略名
            ,'-' AS 一级类目
            ,'-' AS 二级类目
            ,CAST(NULL AS BIGINT) AS vid_rank
            ,'__总计__' AS vid
            ,CAST(NULL AS STRING) AS 视频标题
            ,CAST(NULL AS STRING) AS 视频上传时间
            ,CAST(NULL AS STRING) AS 视频地址
            ,CAST(NULL AS STRING) AS 首发日期
            ,CAST(NULL AS BIGINT) AS 首发播放量
            ,CAST(NULL AS STRING) AS 首发来源
            ,CAST(NULL AS STRING) 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 ("推荐")

    UNION ALL

    -- vid 明细：每 (apptype, abcode, suffix_group, suffix, 召回源) top20 视频（按 exp_pv 降序）row_order=2
    SELECT  2 AS row_order
            ,v.dt AS 日期
            ,v.apptype AS apptype
            ,v.abcode AS abcode
            ,v.suffix_group AS suffix_group
            ,v.suffix AS suffix
            ,v.page AS 页面
            ,v.single_push_from AS 召回源
            ,COALESCE(m.strategy_name, v.single_push_from) AS 召回策略名
            ,COALESCE(m.l1, '其他') AS 一级类目
            ,COALESCE(m.l2, '其他') AS 二级类目
            ,v.vid_rank
            ,CAST(v.vid AS STRING) AS vid
            ,vd.标题 AS 视频标题
            ,CAST(vd.上传时间 AS STRING) AS 视频上传时间
            ,vd.视频地址 AS 视频地址
            ,CAST(vd.首发日期 AS STRING) AS 首发日期
            ,vd.首发播放量 AS 首发播放量
            ,vd.首发来源 AS 首发来源
            ,vd.首发渠道 AS 首发渠道
            ,v.str
            ,v.rosn
            ,v.rovn
            ,v.rosn_noself
            ,v.rovn_noself
            ,v.vovh24
            ,v.exp_uv
            ,t2.total_exp_uv
            ,ROUND(v.exp_uv / t2.total_exp_uv,4) AS exp_uv_rate
            ,v.exp_pv
            ,t2.total_exp_pv
            ,ROUND(v.exp_pv / t2.total_exp_pv,4) AS exp_pv_rate
            ,v.share_pv
            ,t2.total_share_pv
            ,ROUND(v.share_pv / t2.total_share_pv,4) AS share_pv_rate
            ,v.return_n_uv
            ,t2.total_return_n_uv
            ,ROUND(v.return_n_uv / t2.total_return_n_uv,4) AS return_n_uv_rate
            ,ROUND(IF(v.exp_pv / t2.total_exp_pv > 0
                      ,(v.return_n_uv / t2.total_return_n_uv) / (v.exp_pv / t2.total_exp_pv) - 1
                      ,NULL), 4) AS 消耗比
            ,v.return_n_uv_noself
            ,t2.total_return_n_uv_noself
            ,ROUND(v.return_n_uv_noself / t2.total_return_n_uv_noself,4) AS return_n_uv_rate_noself
            ,ROUND(IF(v.exp_pv / t2.total_exp_pv > 0
                      ,(v.return_n_uv_noself / t2.total_return_n_uv_noself) / (v.exp_pv / t2.total_exp_pv) - 1
                      ,NULL), 4) AS 消耗比_noself
            ,v.new_exposure_cnt
            ,t2.total_new_exposure_cnt
            ,ROUND(v.new_exposure_cnt / t2.total_new_exposure_cnt,4) AS new_exposure_rate
            ,1 AS vid_cnt
            ,t2.total_vid_cnt
            ,ROUND(1.0 / t2.total_vid_cnt,4) AS vid_cnt_rate
            ,ROUND(v.exp_pv / v.exp_uv,4) AS 人均曝光pv
            ,ROUND(v.share_pv / v.exp_uv,4) AS 人均分享pv
            ,CAST(v.exp_pv AS DOUBLE) AS 视频平均曝光次数
            ,CAST(v.return_n_uv AS BIGINT) AS max_return_n_uv
    FROM    vid_detail_table v
    JOIN    total_tab t2
    ON      v.dt = t2.dt
    AND     v.apptype = t2.apptype
    AND     v.abcode = t2.abcode
    AND     v.suffix_group = t2.suffix_group
    AND     v.suffix = t2.suffix
    AND     v.page = t2.page
    LEFT JOIN push_from_map m
    ON      v.single_push_from = m.push_from
    LEFT JOIN (
        SELECT  dt, 视频id, 标题, 上传时间, 视频地址,
                首发日期, 首发播放量, 首发来源, 首发渠道
        FROM    loghubods.video_dimension_detail_add_column
        WHERE   dt = '${dt}'
    ) vd
    ON      v.dt = vd.dt AND CAST(v.vid AS BIGINT) = vd.视频id
    WHERE   v.page IN ("推荐")
    AND     v.vid_rank <= 20

    UNION ALL

    -- 总计 vid 明细：每 (apptype, abcode, suffix_group, suffix) 大盘 top20 视频（不分召回源）row_order=2, 召回源='__总计__'
    SELECT  2 AS row_order
            ,vt.dt AS 日期
            ,vt.apptype AS apptype
            ,vt.abcode AS abcode
            ,vt.suffix_group AS suffix_group
            ,vt.suffix AS suffix
            ,vt.page AS 页面
            ,'__总计__' AS 召回源
            ,'总计' AS 召回策略名
            ,'-' AS 一级类目
            ,'-' AS 二级类目
            ,vt.vid_rank
            ,CAST(vt.vid AS STRING) AS vid
            ,vd.标题 AS 视频标题
            ,CAST(vd.上传时间 AS STRING) AS 视频上传时间
            ,vd.视频地址 AS 视频地址
            ,CAST(vd.首发日期 AS STRING) AS 首发日期
            ,vd.首发播放量 AS 首发播放量
            ,vd.首发来源 AS 首发来源
            ,vd.首发渠道 AS 首发渠道
            ,vt.str
            ,vt.rosn
            ,vt.rovn
            ,vt.rosn_noself
            ,vt.rovn_noself
            ,vt.vovh24
            ,vt.exp_uv
            ,t2.total_exp_uv
            ,ROUND(vt.exp_uv / t2.total_exp_uv,4) AS exp_uv_rate
            ,vt.exp_pv
            ,t2.total_exp_pv
            ,ROUND(vt.exp_pv / t2.total_exp_pv,4) AS exp_pv_rate
            ,vt.share_pv
            ,t2.total_share_pv
            ,ROUND(vt.share_pv / t2.total_share_pv,4) AS share_pv_rate
            ,vt.return_n_uv
            ,t2.total_return_n_uv
            ,ROUND(vt.return_n_uv / t2.total_return_n_uv,4) AS return_n_uv_rate
            ,ROUND(IF(vt.exp_pv / t2.total_exp_pv > 0
                      ,(vt.return_n_uv / t2.total_return_n_uv) / (vt.exp_pv / t2.total_exp_pv) - 1
                      ,NULL), 4) AS 消耗比
            ,vt.return_n_uv_noself
            ,t2.total_return_n_uv_noself
            ,ROUND(vt.return_n_uv_noself / t2.total_return_n_uv_noself,4) AS return_n_uv_rate_noself
            ,ROUND(IF(vt.exp_pv / t2.total_exp_pv > 0
                      ,(vt.return_n_uv_noself / t2.total_return_n_uv_noself) / (vt.exp_pv / t2.total_exp_pv) - 1
                      ,NULL), 4) AS 消耗比_noself
            ,vt.new_exposure_cnt
            ,t2.total_new_exposure_cnt
            ,ROUND(vt.new_exposure_cnt / t2.total_new_exposure_cnt,4) AS new_exposure_rate
            ,1 AS vid_cnt
            ,t2.total_vid_cnt
            ,ROUND(1.0 / t2.total_vid_cnt,4) AS vid_cnt_rate
            ,ROUND(vt.exp_pv / vt.exp_uv,4) AS 人均曝光pv
            ,ROUND(vt.share_pv / vt.exp_uv,4) AS 人均分享pv
            ,CAST(vt.exp_pv AS DOUBLE) AS 视频平均曝光次数
            ,CAST(vt.return_n_uv AS BIGINT) AS max_return_n_uv
    FROM    vid_total_table vt
    JOIN    total_tab t2
    ON      vt.dt = t2.dt
    AND     vt.apptype = t2.apptype
    AND     vt.abcode = t2.abcode
    AND     vt.suffix_group = t2.suffix_group
    AND     vt.suffix = t2.suffix
    AND     vt.page = t2.page
    LEFT JOIN (
        SELECT  dt, 视频id, 标题, 上传时间, 视频地址,
                首发日期, 首发播放量, 首发来源, 首发渠道
        FROM    loghubods.video_dimension_detail_add_column
        WHERE   dt = '${dt}'
    ) vd
    ON      vt.dt = vd.dt AND CAST(vt.vid AS BIGINT) = vd.视频id
    WHERE   vt.page IN ("推荐")
    AND     vt.vid_rank <= 20
) u
WHERE   exp_pv >= 100
;
