助力工业物联网，工业大数据之工业大数据之油站维度设计【十四】

文章目录

01：油站维度设计

• 目标：掌握油站维度的需求与设计
• 路径- step1：需求- step2：设计
• 实施- 需求：构建油站维度表，得到油站id、油站名称、油站所属的地理区域、所属公司、油站状态等- 设计- 数据来源- ciss_base_oilstation：油站信息表select id, name, code, customer_id, customer_name, province, city, region, township,status, customer_classify, dtfrom one_make_dwd.ciss_base_oilstationwhere id !=''and name isnotnulland name !='null'and customer_id isnotnull;- eos_dict_type：字典状态类别表，记录所有需要使用字典标记的表select * from eos_dict_type where dicttypename = '油站状态';- eos_dict_entry：字典状态明细表，记录所有具体的状态或者类别信息select*from eos_dict_entry where dicttypeid ='BUSS_OILSTATION_STATUS';- ciss_base_baseinfo：客户公司信息表【公司ID、公司名称】select ygcode, companyname from one_make_dwd.ciss_base_baseinfo groupby ygcode, companyname;- 数据有重复，做个去重- ciss_base_customer：客户信息表【客户id、客户省份名称、所属公司ID】select code, province, company from one_make_dwd.ciss_base_customer;- ciss_base_areas：行政地区信息表- 通过具体的id关联所有地区信息- 实现设计- 所有表按照对应字段关联，获取对应的属性字段
• 小结- 掌握油站维度的需求与设计

02：油站维度构建

• 目标：实现油站维度的构建
• 实施- 建维度表-- 创建油站维度表create external tableifnotexists one_make_dws.dim_oilstation( id string comment'油站ID', name string comment'油站名称', code string comment'油站编码', customer_id string comment'客户ID', customer_name string comment'客户名称', province_id intcomment'省份id', province_name string comment'省份名称', city_id intcomment'城市id', city_name string comment'城市名称', county_id intcomment'县城ID', county_name string comment'县城名称', area_id intcomment'区域id', area_name string comment'区域名称', customer_classify_id string comment'客户分类ID', customer_classify_name string comment'客户分类名称',statusintcomment'油站状态（1、2）', status_name string comment'油站状态名（正常、停用）', company_id intcomment'所属公司ID', company_name string comment'所属公司名称', customer_province_id intcomment'客户所属省份ID', customer_province_name string comment'客户所属省份')COMMENT'油站维度表'PARTITIONED BY(dt STRING)STORED AS TEXTFILELOCATION '/data/dw/dws/one_make/dim_oilstation';- 抽取数据insert overwrite table one_make_dws.dim_oilstation partition(dt ='20210101')select oil.id, oil.name, oil.code, customer_id, customer_name , oil.province province_id, p.areaname province_name , oil.city city_id, c.areaname city_name , oil.region county_id, county.areaname county_name , oil.township area_id, a.areaname area_name , oil.customer_classify customer_classify_id, ede.dictname customer_classify_name , oil.statusstatus, eosde.dictname status_name , cbc.company company_id, binfo.companyname company_name , proname.id customer_province_id, proname.areaname customer_province_namefrom(select id, name, code, customer_id, customer_name, province, city, region, township,status, customer_classify, dt from one_make_dwd.ciss_base_oilstation where id !=''and name isnotnulland name !='null'and customer_id isnotnull) oil leftjoin(select id, areaname, parentid from one_make_dwd.ciss_base_areas where rank =1) p on oil.province = p.id leftjoin(select id, areaname, parentid from one_make_dwd.ciss_base_areas where rank =2) c on oil.city = c.id leftjoin(select id, areaname, parentid from one_make_dwd.ciss_base_areas where rank =3) county on oil.region = county.id leftjoin(select id, areaname, parentid from one_make_dwd.ciss_base_areas where rank =4) a on oil.township = a.id leftjoin(select dictid, dictname from one_make_dwd.eos_dict_entry) ede on oil.customer_classify = ede.dictid leftjoin(select dictid, dictname from one_make_dwd.eos_dict_entry t1 leftjoin one_make_dwd.eos_dict_type t2 on t1.dicttypeid = t2.dicttypeid where t2.dicttypename ='油站状态') eosde on oil.status= eosde.dictid -- 客户所属公司id，所属公司名称，所属省份id，所属省份名称leftjoin(select code, province, company from one_make_dwd.ciss_base_customer) cbc on oil.customer_id = cbc.code leftjoin(select id, areaname from one_make_dwd.ciss_base_areas where rank =1and id !=83) proname on cbc.province = proname.areaname leftjoin(select ygcode, companyname from one_make_dwd.ciss_base_baseinfo groupby ygcode, companyname) binfo on cbc.company = binfo.ygcode where dt ='20210101';- 查看结果
• 小结- 实现油站维度的构建