助力工业物联网，工业大数据之维修事实指标需求分析【二十二】(2)

最新推荐文章于 2024-05-30 16:39:23 发布

2401_84166497

最新推荐文章于 2024-05-30 16:39:23 发布

阅读量984

点赞数 28

分类专栏：程序员文章标签：大数据

本文链接：https://blog.csdn.net/2401_84166497/article/details/138429143

版权

程序员专栏收录该内容

184 篇文章 0 订阅

订阅专栏

	```
	select
	    id,               --设备id
	    service_order_id  --服务单id
	from ciss_service_order_device;
	
	```
	- **ciss\_service\_fault\_dtl**：设备故障信息表
	
	 
	```
	select
	    serviceorder_device_id,--服务单设备id
	    solution_id,--解决方案id,1-维修，2-更换，3-升级
	    fault_type_id --故障分类id
	from ciss_service_fault_dtl;
	
	```

小结
- 掌握DWB层维修事实指标表的需求分析

2：维修事实指标构建

目标：实现DWB层维修事实指标表的构建

实施

建表

drop table if exists one_make_dwb.fact_srv_repair;
create table if not exists one_make_dwb.fact_srv_repair(
    rpr_id string comment '维修单id'
    , rpr_code string comment '维修单编码'
    , srv_user_id string comment '服务人员用户id'
    , ss_id string comment '服务网点id'
    , os_id string comment '油站id'
    , date_id string comment '日期id'
    , exp_rpr_num string comment '收费维修数量'
    , hour_money int comment '工时费用'
    , parts_money int comment '配件费用'
    , fars_money int comment '车船费用'
    , rpr_device_num int comment '维修设备数量'
    , rpr_mtrl_num int comment '维修配件数量'
    , exchg_parts_num int comment '更换配件数量'
    , upgrade_parts_num int comment '升级配件数量'
    , fault_type_ids string comment '故障类型id集合'
) comment '维修单事实表'
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one\_make/fact\_srv\_repair';

抽取

insert overwrite table one_make_dwb.fact_srv_repair partition(dt = '20210101')
select
    repair.id rpr_id                                              --维修单id
	, repair.code rpr_code                                        --维修单号
	, swo.service_userid srv_user_id                              --工程师id
	, swo.service_station_id ss_id                                --服务网点id
	, swo.oil_station_id os_id                                    --油站id
	, swo.create_time date_id                                     --创建时间
	, case when repair.is_pay = 1 then 1 else 0 end exp_rpr_num   --收费维修数量
	, repair.hour_charge hour_money                               --工时费用
	, repair.parts_charge parts_money                             --配件费用
	, repair.fares_charge fars_money                              --车船费用
	, rpr_device_num                                              --维修设备数量
	, rpr_mtrl_num                                                --维修配件数量
	, exchg_parts_num                                             --更换配件数量
	, upgrade_parts_num                                           --升级配件数量
	, fault_type_ids                                              --故障类型id集合
	--维修信息表
from one_make_dwd.ciss_service_repair repair
	--服务单信息表
    left join one_make_dwd.ciss_service_order sorder on repair.service_id = sorder.id
	--工单信息表
    left join one_make_dwd.ciss_service_workorder swo on sorder.workorder_id = swo.id
	--获取维修设备数量
    left join (
		select
			rep.id, count(rep.id) rpr_device_num
		from one_make_dwd.ciss_service_repair rep
		left join one_make_dwd.ciss_service_order sod on rep.service_id = sod.id
		left join one_make_dwd.ciss_service_order_device dev on sod.id = dev.service_order_id
		group by rep.id
    ) repairdvc on repair.id = repairdvc.id
	--获取维修、更换、升级配件数量
    left join (
		select
			rep.id,
    	   sum(case when sfd.solution_id = 1 then 1 else 0 end) rpr_mtrl_num,
    	   sum(case when sfd.solution_id = 2 then 1 else 0 end) exchg_parts_num,
    	   sum(case when sfd.solution_id = 3 then 1 else 0 end) upgrade_parts_num
		from one_make_dwd.ciss_service_repair rep
    	left join one_make_dwd.ciss_service_order sod on rep.service_id = sod.id
    	left join one_make_dwd.ciss_service_order_device dev on sod.id = dev.service_order_id
    	left join one_make_dwd.ciss_service_fault_dtl sfd on dev.id = sfd.serviceorder_device_id
		group by dev.id,rep.id
    ) dvcnum on repair.id = dvcnum.id
	--获取故障类型ID
    left join (
		select
			rep.id, concat_ws(',', collect_set(sfd.fault_type_id)) fault_type_ids
		from one_make_dwd.ciss_service_repair rep
    	left join one_make_dwd.ciss_service_order sod on rep.service_id = sod.id
    	left join one_make_dwd.ciss_service_order_device dev on sod.id = dev.service_order_id
    	left join one_make_dwd.ciss_service_fault_dtl sfd on dev.id = sfd.serviceorder_device_id
		where sfd.fault_type_id is not null
		group by rep.id
    ) faulttype on repair.id = faulttype.id
where repair.dt = '20210101'
;