助力工业物联网，工业大数据之费用事实指标分析及实现【二十四】
文章目录
- 1：费用事实指标分析及实现
- 2：差旅事实指标分析及实现
- 3：网点物料事实指标分析及实现
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层网点物料事实指标表的构建