渠道映射等关系

--odps sql 
--********************************************************************--
--description:保姆DSP朋友圈渠道数据报表
--author:shanwei
--create time:2018-12-19 11:12:00
--modifier:单伟
--modify reason:改为全量更新数据(2019-01-01以后)，删掉分区
--modify time:2019-06-20 15:20:00
--********************************************************************--
insert overwrite table a_bm_dsp_channel_statistical

select
     dates
     ,hmsr
     ,name_2
     ,sum(pv_num) pv_num --PV(访问量)
     ,sum(uv_num) uv_num  --UV(用户量)
     ,sum(clue_num) clue_num  --线索数
     ,sum(eff_clue_num) eff_clue_num  --有效线索数
     ,sum(sign_num) sign_num --签单数
      ,sum(audit_order) as audit_order
    ,sum(a_audit_order) as a_audit_order
    ,sum(b_audit_order) as b_audit_order
from (
     --计算 PV、UV
     select
          to_date(a.dt,‘yyyymmdd‘) dates
          ,a.hmsr
          ,b.name_2
          ,count(a.cookie_id) pv_num  --PV(访问量)
          ,count(distinct a.cookie_id) uv_num  --UV(用户量)
          ,0 clue_num   --线索数
          ,0 eff_clue_num  --有效线索数
          ,0 sign_num    --签单数
           ,0 as audit_order
        ,0 as a_audit_order
        ,0 as b_audit_order
     from (
         select *
         from f_jz_traffic_base
         where cate_id like ‘2%‘    --取保姆
           and dt >= ‘20190101‘
           and event_type=‘pageview‘
           ) a
     left join d_source b
            on a.source_id = b.id
     where b.name_2 in (‘DSP_微信朋友圈‘,‘DSP_快手‘,‘DSP_知乎‘,‘DSP_闪投‘,‘DSP_网易新闻‘,‘DSP_搜狐新闻‘, ‘DSP_一点资讯‘,‘DSP_粉丝通‘,‘DSP_UC头条‘)
     group by a.dt,a.hmsr,b.name_2

     union all
     --计算线索
     select
          to_date(substr(a.create_time,1,10),‘yyyy-mm-dd‘) dates
          ,a.max_hmsr as hmsr
          ,b.name_2
          ,0 pv_num  --PV(访问量)
          ,0 uv_num  --UV(用户量)
          ,count(distinct a.id) as clue_num --线索数
          ,0 eff_clue_num --有效线索数
          ,0 sign_num --签单数
           ,0 as audit_order
        ,0 as a_audit_order
        ,0 as b_audit_order
     from (
         select *
         from f_jz_clue_main
         where category_id = 212
            and substr(create_time,1,10) >= ‘2019-01-01‘
          ) a
     left join d_source b
            on a.original_source_id = b.id
     where b.name_2 in (‘DSP_微信朋友圈‘,‘DSP_快手‘,‘DSP_知乎‘,‘DSP_闪投‘,‘DSP_网易新闻‘,‘DSP_搜狐新闻‘, ‘DSP_一点资讯‘,‘DSP_粉丝通‘,‘DSP_UC头条‘)
     group by substr(a.create_time,1,10),a.max_hmsr,b.name_2
     
     union all
     --计算有效线索
     select
          to_date(substr(a.chance_create_time,1,10),‘yyyy-mm-dd‘) dates
          ,a.max_hmsr as hmsr
          ,b.name_2
          ,0 pv_num  --PV(访问量)
          ,0 uv_num  --UV(用户量)
          ,0 clue_num --线索数
          ,count(distinct a.id) eff_clue_num --有效线索数
          ,0 sign_num --签单数
           ,0 as audit_order
        ,0 as a_audit_order
        ,0 as b_audit_order
     from (
         select *
         from f_jz_clue_main
         where category_id = 212
            and substr(chance_create_time,1,10) >= ‘2019-01-01‘
          ) a
     left join d_source b
            on a.original_source_id = b.id
     where b.name_2 in (‘DSP_微信朋友圈‘,‘DSP_快手‘,‘DSP_知乎‘,‘DSP_闪投‘,‘DSP_网易新闻‘,‘DSP_搜狐新闻‘, ‘DSP_一点资讯‘,‘DSP_粉丝通‘,‘DSP_UC头条‘)
     group by substr(a.chance_create_time,1,10),a.max_hmsr,b.name_2
     
     union all
     --计算签单量
     select
          to_date(substr(a.order_date,1,10),‘yyyy-mm-dd‘) dates
          ,clue.max_hmsr as hmsr
          ,b.name_2
          ,0 pv_num  --PV(访问量)
          ,0 uv_num  --UV(用户量)
          ,0 clue_num --线索数
          ,0 eff_clue_num --有效线索数
          ,count(distinct a.id) sign_num --签单数
           ,0 as audit_order
        ,0 as a_audit_order
        ,0 as b_audit_order
     from (
         select *
         from f_jz_order_main
         where category_id = 212
            and substr(order_date,1,10) >= ‘2019-01-01‘
          ) a
     left join f_jz_clue_main clue
            on a.clue_id = clue.id
     left join d_source b
            on a.original_source_id = b.id
     where b.name_2 in (‘DSP_微信朋友圈‘,‘DSP_快手‘,‘DSP_知乎‘,‘DSP_闪投‘,‘DSP_网易新闻‘,‘DSP_搜狐新闻‘, ‘DSP_一点资讯‘,‘DSP_粉丝通‘,‘DSP_UC头条‘)
     group by substr(a.order_date,1,10),clue.max_hmsr,b.name_2

      union all
     --计算审核单量
     select
          to_date(substr(work_date,1,10),‘yyyy-mm-dd‘) dates
          ,null as hmsr
          ,last_name_2 name_2
          ,0 pv_num  --PV(访问量)
          ,0 uv_num  --UV(用户量)
          ,0 clue_num --线索数
          ,0 eff_clue_num --有效线索数
          ,0 as sign_num
          ,sum(aduit) as audit_order
          ,sum(label_type_a_ne_aduit) as a_audit_order
          ,sum(label_type_b_ne_aduit) as b_audit_order
     from 
         m_bm_adviser_quota_all
     where 
        dt = ‘${bdp.system.bizdate}‘
     group by 
         work_date 
          ,last_name_2
      ) t
group by dates,hmsr,name_2