目标:掌握DWB层客户回访事实指标表的需求分析
路径
实施
目标需求:基于客户回访数据统计工单满意数量、不满意数量、返修数量等指标

数据来源
ciss_service_return_visit:回访信息表
select
id,--回访id
code,--回访编号
workorder_id,--工单id
create_userid, --回访人员id
service_attitude,--服务态度
response_speed,--响应速度
repair_level,--服务维修水平
is_repair --是否返修
from ciss_service_return_visit;
ciss_service_workorder:服务工单信息表
select
id,--工单id
service_userid,--工程师id
service_station_id,--服务站点id
oil_station_id --油站id
from ciss_service_workorder;
小结
目标:实现DWB层客户回访事实指标表的构建
实施
建表
-- 创建客户回访实时表
drop table if exists one_make_dwb.fact_srv_rtn_visit;
create table if not exists one_make_dwb.fact_srv_rtn_visit(
vst_id string comment '回访id'
, vst_code string comment '回访编号'
, wrkodr_id string comment '工单id'
, srv_user_id string comment '服务人员用户id'
, os_id string comment '油站id'
, ss_id string comment '服务网点id'
, vst_user_id string comment '回访人员id'
, satisfied_num int comment '满意数量'
, unsatisfied_num int comment '不满意数量'
, srv_atu_num int comment '服务态度满意数量'
, srv_bad_atu_num int comment '服务态度不满意数量'
, srv_rpr_prof_num int comment '服务维修水平满意数量'
, srv_rpr_unprof_num int comment '服务维修水平不满意数量'
, srv_high_res_num int comment '服务响应速度满意数量'
, srv_low_res_num int comment '服务响应速度不满意数量'
, rtn_rpr_num int comment '返修数量'
) comment '客户回访事实表'
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_srv_rtn_visit';
抽取
insert overwrite table one_make_dwb.fact_srv_rtn_visit partition(dt = '20210101')
select
visit.id vst_id --回访id
, visit.code vst_code --回访编号
, visit.workorder_id wrkodr_id --工单id
, swo.service_userid srv_user_id --工程师id
, swo.oil_station_id os_id --油站id
, swo.service_station_id ss_id --服务网点id
, visit.create_userid vst_user_id --回访人员id
, satisfied_num --满意数量
, unsatisfied_num --不满意数量
, srv_atu_num --服务态度满意数量
, srv_bad_atu_num --服务态度不满意数量
, srv_rpr_prof_num --服务水平满意数量
, srv_rpr_unprof_num --服务水平不满意数量
, srv_high_res_num --服务响应速度满意数量
, srv_low_res_num --服务响应速度不满意数量
, rtn_rpr_num --返修数量
--回访信息表
from one_make_dwd.ciss_service_return_visit visit
--工单信息表
left join one_make_dwd.ciss_service_workorder swo on visit.workorder_id = swo.id
--获取满意与不满意个数
left join (
select visit.workorder_id,
sum(case when visit.service_attitude = 1 and visit.response_speed = 1 and visit.repair_level = 1 then 1 else 0 end) satisfied_num,
sum(case when visit.service_attitude = 0 then 1 when visit.response_speed = 0 then 1 when visit.repair_level = 0 then 1 when visit.yawp_problem_type = 0 then 1 else 0 end) unsatisfied_num,
sum(case when visit.service_attitude = 1 then 1 else 0 end) srv_atu_num,
sum(case when visit.service_attitude = 0 then 1 else 0 end) srv_bad_atu_num,
sum(case when visit.repair_level = 1 then 1 else 0 end) srv_rpr_prof_num,
sum(case when visit.repair_level = 0 then 1 else 0 end) srv_rpr_unprof_num,
sum(case when visit.response_speed = 1 then 1 else 0 end) srv_high_res_num,
sum(case when visit.response_speed = 0 then 1 else 0 end) srv_low_res_num,
sum(case when visit.is_repair = 1 then 1 else 0 end) rtn_rpr_num
from one_make_dwd.ciss_service_return_visit visit
left join one_make_dwd.ciss_service_workorder swo on visit.workorder_id = swo.id
where visit.dt = '20210101'
group by visit.workorder_id
) vstswo on visit.workorder_id = vstswo.workorder_id
where visit.dt = '20210101'
;
小结