爱迪生商业大盘SQL解析

SELECT
    t1.event_day, t1.cmatch, t1.trans_type, t1.exp_id,
    sum(eshow),          -- 曝光
    sum(click),          -- 点击
    sum(charge),         -- 实际消费
    sum(convert_num),    -- 转化数
    sum(target_charge)   -- 目标消费（即"该花多少钱"）
FROM t1 LEFT JOIN t2
    ON (searchid_decimal = search_id AND ideaid = idea_id)
GROUP BY event_day, cmatch, trans_type, exp_id

SELECT
    event_day, searchid_decimal, ideaid, cmatch,
    -- 实验分组
    CASE WHEN ovl_exp LIKE '%153613-0%' THEN 'exp'
         WHEN ovl_exp LIKE '%153613-dz%' THEN 'dz' END AS exp_id,
    trans_type,
    eshow,               -- 曝光（表里自带字段）
    clk AS click,        -- 点击
    -- 消费计算：区分计价方式
    CASE
        WHEN (bid_type='3' AND pricing_type='1') OR bid_type='2'
            THEN price / 1000 / 100    -- oCPM: price 单位是厘/千次
        ELSE clk * price / 100          -- CPC: 点击 × 单价
    END AS charge
FROM fc_nad.nativeads_feed_asp_view
WHERE event_day BETWEEN '20251114' AND '20251119'
    AND event_type = 'browser'
    AND cmatch = '719'
    AND (ovl_exp LIKE '%153613-0%' OR ovl_exp LIKE '%153613-dz%')

来源: nativeads_ocpx_charge_hour
产出: convert_num（转化数）+ target_charge（目标消费）

来源: nativeads_video_play_conv (ocpx_join_flag = '0')
产出: 同上，convert_num + target_charge

来源: asp_view LEFT JOIN nativeads_ods_ocpc_all_convert
适用: trans_type in (14,78,80-88,90)
产出: convert_num = 0, target_charge = 按ROI逻辑算

来源: asp_view LEFT JOIN nativeads_feed_ocpc_all_convert
适用: trans_type = 118
产出: convert_num = 0, target_charge = 按明投ROI逻辑算

-- 出价：是深度转化就用深度出价，否则用普通出价
ocpc_bid_2 = IF(is_ocpc_deep=1 AND deep_trans_type NOT IN ('28','29'),
                deep_ocpc_bid, ocpc_bid)
 
-- 转化数：是深度转化就看深度转化类型是否匹配，否则直接用 convert_num
convert_num = IF(is_ocpc_deep=1 AND deep_trans_type NOT IN ('28','29'),
                 IF(convert_num>0 AND array_contains(..., deep_trans_type), convert_num, 0),
                 convert_num)

target_charge = CASE
    WHEN trans_type IN (14,80-88,90,118) THEN 0        -- 这些类型留给第3、4路算
    ELSE ocpc_bid_2 * convert_num / 100.0              -- 常规：出价 × 转化数
END

roi_tag = CASE
    WHEN pla_rank=1 AND bw_idf_ratio>0 THEN 'ROI出价'
    ELSE '非ROI出价'
END

-- convert_num: trans_type 和 convert_type 匹配就算 1 次转化
-- convert_value: 从 convert_value_time 数组里炸开，取金额部分

target_charge = CASE
    WHEN 非ROI出价 THEN ocpc_bid * convert_num / 100    -- 普通算法
    WHEN ROI出价   THEN convert_value / bw_idf_ratio     -- 转化价值 / ROI系数
END

SELECT search_id, idea_id,
    SUM(convert_num) AS convert_num,       -- 只有 1、2 路贡献
    SUM(target_charge) AS target_charge    -- 四路都贡献
FROM (第1路 UNION ALL 第2路 UNION ALL 第3路 UNION ALL 第4路)
GROUP BY search_id, idea_id