助力工业物联网，工业大数据之服务域：安装主题分析实现【三十】

select
    install_num,--安装数
    repair_num,--维修数
    remould_num,--改造数
    wo_num,--工单数
    alread_complete_num,--完成数，已回访
    oil_station_id, --油站id
    dt --日期
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;

drop table if exists one_make_st.subj_customer;
create table if not exists one_make_st.subj_customer(
    sum_install_num int comment '安装数量'
    ,max_install_num int comment '安装最大数量'
    ,min_install_num int comment '安装最小数量'
    ,avg_min_install_num decimal(20, 1) comment '安装平均数量'
    ,sum_repair_num int comment '维修数量'
    ,max_repair_num int comment '维修最大数量'
    ,min_repair_num int comment '维修最小数量'
    ,avg_repair_num decimal(20, 1) comment '维修平均数量'
    ,sum_wo_num int comment '派工数量'
    ,max_sum_wo_num int comment '派工最大数量'
    ,min_sum_wo_num int comment '派工最小数量'
    ,avg_sum_wo_num decimal(20, 1) comment '派工平均数量'
    ,sum_remould_num int comment '巡检数量'
    ,max_remould_num int comment '巡检最大数量'
    ,min_remould_num int comment '巡检最小数量'
    ,avg_remould_num decimal(20, 1) comment '巡检平均数量'
    ,sum_alread_complete_num int comment '回访数量'
    ,max_alread_complete_num int comment '回访最大数量'
    ,min_alread_complete_num int comment '回访最小数量'
    ,avg_alread_complete_num decimal(20, 1) 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_customer'
;

insert overwrite table one_make_st.subj_customer partition(month = '202101', week='2021W1', day='20210101')
  select
      sum(fwo.install_num) sum_install_num,                  --安装数量
    max(fwo.install_num) max_install_num,                  --最大安装数量
    min(fwo.install_num) min_install_num,                  --最小安装数量
      avg(fwo.install_num) avg_min_install_num,              --平均安装数量
    sum(fwo.repair_num) sum_repair_num,                    --维修数量
    max((fwo.repair_num)) max_repair_num,                  --最大维修数量
      min(fwo.repair_num) min_repair_num,                    --最小维修数量
    avg((fwo.repair_num)) avg_repair_num,                  --平均维修数量
    sum(fwo.wo_num) sum_wo_num,                            --派工数量
    max(fwo.wo_num) max_sum_wo_num,                        --最大派工数量
      min(fwo.wo_num) min_sum_wo_num,                        --最小派工数量
    avg(fwo.wo_num) avg_wo_num,                            --平均派工数量
    sum(fwo.remould_num) sum_remould_num,                  --改造数量
    max(fwo.remould_num) max_remould_num,                  --最大改造数量
      min(fwo.remould_num) min_remould_num,                  --最小改造数量
    avg(fwo.remould_num) avg_remould_num,                  --平均改造数量
    sum(fwo.alread_complete_num) sum_alread_complete_num,  --回访数量
      max(fwo.alread_complete_num) max_alread_complete_num,  --最大回访数量
    min(fwo.alread_complete_num) min_alread_complete_num,  --最小回访数量
      avg(fwo.alread_complete_num) avg_alread_complete_num,  --平均回访数量
    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_worker_order fwo
  --日期维度表
  left join one_make_dws.dim_date dd on fwo.dt = dd.date_id
  --油站维度表
  left join one_make_dws.dim_oilstation dimoil on fwo.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, dimoil.company_name, dimoil.province_name, dimoil.city_name, dimoil.county_name,
           dimoil.customer_classify_name, dimoil.customer_province_name
  ;

create database if not exists one_make_dm;

drop table if exists one_make_dm.mart_operation_dept;
create table if not exists one_make_dm.mart_operation_dept(
    wo_id string comment '工单ID'
    ,userids string comment '工单服务用户ID'
    ,callaccept_id string comment '来电受理ID'
    ,oil_station_id string comment '油站ID'
    ,os_name string comment '油站名称'
    ,service_total_duration decimal(20,2) comment '服务工时(小时)'
    ,repair_num bigint comment '维修工单数量'
    ,wo_num bigint comment '工单数量'
    ,avg_wo_num int comment '平均工单'
    ,sum_os_online int comment '加油机在线设备总数'
    ,atu_num_rate decimal(5,2) comment '客户回访满意度率'
    ,rtn_visit_duration decimal(20,2) comment '来电受理时长(小时)'
    ,dws_day string comment '日期维度-按天'
    ,dws_week string comment '日期维度-按周'
    ,dws_month string comment '日期维度-按月'
) comment '运营部数据集市表'
partitioned by (month String, week String, day String)
stored as orc
location '/data/dw/dm/one_make/mart_operation_dept';

insert overwrite table one_make_dm.mart_operation_dept partition(month = '202101', week='2021W1', day='20210101')
    select
        fwo.wo_id                                                                            --工单id
        , max(fwo.userids) userids                                                           --工程师id
        , max(fwo.callaccept_id) callaccept_id                                               --来电受理id
        , max(fwo.oil_station_id) oil_station_id                                             --油站id
      , max(fos.os_name) os_name                               --油站名称
        , max(fwo.service_total_duration) service_total_duration                             --服务工时
        , sum(fwo.wo_num) wo_num                                                             --维修工单数量
        , count(fos.os_id) sum_os_num                                                        --工单数量
        , avg(fwo.wo_num) avg_wo_num                                                         --平均工单
        , sum(fos.valid_os_num) sum_os_online                                                --加油机在线设备总数
        , max(fsrv.srv_atu_num / (fsrv.srv_atu_num + fsrv.srv_bad_atu_num)) atu_num_rate     --客户回访满意度
        , max(fcs.interval / 3600.0) rtn_visit_duration                                      --来电受理时长
        , dd.date_id dws_day                                                                 --日期天
        , dd.week_in_year_id dws_week                                                        --日期周
        , dd.year_month_id dws_month                                                         --日期月
    --工单事务事实表
    from one_make_dwb.fact_worker_order fwo
    --油站事务事实表
    left join one_make_dwb.fact_oil_station fos on fwo.oil_station_id = fos.os_id
    --回访事务事实表
    left join one_make_dwb.fact_srv_rtn_visit fsrv on fwo.wo_id = fsrv.wrkodr_id
    --呼叫中心事务事实表
    left join one_make_dwb.fact_call_service fcs on fwo.callaccept_id = fcs.id
    --日期维度表
    left join one_make_dws.dim_date dd on fwo.dt = dd.date_id
    where dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and  dd.date_id = '20210101'
    group by fwo.wo_id, dd.date_id, dd.week_in_year_id, dd.year_month_id;