select
  sum(if (t2.is_automatch=1, t2.cost, 0)) as smart_cost,
  sum(if (t2.is_automatch=0, t2.cost, 0)) as std_cost,
  round(sum(if (t2.is_automatch=1, t2.cost, 0)) / sum(t2.cost), 5) AS rate
from (
  select
    custid
  from
    alimama_kgb_cmop_dev.p4p_cmop_n_sampler_cust_list_tmp
  where
    ds=20170503 and 
    sample_custs_id='1595_1493790275_a'
)t1
join(
  select
    is_automatch,
    cust_id,
    sum(cost)  as cost
  from
    ad_sds_hour.dws_p4p_ad_pvclk_hh
  where ds='20170727' AND is_match=0 AND pvtype_id=4
  group by is_automatch, cust_id
) t2
ON t1.custid=t2.cust_id;


SELECT
    t.ad_num        AS 广告数目,
    t.gain_rate     AS 智能匹配高出标准推广ROI百分比,
    t.greater_rate  AS 智能匹配比标准推广ROI好的广告占比,
    t.ds            AS 日期
FROM (
    SELECT 
        *
    FROM alimama_kgb_cmop_dev.p4p_cmop_n_athena_test_statistics_result
    WHERE type='daily' AND style='ROI' AND ad_level='(300, +oo)'
    ORDER BY ds DESC LIMIT 10
)t;


INSERT INTO TABLE alimama_kgb_cmop_dev.p4p_cmop_n_athena_result
PARTITION(ds='20170718')
SELECT 
  '1109815002',
  '22256007',
  '761068635',
  t4.keyword,
  'A',
  0
FROM(
  SELECT 
    DISTINCT t1.cust_id, t1.campaign_id, t1.adgroupid
  FROM(
    SELECT
      *
    FROM alimama_algo.p4p_cmop_n_athena_result
    WHERE ds='20170717'
  )t1
  JOIN(
      SELECT
          DISTINCT adgroup_id
      FROM alimama_algo.p4p_cmop_n_athena_adgroup_info
      WHERE ds='20170717' AND flag = '2'
  )t2
  ON t1.adgroupid=t2.adgroup_id
)t3
JOIN(
  SELECT 
    tx.adgroup_id AS adgroup_id,
    split(tx.query_list, '\003')[0] AS keyword
  FROM(
    SELECT 
      trans_array(1, '\002', adgroup_id, query_list) AS (adgroup_id, query_list)
    FROM alimama_algo.p4p_cmop_n_athena_adgroup_kwrecm_sort_result
    WHERE ds='20170717'
  )tx
  JOIN(
    SELECT 
      tm.*
    FROM(
      SELECT 
        tt.*
      FROM(
        SELECT 
          t.adgroup_id AS adgroup_id,
          COUNT(*) AS word_num
        FROM(
          SELECT 
            trans_array(1, '\002', adgroup_id, query_list) AS (adgroup_id, query_list)
          FROM alimama_algo.p4p_cmop_n_athena_adgroup_kwrecm_sort_result
          WHERE ds='20170717'
        )t
        GROUP BY t.adgroup_id
      )tt
      WHERE tt.word_num > 98
      LIMIT 50
    )tm 
    JOIN(
      SELECT
          DISTINCT adgroup_id
      FROM alimama_algo.p4p_cmop_n_athena_adgroup_info
      WHERE ds='20170717' AND flag = '2'
    )tn 
    ON tm.adgroup_id=tn.adgroup_id
  )ty
  ON tx.adgroup_id=ty.adgroup_id
)t4
ON t3.adgroupid=t4.adgroup_id
LIMIT 100;



create table if not exists alimama_kgb_cmop_dev.p4p_cmop_n_athena_adgroup_roi as
select
  tb.*
from(
  select
    t2.adgroup_id,
    t2.is_automatch,
    sum(imp) as imp,
    sum(click) as click,
    sum(cost) as cost,
    
    sum(if (pay_num is null, 0, pay_num)) as trade_cnt,
    sum(if (trade_amt is null, 0, trade_amt)) as trade_amt,
    sum(if (cart_num is null, 0, cart_num)) as cart_num,
    sum(if (col_num is null, 0, col_num)) as col_num,

    sum(click) / (sum(imp) + 1.0) as ctr,
    sum(cost) / (sum(click) + 1.0) as ppc,
    sum(if (pay_num is null, 0, pay_num)) / (sum(click) + 1.0) as cvr,
    sum(if (trade_amt is null, 0, trade_amt)) /  (sum(cost) + 1.0) as roi
  from
  (
    select
      custid
    from
      alimama_kgb_cmop_dev.p4p_cmop_n_sampler_cust_list_tmp
    where
      ds=20170503 and 
      sample_custs_id='1595_1493790275_a'
  )t1
  join
  (
    select
      is_automatch,
      pvtype_id,
      cust_id,
      adgroup_id,
      bidword_id,
      sum(impression) as imp,
      sum(click) as click,
      sum(cost)  as cost
    from
      ad_sds_hour.dws_p4p_ad_pvclk_hh
    where
      ds = '20170704' and is_match = 0 and pvtype_id = 4
    group by
      is_automatch,pvtype_id,cust_id, adgroup_id, bidword_id
  ) t2
  on
    t1.custid = t2.cust_id
  left outer join
  (
    select
      pv_type,
      adgroup_id,
      bidword_id,
      sum(pay_amt_direct+pay_amt_inner) as trade_amt,
      sum(cart_item_direct+cart_item_inner) as cart_num,
      sum(col_item_direct+col_item_inner) as col_num,
      sum(pay_num_direct+pay_num_inner) as pay_num
    from
      ad_sds_hour.halfhour_r_rpt_effect_mutex_forp4p
    where
      ds='20170704' and is_match=0 and pv_type=4
    group by
      pv_type,adgroup_id,bidword_id
  ) t3
  on
    t2.pvtype_id = t3.pv_type and
    t2.adgroup_id = t3.adgroup_id and
    t2.bidword_id = t3.bidword_id
  where t2.pvtype_id=4
  group by t2.adgroup_id, t2.is_automatch
)tb;


CREATE TABLE IF NOT EXISTS alimama_kgb_cmop_dev.p4p_cmop_n_athena_adgroup_roi_tmp as 
select 
  tb.*
from(
  select 
    tt.level as level,
    tt.ad_num as ad_num,
    round(tt.std_roi, 4)  AS std_roi,
    round(tt.smart_roi, 4)  AS smart_roi,
    round((tt.smart_roi - tt.std_roi) / tt.std_roi, 4) AS gain_rate,
    round(tt.greater_num / tt.ad_num, 4) AS greater_rate,
    round(1.0 * tt.less_num / tt.ad_num, 4) AS less_rate,
    round(1.0 * tt.equal_num / tt.ad_num, 4) AS equal_rate
  from(
    select 
      '(0, +oo)' as level,
      COUNT(*)   AS ad_num,
      1.0 * SUM(t1.trade_amt) / SUM(t1.cost) as std_roi,
      1.0 * SUM(t2.trade_amt) / SUM(t2.cost) as smart_roi,
      SUM(if(t2.roi > t1.roi, 1, 0)) AS greater_num,
      SUM(if(t2.roi < t1.roi, 1, 0)) AS less_num,
      SUM(if(abs(t2.roi - t1.roi) < 0.000001, 1, 0)) AS equal_num
    from (
      select 
        *
      from alimama_kgb_cmop_dev.p4p_cmop_n_athena_adgroup_roi
      where is_automatch=0 and click > 0
    )t1
    join(
      select 
        *
      from alimama_kgb_cmop_dev.p4p_cmop_n_athena_adgroup_roi
      where is_automatch=1 and click > 0
    )t2
    on t1.adgroup_id=t2.adgroup_id
  )tt

  UNION ALL 

  select 
    tt.level as level,
    tt.ad_num as ad_num,
    round(tt.std_roi, 4)  AS std_roi,
    round(tt.smart_roi, 4)  AS smart_roi,
    round((tt.smart_roi - tt.std_roi) / tt.std_roi, 4) AS gain_rate,
    round(tt.greater_num / tt.ad_num, 4) AS greater_rate,
    round(1.0 * tt.less_num / tt.ad_num, 4) AS less_rate,
    round(1.0 * tt.equal_num / tt.ad_num, 4) AS equal_rate
  from(
    select 
      '(100, +oo)' as level,
      COUNT(*)   AS ad_num,
      1.0 * SUM(t1.trade_amt) / SUM(t1.cost) as std_roi,
      1.0 * SUM(t2.trade_amt) / SUM(t2.cost) as smart_roi,
      SUM(if(t2.roi > t1.roi, 1, 0)) AS greater_num,
      SUM(if(t2.roi < t1.roi, 1, 0)) AS less_num,
      SUM(if(abs(t2.roi - t1.roi) < 0.000001, 1, 0)) AS equal_num
    from (
      select 
        *
      from alimama_kgb_cmop_dev.p4p_cmop_n_athena_adgroup_roi
      where is_automatch=0 and click > 100
    )t1
    join(
      select 
        *
      from alimama_kgb_cmop_dev.p4p_cmop_n_athena_adgroup_roi
      where is_automatch=1 and click > 100
    )t2
    on t1.adgroup_id=t2.adgroup_id
  )tt

  UNION ALL 

  select 
    tt.level as level,
    tt.ad_num as ad_num,
    round(tt.std_roi, 4)  AS std_roi,
    round(tt.smart_roi, 4)  AS smart_roi,
    round((tt.smart_roi - tt.std_roi) / tt.std_roi, 4) AS gain_rate,
    round(tt.greater_num / tt.ad_num, 4) AS greater_rate,
    round(1.0 * tt.less_num / tt.ad_num, 4) AS less_rate,
    round(1.0 * tt.equal_num / tt.ad_num, 4) AS equal_rate
  from(
    select 
      '(200, +oo)' as level,
      COUNT(*)   AS ad_num,
      1.0 * SUM(t1.trade_amt) / SUM(t1.cost) as std_roi,
      1.0 * SUM(t2.trade_amt) / SUM(t2.cost) as smart_roi,
      SUM(if(t2.roi > t1.roi, 1, 0)) AS greater_num,
      SUM(if(t2.roi < t1.roi, 1, 0)) AS less_num,
      SUM(if(abs(t2.roi - t1.roi) < 0.000001, 1, 0)) AS equal_num
    from (
      select 
        *
      from alimama_kgb_cmop_dev.p4p_cmop_n_athena_adgroup_roi
      where is_automatch=0 and click > 200
    )t1
    join(
      select 
        *
      from alimama_kgb_cmop_dev.p4p_cmop_n_athena_adgroup_roi
      where is_automatch=1 and click > 200
    )t2
    on t1.adgroup_id=t2.adgroup_id
  )tt

  UNION ALL 

  select 
    tt.level as level,
    tt.ad_num as ad_num,
    round(tt.std_roi, 4)  AS std_roi,
    round(tt.smart_roi, 4)  AS smart_roi,
    round((tt.smart_roi - tt.std_roi) / tt.std_roi, 4) AS gain_rate,
    round(tt.greater_num / tt.ad_num, 4) AS greater_rate,
    round(1.0 * tt.less_num / tt.ad_num, 4) AS less_rate,
    round(1.0 * tt.equal_num / tt.ad_num, 4) AS equal_rate
  from(
    select 
      '(300, +oo)' as level,
      COUNT(*)   AS ad_num,
      1.0 * SUM(t1.trade_amt) / SUM(t1.cost) as std_roi,
      1.0 * SUM(t2.trade_amt) / SUM(t2.cost) as smart_roi,
      SUM(if(t2.roi > t1.roi, 1, 0)) AS greater_num,
      SUM(if(t2.roi < t1.roi, 1, 0)) AS less_num,
      SUM(if(abs(t2.roi - t1.roi) < 0.000001, 1, 0)) AS equal_num
    from (
      select 
        *
      from alimama_kgb_cmop_dev.p4p_cmop_n_athena_adgroup_roi
      where is_automatch=0 and click > 300
    )t1
    join(
      select 
        *
      from alimama_kgb_cmop_dev.p4p_cmop_n_athena_adgroup_roi
      where is_automatch=1 and click > 300
    )t2
    on t1.adgroup_id=t2.adgroup_id
  )tt
)tb;


技术支持业务
技术增强业务
技术引领业务（无人驾驶）

行癫讲话
