助力工业物联网，工业大数据之工单事实指标需求分析【二十】

1：工单事实指标需求分析

目标：掌握DWB层工单事实指标表的需求分析

路径

• step1：目标需求
• step2：数据来源

实施

目标需求：基于工单信息统计等待分配工单数量、完成工单数量、处理工单数量、响应时长、服务时长等指标

字段名	说明	数据来源
wo_id	工单id	one_make_dwd.ciss_service_workorder
callaccept_id	来电受理单id	one_make_dwd.ciss_service_workorder
oil_station_id	油站id	one_make_dwd.ciss_service_workorder
userids	服务该工单用户id(注意：可能会有多个，以逗号分隔)	one_make_dwd.ciss_service_workorder
wo_num	工单单据数量	one_make_dwd.ciss_service_workorder
back_num	退回工单数量（如果工单没有被退回，数量是0）	one_make_dwd.ciss_service_workorder、ciss_service_workorder_back
abolished_num	已作废工单数量	one_make_dwd.ciss_service_workorder
wait_dispatch_num	待派工数量	one_make_dwd.ciss_service_workorder、eos_dict_type、eos_dict_entry派工单状态：待派工（status=4）
alread_complete_num	已完工工单数量（已完工、已回访）	派工单状态：已完工、已回访（status=5 || 6）
processing_num	正在处理工单数量（待离站、待完工）	派工单状态：待离站、待完工（status=3 || 4）
people_num	工单人数数量（一个工单由多人完成）	one_make_dwd.ciss_service_workorder、ciss_service_workorder_user默认为1数据预处理；工单用户id是否为空；工单用户id是否为空
service_total_duration	服务总时长（按小时）->从出发到完工时间（leave_time - start_time）	one_make_dwd.ciss_service_workorder
repair_service_duration	报修响应时长（按小时）->呼叫中心受理到出发时间（start_time-submit_time）	one_make_dwd.ciss_service_workorder
customer_repair_num	客户报修工单数量	one_make_dwd.ciss_service_workorder；is_customer_repairs字段
charg_num	收费工单数量	one_make_dwd.ciss_service_workorder；is_charg字段
repair_device_num	维修设备数量	ciss_service_order、ciss_service_order_device；状态：维修（type=2）
install_device_num	安装设备数据量	ciss_service_order、ciss_service_order_device；状态：安装(type=1)
install_num	安装单数量（以下四个单据的数量有可能会有重叠，例如：一个工单有可能有巡检、也有可能有维修）	one_make_dwd.ciss_service_install
repair_num	维修单数量	ciss4.ciss_service_repair
remould_num	巡检单数量	ciss4.ciss_service_remould
inspection_num	改造单数量	ciss4.ciss_service_inspection
workorder_trvl_exp	工单差旅费(通过工单id与ciss4.ciss_service_trvl_exp_dtl关联，取submoney5即可)	ciss4.ciss_service_trvl_exp_dtl

数据来源

ciss_service_workorder：工单详情事实表

代码语言：javascript复制

select
    id,--工单id
    callaccept_id,--来电受理id
    oil_station_id, --油站id
    service_userid,--工程师id
    status,--工单状态
    submit_time,--提交时间
    start_time,--开始时间
    leave_time,--离开时间
    is_customer_repairs,--是否为报修工单
    is_charg --是否为收费工单
from ciss_service_workorder;

ciss_service_workorder_back：回退工单信息表

代码语言：javascript复制

select 
    id,              --回退id
    workorder_id     --工单id
from ciss_service_workorder_back;

ciss_service_workorder_user：工程师信息表

代码语言：javascript复制

select
    workorder_id,  --工单id
    userid,        --工程师id
    username       --工程师姓名
from ciss_service_workorder_user; 

ciss_service_trvl_exp_dtl：差旅费用信息表

代码语言：javascript复制

select
    work_order_id, --工单id
    submoney5 --应收会计扣款金额
from ciss_service_trvl_exp_dtl;

ciss_service_order：服务单信息表

代码语言：javascript复制

select
  id,            --服务单id
  workorder_id,  --工单id
  type           --工单类型,1-安装，2-维修，3-巡检
from ciss_service_order;

ciss_service_order_device：服务单设备信息表

代码语言：javascript复制

select
    id,               --设备id
    service_order_id  --服务单id
from ciss_service_order_device;

工单类型合并表

ciss_service_install：设备安装信息表

• 服务单id、安装工单id

ciss_service_repair：设备维修信息表

服务单id、维修工单id

ciss_service_remould：设备改造信息表

代码语言：javascript复制

- 服务单id、改造工单id

ciss_service_inspection：设备巡检信息表

服务单id、巡检工单id

代码语言：javascript复制

select
    so.id,                     --服务单id
    so.workorder_id,           --工单id
    install.id installid,      --安装单id
    repair.id repairid,        --维修单id
    remould.id remouldid,      --改造单id
    inspection.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_id
  left join one_make_dwd.ciss_service_remould remould on so.id = remould.service_id
  left join one_make_dwd.ciss_service_inspection inspection on so.id = inspection.service_id
  where so.dt = '20210101';

小结

• 掌握DWB层呼叫中心事实指标表的需求分析

2：工单事实指标构建

目标：实现DWB层工单事实指标表的构建

实施

建表

代码语言：javascript复制

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'
;

抽取

代码语言：javascript复制

insert overwrite table one_make_dwb.fact_worker_order partition(dt = '20210101')
select
    --工单id
    wo.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 (
			--统计每个工单的维修设备个数
			select
			    so.workorder_id, count(sod.id) repair_device_num
			--服务单表关联设备表：每个工单对应的设备id
			from one_make_dwd.ciss_service_order so
			left join one_make_dwd.ciss_service_order_device sod
			on so.id = sod.service_order_id
			where so.type = '2' and so.dt='20210101'
			group by so.workorder_id
    ) sod on wo.id = sod.workorder_id
	--关联安装设备个数信息
    left join (
	      --统计每个工单的安装设备个数
          select
		      so.workorder_id, count(sod.id) install_device_num
		  from one_make_dwd.ciss_service_order so
		  left join one_make_dwd.ciss_service_order_device sod
		  on so.id = sod.service_order_id
          --过滤服务单的类型为安装类型的服务单
		  where so.type = '1' and so.dt='20210101'
		  group by so.workorder_id
	) sod2 on wo.id = sod2.workorder_id
	--工单类型合并表：安装、维修、改造、巡检单id
    left join (
          select
		      so.id, so.workorder_id, install.id installid, repair.id repairid, remould.id remouldid, inspection.id inspectionid
		  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_id
          left join one_make_dwd.ciss_service_remould remould on so.id = remould.service_id
          left join one_make_dwd.ciss_service_inspection inspection on so.id = inspection.service_id
          where so.dt = '20210101'
    ) sertype on wo.id = sertype.workorder_id
where wo.dt='20210101'
group by wo.id
;

小结

• 实现DWB层呼叫中心事实指标表的构建

大数据 物联网 service 服务 需求分析

0 人点赞