13：服务域：派单主题分析实现
目标：掌握派单主题的需求分析实现
路径
- step1：需求
- step2：分析
实施
需求：统计不同维度下的派单主题指标的结果
字段名称 | 字段说明 | 来源 |
|---|---|---|
install_sumnum | 安装单数量 | one_make_dwb.fact_worker_order |
repair_sumnum | 维修单数量 | one_make_dwb.fact_worker_order |
remould_sumnum | 巡检单数量 | one_make_dwb.fact_worker_order |
inspection_sumnum | 改造单数量 | one_make_dwb.fact_worker_order |
max_wo_num | 派单数最大值 | one_make_dwb.fact_worker_order |
min_wo_num | 派单数最小值 | one_make_dwb.fact_worker_order |
avg_wo_num | 派单数平均值 | one_make_dwb.fact_worker_order |
call_srv_user | 呼叫中心派单人 | one_make_dwb.fact_call_service |
max_dispatch_cnt | 呼叫中心最大派单 | one_make_dwb.fact_call_service |
min_dispatch_cnt | 呼叫中心最小派单 | one_make_dwb.fact_call_service |
avg_dispatch_cnt | 呼叫中心平均派单 | one_make_dwb.fact_call_service |
people_wo_num | 派单平均值 | one_make_dwb.fact_worker_order |
srv_reps_duration | 派单响应时长 | one_make_dwb.fact_worker_order |
srv_duration | 服务时长 | one_make_dwb.fact_worker_order |
pepople_sumnum | 工单人数 | one_make_dwb.fact_worker_order |
dws_day string | 日期维度-按天 | one_make_dws.dim_date |
dws_week string | 日期维度-按周 | one_make_dws.dim_date |
dws_month string | 日期维度-按月 | one_make_dws.dim_date |
orgname | 组织机构-回访人员所属部门 | one_make_dws.dim_emporg |
posiname | 组织机构-回访人员所属岗位 | one_make_dws.dim_emporg |
posiname | 组织机构-回访人员名称 | one_make_dws.dim_emporg |
oil_type string | 油站类型 | one_make_dws.dim_oilstation |
oil_province | 油站所属省 | one_make_dws.dim_oilstation |
oil_city string | 油站所属市 | one_make_dws.dim_oilstation |
oil_county string | 油站所属区 | one_make_dws.dim_oilstation |
customer_classify | 客户类型 | one_make_dws.dim_oilstation |
customer_province | 客户所属省 | one_make_dws.dim_oilstation |
分析
指标
- 安装单数量、维修单数量、改造单数量、巡检单数量
- 最大派单数、最小派单数、平均派单数
- 工单主题事实表
- 呼叫中心派单人、呼叫中心最大派单、呼叫中心最小派单、呼叫中心平均派单
- 呼叫中心
- 派单平均值、派单响应时长、服务时长、工单人数
维度
- 日期维度：天、周、月
- 组织机构维度：人员部门、人员岗位、人员姓名
- 油站维度：类型、省份、城市、区域
- 客户维度：类型、省份
数据
事实表
fact_call_service：呼叫中心事务事实表
代码语言：javascript复制select
userid,--受理人员id
dispatch_cnt,--派工数量
id,--呼叫受理id
oil_station_id, --油站id
dt --日期
from fact_call_service;fact_worker_order：工单事务事实表
代码语言：javascript复制select
callaccept_id,--呼叫受理id
install_num,--安装数量
repair_num,--维修数量
remould_num,--改造数量
inspection_num,--巡检数量
wo_num, --工单数量
people_num, --工单人数
repair_service_duration,--报修响应时长
service_total_duration --服务总时长
from fact_worker_order;
维度表
dim_oilstation：油站维度表
代码语言：javascript复制select
id,--油站id
company_name,--公司名称
province_name,--省份名称
city_name,--城市名称
county_name,--区域名称
customer_classify_name,--客户名称
customer_province_name--客户省份
from dim_oilstation;dim_date：时间维度表
代码语言：javascript复制select
date_id,--天
week_in_year_id,--周
year_month_id --月
from dim_date;dim_emporg：组织机构维度
代码语言：javascript复制select
empid,--人员id
orgname,--部门名称
posiname,--岗位名称
empname --员工名称
from dim_emporg;实现
建表
代码语言：javascript复制drop table if exists one_make_st.subj_dispatch;
create table if not exists one_make_st.subj_dispatch(
install_sumnum int comment '安装单数量'
,repair_sumnum int comment '维修单数量'
,remould_sumnum int comment '改造单数量'
,inspection_sumnum int comment '巡检单数量'
,max_wo_num int comment '派单数最大值'
,min_wo_num int comment '派单数最小值'
,avg_wo_num decimal(20, 1) comment '派单数平均值'
,call_srv_user int comment '呼叫中心派单人'
,max_dispatch_cnt int comment '呼叫中心最大派单'
,min_dispatch_cnt int comment '呼叫中心最小派单'
,avg_dispatch_cnt decimal(20, 1) comment '呼叫中心平均派单'
,people_wo_num decimal(20, 1) comment '派单平均值'
,srv_reps_duration int comment '派单响应时长'
,srv_duration int comment '服务时长'
,pepople_sumnum int comment '工单人数'
,dws_day string comment '日期维度-按天'
,dws_week string comment '日期维度-按周'
,dws_month string comment '日期维度-按月'
,orgname string comment '组织机构维度-回访人员所属部门'
,posiname string comment '组织机构维度-回访人员所属岗位'
,empname string comment '组织机构维度-回访人员名称'
,oil_type string comment '油站维度-油站类型'
,oil_province string comment '油站维度-油站所属省'
,oil_city string comment '油站维度-油站所属市'
,oil_county string comment '油站维度-油站所属区'
,customer_classify string comment '客户维度-客户类型'
,customer_province string comment '客户维度-客户所属省'
) comment '派单主题表'
partitioned by (month String, week String, day String)
stored as orc
location '/data/dw/st/one_make/subj_dispatch'
;构建
代码语言：javascript复制insert overwrite table one_make_st.subj_dispatch partition(month = '202101', week='2021W1', day='20210101')
select
sum(fwo.install_num) install_sumnum, --安装单数量
sum(fwo.repair_num) repair_sumnum, --维修单数量
sum(fwo.remould_num) remould_sumnum, --改造单数量
sum(fwo.inspection_num) inspection_sumnum, --巡检单数量
max(fwo.wo_num) max_wo_num, --最大派单数
min(fwo.wo_num) min_wo_num, --最小派单数
avg(fwo.wo_num) avg_wo_num, --平均派单数
sum(fcs.userid) call_srv_user, --呼叫中心派单人
max(fcs.dispatch_cnt) max_dispatch_cnt, --呼叫中心最大派单
min(fcs.dispatch_cnt) min_dispatch_cnt, --呼叫中心最小派单
avg(fcs.dispatch_cnt) avg_dispatch_cnt, --呼叫中心平均派单
sum(fwo.wo_num) / sum(fwo.people_num) people_wo_num, --派单平均值
sum(fwo.repair_service_duration) srv_reps_duration, --派单响应时长
sum(fwo.service_total_duration) srv_duration, --服务时长
sum(fwo.people_num) pepople_sumnum, --工单人数
dd.date_id dws_day, --日期日
dd.week_in_year_id dws_week, --日期周
dd.year_month_id dws_month, --日期月
emp.orgname, --组织机构人员部门
emp.posiname, --组织机构人员岗位
emp.empname, --组织机构人员名称
dimoil.company_name oil_type, --油站类型
dimoil.province_name oil_province, --油站省份
dimoil.city_name oil_city, --油站城市
dimoil.county_name oil_county, --油站区域
dimoil.customer_classify_name customer_classify, --客户类型
dimoil.customer_province_name customer_province --客户省份
--呼叫中心事务事实表
from one_make_dwb.fact_call_service fcs
--关联工单事实表
left join one_make_dwb.fact_worker_order fwo on fcs.id = fwo.callaccept_id
--关联组织机构维度表
left join one_make_dws.dim_emporg emp on fcs.userid = emp.empid
--关联日期维度表
left join one_make_dws.dim_date dd on fcs.dt = dd.date_id
--关联油站维度表
left join one_make_dws.dim_oilstation dimoil on fcs.oil_station_id = dimoil.id
where dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101'
group by dd.date_id, dd.week_in_year_id, dd.year_month_id, emp.orgname, emp.posiname, emp.empname, dimoil.company_name, dimoil.province_name,
dimoil.city_name, dimoil.county_name, dimoil.customer_classify_name, dimoil.customer_province_name
;小结
- 掌握派单主题的需求分析及实现
14：服务域：费用主题分析
目标：掌握费用主题的需求分析
路径
- step1：需求
- step2：分析
实施
需求:统计不同维度下的费用主题指标的结果
字段名称 | 字段说明 | 来源 |
|---|---|---|
install_money | 安装费用 | one_make_dwb.fact_srv_install |
max_install_money | 最大安装费用 | one_make_dwb.fact_srv_install |
min_install_money | 最小安装费用 | one_make_dwb.fact_srv_install |
avg_install_money | 平均安装费用 | one_make_dwb.fact_srv_install |
sumbiz_trip_money | 外出差旅费用金额总计 | one_make_dwb.fact_trvl_exp |
sumin_city_traffic_money | 市内交通费用金额总计 | one_make_dwb.fact_trvl_exp |
sumhotel_money | 住宿费费用金额总计 | one_make_dwb.fact_trvl_exp |
sumfars_money | 车船费用金额总计 | one_make_dwb.fact_trvl_exp |
sumsubsidy_money | 补助费用金额总计 | one_make_dwb.fact_trvl_exp |
sumroad_toll_money | 过桥过路费用金额总计 | one_make_dwb.fact_trvl_exp |
sumoil_money | 油费金额总计 | one_make_dwb.fact_trvl_exp |
exp_item_total | 差旅费用扣款明细总计 | one_make_dwb.fact_regular_exp |
actual_total_money | 差旅费用总额统计 | one_make_dwb.fact_trvl_exp |
sum_secondary_money | 差旅费用二阶段扣款总计 | one_make_dwb.fact_trvl_exp |
sum_third_money | 差旅费用三阶段扣款总计 | one_make_dwb.fact_trvl_exp |
max_secondary_money | 差旅费用二阶段最大扣款总计 | one_make_dwb.fact_trvl_exp |
max_third_money | 差旅费用三阶段最大扣款总计 | one_make_dwb.fact_trvl_exp |
sum_srv_user | 报销人员总数量 | one_make_dwb.fact_trvl_exp |
max_srv_user | 报销人员最大数量 | one_make_dwb.fact_trvl_exp |
min_srv_user | 报销人员最小数量 | one_make_dwb.fact_trvl_exp |
avg_srv_user | 报销人员平均数量 | one_make_dwb.fact_trvl_exp |
dws_day string | 日期维度-按天 | one_make_dws.dim_date |
dws_week string | 日期维度-按周 | one_make_dws.dim_date |
dws_month string | 日期维度-按月 | one_make_dws.dim_date |
oil_type string | 油站类型 | one_make_dws.dim_oilstation |
oil_province | 油站所属省 | one_make_dws.dim_oilstation |
oil_city string | 油站所属市 | one_make_dws.dim_oilstation |
oil_county string | 油站所属区 | one_make_dws.dim_oilstation |
customer_classify | 客户类型 | one_make_dws.dim_oilstation |
customer_province | 客户所属省 | one_make_dws.dim_oilstation |
分析
指标
- 安装费用、最大安装费用、最小安装费用、平均安装费用
- 外出差旅费用金额总计、市内交通费用金额总计、住宿费用金额总计、车船费用金额总计、补助费用金额总计、过桥过路费用金额总计、油费金额总计
- 差旅费用扣款明细总计、差旅费用总额统计、差旅费用二阶段扣款总计、差旅费用三阶段扣款总计、差旅费用二阶段最大扣款总计、差旅费用三阶段最大扣款总计
- 报销人员数量、报销人员最大数量、报销人员最小数量、报销人员平均数量
维度
- 日期维度：天、周、月
- 油站维度：类型、省份、城市、区域
- 客户维度：类型、省份
数据
事实表
fact_trvl_exp：差旅事务事实表
代码语言：javascript复制select
biz_trip_money,--外出差旅费用
in_city_traffic_money,--市内交通费用
hotel_money,--住宿费用
fars_money,--车船费用
subsidy_money,--补助费用
road_toll_money,--过桥过路费用
oil_money,--油费
secondary_money,--二单补助费用总计
third_money, --三单补助费用总计
actual_total_money,--费用报销总计
ss_id,--服务网点id
srv_user_id,--工程师id
dt --日期
from fact_trvl_exp;fact_regular_exp：报销事务事实表
代码语言：javascript复制select
ss_id,--服务网点id
srv_user_id,--工程师id
exp_item_name --费用项目名称
from fact_regular_exp;fact_srv_install：安装事务事实表
代码语言：javascript复制select
ss_id,--服务网点id
exp_device_money,--安装费用
os_id --油站id
from fact_srv_install;维度表
dim_oilstation：油站维度表
代码语言：javascript复制 select
id,--油站id
company_name,--公司名称
province_name,--省份名称
city_name,--城市名称
county_name,--区域名称
customer_classify_name,--客户名称
customer_province_name--客户省份
from dim_oilstation;dim_date：时间维度表
代码语言：javascript复制 select
date_id,--天
week_in_year_id,--周
year_month_id --月
from dim_date;小结
- 掌握费用主题的需求分析
15：服务域：费用主题实现
目标：实现费用主题表的维度指标构建
实施
建表
代码语言：javascript复制drop table if exists one_make_st.subj_expense;
create table if not exists one_make_st.subj_expense(
install_money decimal(20,1) comment '安装费用'
,max_install_money decimal(20,1) comment '最大安装费用'
,min_install_money decimal(20,1) comment '最小安装费用'
,avg_install_money decimal(20,1) comment '平均安装费用'
,sumbiz_trip_money decimal(20, 1) comment '外出差旅费用金额总计'
,sumin_city_traffic_money decimal(20, 1) comment '市内交通费用金额总计'
,sumhotel_money decimal(20, 1) comment '住宿费费用金额总计'
,sumfars_money decimal(20, 1) comment '车船费用金额总计'
,sumsubsidy_money decimal(20, 1) comment '补助费用金额总计'
,sumroad_toll_money decimal(20, 1) comment '过桥过路费用金额总计'
,sumoil_money decimal(20, 1) comment '油费金额总计'
,exp_item_total int comment '差旅费用扣款明细总计'
,actual_total_money decimal(20, 1) comment '差旅费用总额统计'
,sum_secondary_money decimal(20, 1) comment '差旅费用二阶段扣款总计'
,sum_third_money decimal(20, 1) comment '差旅费用三阶段扣款总计'
,max_secondary_money decimal(20, 1) comment '差旅费用二阶段最大扣款总计'
,max_third_money decimal(20, 1) comment '差旅费用三阶段最大扣款总计'
,sum_srv_user int comment '报销人员总数量'
,max_srv_user int comment '报销人员最大数量'
,min_srv_user int comment '报销人员最小数量'
,avg_srv_user int comment '报销人员平均数量'
,dws_day string comment '日期维度-按天'
,dws_week string comment '日期维度-按周'
,dws_month string comment '日期维度-按月'
,oil_type string comment '油站维度-油站类型'
,oil_province string comment '油站维度-油站所属省'
,oil_city string comment '油站维度-油站所属市'
,oil_county string comment '油站维度-油站所属区'
,customer_classify string comment '客户维度-客户类型'
,customer_province string comment '客户维度-客户所属省'
) comment '费用主题表'
partitioned by (month String, week String, day String)
stored as orc
location '/data/dw/st/one_make/subj_expense'
;构建
代码语言：javascript复制insert overwrite table one_make_st.subj_expense partition(month = '202101', week='2021W1', day='20210101')
select
sum(install.exp_device_money) install_money, --安装费用
max(install.exp_device_money) max_install_money, --最大安装费用
min(install.exp_device_money) min_install_money, --最小安装费用
avg(install.exp_device_money) avg_install_money, --平均安装费用
sum(fte.biz_trip_money) sumbiz_trip_money, --外出差旅费用金额总计
sum(fte.in_city_traffic_money) sumin_city_traffic_money, --市内交通费用金额总计
sum(fte.hotel_money) sumhotel_money, --住宿费用金额总计
sum(fte.fars_money) sumfars_money, --车船费用金额总计
sum(fte.subsidy_money) sumsubsidy_money, --补助费用金额总计
sum(fte.road_toll_money) sumroad_toll_money, --过桥过路费用金额总计
sum(fte.oil_money) sumoil_money, --油费金额总计
count(distinct fre.exp_item_name) exp_item_total, --差旅费用扣款明细总计
sum(fte.actual_total_money) actual_total_money, --差旅费用总额统计
sum(fte.secondary_money) sum_secondary_money, --差旅费用二阶段扣款总计
sum(fte.third_money) sum_third_money, --差旅费用三阶段扣款总计
max(fte.secondary_money) max_secondary_money, --差旅费用二阶段最大扣款总计
max(fte.third_money) max_third_money, --差旅费用三阶段最大扣款总计
sum(size(split(fre.srv_user_id,','))) sum_srv_user, --报销人员数量
max(size(split(fre.srv_user_id,','))) max_srv_user, --报销人员最大数量
min(size(split(fre.srv_user_id,','))) min_srv_user, --报销人员最小数量
avg(size(split(fre.srv_user_id,','))) avg_srv_user, --报销人员平均数量
dd.date_id dws_day, --日期天
dd.week_in_year_id dws_week, --日期周
dd.year_month_id dws_month, --日期月
dimoil.company_name oil_type, --油站类型
dimoil.province_name oil_province, --油站省份
dimoil.city_name oil_city, --油站城市
dimoil.county_name oil_county, --油站区域
dimoil.customer_classify_name customer_classify, --客户类型
dimoil.customer_province_name customer_province --客户省份
--差旅事务事实表
from one_make_dwb.fact_trvl_exp fte
--安装事务事实表
left join one_make_dwb.fact_srv_install install on fte.ss_id = install.ss_id
--报销事务事实表
left join one_make_dwb.fact_regular_exp fre on fte.srv_user_id = fre.srv_user_id
--关联日期维度表
left join one_make_dws.dim_date dd on fte.dt = dd.date_id
--关联油站维度表
left join one_make_dws.dim_oilstation dimoil on install.os_id = dimoil.id
where dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101'
group by inst_type_id, dd.date_id, dd.week_in_year_id, dd.year_month_id, dimoil.company_name, dimoil.province_name, dimoil.city_name, dimoil.county_name,
dimoil.customer_classify_name, dimoil.customer_province_name
;小结
- 实现费用主题表的维度指标构建
