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

1：工单事实指标需求分析

• 目标：掌握DWB层工单事实指标表的需求分析

• 路径

  • step1：目标需求
  • step2：数据来源

• 实施

  • 目标需求：基于工单信息统计等待分配工单数量、完成工单数量、处理工单数量、响应时长、服务时长等指标

    字段名	说明	数据来源
    wo_id	工单id	one_make_dwd.ciss_service_workorder
    callaccept_id	来电受理单id	one_make_dwd.ciss_service_workorder
    oil_station_id	油站id	one_make_dwd.ciss_service_workorder
    userids	服务该工单用户id(注意：可能会有多个，以逗号分隔)	one_make_dwd.ciss_service_workorder
    wo_num	工单单据数量	one_make_dwd.ciss_service_workorder
    back_num	退回工单数量（如果工单没有被退回，数量是0）	one_make_dwd.ciss_service_workorder、ciss_service_workorder_back
    abolished_num	已作废工单数量	one_make_dwd.ciss_service_workorder
    wait_dispatch_num	待派工数量	one_make_dwd.ciss_service_workorder、eos_dict_type、eos_dict_entry派工单状态：待派工（status=4）
    alread_complete_num	已完工工单数量（已完工、已回访）	派工单状态：已完工、已回访（status=5 || 6）
    processing_num	正在处理工单数量（待离站、待完工）	派工单状态：待离站、待完工（status=3 || 4）
    people_num	工单人数数量（一个工单由多人完成）	one_make_dwd.ciss_service_workorder、ciss_service_workorder_user默认为1数据预处理；工单用户id是否为空；工单用户id是否为空
    service_total_duration	服务总时长（按小时）->从出发到完工时间（leave_time - start_time）	one_make_dwd.ciss_service_workorder
    repair_service_duration	报修响应时长（按小时）->呼叫中心受理到出发时间（start_time-submit_time）	one_make_dwd.ciss_service_workorder
    customer_repair_num	客户报修工单数量	one_make_dwd.ciss_service_workorder；is_customer_repairs字段
    charg_num	收费工单数量	one_make_dwd.ciss_service_workorder；is_charg字段
    repair_device_num	维修设备数量	ciss_service_order、ciss_service_order_device；状态：维修（type=2）
    install_device_num	安装设备数据量	ciss_service_order、ciss_service_order_device；状态：安装(type=1)
    install_num	安装单数量（以下四个单据的数量有可能会有重叠，例如：一个工单有可能有巡检、也有可能有维修）	one_make_dwd.ciss_service_install
    repair_num	维修单数量	ciss4.ciss_service_repair
    remould_num	巡检单数量	ciss4.ciss_service_remould
    inspection_num	改造单数量	ciss4.ciss_service_inspection
    workorder_trvl_exp	工单差旅费(通过工单id与ciss4.ciss_service_trvl_exp_dtl关联，取submoney5即可)	ciss4.ciss_service_trvl_exp_dtl

  • 数据来源

    • ciss_service_workorder：工单详情事实表

      selectid,--工单idcallaccept_id,--来电受理idoil_station_id, --油站idservice_userid,--工程师idstatus,--工单状态submit_time,--提交时间start_time,--开始时间leave_time,--离开时间is_customer_repairs,--是否为报修工单is_charg --是否为收费工单
      from ciss_service_workorder;
      

    • ciss_service_workorder_back：回退工单信息表

      select id,              --回退idworkorder_id     --工单id
      from ciss_service_workorder_back;
      

    • ciss_service_workorder_user：工程师信息表

      selectworkorder_id,  --工单iduserid,        --工程师idusername       --工程师姓名
      from ciss_service_workorder_user; 
      

    • ciss_service_trvl_exp_dtl：差旅费用信息表

      selectwork_order_id, --工单idsubmoney5 --应收会计扣款金额
      from ciss_service_trvl_exp_dtl;
      

    • ciss_service_order：服务单信息表

      selectid,            --服务单idworkorder_id,  --工单idtype           --工单类型,1-安装，2-维修，3-巡检
      from ciss_service_order;
      

    • ciss_service_order_device：服务单设备信息表

      selectid,               --设备idservice_order_id  --服务单id
      from ciss_service_order_device;
      

    • 工单类型合并表

      • ciss_service_install：设备安装信息表

        • 服务单id、安装工单id

      • ciss_service_repair：设备维修信息表

      • 服务单id、维修工单id

      • ciss_service_remould：设备改造信息表

        - 服务单id、改造工单id
        

        • ciss_service_inspection：设备巡检信息表

          • 服务单id、巡检工单id

            selectso.id,                     --服务单idso.workorder_id,           --工单idinstall.id installid,      --安装单idrepair.id repairid,        --维修单idremould.id remouldid,      --改造单idinspection.id inspectionid --巡检单id--服务单信息表
            from one_make_dwd.ciss_service_order so
            left join one_make_dwd.ciss_service_install install on so.id = install.service_id
            left join one_make_dwd.ciss_service_repair repair on so.id = repair.service_idleft join one_make_dwd.ciss_service_remould remould on so.id = remould.service_idleft join one_make_dwd.ciss_service_inspection inspection on so.id = inspection.service_idwhere so.dt = '20210101';
            

• 小结

  • 掌握DWB层呼叫中心事实指标表的需求分析

2：工单事实指标构建

• 目标：实现DWB层工单事实指标表的构建

• 实施

  • 建表

    drop table if exists one_make_dwb.fact_worker_order;
    create table if not exists one_make_dwb.fact_worker_order(wo_id string comment '工单id', callaccept_id string comment '来电受理单id', oil_station_id string comment '油站id', userids string comment '服务该工单用户id(注意：可能会有多个，以逗号分隔)', wo_num bigint comment '工单单据数量', back_num bigint comment '退回工单数量，默认为0', abolished_num bigint comment '已作废工单数量', wait_dispatch_num bigint comment '待派工数量', wait_departure_num bigint comment '待出发数量', alread_complete_num bigint comment '已完工工单数量（已完工、已回访）', processing_num bigint comment '正在处理工单数量（待离站、待完工）', people_num int comment '工单人数数量（一个工单由多人完成）', service_total_duration int comment '服务总时长（按小时），(leave_time - start_time)', repair_service_duration int comment '报修响应时长（按小时），(start_time-submit_time)', customer_repair_num bigint comment '客户报修工单数量', charg_num bigint comment '收费工单数量', repair_device_num bigint comment '维修设备数量', install_device_num bigint comment '安装设备数据量', install_num bigint comment '安装单数量', repair_num bigint comment '维修单数量', remould_num bigint comment '改造单数量', inspection_num bigint comment '巡检单数量', workorder_trvl_exp decimal(20,1) comment '工单差旅费'
    )
    partitioned by (dt string)
    stored as orc
    location '/data/dw/dwb/one_make/fact_worker_order'
    ;
    

  • 抽取

    insert overwrite table one_make_dwb.fact_worker_order partition(dt = '20210101')
    select--工单idwo.id wo_id--来电受理单id, max(callaccept_id) callaccept_id--油站id, max(oil_station_id) oil_station_id--工程师id, max(case when wo.service_userids is not null then concat_ws(',', wo.service_userid, wo.service_userids) else wo.service_userid end) userids--工单单据数量：安装单、维修单……, count(wo.id) wo_num--退回工单数量, count(wob.id) back_num--已作废工单数量, sum(case when status = '-1' then 1 else 0 end) abolished_num--待派发工单数量, sum(case when status = '4' then 1 else 0 end) wait_dispatch_num--待出发工单数量, sum(case when status = '2' then 1 else 0 end) wait_departure_num--已完工工单数量, sum(case when status = '5' then 1 when status = '6' then 1 else 0 end) alread_complete_num--处理中工单数量, sum(case when status = '3' then 1 when status = '4' then 1 else 0 end) processing_num--工单人数, case when count(usr.id) = 0 then 1 else count(usr.id) end people_num--服务总时长, max((wo.leave_time - wo.start_time) / 3600000) service_total_duration--报修响应时长, max((wo.start_time - wo.submit_time) / 3600000) repair_service_duration--客户报修工单数量, sum(case when wo.is_customer_repairs = '2' then 1 else 0 end) customer_repairs--收费工单数量, sum(case when wo.is_charg = '1' then 1 else 0 end) charg_num--维修设备数量, max(case when sod.repair_device_num = 0 then 1 when sod.repair_device_num is null then 0 else sod.repair_device_num end) repair_device_num--安装设备数量, max(case when sod2.install_device_num = 0 then 1 when sod2.install_device_num is null then 0 else sod2.install_device_num end) install_device_num--安装单数量, sum(case when sertype.installid is not null then 1 else 0 end) install_num--维修单数量, sum(case when sertype.repairid is not null then 1 else 0 end) repair_num--改造单数量, sum(case when sertype.remouldid is not null then 1 else 0 end) remould_num--巡检单数量, sum(case when sertype.inspectionid is not null then 1 else 0 end) inspection_num--工单差旅费, max(case when ed.submoney5 is null then 0.0 else ed.submoney5 end) workorder_trvl_exp
    -- 工单信息表
    from one_make_dwd.ciss_service_workorder wo--关联回退工单：回退工单个数left join one_make_dwd.ciss_service_workorder_back wob on wo.id = wob.workorder_id--关联工程师信息表：工程师人数left join one_make_dwd.ciss_service_workorder_user usr on wo.id = usr.workorder_id--关联差旅费用信息表：工单差旅费用left join one_make_dwd.ciss_service_trvl_exp_dtl ed on wo.id = ed.work_order_id--关联维修设备个数信息left join (--统计每个工单的维修设备个数selectso.workorder_id, count(sod.id) repair_device_num--服务单表关联设备表：每个工单对应的设备idfrom one_make_dwd.ciss_service_order soleft join one_make_dwd.ciss_service_order_device sodon so.id = sod.service_order_idwhere so.type = '2' and so.dt='20210101'group by so.workorder_id) sod on wo.id = sod.workorder_id--关联安装设备个数信息left join (--统计每个工单的安装设备个数selectso.workorder_id, count(sod.id) install_device_numfrom one_make_dwd.ciss_service_order soleft join one_make_dwd.ciss_service_order_device sodon so.id = sod.service_order_id--过滤服务单的类型为安装类型的服务单where so.type = '1' and so.dt='20210101'group by so.workorder_id) sod2 on wo.id = sod2.workorder_id--工单类型合并表：安装、维修、改造、巡检单idleft join (selectso.id, so.workorder_id, install.id installid, repair.id repairid, remould.id remouldid, inspection.id inspectionidfrom one_make_dwd.ciss_service_order soleft join one_make_dwd.ciss_service_install install on so.id = install.service_idleft join one_make_dwd.ciss_service_repair repair on so.id = repair.service_idleft join one_make_dwd.ciss_service_remould remould on so.id = remould.service_idleft join one_make_dwd.ciss_service_inspection inspection on so.id = inspection.service_idwhere so.dt = '20210101') sertype on wo.id = sertype.workorder_id
    where wo.dt='20210101'
    group by wo.id
    ;
    

• 小结

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