-- 全量基线 · V566 实验"供给类型 × 驱动策略"效率分析
-- 基于 base_baseline_named_按桶_独占.sql 改造：把"召回源"维度替换为"供给类型 + 驱动策略"
--
-- 数据源切换说明：
--   ↪ 原: loghubods.dwd_recsys_alg_exposure_base_20250108（仅有 recommendlogvo.pushFromIndexList）
--   ↪ 新: loghubods.dwd_recsys_alg_sample_all_20250212（多了 extend_alg.scoresMap，含 V566 注入的供给字段）
--   ↪ sample 表分区为 (dt, hh) 双层 → 单天扫 24 小时分区，体量 ~24×，注意成本
--
-- 供给字段来源：V566 RankStrategy 把 supplyType / drivingStrategy / sceneDimension 写入 scoresMap：
--   item.getScoresMap().put("supplyType_"     + supplyType,     1.0);
--   item.getScoresMap().put("drivingStrategy_"+ drivingStrategy,1.0);
--   item.getScoresMap().put("sceneDimension_" + sceneDimension, 1.0);
--   item.getScoresMap().put("supplyTypeWeight", weight);  -- 实际打到分上的乘数
-- 提取方式：REGEXP_EXTRACT(scoresmap,'"supplyType_([^"]+)"',1)
--
-- 范围说明：只有 V566 RankStrategy 写这些字段 → WHERE supply_type IS NOT NULL 自动收敛到 V566 桶
--
-- 输出层级（row_order）：
--   0 = (apptype, abcode, suffix_group) 总计
--   1 = (apptype, abcode, suffix_group) × (供给类型, 驱动策略) 汇总
--
-- 跑批：python fetch_daily.py tasks/召回策略效果/base_baseline_named_按桶_供给.sql --days 7

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"
)
,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.vid
            ,sub.mid
            ,sub.share_pv
            ,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.supply_type
            ,sub.driving_strategy
            ,sub.supply_type_weight
    FROM    (
                SELECT  dt, apptype, vid, mid, page,
                        COALESCE(CAST(share_cnt AS BIGINT),0) AS share_pv,
                        COALESCE(CAST(return_n_uv AS BIGINT),0) AS return_n_uv,
                        COALESCE(CAST(return_n_uv_noself AS BIGINT),0) AS return_n_uv_noself,
                        COALESCE(CAST(new_exposure_cnt AS BIGINT),0) AS new_exposure_cnt,
                        -- 从 extend_alg.scoresMap 提取 V566 注入的供给字段
                        -- scoresMap 形如：{"supplyType_自动AGC":1.0,"drivingStrategy_当下供需gap":1.0,...}
                        REGEXP_EXTRACT(
                            REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\",""),
                            '"supplyType_([^"]+)"', 1
                        ) AS supply_type,
                        REGEXP_EXTRACT(
                            REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\",""),
                            '"drivingStrategy_([^"]+)"', 1
                        ) AS driving_strategy,
                        -- supplyTypeWeight 是数字，直接用 GET_JSON_OBJECT
                        CAST(GET_JSON_OBJECT(
                            REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\",""),
                            '$.supplyTypeWeight'
                        ) AS DOUBLE) AS supply_type_weight,
                        SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) AS suffix
                FROM    loghubods.dwd_recsys_alg_sample_all_20250212
                WHERE   dt = '${dt}'
                AND     apptype IN ('0','4')
                AND     extend_alg IS NOT NULL
                AND     GET_JSON_OBJECT(extend_alg,'$.scoresMap') IS NOT NULL
                AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页")
                AND     abcode NOT IN ("ab100")
            ) sub
    INNER JOIN t_suffix_group sg
    ON      sub.suffix = sg.suffix
    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
    -- 只保留 V566 桶（scoresMap 里有 supplyType_*）；非 V566 桶 supply_type 是空串
    WHERE   sub.supply_type IS NOT NULL AND sub.supply_type <> ''
)
,supply_weight_map AS
(
    -- V566 硬编码加权规则（见 RankStrategy4RegionMergeModelV566.getSupplyWeight）：
    --   supply_type ∈ {UGC, 垂直spider}                            → 0.8 (降权)
    --   supply_type = 自动AGC AND driving_strategy = 当下供需gap   → 1.5 (强提权)
    --   supply_type = 人工AGC AND driving_strategy ∈ {人工历史需求,当下供需gap} → 1.2 (提权)
    --   其他                                                          → 1.0 (默认)
              SELECT 'UGC'         AS supply_type, '-'          AS driving_strategy, 0.8 AS weight, '降权'   AS weight_tier
    UNION ALL SELECT '垂直spider',                 '-',                              0.8,           '降权'
    UNION ALL SELECT '自动AGC',                    '当下供需gap',                    1.5,           '强提权'
    UNION ALL SELECT '人工AGC',                    '人工历史需求',                   1.2,           '提权'
    UNION ALL SELECT '人工AGC',                    '当下供需gap',                    1.2,           '提权'
)
,total_tab AS
(
    SELECT  dt, apptype, abcode, suffix_group, 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, page
)
,detail_table AS
(
    SELECT  dt, apptype, abcode, suffix_group, page, supply_type, driving_strategy
            ,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(实际权重,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
    FROM    (
                SELECT  dt, apptype, abcode, suffix_group, page, supply_type, driving_strategy
                        ,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 视频平均曝光次数
                        ,AVG(supply_type_weight) AS 实际权重
                FROM    base_tab
                GROUP BY dt, apptype, abcode, suffix_group, page, supply_type, driving_strategy
            )
)
SELECT  row_order
        ,日期
        ,apptype
        ,abcode
        ,suffix_group
        ,页面
        ,供给类型
        ,驱动策略
        ,权重档
        ,V566配置权重
        ,实际权重
        ,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
        ,视频平均曝光次数
FROM    (
    -- (apptype, abcode, suffix_group, 供给类型, 驱动策略) 汇总（row_order=1）
    SELECT  1 AS row_order
            ,t1.dt AS 日期
            ,t1.apptype, t1.abcode, t1.suffix_group
            ,t1.page AS 页面
            ,t1.supply_type AS 供给类型
            ,t1.driving_strategy AS 驱动策略
            ,COALESCE(m.weight_tier, '默认') AS 权重档
            ,COALESCE(m.weight, 1.0) AS V566配置权重
            ,t1.实际权重
            ,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.视频平均曝光次数
    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.page = t2.page
    LEFT JOIN supply_weight_map m
    ON      t1.supply_type = m.supply_type
    AND     (m.driving_strategy = '-' OR t1.driving_strategy = m.driving_strategy)
    WHERE   t1.page IN ("推荐")
    AND     t1.exp_pv > 100

    UNION ALL

    -- 总计：每 (apptype, abcode, suffix_group) 一行（row_order=0）
    SELECT  0 AS row_order
            ,tt.dt AS 日期
            ,tt.apptype, tt.abcode, tt.suffix_group
            ,tt.page AS 页面
            ,'__总计__' AS 供给类型
            ,'-' AS 驱动策略
            ,'总计' AS 权重档
            ,1.0 AS V566配置权重
            ,0.0 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 消耗比
            ,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 视频平均曝光次数
    FROM    total_tab tt
    WHERE   tt.page IN ("推荐")
) u
WHERE   exp_pv >= 100
;
