一站制造工业项目之DWB介绍及代码运用

DWB:存储每个事实主题需要的事务事实数据以及轻度聚合的结果，供ST层基于DWS层进行
统计聚合得到最终每个主题的指标

掌握一站制造中的业务主题划分及主题指标的设计

基本流程
油站：有安装、维修、巡检、改造需求
呼叫：打电话到呼叫中心下工单
呼叫中心可以直接解决：不会有新的工单产生：咨询类寻求
呼叫中心不能直接解决：构建工单
网点：呼叫中心将工单分派给网点
工单：分配工程师确认工单
油站：实施具体的工单需求
仓储物料：申请仓库调度零配件，有零配件费用
差旅费用：交通费用、住宿费用、加油费用、补贴费用
回访：呼叫中心将完成的订单进行电话回访
呼叫中心事实指标：来电受理次数、分派工单次数
油站事实指标：油站个数、停用油站个数、油站设备个数
工单事实指标：安装工单个数、维修工单个数
安装事实指标：安装个数、安装费用
维修事实指标：维度个数、维修费用
客户回访事实指标：满意个数、不满意个数、态度满意个数、响应速度的满意个数、技术满意
个数
费用事实指标：报销费用、差率费用、补贴费用
差旅事实指标：油费、住宿费用、交通费用
网点物料事实指标：零配件的个数
……

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

建库

create database if not exists one_make_dwb; 

建表

-- 创建呼叫中心 | 来电受理事实表
drop table if exists one_make_dwb.fact_call_service;
create table if not exists one_make_dwb.fact_call_service(
 id string comment '受理id（唯一标识）'
 , code string comment '受理单唯一编码'
 , call_date string comment '来电日期（日期id）'
 , call_hour int comment '来电时间（小时）（事实维度）'
 , call_type_id string comment '来电类型（事实维度）'
 , call_type_name string comment '来电类型名称（事实维度）'
 , process_way_id string comment '受理方式（事实维度）'
 , process_way_name string comment '受理方式（事实维度）'
 , oil_station_id string comment '油站id'
 , userid string comment '受理人员id'
 , cnt int comment '单据数量（指标列）'
 , dispatch_cnt int comment '派工数量'
 , cancellation_cnt int comment '派工单作废数量'
 , chargeback_cnt int comment '派工单退单数量'
 , interval int comment '受理时长（单位：秒）'
 , tel_spt_cnt int comment '电话支持数量'
 , on_site_spt_cnt int comment '现场安装、维修、改造、巡检数量'
 , custm_visit_cnt int comment '回访单据数量'
 , complain_cnt int comment '投诉单据数量'
 , other_cnt int
comment '其他业务单据数量')
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_call_service';

抽取

insert overwrite table one_make_dwb.fact_call_service partition (dt
= '20210101')
select
 call.id   --来电受理唯一id
 , call.code -- 受理单唯一编码
 , date_format(timestamp(call.call_time), 'yyyymmdd') as
call_date -- 来电日期（日期id）
 , hour(timestamp(call.call_time))  -- 来电时间（小时）（事实维度）
 , call.call_type -- 来电类型（事实维度）
 , call_dict.dictname -- 来电类型名称（事实维度）
 , call.process_way -- 受理方式（事实维度）
 , process_dict.dictname -- 受理方式（事实维度）
 , call.call_oilstation_id -- 油站id
 , call.accept_userid -- 受理人员id
 , 1 -- 单据数量（指标列）
 , case when call.process_way = 5 then 1 else 0 end -- 派工数量：
0-自己处理，1-产生派工
 , case when workorder.status = -1 then 1 else 0 end -- 派工单作废
数量
 , case when workorder.status = -2 then 1 else 0 end -- 派工单退单
数量

, floor(to_unix_timestamp(timestamp(call.process_time),'yyyy-
mm-dd hh:mm:ss') - to_unix_timestamp(timestamp(call.call_time),
'yyyy-mm-dd hh:mm:ss') / 1000.0) -- 受理时长（单位：秒）
 , case when call.call_type = 5 then 1 else 0 end -- 电话支持数量
 , case when call.call_type in (1, 2, 3, 4) then 1 else 0 end --
现场安装、维修、改造、巡检数量
 , case when call.call_type = 7 then 1 else 0 end -- 回访单据数量
 , case when call.call_type = 8 then 1 else 0 end -- 投诉单据数量
 , case when call.call_type = 9 or call.call_type = 6 then 1
else 0 end -- 其他业务单据数量
-- 来电详情表
from one_make_dwd.ciss_service_callaccept call
-- 字典信息表：得到来电类型名称
left join one_make_dwb.tmp_dict call_dict on call.call_type =
call_dict.dictid  and call_dict.dicttypename = '来电类型'
-- 字典信息表：受理方式名称
left join one_make_dwb.tmp_dict process_dict on call.process_way =
process_dict.dictid and process_dict.dicttypename = '来电受理单--处理
方式'
-- 工单信息表：得到工单状态：-2:退单，-1:作废
left join one_make_dwd.ciss_service_workorder workorder on
workorder.dt = '20210101' and workorder.callaccept_id = call.id
where call.dt = '20210101' and call.code != 'null' and
call.call_time is not null;