助力工业物联网，工业大数据之费用事实指标分析及实现【二十四】

1：费用事实指标分析及实现

• 目标：实现DWB层费用报销事实指标表的构建

• 路径

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

• 实施

  • 目标需求：基于费用报销数据统计费用报销金额等指标

    

  • 数据来源

    • ciss_service_expense_report：费用信息表

      selectid,--报销单idcreate_user_id,--创建人idsubmoney5, --报销金额create_org_id --创建部门id
      from ciss_service_expense_report;
      

    • ciss_base_servicestation：服务网点信息表

      selectid,--服务网点idorg_id --部门id
      from ciss_base_servicestation;
      

    • ciss_service_exp_report_dtl：费用明细表

      selectexp_report_id,--报销单idsubmoney5,--项目报销实际金额item_id --费用项目id
      from ciss_service_exp_report_dtl;
      

    • tmp_dict：数据字典表

      selectdictid, --项目iddictname --项目名称
      from one_make_dwb.tmp_dict where dicttypename = '费用报销项目';
      

  • 目标实现

    • 建表

      drop table if exists one_make_dwb.fact_regular_exp;
      create table if not exists one_make_dwb.fact_regular_exp(exp_id string comment '费用报销id', ss_id string comment '服务网点id', srv_user_id string comment '服务人员id', actual_exp_money decimal(20,1) comment '费用实际报销金额', exp_item string comment '费用项目id', exp_item_name string comment '费用项目名称', exp_item_money decimal(20,1) comment '费用项目实际金额'
      )
      partitioned by (dt string)
      stored as orc
      location '/data/dw/dwb/one_make/fact_regular_exp';
      

    • 抽取

      insert overwrite table one_make_dwb.fact_regular_exp partition(dt = '20210101')
      select/*+repartitions(1) */exp.id as exp_id                           --费用报销id, ss.id as ss_id                           --服务网点id, exp.create_user_id as srv_user_id        --创建人id, exp.submoney5 as actual_exp_money        --实际报销金额, dict.dictid as exp_item                  --费用项目id, dict.dictname as exp_item_name           --费用项目名称, exp_dtl.submoney5 as exp_item_money      --费用项目金额
      from
      --费用信息表
      (select*from one_make_dwd.ciss_service_expense_reportwhere dt = '20210101' and status = 9 --只取制证会计已审状态
      ) exp
      --服务网点信息表
      left join one_make_dwd.ciss_base_servicestation ss
      on ss.dt = '20210101' and ss.org_id = exp.create_org_id
      --报销明细表
      left join one_make_dwd.ciss_service_exp_report_dtl exp_dtl
      on exp_dtl.dt = '20210101' and exp.id = exp_dtl.exp_report_id
      --数据字典表
      left join one_make_dwb.tmp_dict dict
      on dict.dicttypename = '费用报销项目' and dict.dictid = exp_dtl.item_id
      ;
      

• 小结

  • 实现DWB层费用报销事实指标表的构建

2：差旅事实指标分析及实现

• 目标：实现DWB层差旅报销事实指标表的构建

• 路径

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

• 实施

  • 目标需求：基于差率报销信息统计交通费用、住宿费用、油费金额等报销费用指标

    

  • 数据来源

    • ciss_service_trvl_exp_sum：差旅报销汇总信息表

      selectid,--汇总报销单iduser_id,--报销人id【工程师id】status,--汇总单状态：15表示审核通过submoney5 --应收报销总金额
      from ciss_service_trvl_exp_sum;
      

    • ciss_s_exp_report_wo_payment：汇总报销单与工单费用单对照表

      selectexp_report_id,--汇总报销单idworkorder_travel_exp_id --工单费用单id
      from ciss_s_exp_report_wo_payment;
      

    • ciss_service_travel_expense：差旅报销单信息表

      selectid,--差旅报销单idwork_order_id --工单id
      from ciss_service_travel_expense;
      

    • ciss_service_workorder：工单信息表

      selectid,--工单idservice_station_id --服务网点id
      from ciss_service_workorder;
      

    • ciss_service_trvl_exp_dtl：差旅费用明细表

      selecttravel_expense_id,--费用单iditem,--费用项目名称submoney5 --费用金额
      from ciss_service_trvl_exp_dtl;
      

  • 目标实现

    • 建表

      drop table if exists one_make_dwb.fact_trvl_exp;
      create table if not exists one_make_dwb.fact_trvl_exp(trvl_exp_id string comment '差旅报销单id', ss_id string comment '服务网点id', srv_user_id string comment '服务人员id', biz_trip_money decimal(20,1) comment '外出差旅费用金额总计', in_city_traffic_money decimal(20,1) comment '市内交通费用金额总计', hotel_money decimal(20,1) comment '住宿费费用金额总计', fars_money decimal(20,1) comment '车船费用金额总计', subsidy_money decimal(20,1) comment '补助费用金额总计', road_toll_money decimal(20,1) comment '过桥过路费用金额总计', oil_money decimal(20,1) comment '油费金额总计', secondary_money decimal(20,1) comment '二单补助费用总计', third_money decimal(20,1) comment '三单补助费用总计', actual_total_money decimal(20,1) comment '费用报销总计'
      )
      partitioned by (dt string)
      stored as orc
      location '/data/dw/dwb/one_make/fact_trvl_exp';
      

  • 抽取

    insert overwrite table one_make_dwb.fact_trvl_exp partition(dt = '20210101')
    select--差旅费汇总单idexp_sum.id as trvl_exp_id--服务网点id, wrk_odr.service_station_id as ss_id--服务人员id, exp_sum.user_id as srv_user_id--外出差旅费用金额总计, sum(case when trvl_dtl_sum.item = 1 then trvl_dtl_sum.item_money else 0 end) as biz_trip_money--市内交通费用金额总计, sum(case when trvl_dtl_sum.item = 2 then trvl_dtl_sum.item_money else 0 end) as in_city_traffic_money--住宿费费用金额总计, sum(case when trvl_dtl_sum.item = 3 then trvl_dtl_sum.item_money else 0 end) as hotel_money--车船费用金额总计, sum(case when trvl_dtl_sum.item = 4 then trvl_dtl_sum.item_money else 0 end) as fars_money--补助费用金额总计, sum(case when trvl_dtl_sum.item = 5 then trvl_dtl_sum.item_money else 0 end) as subsidy_money--过桥过路费用金额总计, sum(case when trvl_dtl_sum.item = 6 then trvl_dtl_sum.item_money else 0 end) as road_toll_money--油费金额总计, sum(case when trvl_dtl_sum.item = 7 then trvl_dtl_sum.item_money else 0 end) as oil_money--二单补助费用总计, sum(case when trvl_dtl_sum.item = 8 then trvl_dtl_sum.item_money else 0 end) as secondary_money--三单补助费用总计, sum(case when trvl_dtl_sum.item = 9 then trvl_dtl_sum.item_money else 0 end) as third_money--费用报销总计, max(exp_sum.submoney5) as actual_total_money
    --差旅报销汇总单
    from one_make_dwd.ciss_service_trvl_exp_sum exp_sum
    --汇总报销单与工单费用单对照表
    inner join one_make_dwd.ciss_s_exp_report_wo_payment r on exp_sum.dt = '20210101' and r.dt = '20210101' and exp_sum.id = r.exp_report_id and exp_sum.status = 15
    --差旅报销单信息表
    inner join one_make_dwd.ciss_service_travel_expense exp on exp.dt = '20210101' and exp.id = r.workorder_travel_exp_id
    --工单信息表
    inner join one_make_dwd.ciss_service_workorder wrk_odr on wrk_odr.dt = '20210101' and wrk_odr.id = exp.work_order_id
    --获取每种费用项目总金额
    inner join  (selecttravel_expense_id, item, sum(submoney5) as item_moneyfrom one_make_dwd.ciss_service_trvl_exp_dtlwhere dt = '20210101'group by travel_expense_id, item) as trvl_dtl_sumon trvl_dtl_sum.travel_expense_id = exp.id
    group by exp_sum.id, wrk_odr.service_station_id, exp_sum.user_id
    ;
    

• 小结

  • 实现DWB层差旅报销事实指标表的构建

3：网点物料事实指标分析及实现

• 目标：实现DWB层网点物料事实指标表的构建

• 路径

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

• 差旅报销汇总信息表实施

  • 目标需求：基于物料申请单的信息统计物料申请数量、物料申请金额等指标

    

  • 数据来源

    • ciss_material_wdwl_sqd：物料申请信息表

      selectid,--申请单idcode,--申请单编号service_station_code,--网点编号logistics_type,--物流公司类型logistics_company,--物流公司名称warehouse_code --仓库id
      from ciss_material_wdwl_sqd;
      

    • ciss_base_servicestation：服务网点信息表

      selectid,--服务网点idcode --服务网点编号
      from ciss_base_servicestation;
      

    • ciss_material_wdwl_sqd_dtl：物料申请明细表

      selectwdwl_sqd_id,--申请单idapplication_reason,--申请理由count_approve,--审核数量price,--单价count --个数
      from ciss_material_wdwl_sqd_dtl;
      

  • 目标实现

    • 建表

      create table if not exists one_make_dwb.fact_srv_stn_ma(ma_id string comment '申请单id', ma_code string comment '申请单编码', ss_id string comment '服务网点id', logi_id string comment '物流类型id', logi_cmp_id string comment '物流公司id', warehouse_id string comment '仓库id', total_m_num decimal(10,0) comment '申请物料总数量', total_m_money decimal(10,1) comment '申请物料总金额', ma_form_num decimal(10,0) comment '申请单数量', inst_m_num decimal(10,0) comment '安装申请物料数量', inst_m_money decimal(10,1) comment '安装申请物料金额', bn_m_num decimal(10,0) comment '保内申请物料数量', bn_m_money decimal(10,1) comment '保内申请物料金额', rmd_m_num decimal(10,0) comment '改造申请物料数量', rmd_m_money decimal(10,1) comment '改造申请物料金额', rpr_m_num decimal(10,0) comment '维修申请物料数量', rpr_m_money decimal(10,1) comment '维修申请物料金额', sales_m_num decimal(10,0) comment '销售申请物料数量', sales_m_money decimal(10,1) comment '销售申请物料金额', insp_m_num decimal(10,0) comment '巡检申请物料数量', insp_m_money decimal(10,1) comment '巡检申请物料金额'
      )
      partitioned by (dt string)
      stored as orc
      location '/data/dw/dwb/one_make/fact_srv_stn_ma';
      

    • 抽取

      insert overwrite table one_make_dwb.fact_srv_stn_ma partition(dt = '20210101')
      select    /*+repartition(1) */ ma.id as ma_id, 	                       --物料申请单idma.code as ma_code,                        --申请单编号stn.id as ss_id,                           --服务网点idma.logistics_type as logi_id,              --物流类型idma.logistics_company as logi_cmp_id,       --物流公司idma.warehouse_code as warehouse_id,         --仓库idsum(m_smry.cnt) as total_m_num ,           --申请物料总数量sum(m_smry.money) as total_m_money,        --申请物料总金额count(1) as ma_form_num,                   --申请单数量sum(case when m_smry.ma_rsn = 1 then m_smry.cnt else 0 end) as inst_m_num,        --安装申请物料数量   sum(case when m_smry.ma_rsn = 1 then m_smry.money else 0 end) as inst_m_money,    --安装申请物料金额sum(case when m_smry.ma_rsn = 2 then m_smry.cnt else 0 end) as bn_m_num,          --保内申请物料数量sum(case when m_smry.ma_rsn = 2 then m_smry.money else 0 end) as bn_m_money,      --保内申请物料金额sum(case when m_smry.ma_rsn = 3 then m_smry.cnt else 0 end) as rmd_m_num,         --改造申请物料数量sum(case when m_smry.ma_rsn = 3 then m_smry.money else 0 end) as rmd_m_money,     --改造申请物料金额sum(case when m_smry.ma_rsn = 4 then m_smry.cnt else 0 end) as rpr_m_num,         --维修申请物料数量sum(case when m_smry.ma_rsn = 4 then m_smry.money else 0 end) as rpr_m_money,     --维修申请物料金额sum(case when m_smry.ma_rsn = 5 then m_smry.cnt else 0 end) as sales_m_num,       --销售申请物料数量sum(case when m_smry.ma_rsn = 5 then m_smry.money else 0 end) as sales_m_money,   --销售申请物料金额sum(case when m_smry.ma_rsn = 6 then m_smry.cnt else 0 end) as insp_m_num,        --巡检申请物料数量sum(case when m_smry.ma_rsn = 6 then m_smry.money else 0 end) as insp_m_money     --巡检申请物料金额
      --物料申请信息表:8为审核通过
      from (select * from one_make_dwd.ciss_material_wdwl_sqd where dt = '20210101' and status = 8 ) ma
      --关联站点信息表，获取站点id
      left join one_make_dwd.ciss_base_servicestation stn on stn.dt = '20210101' and ma.service_station_code = stn.code
      --关联物料申请费用明细
      left join (select dtl.wdwl_sqd_id as wdwl_sqd_id, dtl.application_reason as ma_rsn, sum(dtl.count_approve) as cnt,sum(dtl.price * dtl.count) as moneyfrom one_make_dwd.ciss_material_wdwl_sqd_dtl dtlwhere dtl.dt = '20210101'group by dtl.wdwl_sqd_id, dtl.application_reason) m_smry on m_smry.wdwl_sqd_id = ma.id
      group by ma.id, ma.code, stn.id, ma.logistics_type, ma.logistics_company, ma.warehouse_code
      ;
      

• 小结

  • 实现DWB层网点物料事实指标表的构建