dwb层,dm层和rpt层的增量脚本

dwb层建表语句




1,意向报名课程明细宽表
drop table edu_dwb.dwb_clazz_detail;
CREATE TABLE  edu_dwb.dwb_clazz_detail(
  `id` bigint ,
  `create_date_time` string ,
  `update_date_time` string COMMENT '最后更新时间',
  `deleted` bigint COMMENT '是否被删除(禁用)',
  `customer_id` bigint COMMENT '所属客户id',
  `first_id` bigint COMMENT '第一条客户关系id',
  `belonger` bigint COMMENT '归属人',
  `belonger_name` string COMMENT '归属人姓名',
  `initial_belonger` bigint COMMENT '初始归属人',
  `distribution_handler` bigint COMMENT '分配处理人',
  `business_scrm_department_id` bigint COMMENT '归属部门',
  `last_visit_time` string COMMENT '最后回访时间',
  `next_visit_time` string COMMENT '下次回访时间',
  `origin_type` string COMMENT '数据来源',

  `intention_study_type` string COMMENT '意向学习方式',
  `anticipat_signup_date` string COMMENT '预计报名时间',
  `level` string COMMENT '客户级别',
  `creator` bigint COMMENT '创建人',
  `current_creator` bigint COMMENT '当前创建人:初始==创建人,当在公海拉回时为 拉回人',
  `creator_name` string COMMENT '创建者姓名',
  `origin_channel` string COMMENT '来源渠道',
  `comment` string COMMENT '备注',
  `first_customer_clue_id` bigint COMMENT '第一条线索id',
  `last_customer_clue_id` bigint COMMENT '最后一条线索id',
  `process_state` string COMMENT '处理状态',
  `process_time` string COMMENT '处理状态变动时间',
  `payment_state` string COMMENT '支付状态',
  `payment_time` string COMMENT '支付状态变动时间',
  `signup_state` string COMMENT '报名状态',
  `signup_time` string COMMENT '报名时间',
  `notice_state` string COMMENT '通知状态',
  `notice_time` string COMMENT '通知状态变动时间',
  `lock_state` bigint COMMENT '锁定状态',
  `lock_time` string COMMENT '锁定状态修改时间',
  `itcast_clazz_id` bigint COMMENT '所属ems班级id',
  `itcast_clazz_time` string COMMENT '报班时间',
  `payment_url` string COMMENT '付款链接',
  `payment_url_time` string COMMENT '支付链接生成时间',
  `ems_student_id` bigint COMMENT 'ems的学生id',
  `delete_reason` string COMMENT '删除原因',
  `deleter` bigint COMMENT '删除人',
  `deleter_name` string COMMENT '删除人姓名',
  `delete_time` string COMMENT '删除时间',
  `course_id` bigint COMMENT '课程ID',
  `course_name` string COMMENT '课程名称',
  `delete_comment` string COMMENT '删除原因说明',
  `close_state` string COMMENT '关闭装填',
  `close_time` string COMMENT '关闭状态变动时间',
  `appeal_id` bigint COMMENT '申诉id',
  `tenant` bigint COMMENT '租户',
  `total_fee` decimal(19,0) COMMENT '报名费总金额',
  `belonged` bigint COMMENT '小周期归属人',
  `belonged_time` string COMMENT '归属时间',
  `belonger_time` string COMMENT '归属时间',
  `transfer` bigint COMMENT '转移人',
  `transfer_time` string COMMENT '转移时间',
  `follow_type` bigint COMMENT '分配类型,0-自动分配,1-手动分配,2-自动转移,3-手动单个转移,4-手动批量转移,5-公海领取',
  `transfer_bxg_oa_account` string COMMENT '转移到博学谷归属人OA账号',
  `transfer_bxg_belonger_name` string COMMENT '转移到博学谷归属人OA姓名',
   
  `itcast_school_id` bigint COMMENT '校区Id',
  `itcast_subject_id` bigint COMMENT '学科Id',
  `itcast_school_name` string      COMMENT 'ems校区名称',
  `itcast_subject_name` string      COMMENT 'ems学科名称',
  `itcast_brand` string       COMMENT 'ems品牌',
  `clazz_type_state` string       COMMENT '班级类型状态',
  `clazz_type_name` string      COMMENT '班级类型名称',
  `teaching_mode` string       COMMENT '授课模式',
  `detail` string       COMMENT '详情(比如:27期)',
  `uncertain` int    COMMENT '待定班(0:否,1:是)'
) COMMENT '意向报名课程明细宽表'
partitioned by (year string,month string ,day string)
row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress'='SNAPPY'); 



2,线索详情表(customer_relationship表、customer_clue表、customer_appeal)
edu_dwb.dwb_clue_deail


drop table edu_ods.ods_customer_relationship;
create table edu_dwb.dwb_consultation
(
  `rlp_id` bigint ,
  `rlp_customer_id` bigint COMMENT '所属客户id',
  `rlp_first_id` bigint COMMENT '第一条客户关系id',
  `rlp_belonger` bigint COMMENT '归属人',
  `rlp_belonger_name` string COMMENT '归属人姓名',
  `rlp_initial_belonger` bigint COMMENT '初始归属人',
  `rlp_distribution_handler` bigint COMMENT '分配处理人',
  `rlp_business_scrm_department_id` bigint COMMENT '归属部门',
  `rlp_last_visit_time` string COMMENT '最后回访时间',
  `rlp_next_visit_time` string COMMENT '下次回访时间',
  `rlp_origin_type` string COMMENT '数据来源',
  `rlp_itcast_school_id` bigint COMMENT '校区Id',
  `rlp_itcast_subject_id` bigint COMMENT '学科Id',
  `rlp_intention_study_type` string COMMENT '意向学习方式',
  `rlp_anticipat_signup_date` string COMMENT '预计报名时间',
  `rlp_level` string COMMENT '客户级别',
  `rlp_creator` bigint COMMENT '创建人',
  `rlp_current_creator` bigint COMMENT '当前创建人:初始==创建人,当在公海拉回时为 拉回人',
  `rlp_creator_name` string COMMENT '创建者姓名',
  `rlp_origin_channel` string COMMENT '来源渠道',
  `rlp_comment` string COMMENT '备注',
  `rlp_first_customer_clue_id` bigint COMMENT '第一条线索id',
  `rlp_last_customer_clue_id` bigint COMMENT '最后一条线索id',
  `rlp_process_state` string COMMENT '处理状态',
  `rlp_process_time` string COMMENT '处理状态变动时间',
  `rlp_payment_state` string COMMENT '支付状态',
  `rlp_payment_time` string COMMENT '支付状态变动时间',
  `rlp_signup_state` string COMMENT '报名状态',
  `rlp_signup_time` string COMMENT '报名时间',
  `rlp_notice_state` string COMMENT '通知状态',
  `rlp_notice_time` string COMMENT '通知状态变动时间',
  `rlp_lock_state` bigint COMMENT '锁定状态',
  `rlp_lock_time` string COMMENT '锁定状态修改时间',
  `rlp_itcast_clazz_id` bigint COMMENT '所属ems班级id',
  `rlp_itcast_clazz_time` string COMMENT '报班时间',
  `rlp_payment_url` string COMMENT '付款链接',
  `rlp_payment_url_time` string COMMENT '支付链接生成时间',
  `rlp_ems_student_id` bigint COMMENT 'ems的学生id',
  `rlp_delete_reason` string COMMENT '删除原因',
  `rlp_deleter` bigint COMMENT '删除人',
  `rlp_deleter_name` string COMMENT '删除人姓名',
  `rlp_delete_time` string COMMENT '删除时间',
  `rlp_course_id` bigint COMMENT '课程ID',
  `rlp_course_name` string COMMENT '课程名称',
  `rlp_delete_comment` string COMMENT '删除原因说明',
  `rlp_close_state` string COMMENT '关闭装填',
  `rlp_close_time` string COMMENT '关闭状态变动时间',
  `rlp_appeal_id` bigint COMMENT '申诉id',
  `rlp_tenant` bigint COMMENT '租户',
  `rlp_total_fee` decimal(19,0) COMMENT '报名费总金额',
  `rlp_belonged` bigint COMMENT '小周期归属人',
  `rlp_belonged_time` string COMMENT '归属时间',
  `rlp_belonger_time` string COMMENT '归属时间',
  `rlp_transfer` bigint COMMENT '转移人',
  `rlp_transfer_time` string COMMENT '转移时间',
  `rlp_follow_type` bigint COMMENT '分配类型,0-自动分配,1-手动分配,2-自动转移,3-手动单个转移,4-手动批量转移,5-公海领取',
  `rlp_transfer_bxg_oa_account` string COMMENT '转移到博学谷归属人OA账号',
  `rlp_transfer_bxg_belonger_name` string COMMENT '转移到博学谷归属人OA姓名',
  `app_customer_relationship_first_id` bigint COMMENT '第一条客户关系id',
  `app_employee_id` bigint COMMENT '申诉人',
  `app_employee_name` string COMMENT '申诉人姓名',
  `app_employee_department_id` bigint COMMENT '申诉人部门',
  `app_employee_tdepart_id` bigint COMMENT '申诉人所属部门',
  `app_appeal_status` bigint COMMENT '申诉状态,0:待稽核 1:无效 2:有效',
  `app_audit_id` bigint COMMENT '稽核人id',
  `app_audit_name` string COMMENT '稽核人姓名',
  `app_audit_department_id` bigint COMMENT '稽核人所在部门',
  `app_audit_department_name` string COMMENT '稽核人部门名称',
  `app_audit_date_time` string COMMENT '稽核时间',
  `clue_customer_id` bigint COMMENT '客户id',
  `customer_relationship_id` bigint COMMENT '客户关系id',
  `session_id` string COMMENT '七陌会话id',
  `sid` string COMMENT '访客id',
  `status` string COMMENT '状态(undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转)',
  `user` string COMMENT '所属坐席',
  `create_time` string COMMENT '七陌创建时间',
  `platform` string COMMENT '平台来源 (pc-网站咨询|wap-wap咨询|sdk-app咨询|weixin-微信咨询)',
  `s_name` string COMMENT '用户名称',
  `seo_source` string COMMENT '搜索来源',
  `seo_keywords` string COMMENT '关键字',
  `ip` string COMMENT 'IP地址',
  `referrer` string COMMENT '上级来源页面',
  `from_url` string COMMENT '会话来源页面',
  `landing_page_url` string COMMENT '访客着陆页面',
  `url_title` string COMMENT '咨询页面title',
  `to_peer` string COMMENT '所属技能组',
  `manual_time` string COMMENT '人工开始时间',
  `begin_time` string COMMENT '坐席领取时间 ',
  `reply_msg_count` bigint COMMENT '客服回复消息数',
  `total_msg_count` bigint COMMENT '消息总数',
  `msg_count` bigint COMMENT '客户发送消息数',
  `clue_comment` string COMMENT '备注',
  `finish_reason` string COMMENT '结束类型',
  `finish_user` string COMMENT '结束坐席',
  `end_time` string COMMENT '会话结束时间',
  `platform_description` string COMMENT '客户平台信息',
  `browser_name` string COMMENT '浏览器名称',
  `os_info` string COMMENT '系统名称',
  `area` string COMMENT '区域',
  `country` string COMMENT '所在国家',
  `province` string COMMENT '省',
  `city` string COMMENT '城市',
  `creator` bigint COMMENT '创建人',
  `name` string COMMENT '客户姓名',
  `idcard` string COMMENT '身份证号',
  `phone` string COMMENT '手机号',
  `itcast_school_id` bigint COMMENT '校区Id',
  `itcast_school` string COMMENT '校区',
  `itcast_subject_id` bigint COMMENT '学科Id',
  `itcast_subject` string COMMENT '学科',
  `wechat` string COMMENT '微信',
  `qq` string COMMENT 'qq号',
  `email` string COMMENT '邮箱',
  `gender` string COMMENT '性别',
  `level` string COMMENT '客户级别',
  `origin_type` string COMMENT '数据来源渠道',
  `information_way` string COMMENT '资讯方式',
  `working_years` string COMMENT '开始工作时间',
  `technical_directions` string COMMENT '技术方向',
  `customer_state` string COMMENT '当前客户状态',
  `valid` bigint COMMENT '该线索是否是网资有效线索',
  `anticipat_signup_date` string COMMENT '预计报名时间',
  `clue_state` string COMMENT '线索状态',
  `scrm_department_id` bigint COMMENT 'SCRM内部部门id',
  `superior_url` string COMMENT '诸葛获取上级页面URL',
  `superior_source` string COMMENT '诸葛获取上级页面URL标题',
  `landing_url` string COMMENT '诸葛获取着陆页面URL',
  `landing_source` string COMMENT '诸葛获取着陆页面URL来源',
  `info_url` string COMMENT '诸葛获取留咨页URL',
  `info_source` string COMMENT '诸葛获取留咨页URL标题',
  `origin_channel` string COMMENT '投放渠道',
  `course_id` bigint,
  `course_name` string ,
  `zhuge_session_id` string,
  `is_repeat` bigint COMMENT '是否重复线索(手机号维度) 0:正常 1:重复',
  `tenant` bigint COMMENT '租户id',
  `activity_id` string COMMENT '活动id',
  `activity_name` string COMMENT '活动名称',
  `follow_type` bigint COMMENT '分配类型,0-自动分配,1-手动分配,2-自动转移,3-手动单个转移,4-手动批量转移,5-公海领取',
  `shunt_mode_id` bigint COMMENT '匹配到的技能组id',
  `shunt_employee_group_id` bigint COMMENT '所属分流员工组'
)partitioned BY
(year string,month string,day string)  
row format delimited fields terminated by '\t' 
stored as orc tblproperties ('orc.compress'='snappy');


3,员工详情表()
edu_dwn.dwb_employee_detail




CREATE TABLE  edu_dwb.dwb_employee_detail(
  `id` bigint ,
  `create_date_time` string ,
  `update_date_time` string COMMENT '最后更新时间',
  `deleted` bigint COMMENT '是否被删除(禁用)',
  `customer_id` bigint COMMENT '所属客户id',
  `first_id` bigint COMMENT '第一条客户关系id',
  `belonger` bigint COMMENT '归属人',
  `belonger_name` string COMMENT '归属人姓名',
  `initial_belonger` bigint COMMENT '初始归属人',
  `distribution_handler` bigint COMMENT '分配处理人',
  `business_scrm_department_id` bigint COMMENT '归属部门',
  `last_visit_time` string COMMENT '最后回访时间',
  `next_visit_time` string COMMENT '下次回访时间',
  `origin_type` string COMMENT '数据来源',
 `itcast_school_id` bigint COMMENT '校区Id',
  `itcast_subject_id` bigint COMMENT '学科Id',
  `intention_study_type` string COMMENT '意向学习方式',
  `anticipat_signup_date` string COMMENT '预计报名时间',
  `level` string COMMENT '客户级别',
  `creator` bigint COMMENT '创建人',
  `current_creator` bigint COMMENT '当前创建人:初始==创建人,当在公海拉回时为 拉回人',
  `creator_name` string COMMENT '创建者姓名',
  `origin_channel` string COMMENT '来源渠道',
  `comment` string COMMENT '备注',
  `first_customer_clue_id` bigint COMMENT '第一条线索id',
  `last_customer_clue_id` bigint COMMENT '最后一条线索id',
  `process_state` string COMMENT '处理状态',
  `process_time` string COMMENT '处理状态变动时间',
  `payment_state` string COMMENT '支付状态',
  `payment_time` string COMMENT '支付状态变动时间',
  `signup_state` string COMMENT '报名状态',
  `signup_time` string COMMENT '报名时间',
  `notice_state` string COMMENT '通知状态',
  `notice_time` string COMMENT '通知状态变动时间',
  `lock_state` bigint COMMENT '锁定状态',
  `lock_time` string COMMENT '锁定状态修改时间',
  `itcast_clazz_id` bigint COMMENT '所属ems班级id',
  `itcast_clazz_time` string COMMENT '报班时间',
  `payment_url` string COMMENT '付款链接',
  `payment_url_time` string COMMENT '支付链接生成时间',
  `ems_student_id` bigint COMMENT 'ems的学生id',
  `delete_reason` string COMMENT '删除原因',
  `deleter` bigint COMMENT '删除人',
  `deleter_name` string COMMENT '删除人姓名',
  `delete_time` string COMMENT '删除时间',
  `course_id` bigint COMMENT '课程ID',
  `course_name` string COMMENT '课程名称',
  `delete_comment` string COMMENT '删除原因说明',
  `close_state` string COMMENT '关闭装填',
  `close_time` string COMMENT '关闭状态变动时间',
  `appeal_id` bigint COMMENT '申诉id',
  `tenant` bigint COMMENT '租户',
  `total_fee` decimal(19,0) COMMENT '报名费总金额',
  `belonged` bigint COMMENT '小周期归属人',
  `belonged_time` string COMMENT '归属时间',
  `belonger_time` string COMMENT '归属时间',
  `transfer` bigint COMMENT '转移人',
  `transfer_time` string COMMENT '转移时间',
  `follow_type` bigint COMMENT '分配类型,0-自动分配,1-手动分配,2-自动转移,3-手动单个转移,4-手动批量转移,5-公海领取',
  `transfer_bxg_oa_account` string COMMENT '转移到博学谷归属人OA账号',
  `transfer_bxg_belonger_name` string COMMENT '转移到博学谷归属人OA姓名',


 
    `employee_id`       bigint ,
    email               string comment '公司邮箱,OA登录账号',
    real_name           string comment '员工的真实姓名',
    phone               string comment '手机号,目前还没有使用;隐私问题OA接口没有提供这个属性,',
    department_id       string comment 'OA中的部门编号,有负值',
    department_name     string comment 'OA中的部门名',
    remote_login        bigint      comment '员工是否可以远程登录',
    job_number          string comment '员工工号',
    cross_school        bigint      comment '是否有跨校区权限',
    last_login_date     string comment '最后登录日期',
    scrm_department_ida  bigint  comment 'SCRM内部部门id',
    leave_office        bigint comment '离职状态',
    leave_office_time   string comment '离职时间',
    reinstated_time     string comment '复职时间',
    superior_leaders_id bigint comment '上级领导ID',
    tdepart_id          bigint comment '直属部门',
    ems_user_name       string,



  `scrm_department_id` bigint COMMENT '部门id',
  `name` string COMMENT '部门名称',
  `parent_id` bigint COMMENT '父部门id',
  `id_path` string COMMENT '编码全路径',
  `tdepart_code` bigint COMMENT '直属部门',
  `depart_level` bigint COMMENT '部门层级',
  `depart_sign` bigint COMMENT '部门标志,暂时默认1',
  `depart_line` bigint COMMENT '业务线,存储业务线编码',
  `depart_sort` bigint COMMENT '排序字段',
  `disable_flag` bigint COMMENT '禁用标志',
  dt string 

) COMMENT '员工详情宽表'
partitioned by (year string,month string ,day string)
row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress'='SNAPPY');

将dwd层数据插入dwb层

1,意向报名课程明细宽表

insert overwrite table edu_dwb.dwb_clazz_detail  partition(year,month,day)
select 
cr.id
,cr.create_date_time
,cr.update_date_time
,cr.deleted
,cr.customer_id
,cr.first_id
,cr.belonger
,cr.belonger_name
,cr.initial_belonger
,cr.distribution_handler
,cr.business_scrm_department_id
,cr.last_visit_time
,cr.next_visit_time
,cr.origin_type

,cr.intention_study_type
,cr.anticipat_signup_date
,cr.level
,cr.creator
,cr.current_creator
,cr.creator_name
,cr.origin_channel
,cr.comment
,cr.first_customer_clue_id
,cr.last_customer_clue_id
,cr.process_state
,cr.process_time
,cr.payment_state
,cr.payment_time
,cr.signup_state
,cr.signup_time
,cr.notice_state
,cr.notice_time
,cr.lock_state
,cr.lock_time
,cr.itcast_clazz_id
,cr.itcast_clazz_time
,cr.payment_url
,cr.payment_url_time
,cr.ems_student_id
,cr.delete_reason
,cr.deleter
,cr.deleter_name
,cr.delete_time
,cr.course_id
,cr.course_name
,cr.delete_comment
,cr.close_state
,cr.close_time
,cr.appeal_id
,cr.tenant
,cr.total_fee
,cr.belonged
,cr.belonged_time
,cr.belonger_time
,cr.transfer
,cr.transfer_time
,cr.follow_type
,cr.transfer_bxg_oa_account
,cr.transfer_bxg_belonger_name

,ic.itcast_school_id
,ic.itcast_subject_id
,ic.itcast_school_name
,ic.itcast_subject_name
,ic.itcast_brand
,ic.clazz_type_state
,ic.clazz_type_name
,ic.teaching_mode
,ic.detail
,ic.uncertain,
	year(cr.create_date_time) as year,
	month(cr.create_date_time) as month,
	day(cr.create_date_time) as day
	from edu_dwd.fact_customer_relationship cr left join edu_dwd.dim_itcast_clazz   ic
	on cr.id=ic.id
	

creator employee__id

2,线索详情表(customer_relationship表、customer_clue表、customer_appeal)

set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.optimize.sort.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;



insert into edu_dwb.dwb_consultation partition (year,month,day)
select 
t1.id
,cr.customer_id
,cr.first_id
,cr.belonger
,cr.belonger_name
,cr.initial_belonger
,cr.distribution_handler
,cr.business_scrm_department_id
,cr.last_visit_time
,cr.next_visit_time
,cr.origin_type
,cr.itcast_school_id
,cr.itcast_subject_id
,cr.intention_study_type
,cr.anticipat_signup_date
,cr.`level`
,cr.creator
,cr.current_creator
,cr.creator_name
,cr.origin_channel
,cr.comment
,cr.first_customer_clue_id
,cr.last_customer_clue_id
,cr.process_state
,cr.process_time
,cr.payment_state
,cr.payment_time
,cr.signup_state
,cr.signup_time
,cr.notice_state
,cr.notice_time
,cr.lock_state
,cr.lock_time
,cr.itcast_clazz_id
,cr.itcast_clazz_time
,cr.payment_url
,cr.payment_url_time
,cr.ems_student_id
,cr.delete_reason
,cr.deleter
,cr.deleter_name
,cr.delete_time
,cr.course_id
,cr.course_name
,cr.delete_comment
,cr.close_state
,cr.close_time
,cr.appeal_id
,cr.tenant
,cr.total_fee
,cr.belonged
,cr.belonged_time
,cr.belonger_time
,cr.transfer
,cr.transfer_time
,cr.follow_type
,cr.transfer_bxg_oa_account
,cr.transfer_bxg_belonger_name
,ca.customer_relationship_first_id
,ca.employee_id
,ca.employee_name
,ca.employee_department_id
,ca.employee_tdepart_id
,ca.appeal_status
,ca.audit_id
,ca.audit_name
,ca.audit_department_id
,ca.audit_department_name
,ca.audit_date_time
,cc.customer_id
,cc.customer_relationship_id
,cc.session_id
,cc.sid
,cc.status
,cc.`user`
,cc.create_time
,cc.platform
,cc.s_name
,cc.seo_source
,cc.seo_keywords
,cc.ip
,cc.referrer
,cc.from_url
,cc.landing_page_url
,cc.url_title
,cc.to_peer
,cc.manual_time
,cc.begin_time
,cc.reply_msg_count
,cc.total_msg_count
,cc.msg_count
,cc.comment
,cc.finish_reason
,cc.finish_user
,cc.end_time
,cc.platform_description
,cc.browser_name
,cc.os_info
,cc.area
,cc.country
,cc.province
,cc.city
,cc.creator
,cc.name
,cc.idcard
,cc.phone
,cc.itcast_school_id
,cc.itcast_school
,cc.itcast_subject_id
,cc.itcast_subject
,cc.wechat
,cc.qq
,cc.email
,cc.gender
,cc.`level`
,cc.origin_type
,cc.information_way
,cc.working_years
,cc.technical_directions
,cc.customer_state
,cc.valid
,cc.anticipat_signup_date
,cc.clue_state
,cc.scrm_department_id
,cc.superior_url
,cc.superior_source
,cc.landing_url
,cc.landing_source
,cc.info_url
,cc.info_source
,cc.origin_channel
,cc.course_id
,cc.course_name
,cc.zhuge_session_id
,cc.is_repeat
,cc.tenant
,cc.activity_id
,cc.activity_name
,cc.follow_type
,cc.shunt_mode_id
,cc.shunt_employee_group_id
,cc.`year`
,cc.`month`
,cc.`day`
from 
edu_dwd.dwd_customer_relationship as  cr
left join
edu_dwd.dwd_customer_clue as  cc
on cr.id = cc.customer_relationship_id 
left join 
edu_dwd.dwd_customer_appeal as  ca
on cr.id = ca.customer_relationship_first_id ;









3,员工详情表插入


	
--解决code2问题
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.optimize.sort.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;


insert overwrite table edu_dwb.dwb_employee_detail partition(year,month,day)
select 
 cr.`id` 
,cr.`create_date_time`
,cr.`update_date_time`
,cr.`deleted` 
,cr.`customer_id` 
,cr.`first_id` 
,cr.`belonger` 
,cr.`belonger_name` 
,cr.`initial_belonger` 
,cr.`distribution_handler` 
,cr.`business_scrm_department_id` 
,cr.`last_visit_time` 
,cr.`next_visit_time` 
,cr.`origin_type` 
,cr.`itcast_school_id` 
,cr.`itcast_subject_id` 
,cr.`intention_study_type` 
,cr.`anticipat_signup_date` 
,cr.`level` 
,cr.`creator`
,cr.`current_creator` 
,cr.`creator_name` 
,cr.`origin_channel` 
,cr.`comment` 
,cr.`first_customer_clue_id` 
,cr.`last_customer_clue_id`
,cr.`process_state` 
,cr.`process_time` 
,cr.`payment_state` 
,cr.`payment_time` 
,cr.`signup_state` 
,cr.`signup_time` 
,cr.`notice_state` 
,cr.`notice_time` 
,cr.`lock_state` 
,cr.`lock_time` 
,cr.`itcast_clazz_id` 
,cr.`itcast_clazz_time`
,cr.`payment_url` 
,cr.`payment_url_time`
,cr.`ems_student_id` 
,cr.`delete_reason` 
,cr.`deleter` 
,cr.`deleter_name` 
,cr.`delete_time` 
,cr.`course_id` 
,cr.`course_name` 
,cr.`delete_comment` 
,cr.`close_state` 
,cr.`close_time` 
,cr.`appeal_id`
,cr.`tenant` 
,cr.`total_fee` 
,cr.`belonged` 
,cr.`belonged_time` 
,cr.`belonger_time` 
,cr.`transfer`
,cr.`transfer_time`
,cr.`follow_type`
,cr.`transfer_bxg_oa_account` 
,cr.`transfer_bxg_belonger_name` 
,year(cr.create_date_time) as year,
	month(cr.create_date_time) as month,
	day(cr.create_date_time) as day
	
	
	
	
   ,de.`id`      
   ,de.email              
   ,de.real_name          
   ,de.phone              
   ,de.department_id      
   ,de.department_name    
   ,de.remote_login       
   ,de.job_number         
   ,de.cross_school       
   ,de.last_login_date    
   ,de.scrm_department_id
   ,de.leave_office       
   ,de.leave_office_time  
   ,de.reinstated_time    
   ,de.superior_leaders_id
   ,de.tdepart_id         
   ,de.ems_user_name      

 , dd.`id` 
 , dd.`name` 
 , dd.`parent_id` 
 , dd.`id_path` 
 , dd.`tdepart_code` 
 , dd.`depart_level` 
 , dd.`depart_sign` 
 , dd.`depart_line` 
 , dd.`depart_sort` 
 , dd.`disable_flag` 
 , dd.dt string 
	
	
	
from 
edu_dwd.fact_customer_relationship cr
left join   edu_dwd.dim_employee  de
on cr.creator= de.id
left join edu_dwd.dim_scrm_department  dd
on de.tdepart_id=dd.id;

dm层建表语句

--统计时间维度(year,month,day),年,月,天,校区id,校区name,学科id,学科name,报名人数总量(count(id)),分组类型group_type
--edu_dm.dm_apply_total建表语句
create database edu_dm;
drop table edu_dm.dm_apply_total ;
truncate table edu_dm.dm_apply_total ;

create table edu_dm.dm_apply_total(
label_date string comment '类型标志位year,month,day,学科,校区,校区学科',
`year` string comment '分组类型年',
`month` string comment '分组类型月',
`day` string comment '分组类型天',
itcast_school_id  int comment '学校id',
itcast_school_name string comment '学校名字',
itcast_subject_id int comment '学科id',
itcast_subject_name string comment '学科名字',
count_total int comment '统计总量',
group_type string  comment '分组类型',
origin_type string comment '0是线上1是线下'
);



--realationship dm层的表dm_customer_relationship
drop table edu_dm.dm_customer_relationship;
truncate table edu_dm.dm.customer_relationship;
create table edu_dm.dm_customer_relationship(
label_date string comment'分组日期标志位',
`year` string comment '分组类型年',
`month` string comment '分组类型月',
`day` string comment '分组类型天',
sum_total int comment '每天的报名人数总量',
type_other_total int comment'来源类型为other的总数',
type_phone_total int comment'来源类型为phone的总数',
type_visited_total int comment'来源类型为visited的总数',
type_netservice_total int comment'来源类型为netservice的总数',
type_school_total int comment'来源类型为school的总数',
type_online_total int comment'来源类型为online的总数',
type_local_total int comment'来源类型为local的总数',
paid_total int comment'支付类型为paid的总数'
);



--建立dm层的表edu_dm.dm_clue
drop table edu_dm.dm_clue
 
 create table edu_dm.dm_clue(
label_date string comment'分组日期标志位year,month,day',
`year` string comment '分组类型年',
`month` string comment '分组类型月',
`day` string comment '分组类型天',
sum_total int comment '每天的报名人数总量',
online_effect_baomin  int comment'线上有效线索量',
online_baomin int comment'线上报名量',
origin_type string comment'0为线上1为线下'
);




--建立dm层的表edu_dm.dm_employee
drop table edu_dm.dm_employee;
create table edu_dm.dm_employee(
label_date string comment'分组日期标志位year,month,day',
`year` string comment '分组类型年',
`month` string comment '分组类型月',
`day` string comment '分组类型天',
sum_total int comment '统计总量',
name string,
type_online_total int,
type_local_total int 
);

dwb层数据插入dm层

--统计时间维度(year,month,day),年,月,天,校区id,校区name,学科id,学科name,报名人数总量(count(id)),分组类型group_type
--edu_dm.dm_apply_total建表语句
create database edu_dm;
drop table edu_dm.dm_apply_total ;
truncate table edu_dm.dm_apply_total ;

create table edu_dm.dm_apply_total(
label_date string comment '类型标志位year,month,day,学科,校区,校区学科',
`year` string comment '分组类型年',
`month` string comment '分组类型月',
`day` string comment '分组类型天',
itcast_school_id  int comment '学校id',
itcast_school_name string comment '学校名字',
itcast_subject_id int comment '学科id',
itcast_subject_name string comment '学科名字',
count_total int comment '统计总量',
group_type string  comment '分组类型',
origin_type string comment '0是线上1是线下'
);



--realationship dm层的表dm_customer_relationship
drop table edu_dm.dm_customer_relationship;
truncate table edu_dm.dm.customer_relationship;
create table edu_dm.dm_customer_relationship(
label_date string comment'分组日期标志位',
`year` string comment '分组类型年',
`month` string comment '分组类型月',
`day` string comment '分组类型天',
sum_total int comment '每天的报名人数总量',
type_other_total int comment'来源类型为other的总数',
type_phone_total int comment'来源类型为phone的总数',
type_visited_total int comment'来源类型为visited的总数',
type_netservice_total int comment'来源类型为netservice的总数',
type_school_total int comment'来源类型为school的总数',
type_online_total int comment'来源类型为online的总数',
type_local_total int comment'来源类型为local的总数',
paid_total int comment'支付类型为paid的总数'
);



--建立dm层的表edu_dm.dm_clue
drop table edu_dm.dm_clue
 
 create table edu_dm.dm_clue(
label_date string comment'分组日期标志位year,month,day',
`year` string comment '分组类型年',
`month` string comment '分组类型月',
`day` string comment '分组类型天',
sum_total int comment '每天的报名人数总量',
online_effect_baomin  int comment'线上有效线索量',
online_baomin int comment'线上报名量',
origin_type string comment'0为线上1为线下'
);




--建立dm层的表edu_dm.dm_employee
drop table edu_dm.dm_employee;
create table edu_dm.dm_employee(
label_date string comment'分组日期标志位year,month,day',
`year` string comment '分组类型年',
`month` string comment '分组类型月',
`day` string comment '分组类型天',
sum_total int comment '统计总量',
name string,
type_online_total int,
type_local_total int 
);

rpt层建表语句

cast(cast(miniapp_order_cnt as DECIMAL(38,4)) / order_cnt * 100 as DECIMAL(5,2)) as miniapp_order_ratio,

case when origin_type = 0 then '线上'
when origin_type = 1 then  '线下'

--3.1校区分类统计数量,建表语句edu_rpt.rpt_school_baomin
create database edu_rpt;
create table edu_rpt.rpt_school_baomin(
`year` int,
`month` int,
`day` int,
count_total int,
itcast_school_name string,
origin int
);

--为了在presto插入后更改数据
drop table edu_rpt.rpt_school_baomin;
create table edu_rpt.rpt_school_baomin(
year int,
month int,
day int,
count_total int,
itcast_school_name string,
origin int
);


--3.2学科分类统计数量,建表语句edu_rpt.rpt_subject_baomin
truncate table edu_rpt.rpt_subject_baomin;
drop table edu_rpt.rpt_subject_baomin;

create table edu_rpt.rpt_subject_baomin(
`year` int,
`month` int,
`day`  int,
count_total int,
itcast_subject_name string,
origin int
);





3.2校区报名,每年,每月,每天各学科线上报名人数
select 
year,
month,
day,
 count_total ,
itcast_school_id,
itcast_school_name
 from edu_dm.dm_clazz_detail
 where group_type = '学科' and origin_type=0
order by count_total desc 
校区报名,每年,每月,每天各学科线下报名人数
select 
year,
month,
day,
 count_total ,
itcast_subject_id,
itcast_subject_name
 from edu_dm.dm_clazz_detail
 where group_type = '学科' and origin_type=1
order by count_total desc 


3.8 校区学科报名学员top排名,学科排行榜
横轴为校区,纵轴为学科,学科有值为count_total


select 
year,
month,
day,
 count_total ,
itcast_subject_id,
itcast_subject_name,
itcast_school_id,
itcast_school_name
 from edu_dm.dm_clazz_detail
 where group_type = '学科校区' and origin_type=1
order by count_total desc 


--分校区然后再分学科
rownumber()   partition by(itcast_subject_name order by (count_total))

----------------------------------------------------------------------------------------------------------------

--3.3总报名量,建表语句edu_rpt.rpt_baomin_total

create table  edu_rpt.rpt_relationship(
`year` int,
`month` int,
`day` int,
paid_total  int
);
----------------------------------------------------------------------------------------
3.4线上报名量,建表edu_rpt.rpt_online_baomin_total
drop table edu_rpt.rpt_online_baomin_toal;
create table edu_rpt.rpt_online_baomin_toal(
`year` int,
`month` int,
`day` int,
type_online_total  int  comment'线上报名总量'
);

-------------------------------------------------------------------------------------------
3.5每天的报名转化率,建表edu_rpt.rpt_baomin_rate
create table edu_rpt.rpt_baomin_rate(
`year` int,
`month` int,
`day` int,
baomin_rate string comment '报名转化率'
);

--------------------------------------------------------------------------------------------------
3.6有效线索报名转化率
drop table edu_rpt.rpt_effective_rate;
 create table edu_rpt.rpt_effective_rate(
`year` string comment '分组类型年',
`month`  string comment '分组类型月',
`day`  string comment '分组类型天',
online_baomin int,
effective_rate string comment '线上有效报名转化率'
);


----------------------------------------------------------------------------
3.7建表语句
 create table edu_rpt.rpt_day_trend(
`year` int comment '分组类型年',
`month` int comment '分组类型月',
`day` int comment '分组类型天',
type_online_total int comment'来源类型为online的总数',
type_local_total int comment'来源类型为local的总数'
);

-----------------------------------------------------------------------------------
3.8建表语句
drop table edu_rpt.rpt_subject_rop;
 create table edu_rpt.rpt_subject_top(
`year` int comment '分组类型年',
`month` int comment '分组类型月',
`day` int comment '分组类型天',
 itcast_school_name string,
itcast_subject_name string,
count_total int,
count_ranking int comment '统计排名'
);

---------------------------------------------------------------------------------------------
3.9来源渠道占比建表语句
drop table edu_rpt.rpt_origin_rate;
create table edu_rpt.rpt_origin_rate(
`year` int comment '分组类型年',
`month` int comment '分组类型月',
`day` int comment '分组类型天',

type_other_total int comment'来源类型为other的总数',
type_phone_total int comment'来源类型为phone的总数',
type_visited_total int comment'来源类型为visited的总数',
type_netservice_total int comment'来源类型为netservice的总数',
type_school_total int comment'来源类型为school的总数'
);


---------------------------------------------------------------------------
3.10建表语句

create table rpt_employee_contribute(
`year` int comment '分组类型年',
`month` int comment '分组类型月',
`day` int comment '分组类型天',
sum_total  int ,
online_rate string  comment '线上比率',
local_rate string  comment'线下比率'
);

dm层数据插入rpt层


3.1校区报名,每年,每月,每天各校区线上报名人数

insert into edu_rpt.rpt_school_baomin
select 
 cast(dm.`year`  as int),
cast(dm.`month`  as int),
cast(dm.`day` as int) ,
count_total,
itcast_school_name,
cast(origin_type  as int)
 from edu_dm.dm_apply_total  dm
 where label_date= '校区'  and itcast_school_name is not NULL 
  order by count_total desc 
---------------------上面的插入了,下面的没插入-----------------------------------------
insert overwrite table hive.edu_dm.dm_apply_total
select 
year,
month,
day,
count_total ,
itcast_school_name,
origin_type
 from edu_dm.dm_clazz_detail
 where label_date= '校区' and origin_type=0
order by count_total desc 
校区报名,每年,每月,每天各校区线下报名人数
insert overwrite table hive.edu_dm.dm_apply_total
select 
year,
month,
day,
 count_total ,
itcast_school_name
 from edu_dm.dm_clazz_detail
 where label_date = '校区' and origin_type=1
order by count_total desc 





3.2校区报名,每年,每月,每天各学科线上报名人数
怎么在bi中展示线上和线下的数据
insert into edu_rpt.rpt_subject_baomin
select 
 cast(dm.`year`  as int),
cast(dm.`month`  as int),
cast(dm.`day` as int) ,
count_total,
itcast_subject_name,
cast(origin_type  as int)
 from edu_dm.dm_apply_total  dm
 where label_date= '校区'  and itcast_subject_name is not NULL 
  order by count_total desc 


---------------------------------------------------------------------------------------------------
3.3总报名量
insert into table edu_rpt.rpt_relationship
select 
 cast(`year`  as int),
cast(`month` as int),
cast(`day` as int),
paid_total int
from
edu_dm.dm_relationship


------------------------------------------------------------------------------------------
3.4线上报名量(总报名量,线上报名量)
insert into table edu_rpt.rpt_online_baomin_toal
select 
cast(`year`  as int),
cast(`month` as int),
cast(`day` as int),
type_online_total 
from 
edu_dm.dm_customer_relationship

------------------------------------------------------------------------------------------------------
3.5插入语句edu_rpt.rpt_baomin_rate
insert into table edu_rpt.rpt_baomin_rate
select 
cast(`year`  as int),
cast(`month` as int),
cast(`day` as int),
concat(round((paid_total/sum_total)*100,2),'%')
from
edu_dm.dm_customer_relationship

------------------------------------------------------------------------------------
3.6插入语句edu_rpt.rpt_effective_rate
insert into table edu_rpt.rpt_effective_rate
select 
`year`,
`month`,
`day`,
online_baomin,
concat(round((online_effect_baomin/online_baomin)*100,2),'%')
from 
edu_dm.dm_clue

----------------------------------------------------------------------------------------
3.7日报名趋势图edu_rpt.rpt_day_trend
insert into table edu_rpt.rpt_day_trend
select 
cast(`year`  as int),
cast(`month` as int),
cast(`day` as int),
type_online_total,
type_local_total
from
edu_dm.dm_customer_relationship

--------------------------------------------------------------------------------
3.8 校区学科报名学员top排名,学科排行榜
横轴为校区,纵轴为学科,学科有值为count_total
insert into table edu_rpt.rpt_subject_top
select 
YEAR ,
month,
day,
 itcast_school_name,
itcast_subject_name,
count_total ,
DENSE_RANK() over(partition by itcast_subject_name order by count_total desc) as `count_ranking` 
 from edu_dm.dm_apply_total
 where label_date = '校区学科' and itcast_school_name is not null and itcast_subject_name is not null
order by count_total ASC  
  

----------------------------------------------------------------------------------------------------------
3.9来源渠道占比
insert into table edu_rpt.rpt_origin_rate 
select 
YEAR ,
month,
day,
type_other_total,
type_phone_total,
type_visited_total,
type_netservice_total,
type_school_total
from
edu_dm.dm_customer_relationship

------------------------------------------------------------------------------------------------
3.10咨询中心报名贡献rpt_employee_contribute
insert into table edu_rpt.rpt_employee_contribute
select 
`year` ,
`month`,
`day` ,
sum_total ,
concat(round((type_online_total/sum_total)*100,2),'%'),
concat(round((type_local_total/sum_total)*100,2),'%')
from
edu_dm.dm_employee
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

祈愿lucky

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值