2024年助力工业物联网，工业大数据之费用事实指标分析及实现【二十四】-优快云博客

本文链接：https://blog.youkuaiyun.com/2401_84181911/article/details/138394134

本文介绍了如何通过SQL查询从多个相关数据库表中提取数据，创建事实指标表，包括差旅报销汇总信息和物料申请详细分析，以支持财务管理和业务决策。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

	- 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
;