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

文章目录

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

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

• 路径

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

• 实施

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

  • 数据来源

    • ciss_service_expense_report：费用信息表

      select
          id,--报销单id
          create_user_id,--创建人id
          submoney5, --报销金额
          create_org_id --创建部门id
      from ciss_service_expense_report;
      

    • ciss_base_servicestation：服务网点信息表

      select
          id,--服务网点id
          org_id --部门id
      from ciss_base_servicestation;
      

    • ciss_service_exp_report_dtl：费用明细表

      select
          exp_report_id,--报销单id
          submoney5,--项目报销实际金额
          item_id --费用项目id
      from ciss_service_exp_report_dtl;
      

    • tmp_dict：数据字典表

      select
             dictid, --项目id
             dictname --项目名称
      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_report
          where 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：差旅报销汇总信息表

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

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

      select
          exp_report_id,--汇总报销单id
          workorder_travel_exp_id --工单费用单id
      from ciss_s_exp_report_wo_payment;
      

    • ciss_service_travel_expense：差旅报销单信息表

      select
          id,--差旅报销单id
          work_order_id --工单id
      from ciss_service_travel_expense;
      

    • ciss_service_workorder：工单信息表

      select
          id,--工单id
          service_station_id --服务网点id
      from ciss_service_workorder;
      

    • ciss_service_trvl_exp_dtl：差旅费用明细表

      select
          travel_expense_id,--费用单id
          item,--费用项目名称
          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
    	--差旅费汇总单id
        exp_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  (
    				select
    					travel_expense_id, item, sum(submoney5) as item_money
    				from one_make_dwd.ciss_service_trvl_exp_dtl
    				where dt = '20210101'
    				group by travel_expense_id, item
    		) as trvl_dtl_sum
      on 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：物料申请信息表

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

    • ciss_base_servicestation：服务网点信息表

      select
          id,--服务网点id
          code --服务网点编号
      from ciss_base_servicestation;
      

    • ciss_material_wdwl_sqd_dtl：物料申请明细表

      select
          wdwl_sqd_id,--申请单id
          application_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, 	                       --物料申请单id
      	ma.code as ma_code,                        --申请单编号
      	stn.id as ss_id,                           --服务网点id
      	ma.logistics_type as logi_id,              --物流类型id
      	ma.logistics_company as logi_cmp_id,       --物流公司id
          ma.warehouse_code as warehouse_id,         --仓库id
      	sum(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 money
                   from one_make_dwd.ciss_material_wdwl_sqd_dtl dtl
      			 where 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层网点物料事实指标表的构建