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

select
    id,--维修单id
    code,--维修单号
    service_id,--服务单id
    is_pay,--是否收费 1-收费，0-免费
    hour_charge,--工时费用
    parts_charge,--配件费用
    fares_charge --车船费用
from ciss_service_repair;

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

select
    id,--工单id
    service_userid,--工程师id
    service_station_id,--服务站点id
    oil_station_id,--油站id
    create_time --创建时间
from ciss_service_workorder;

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

select
    serviceorder_device_id,--服务单设备id
    solution_id,--解决方案id,1-维修，2-更换，3-升级
    fault_type_id --故障分类id
from ciss_service_fault_dtl;

drop table if exists one_make_dwb.fact_srv_repair;
create table if not exists one_make_dwb.fact_srv_repair(
    rpr_id string comment '维修单id'
    , rpr_code string comment '维修单编码'
    , srv_user_id string comment '服务人员用户id'
    , ss_id string comment '服务网点id'
    , os_id string comment '油站id'
    , date_id string comment '日期id'
    , exp_rpr_num string comment '收费维修数量'
    , hour_money int comment '工时费用'
    , parts_money int comment '配件费用'
    , fars_money int comment '车船费用'
    , rpr_device_num int comment '维修设备数量'
    , rpr_mtrl_num int comment '维修配件数量'
    , exchg_parts_num int comment '更换配件数量'
    , upgrade_parts_num int comment '升级配件数量'
    , fault_type_ids string comment '故障类型id集合'
) comment '维修单事实表'
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_srv_repair';

insert overwrite table one_make_dwb.fact_srv_repair partition(dt = '20210101')
select
    repair.id rpr_id                                              --维修单id
  , repair.code rpr_code                                        --维修单号
  , swo.service_userid srv_user_id                              --工程师id
  , swo.service_station_id ss_id                                --服务网点id
  , swo.oil_station_id os_id                                    --油站id
  , swo.create_time date_id                                     --创建时间
  , case when repair.is_pay = 1 then 1 else 0 end exp_rpr_num   --收费维修数量
  , repair.hour_charge hour_money                               --工时费用
  , repair.parts_charge parts_money                             --配件费用
  , repair.fares_charge fars_money                              --车船费用
  , rpr_device_num                                              --维修设备数量
  , rpr_mtrl_num                                                --维修配件数量
  , exchg_parts_num                                             --更换配件数量
  , upgrade_parts_num                                           --升级配件数量
  , fault_type_ids                                              --故障类型id集合
  --维修信息表
from one_make_dwd.ciss_service_repair repair
  --服务单信息表
    left join one_make_dwd.ciss_service_order sorder on repair.service_id = sorder.id
  --工单信息表
    left join one_make_dwd.ciss_service_workorder swo on sorder.workorder_id = swo.id
  --获取维修设备数量
    left join (
    select
      rep.id, count(rep.id) rpr_device_num
    from one_make_dwd.ciss_service_repair rep
    left join one_make_dwd.ciss_service_order sod on rep.service_id = sod.id
    left join one_make_dwd.ciss_service_order_device dev on sod.id = dev.service_order_id
    group by rep.id
    ) repairdvc on repair.id = repairdvc.id
  --获取维修、更换、升级配件数量
    left join (
    select
      rep.id,
         sum(case when sfd.solution_id = 1 then 1 else 0 end) rpr_mtrl_num,
         sum(case when sfd.solution_id = 2 then 1 else 0 end) exchg_parts_num,
         sum(case when sfd.solution_id = 3 then 1 else 0 end) upgrade_parts_num
    from one_make_dwd.ciss_service_repair rep
      left join one_make_dwd.ciss_service_order sod on rep.service_id = sod.id
      left join one_make_dwd.ciss_service_order_device dev on sod.id = dev.service_order_id
      left join one_make_dwd.ciss_service_fault_dtl sfd on dev.id = sfd.serviceorder_device_id
    group by dev.id,rep.id
    ) dvcnum on repair.id = dvcnum.id
  --获取故障类型ID
    left join (
    select
      rep.id, concat_ws(',', collect_set(sfd.fault_type_id)) fault_type_ids
    from one_make_dwd.ciss_service_repair rep
      left join one_make_dwd.ciss_service_order sod on rep.service_id = sod.id
      left join one_make_dwd.ciss_service_order_device dev on sod.id = dev.service_order_id
      left join one_make_dwd.ciss_service_fault_dtl sfd on dev.id = sfd.serviceorder_device_id
    where sfd.fault_type_id is not null
    group by rep.id
    ) faulttype on repair.id = faulttype.id
where repair.dt = '20210101'
;