助力工业物联网，工业大数据之服务域：派单主题分析实现【二十九】

select
    userid,--受理人员id
    dispatch_cnt,--派工数量
    id,--呼叫受理id
    oil_station_id, --油站id
    dt --日期
from fact_call_service;

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;

select
    id,--油站id
    company_name,--公司名称
    province_name,--省份名称
    city_name,--城市名称
    county_name,--区域名称
    customer_classify_name,--客户名称
    customer_province_name--客户省份
from dim_oilstation;

select
    date_id,--天
    week_in_year_id,--周
    year_month_id --月
from dim_date;

select      
empid,--人员id      
orgname,--部门名称      
posiname,--岗位名称      
empname --员工名称  
from dim_emporg;

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

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
;

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;

select
    ss_id,--服务网点id
    srv_user_id,--工程师id
    exp_item_name --费用项目名称
from fact_regular_exp;

select
    ss_id,--服务网点id
    exp_device_money,--安装费用
    os_id --油站id
from fact_srv_install;

  select
      id,--油站id
      company_name,--公司名称
      province_name,--省份名称
      city_name,--城市名称
      county_name,--区域名称
      customer_classify_name,--客户名称
      customer_province_name--客户省份
  from dim_oilstation;

  select
      date_id,--天
      week_in_year_id,--周
      year_month_id --月
  from dim_date;

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

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
;