东莞网站建设排行,网站建设酷万网络,布吉网站建设找哪家公司好,网站建设 上海网站文章目录 1#xff1a;工单事实指标需求分析2#xff1a;工单事实指标构建 1#xff1a;工单事实指标需求分析 目标#xff1a;掌握DWB层工单事实指标表的需求分析 路径 step1#xff1a;目标需求step2#xff1a;数据来源 实施 目标需求#xff1a;基于工单信息统计等… 文章目录 1工单事实指标需求分析2工单事实指标构建 1工单事实指标需求分析 目标掌握DWB层工单事实指标表的需求分析 路径 step1目标需求step2数据来源 实施 目标需求基于工单信息统计等待分配工单数量、完成工单数量、处理工单数量、响应时长、服务时长等指标 字段名说明数据来源wo_id工单idone_make_dwd.ciss_service_workordercallaccept_id来电受理单idone_make_dwd.ciss_service_workorderoil_station_id油站idone_make_dwd.ciss_service_workorderuserids服务该工单用户id(注意可能会有多个以逗号分隔)one_make_dwd.ciss_service_workorderwo_num工单单据数量one_make_dwd.ciss_service_workorderback_num退回工单数量如果工单没有被退回数量是0one_make_dwd.ciss_service_workorder、ciss_service_workorder_backabolished_num已作废工单数量one_make_dwd.ciss_service_workorderwait_dispatch_num待派工数量one_make_dwd.ciss_service_workorder、eos_dict_type、eos_dict_entry派工单状态待派工status4alread_complete_num已完工工单数量已完工、已回访派工单状态已完工、已回访status5 || 6processing_num正在处理工单数量待离站、待完工派工单状态待离站、待完工status3 || 4people_num工单人数数量一个工单由多人完成one_make_dwd.ciss_service_workorder、ciss_service_workorder_user默认为1数据预处理工单用户id是否为空工单用户id是否为空service_total_duration服务总时长按小时-从出发到完工时间leave_time - start_timeone_make_dwd.ciss_service_workorderrepair_service_duration报修响应时长按小时-呼叫中心受理到出发时间start_time-submit_timeone_make_dwd.ciss_service_workordercustomer_repair_num客户报修工单数量one_make_dwd.ciss_service_workorderis_customer_repairs字段charg_num收费工单数量one_make_dwd.ciss_service_workorderis_charg字段repair_device_num维修设备数量ciss_service_order、ciss_service_order_device状态维修type2install_device_num安装设备数据量ciss_service_order、ciss_service_order_device状态安装(type1)install_num安装单数量以下四个单据的数量有可能会有重叠例如一个工单有可能有巡检、也有可能有维修one_make_dwd.ciss_service_installrepair_num维修单数量ciss4.ciss_service_repairremould_num巡检单数量ciss4.ciss_service_remouldinspection_num改造单数量ciss4.ciss_service_inspectionworkorder_trvl_exp工单差旅费(通过工单id与ciss4.ciss_service_trvl_exp_dtl关联取submoney5即可)ciss4.ciss_service_trvl_exp_dtl 数据来源 ciss_service_workorder工单详情事实表 selectid,--工单idcallaccept_id,--来电受理idoil_station_id, --油站idservice_userid,--工程师idstatus,--工单状态submit_time,--提交时间start_time,--开始时间leave_time,--离开时间is_customer_repairs,--是否为报修工单is_charg --是否为收费工单
from ciss_service_workorder;ciss_service_workorder_back回退工单信息表 select id, --回退idworkorder_id --工单id
from ciss_service_workorder_back;ciss_service_workorder_user工程师信息表 selectworkorder_id, --工单iduserid, --工程师idusername --工程师姓名
from ciss_service_workorder_user; ciss_service_trvl_exp_dtl差旅费用信息表 selectwork_order_id, --工单idsubmoney5 --应收会计扣款金额
from ciss_service_trvl_exp_dtl;ciss_service_order服务单信息表 selectid, --服务单idworkorder_id, --工单idtype --工单类型,1-安装2-维修3-巡检
from ciss_service_order;ciss_service_order_device服务单设备信息表 selectid, --设备idservice_order_id --服务单id
from ciss_service_order_device;工单类型合并表 ciss_service_install设备安装信息表 服务单id、安装工单id ciss_service_repair设备维修信息表 服务单id、维修工单id ciss_service_remould设备改造信息表 - 服务单id、改造工单idciss_service_inspection设备巡检信息表 服务单id、巡检工单id selectso.id, --服务单idso.workorder_id, --工单idinstall.id installid, --安装单idrepair.id repairid, --维修单idremould.id remouldid, --改造单idinspection.id inspectionid --巡检单id--服务单信息表
from one_make_dwd.ciss_service_order so
left join one_make_dwd.ciss_service_install install on so.id install.service_id
left join one_make_dwd.ciss_service_repair repair on so.id repair.service_idleft join one_make_dwd.ciss_service_remould remould on so.id remould.service_idleft join one_make_dwd.ciss_service_inspection inspection on so.id inspection.service_idwhere so.dt 20210101;小结 掌握DWB层呼叫中心事实指标表的需求分析
2工单事实指标构建 目标实现DWB层工单事实指标表的构建 实施 建表 drop table if exists one_make_dwb.fact_worker_order;
create table if not exists one_make_dwb.fact_worker_order(wo_id string comment 工单id, callaccept_id string comment 来电受理单id, oil_station_id string comment 油站id, userids string comment 服务该工单用户id(注意可能会有多个以逗号分隔), wo_num bigint comment 工单单据数量, back_num bigint comment 退回工单数量默认为0, abolished_num bigint comment 已作废工单数量, wait_dispatch_num bigint comment 待派工数量, wait_departure_num bigint comment 待出发数量, alread_complete_num bigint comment 已完工工单数量已完工、已回访, processing_num bigint comment 正在处理工单数量待离站、待完工, people_num int comment 工单人数数量一个工单由多人完成, service_total_duration int comment 服务总时长按小时(leave_time - start_time), repair_service_duration int comment 报修响应时长按小时(start_time-submit_time), customer_repair_num bigint comment 客户报修工单数量, charg_num bigint comment 收费工单数量, repair_device_num bigint comment 维修设备数量, install_device_num bigint comment 安装设备数据量, install_num bigint comment 安装单数量, repair_num bigint comment 维修单数量, remould_num bigint comment 改造单数量, inspection_num bigint comment 巡检单数量, workorder_trvl_exp decimal(20,1) comment 工单差旅费
)
partitioned by (dt string)
stored as orc
location /data/dw/dwb/one_make/fact_worker_order
;抽取 insert overwrite table one_make_dwb.fact_worker_order partition(dt 20210101)
select--工单idwo.id wo_id--来电受理单id, max(callaccept_id) callaccept_id--油站id, max(oil_station_id) oil_station_id--工程师id, max(case when wo.service_userids is not null then concat_ws(,, wo.service_userid, wo.service_userids) else wo.service_userid end) userids--工单单据数量安装单、维修单……, count(wo.id) wo_num--退回工单数量, count(wob.id) back_num--已作废工单数量, sum(case when status -1 then 1 else 0 end) abolished_num--待派发工单数量, sum(case when status 4 then 1 else 0 end) wait_dispatch_num--待出发工单数量, sum(case when status 2 then 1 else 0 end) wait_departure_num--已完工工单数量, sum(case when status 5 then 1 when status 6 then 1 else 0 end) alread_complete_num--处理中工单数量, sum(case when status 3 then 1 when status 4 then 1 else 0 end) processing_num--工单人数, case when count(usr.id) 0 then 1 else count(usr.id) end people_num--服务总时长, max((wo.leave_time - wo.start_time) / 3600000) service_total_duration--报修响应时长, max((wo.start_time - wo.submit_time) / 3600000) repair_service_duration--客户报修工单数量, sum(case when wo.is_customer_repairs 2 then 1 else 0 end) customer_repairs--收费工单数量, sum(case when wo.is_charg 1 then 1 else 0 end) charg_num--维修设备数量, max(case when sod.repair_device_num 0 then 1 when sod.repair_device_num is null then 0 else sod.repair_device_num end) repair_device_num--安装设备数量, max(case when sod2.install_device_num 0 then 1 when sod2.install_device_num is null then 0 else sod2.install_device_num end) install_device_num--安装单数量, sum(case when sertype.installid is not null then 1 else 0 end) install_num--维修单数量, sum(case when sertype.repairid is not null then 1 else 0 end) repair_num--改造单数量, sum(case when sertype.remouldid is not null then 1 else 0 end) remould_num--巡检单数量, sum(case when sertype.inspectionid is not null then 1 else 0 end) inspection_num--工单差旅费, max(case when ed.submoney5 is null then 0.0 else ed.submoney5 end) workorder_trvl_exp
-- 工单信息表
from one_make_dwd.ciss_service_workorder wo--关联回退工单回退工单个数left join one_make_dwd.ciss_service_workorder_back wob on wo.id wob.workorder_id--关联工程师信息表工程师人数left join one_make_dwd.ciss_service_workorder_user usr on wo.id usr.workorder_id--关联差旅费用信息表工单差旅费用left join one_make_dwd.ciss_service_trvl_exp_dtl ed on wo.id ed.work_order_id--关联维修设备个数信息left join (--统计每个工单的维修设备个数selectso.workorder_id, count(sod.id) repair_device_num--服务单表关联设备表每个工单对应的设备idfrom one_make_dwd.ciss_service_order soleft join one_make_dwd.ciss_service_order_device sodon so.id sod.service_order_idwhere so.type 2 and so.dt20210101group by so.workorder_id) sod on wo.id sod.workorder_id--关联安装设备个数信息left join (--统计每个工单的安装设备个数selectso.workorder_id, count(sod.id) install_device_numfrom one_make_dwd.ciss_service_order soleft join one_make_dwd.ciss_service_order_device sodon so.id sod.service_order_id--过滤服务单的类型为安装类型的服务单where so.type 1 and so.dt20210101group by so.workorder_id) sod2 on wo.id sod2.workorder_id--工单类型合并表安装、维修、改造、巡检单idleft join (selectso.id, so.workorder_id, install.id installid, repair.id repairid, remould.id remouldid, inspection.id inspectionidfrom one_make_dwd.ciss_service_order soleft join one_make_dwd.ciss_service_install install on so.id install.service_idleft join one_make_dwd.ciss_service_repair repair on so.id repair.service_idleft join one_make_dwd.ciss_service_remould remould on so.id remould.service_idleft join one_make_dwd.ciss_service_inspection inspection on so.id inspection.service_idwhere so.dt 20210101) sertype on wo.id sertype.workorder_id
where wo.dt20210101
group by wo.id
;小结 实现DWB层呼叫中心事实指标表的构建
