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

最新推荐文章于 2024-11-09 14:07:11 发布

2401_84185074

最新推荐文章于 2024-11-09 14:07:11 发布

阅读量747

点赞数 9

分类专栏： 2024年程序员学习文章标签：大数据 java 开发语言

本文链接：https://blog.csdn.net/2401_84185074/article/details/137897832

版权

2024年程序员学习专栏收录该内容

59 篇文章 1 订阅

订阅专栏

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新大数据全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip204888 （备注大数据）

正文

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