知识点01：课程回顾
-
数仓主题设计中有哪些主题，以及每个主题的核心指标有哪些？
- DWB：轻度汇总层：基于每个主题，构建主题事务事实表
- 关联：按照主题，将主题需要用到的字段合并到一张表
- 聚合：基础聚合，构建一些基础指标
- 呼叫中心主题：派工次数、回退工单、作废工单
- 工单主题：待派工、处理中、完成工单
- 油站主题：总油站个数、新增油站个数、停用油站个数
- 安装主题：联调安装、全新安装、是否收费
- 维修主题：维修工单、零件个数、设备个数
- 差旅主题：报销金额、车船费用、住宿费用
- DWB：轻度汇总层：基于每个主题，构建主题事务事实表
-
更正呼叫中心事实主题
insert overwrite table one_make_dwb.fact_call_service partition (dt = '20210101') select call.id --来电受理唯一id , call.code -- 受理单唯一编码 , date_format(timestamp(call.call_time), 'yyyymmdd') as call_date -- 来电日期（日期id） , hour(timestamp(call.call_time)) -- 来电时间（小时）（事实维度） , call.call_type -- 来电类型（事实维度） , call_dict.dictname -- 来电类型名称（事实维度） , call.process_way -- 受理方式（事实维度） , process_dict.dictname -- 受理方式（事实维度） , call.call_oilstation_id -- 油站id , call.accept_userid -- 受理人员id , 1 -- 单据数量（指标列） , case when call.process_way = 5 then 1 else 0 end -- 派工数量：0-自己处理，1-产生派工 , case when workorder.status = -1 then 1 else 0 end -- 派工单作废数量 , case when workorder.status = -2 then 1 else 0 end -- 派工单退单数量 --floor(to_unix_timestamp(timestamp(call.process_time),'yyyy-mm-dd hh:mm:ss') - to_unix_timestamp(timestamp(call.call_time), 'yyyy-mm-dd hh:mm:ss') / 1000.0) -- 受理时长（单位：秒） , floor((call.process_time - call.call_time ) / 1000) -- 受理时长（单位：秒） , case when call.call_type = 5 then 1 else 0 end -- 电话支持数量 , case when call.call_type in (1, 2, 3, 4) then 1 else 0 end -- 现场安装、维修、改造、巡检数量 , case when call.call_type = 7 then 1 else 0 end -- 回访单据数量 , case when call.call_type = 8 then 1 else 0 end -- 投诉单据数量 , case when call.call_type = 9 or call.call_type = 6 then 1 else 0 end -- 其他业务单据数量 -- 来电详情表 from one_make_dwd.ciss_service_callaccept call -- 字典信息表：得到来电类型名称 left join one_make_dwb.tmp_dict call_dict on call.call_type = call_dict.dictid and call_dict.dicttypename = '来电类型' -- 字典信息表：受理方式名称 left join one_make_dwb.tmp_dict process_dict on call.process_way = process_dict.dictid and process_dict.dicttypename = '来电受理单--处理方式' -- 工单信息表：得到工单状态：-2:退单，-1:作废 left join one_make_dwd.ciss_service_workorder workorder on workorder.dt = '20210101' and workorder.callaccept_id = call.id where call.dt = '20210101' and call.code != 'null' and call.call_time is not null; -
问题
-
主题、维度、表很多，关系也很复杂，课后怎么去梳理？
- step1：表结构和字段：10个字段
- 对字段进行分类：工单、费用、呼叫中心、油站
- 这张表的作用：熟悉业务
- step2：看业务数据表：desc
- 找每张表中有没有对应字段：大部分字段
- 相关联的业务表：小部分字段
- step3：每张表之间的关系
- step4：子查询 + Join
- step1：表结构和字段：10个字段
-
工作中如何开发SparkSQL实现ETL的需求等等？
-
ETL => DSL + SQL
-
DSL：函数式编程：RDD算子 + DSL函数
- df.map.filter.flatMap.select.where.groupBy
-
SQL：提交SQL语句
# todo:1-构建SparkSession spark = SparkSession.setMaster.appName //todo:2-处理逻辑 //step1:读取数据 inputData = spark.read.text(Path) //step2:处理数据 rsData = inputData.map.flatMap…… //step3:保存结果 rsData.write.saveAsTable() spark.sql("") # todo:3-关闭资源 spark.stopsc =SparkContext # 建库 # 构建连接SparkSQL：pySpark ,pyHive conn.cursor.execute(sql) -
工具使用
- DataGrip：可视化数据库交互工具：测试开发
- SQL测试
- PyCharm | IDEA：所有代码在这里开发：测试开发
- 运行：将代码打包到集群运行
- spark-submit jar file | python file
- ThirftServer
- DataGrip：可视化数据库交互工具：测试开发
-
-
知识点02：课程目标
- 构建ST层：数据应用层
- 掌握每个主题的聚合指标和聚合的维度
- 工单主题
- 油站主题
- 回访主题
- 安装主题
- 费用主题
- DM层的设计
- 运营部门需要的数据抽取
知识点03：数仓分层回顾
-
目标：回顾一站制造项目分层设计
-
实施
- ODS层 ：原始数据层：原始事务事实表
- DWD层：明细数据层：原始事务事实表
- DWS层：维度数据层：维度数据表
- DWB层：轻度聚合层：主题事务事实表
- ST层：数据应用层：主题周期快照表
- 报表
- DM层：数据集市层：每个部门需求的数据
- 部门其他数据应用
-
小结
- 回顾一站制造项目分层设计
知识点04：ST层的设计
-
目标：掌握ST层的设计
-
路径
- step1：功能
- step2：来源
- step3：需求
-
实施
-
功能：数据应用层，用于支撑对外所有主题的报表应用数据的结果
- 对外提供整个公司所有运营的报表
-
来源：对DWB层的主题事实数据关联DWS层的维度表进行最终聚合
-
DWS：维度表：时间、地区、油站、组织机构
维度id 维度值 -
DWB：工单、呼叫中心、费用
维度id 指标
-
-
需求：按照一站制造的业务主题的划分需求，构建每个主题的ST层的数据
维度id 维度值 聚合指标
-
-
小结
- 掌握ST层的设计
知识点05：服务域：工单主题分析
-
目标：掌握工单主题的需求分析
-
路径
- step1：需求
- step2：分析
-
实施
-
需求：统计不同维度下的工单主题指标的结果
字段名称 字段说明 来源 owner_process 派工方式-自己处理数量 one_make_dwb.fact_call_service tran_process 派工方式-转派工数量 one_make_dwb.fact_call_service wokerorder_num 工单总数 one_make_dwb.fact_worker_order wokerorder_num_max 工单总数最大值 one_make_dwb.fact_worker_order wokerorder_num_min 工单总数最小值 one_make_dwb.fact_worker_order wokerorder_num_avg 工单总数平均值 one_make_dwb.fact_worker_order install_sumnum 派工类型-安装总数 one_make_dwb.fact_worker_order repair_sumnum 派工类型-维修总数 one_make_dwb.fact_worker_order remould_sumnum 派工类型-巡检总数 one_make_dwb.fact_worker_order inspection_sumnum 派工类型-改造总数 one_make_dwb.fact_worker_order alread_complete_sumnum 完工总数 one_make_dwb.fact_worker_order customer_classify_zsh 客户类型-中石化数量 one_make_dws.dim_oilstation customer_classify_jxs 客户类型-经销商数量 one_make_dws.dim_oilstation customer_classify_qtzx 客户类型-其他直销数量 one_make_dws.dim_oilstation customer_classify_zsy 客户类型-中石油数量 one_make_dws.dim_oilstation customer_classify_qtwlh 客户类型-其他往来户数量 one_make_dws.dim_oilstation customer_classify_zhjt 客户类型-中化集团数量 one_make_dws.dim_oilstation customer_classify_zhy 客户类型-中海油数量 one_make_dws.dim_oilstation customer_classify_gys 客户类型-供应商数量 one_make_dws.dim_oilstation customer_classify_onemake 客户类型-一站制造**数量 one_make_dws.dim_oilstation customer_classify_fwy 客户类型-服务员数量 one_make_dws.dim_oilstation customer_classify_zt 客户类型-中铁数量 one_make_dws.dim_oilstation customer_classify_hzgs 客户类型-合资公司数量 one_make_dws.dim_oilstation customer_classify_jg 客户类型-军供数量 one_make_dws.dim_oilstation customer_classify_zhhangy 客户类型-中航油数量 one_make_dws.dim_oilstation dws_day string 日期维度-按天 one_make_dws.dim_date dws_week string 日期维度-按周 one_make_dws.dim_date dws_month string 日期维度-按月 one_make_dws.dim_date oil_type string 油站类型 one_make_dws.dim_oilstation oil_province 油站所属省 one_make_dws.dim_oilstation oil_city string 油站所属市 one_make_dws.dim_oilstation oil_county string 油站所属区 one_make_dws.dim_oilstation customer_classify 客户类型 one_make_dws.dim_oilstation customer_province 客户所属省 one_make_dws.dim_oilstation - 呼叫中心主题事实
-
分析
-
指标
- 工单自处理个数、工单转派工个数
- 工单总数、最大值、最小值、平均值
- 安装总数、维修总数、巡检总数、改造总数、完工总数
- 中石化数量、经销商数量、其他直销数量、中石油数量、其他往来户数量、中化集团数量、中海油数量
- 供应商数量、一站制造数量、服务工程师数量、中铁数量、合资公司数量、军供数量、中航油数量
-
维度
- 日期维度：天、周、月
- 油站维度：类型、省份、城市、地区
- 客户维度：类型、省份
-
数据表
-
事实表
-
fact_worker_order：工单事实表
select wo_num, --工单数量 callaccept_id,--来电受理单id oil_station_id, --油站id dt --日期
from fact_worker_order;
- fact_call_service：呼叫中心事实表 ```sql select id,--来电受理单id process_way_name --处理方式 from fact_call_service;[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KMnBhIub-1672022216324)(Day1013_数仓主题应用层ST层构建.assets/image-20211013101145353.png)]
-
-
-
维度表
-
dim_oilstation：油站维度表
select id,--油站id company_name,--公司名称 province_name,--省份名称 city_name,--城市名称 county_name,--区域名称 customer_classify_name,--客户名称 customer_province_name--客户省份 from dim_oilstation; -
dim_date：时间维度表
select date_id,--天 week_in_year_id,--周 year_month_id --月 from dim_date;
-
-
-
实现分析【不能运行】
-- 工单事实表 select sum(case when b.process_way_name = '自己处理' then 1 else 0 end) as own, sum(case when b.process_way_name = '转派工' then 1 else 0 end) as other, sum(a.wo_num), --工单数量 max(a.wo_num), min(a.wo_num), avg(a.wo_num), sum(a.install_num), sum(a.repair_num), sum(a.remould_num), sum(a.inspection_num), sum(a.alread_complete_num), sum(case when c.customer_classify_name = '中石化' then 1 else 0 end) as zsy_count, …… a.callaccept_id,--来电受理单id c.id, --油站id c.company_name,--公司名称 c.province_name,--省份名称 c.city_name,--城市名称 c.county_name,--区域名称 c.customer_classify_name,--客户名称 c.customer_province_name ,--客户省份 d.date_id,--天 d.week_in_year_id,--周 d.year_month_id --月 from fact_worker_order a left join fact_call_service b on a.callaccept_id = b.id left join one_make_dws.dim_oilstation c on a.oil_station_id = c.id left join one_make_dws.dim_date d on a.dt = d.date_id group by c.id, --油站id c.company_name,--公司名称 c.province_name,--省份名称 c.city_name,--城市名称 c.county_name,--区域名称 c.customer_classify_name,--客户名称 c.customer_province_name ,--客户省份 d.date_id,--天 d.week_in_year_id,--周 d.year_month_id; --月;
-
-
小结
- 掌握工单主题的需求分析
知识点06：服务域：工单主题实现
-
目标：实现工单主题表的维度指标构建
-
实施
-
建库
create database if not exists one_make_st; -
建表
-- 创建工单主题表 drop table if exists one_make_st.subj_worker_order; create table if not exists one_make_st.subj_worker_order( owner_process bigint comment '派工方式-自己处理数量' ,tran_process bigint comment '派工方式-转派工数量' ,wokerorder_num bigint comment '工单总数' ,wokerorder_num_max int comment '工单总数最大值' ,wokerorder_num_min int comment '工单总数最小值' ,wokerorder_num_avg int comment '工单总数平均值' ,install_sumnum bigint comment '派工类型-安装总数' ,repair_sumnum bigint comment '派工类型-维修总数' ,remould_sumnum bigint comment '派工类型-巡检总数' ,inspection_sumnum bigint comment '派工类型-改造总数' ,alread_complete_sumnum bigint comment '完工总数' ,customer_classify_zsh bigint comment '客户类型-中石化数量' ,customer_classify_jxs bigint comment '客户类型-经销商数量' ,customer_classify_qtzx bigint comment '客户类型-其他直销数量' ,customer_classify_zsy bigint comment '客户类型-中石油数量' ,customer_classify_qtwlh bigint comment '客户类型-其他往来户数量' ,customer_classify_zhjt bigint comment '客户类型-中化集团数量' ,customer_classify_zhy bigint comment '客户类型-中海油数量' ,customer_classify_gys bigint comment '客户类型-供应商数量' ,customer_classify_onemake bigint comment '客户类型-一站制造**数量' ,customer_classify_fwy bigint comment '客户类型-服务员数量' ,customer_classify_zt bigint comment '客户类型-中铁数量' ,customer_classify_hzgs bigint comment '客户类型-合资公司数量' ,customer_classify_jg bigint comment '客户类型-军供数量' ,customer_classify_zhhangy bigint comment '客户类型-中航油数量' ,dws_day string comment '日期维度-按天' ,dws_week string comment '日期维度-按周' ,dws_month string comment '日期维度-按月' ,oil_type string comment '油站维度-油站类型' ,oil_province string comment '油站维度-油站所属省' ,oil_city string comment '油站维度-油站所属市' ,oil_county string comment '油站维度-油站所属区' ,customer_classify string comment '客户维度-客户类型' ,customer_province string comment '客户维度-客户所属省' ) comment '工单主题表' partitioned by (month String, week String, day String) stored as orc location '/data/dw/st/one_make/subj_worker_order' ; -
构建
insert overwrite table one_make_st.subj_worker_order partition(month = '202101', week='2021W1', day='20210101') select sum(case when fcs.process_way_name = '自己处理' then 1 else 0 end) owner_process, --工单自处理个数 sum(case when fcs.process_way_name = '转派工' then 1 else 0 end) tran_process, --工单转派工个数 sum(fwo.wo_num) wokerorder_num, --工单总数 max(fwo.wo_num) wokerorder_num_max, --最大值 min(fwo.wo_num) wokerorder_num_min, --最小值 avg(fwo.wo_num) wokerorder_num_avg, --平均值 sum(fwo.install_num) install_sumnum, --安装总数 sum(fwo.repair_num) repair_sumnum, --维修总数 sum(fwo.remould_num) remould_sumnum, --巡检总数 sum(fwo.inspection_num) inspection_sumnum, --改造总数 sum(fwo.alread_complete_num) alread_complete_sumnum, --完工总数 sum(case when oil.customer_classify_name ='中石化' then 1 else 0 end) customer_classify_zsh, --中石化数量 sum(case when oil.customer_classify_name ='经销商' then 1 else 0 end) customer_classify_jxs, --经销商数量 sum(case when oil.customer_classify_name ='其他直销' then 1 else 0 end) customer_classify_qtzx, --其他直销数量 sum(case when oil.customer_classify_name ='中石油' then 1 else 0 end) customer_classify_zsy, --中石油数量 sum(case when oil.customer_classify_name ='其他往来户' then 1 else 0 end) customer_classify_qtwlh, --其他往来户数量 sum(case when oil.customer_classify_name ='中化集团' then 1 else 0 end) customer_classify_zhjt, --中化集团数量 sum(case when oil.customer_classify_name ='中海油' then 1 else 0 end) customer_classify_zhy, --中海油数量 sum(case when oil.customer_classify_name ='供应商' then 1 else 0 end) customer_classify_gys, --供应商数量 sum(case when oil.customer_classify_name ='一站制造**' then 1 else 0 end) customer_classify_onemake, --一站制造数量 sum(case when oil.customer_classify_name ='服务工程师' then 1 else 0 end) customer_classify_fwy, --服务工程师数量 sum(case when oil.customer_classify_name ='中铁' then 1 else 0 end) customer_classify_zt, --中铁数量 sum(case when oil.customer_classify_name ='合资公司' then 1 else 0 end) customer_classify_hzgs, --合资公司数量 sum(case when oil.customer_classify_name ='军供' then 1 else 0 end) customer_classify_jg, --军供数量 sum(case when oil.customer_classify_name ='中航油' then 1 else 0 end) customer_classify_zhhangy, --中航油数量 dd.date_id dws_day, --时间天 dd.week_in_year_id dws_week, --时间周 dd.year_month_id dws_month, --时间月 oil.company_name oil_type, --油站类型 oil.province_name oil_province, --油站省份 oil.city_name oil_city, --油站城市 oil.county_name oil_county, --油站地区 oil.customer_classify_name customer_classify, --客户类型 oil.customer_province_name customer_province --客户省份 --工单事务事实表 from one_make_dwb.fact_worker_order fwo --获取自处理个数,转派单个数 left join one_make_dwb.fact_call_service fcs on fwo.callaccept_id = fcs.id --关联日期维度 left join one_make_dws.dim_date dd on fwo.dt = dd.date_id --关联油站维度 left join one_make_dws.dim_oilstation oil on fwo.oil_station_id = oil.id where dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101' --按照维度字段分组 group by dd.date_id, dd.week_in_year_id, dd.year_month_id, oil.company_name, oil.province_name, oil.city_name, oil.county_name,oil.customer_classify_name, oil.customer_province_name ;
-
-
小结
- 实现工单主题表的维度指标构建
知识点07：服务域：油站主题分析
-
目标：掌握油站主题的需求分析
-
路径
- step1：需求
- step2：分析
-
实施
- 需求：统计不同维度下的油站主题指标的结果
-
分析
-
指标：油站数量、新增油站数量
-
维度
- 日期维度：天、周、月
- 油站维度：类型、省份、城市、地区
- 客户维度：类型、省份
-
数据表
-
事实表
-
fact_oil_station：油站事实表
select os_num,--油站个数 current_new_os_num --新增油站个数 from fact_oil_station;
-
-
维度表
-
dim_oilstation：油站维度表
select id,--油站id company_name,--公司名称 province_name,--省份名称 city_name,--城市名称 county_name,--区域名称 customer_classify_name,--客户名称 customer_province_name--客户省份 from dim_oilstation; -
dim_date：时间维度表
select date_id,--天 week_in_year_id,--周 year_month_id --月 from dim_date;
-
-
实现分析
select a.os_id,--油站id sum(a.os_num),--油站数量 sum(a.current_new_os_num),--新增油站数量 b.date_id,--天 b.week_in_year_id,--周 b.year_month_id, --月 c.company_name,--公司名称 c.province_name,--省份名称 c.city_name,--城市名称 c.county_name,--区域名称 c.customer_classify_name,--客户名称 c.customer_province_name--客户省份 from fact_oil_station a join one_make_dws.dim_date b on a.dt = b.date_id join one_make_dws.dim_oilstation c on a.os_id = c.id group by b.date_id,--天 b.week_in_year_id,--周 b.year_month_id, --月 c.company_name,--公司名称 c.province_name,--省份名称 c.city_name,--城市名称 c.county_name,--区域名称 c.customer_classify_name,--客户名称 c.customer_province_name;--客户省份;
-
-
-
小结
- 掌握油站主题的需求分析
知识点08：服务域：油站主题实现
-
目标：实现油站主题表的维度指标构建
-
实施
-
建表
-- 创建油站主题表 drop table if exists one_make_st.subj_oilstation; create table if not exists one_make_st.subj_oilstation( sum_osnum bigint comment '油站数量' ,sumnew_osnum int comment '新增油站数量' ,dws_day string comment '日期维度-按天' ,dws_week string comment '日期维度-按周' ,dws_month string comment '日期维度-按月' ,oil_type string comment '油站维度-油站类型' ,oil_province string comment '油站维度-油站所属省' ,oil_city string comment '油站维度-油站所属市' ,oil_county string comment '油站维度-油站所属区' ,customer_classify string comment '客户维度-客户类型' ,customer_province string comment '客户维度-客户所属省' ) comment '油站主题表' partitioned by (month String, week String, day String) stored as orc location '/data/dw/st/one_make/subj_oilstation'; -
构建
insert overwrite table one_make_st.subj_oilstation partition(month = '202101', week='2021W1', day='20210101') select sum(oil.os_num) sum_osnum, --油站数量 sum(oil.current_new_os_num) sumnew_osnum, --新增油站数量 dd.date_id dws_day, --日期天 dd.week_in_year_id dws_week, --日期周 dd.year_month_id dws_month, --日期月 dimoil.company_name oil_type, --油站类型 dimoil.province_name oil_province, --油站省份 dimoil.city_name oil_city, --油站城市 dimoil.county_name oil_county, --油站区域 dimoil.customer_classify_name customer_classify, --客户类型 dimoil.customer_province_name customer_province --客户省份 --油站事务事实表 from one_make_dwb.fact_oil_station oil --关联日期维度表 left join one_make_dws.dim_date dd on oil.dt = dd.date_id --关联油站维度表 left join one_make_dws.dim_oilstation dimoil on oil.os_id = dimoil.id where dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101' --按照维度字段分组 group by dd.date_id, dd.week_in_year_id, dd.year_month_id, dimoil.company_name, dimoil.province_name, dimoil.city_name, dimoil.county_name, dimoil.customer_classify_name, dimoil.customer_province_name;
-
-
小结
- 实现油站主题表的维度指标构建
知识点09：服务域：安装主题分析实现
-
目标：掌握安装主题的需求分析及实现
-
路径
- step1：需求
- step2：分析
- step3：实现
-
实施
- 需求：统计不同维度下的安装主题指标的结果
-
分析
-
指标：安装数量、支付金额
-
维度
- 安装方式
- 日期维度：天、周、月
- 油站维度：类型、省份、城市、地区
- 客户维度：类型、省份
-
数据
-
事实表
-
fact_srv_install：安装事务事实表
select inst_id, --安装单id inst_type_id, --安装方式id 1-设备安装，2-设备联调 exp_device_money, --安装费用 dt,--日期 os_id --油站id from one_make_dwb.fact_srv_install;
-
-
-
维度表
-
dim_oilstation：油站维度表
select id,--油站id company_name,--公司名称 province_name,--省份名称 city_name,--城市名称 county_name,--区域名称 customer_classify_name,--客户名称 customer_province_name--客户省份 from dim_oilstation; -
dim_date：时间维度表
select date_id,--天 week_in_year_id,--周 year_month_id --月 from dim_date;
-
-
-
实现
-
建表
create table if not exists one_make_st.subj_install( install_way string comment '安装方式' ,install_sum bigint comment '安装数量' ,sum_money int comment '支付费用' ,dws_day string comment '日期维度-按天' ,dws_week string comment '日期维度-按周' ,dws_month string comment '日期维度-按月' ,oil_type string comment '油站维度-油站类型' ,oil_province string comment '油站维度-油站所属省' ,oil_city string comment '油站维度-油站所属市' ,oil_county string comment '油站维度-油站所属区' ,customer_classify string comment '客户维度-客户类型' ,customer_province string comment '客户维度-客户所属省' ) comment '安装主题表' partitioned by (month string, week string, day string) stored as orc location '/data/dw/st/one_make/subj_install'; -
构建
insert overwrite table one_make_st.subj_install partition(month = '202101', week='2021W1', day='20210101') select --安装方式 max(case when install.inst_type_id = 1 then '设备安装' when install.inst_type_id = 2 then '设备联调' else '未知' end) install_way , count(install.inst_id) install_sum --安装数量 , sum(install.exp_device_money) sum_money --支付金额 , dd.date_id dws_day --日期天 , dd.week_in_year_id dws_week --日期周 , dd.year_month_id dws_month --日期月 , dimoil.company_name oil_type --油站类型 , dimoil.province_name oil_province --油站省份 , dimoil.city_name oil_city --油站城市 , dimoil.county_name oil_county --油站地区 , dimoil.customer_classify_name customer_classify --客户类型 , dimoil.customer_province_name customer_province --客户省份 --安装事务事实表 from one_make_dwb.fact_srv_install install --关联日期维度表 left join one_make_dws.dim_date dd on install.dt = dd.date_id --关联油站维度表 left join one_make_dws.dim_oilstation dimoil on install.os_id = dimoil.id where dd.year_month_id = '202101' and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101' --按照维度分组 group by inst_type_id, dd.date_id, dd.week_in_year_id, dd.year_month_id, dimoil.company_name, dimoil.province_name, dimoil.city_name, dimoil.county_name, dimoil.customer_classify_name, dimoil.customer_province_name ;
-
-
小结
- 掌握安装主题的需求分析及实现
知识点10：服务域：维修主题分析实现
-
目标：掌握维修主题的需求分析及实现
-
路径
- step1：需求
- step2：分析
- step3：实现
-
实施
- 需求：统计不同维度下的维修主题指标的结果
-
分析
-
指标
- 支付费用、工时费用、零部件费用、交通费用
- 故障总数、最大数量、平均数量
-
维度
- 日期维度：天、周、月
- 油站维度：类型、省份、城市、地区
- 客户维度：类型、省份
- 物流公司
-
数据
-
事实表
-
fact_srv_repair：维修事务事实表
select hour_money,--工时费用 parts_money,--配件物料费用 fars_money,--交通费用 fault_type_ids, --故障id集合 dt,--日期 os_id,--油站id ss_id --服务网点id from fact_srv_repair;
-
-
fact_srv_stn_ma：网点物料事务事实表
select ss_id,--服务网点id logi_cmp_id --物流公司id from fact_srv_stn_ma;
-
-
维度表
-
dim_oilstation：油站维度表
select id,--油站id company_name,--公司名称 province_name,--省份名称 city_name,--城市名称 county_name,--区域名称 customer_classify_name,--客户名称 customer_province_name--客户省份 from dim_oilstation; -
dim_date：时间维度表
select date_id,--天 week_in_year_id,--周 year_month_id --月 from dim_date; -
dim_logistics：物流维度表
select type_id, --物流公司id type_name --物流公司名称 from one_make_dws.dim_logistics where prop_name = '物流公司';
-
-
-
实现
-
建表
drop table if exists one_make_st.subj_repair; create table if not exists one_make_st.subj_repair( sum_pay_money decimal(20,1) comment '支付费用' ,sum_hour_money decimal(20,1) comment '小时费用' ,sum_parts_money decimal(20,1) comment '零部件费用' ,sum_fars_money decimal(20,1) comment '交通费用' ,sum_faulttype_num bigint comment '故障类型总数' ,max_faulttype_num int comment '故障类型最大数量' ,avg_faulttype_num int comment '故障类型平均数量' ,dws_day string comment '日期维度-按天' ,dws_week string comment '日期维度-按周' ,dws_month string comment '日期维度-按月' ,oil_type string comment '油站维度-油站类型' ,oil_province string comment '油站维度-油站所属省' ,oil_city string comment '油站维度-油站所属市' ,oil_county string comment '油站维度-油站所属区' ,customer_classify string comment '客户维度-客户类型' ,customer_province string comment '客户维度-客户所属省' ,logi_company string comment '物流公司维度-物流公司名称' ) comment '维修主题表' partitioned by (month String, week String, day String) stored as orc location '/data/dw/st/one_make/subj_repair'; -
构建
insert overwrite table one_make_st.subj_repair partition(month = '202101', week='2021W1', day='20210101') select sum(pay_money) sum_pay_money, --支付费用 sum(hour_money) sum_hour_money, --工时费用 sum(parts_money) sum_parts_money, --物料费用 sum(fars_money) sum_fars_money, --交通费用 sum(fault_type_num) sum_faulttype_num, --故障类型总数 max(fault_type_num) max_faulttype_num, --最大故障数量 avg(fault_type_num) avg_faulttype_num, --平均故障数量 dws_day, --日期天 dws_week, --日期周 dws_month, --日期月 oil_type, --油站类型 oil_province, --油站省份 oil_city, --油站城市 oil_county, --油站区域 customer_classify, --客户类型 customer_province, --客户省份 logi_company --物流公司 from ( select (hour_money + parts_money+fars_money) pay_money, hour_money, parts_money, fars_money, case when (size(split(fault_type_ids, ','))) <= 0 then 0 else (size(split(fault_type_ids, ','))) end fault_type_num, dd.date_id dws_day, dd.week_in_year_id dws_week, dd.year_month_id dws_month, dimoil.company_name oil_type, dimoil.province_name oil_province, dimoil.city_name oil_city, dimoil.county_name oil_county, dimoil.customer_classify_name customer_classify, dimoil.customer_province_name customer_province, type_name logi_company --维修事务事实表 from one_make_dwb.fact_srv_repair repair --关联日期维度表 left join one_make_dws.dim_date dd on repair.dt = dd.date_id --关联油站维度表 left join one_make_dws.dim_oilstation dimoil on repair.os_id = dimoil.id --关联网点物料事实表：获取物流公司id left join one_make_dwb.fact_srv_stn_ma fssm on repair.ss_id = fssm.ss_id --关联物流维度表：获取物流公司名称 left join ( select type_id, type_name from one_make_dws.dim_logistics where prop_name = '物流公司' ) dl on fssm.logi_cmp_id = dl.type_id where dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101' and exp_rpr_num = 1 ) repair_tmp group by dws_day, dws_week, dws_month, oil_type, oil_province, oil_city, oil_county,customer_classify, customer_province,logi_company;
-
-
小结
- 掌握维修主题的需求分析与实现
知识点11：服务域：回访主题分析
-
目标：掌握回访主题的需求分析
-
路径
- step1：需求
- step2：分析
-
实施
-
需求：统计不同维度下的回访主题指标的结果
字段名称 字段说明 来源 rtn_srv_num 回访服务人员数量 one_make_dwb.fact_srv_rtn_visit vst_user 回访人员数量 one_make_dwb.fact_srv_rtn_visit wait_dispatch_num 待派工数量 one_make_dwb.fact_worker_order wait_departure_num 待出发数量 one_make_dwb.fact_worker_order alread_complete_num 已完工工单数量 one_make_dwb.fact_worker_order processing_num 正在处理工单数量 one_make_dwb.fact_worker_order satisfied_num 满意数量 one_make_dwb.fact_srv_rtn_visit unsatisfied_num 不满意数量 one_make_dwb.fact_srv_rtn_visit srv_atu_num 服务态度满意数量 one_make_dwb.fact_srv_rtn_visit srv_bad_atu_num 服务态度不满意数量 one_make_dwb.fact_srv_rtn_visit srv_rpr_prof_num 服务维修水平满意数量 one_make_dwb.fact_srv_rtn_visit srv_rpr_unprof_num 服务维修水平不满意数量 one_make_dwb.fact_srv_rtn_visit srv_high_res_num 服务响应速度满意数量 one_make_dwb.fact_srv_rtn_visit srv_low_res_num 服务响应速度不满意数量 one_make_dwb.fact_srv_rtn_visit rtn_rpr_num 返修数量 one_make_dwb.fact_srv_rtn_visit max_vst_user 回访人员最大数量 one_make_dwb.fact_srv_rtn_visit min_vst_user 回访人员最小数量 one_make_dwb.fact_srv_rtn_visit dws_day string 日期维度-按天 one_make_dws.dim_date dws_week string 日期维度-按周 one_make_dws.dim_date dws_month string 日期维度-按月 one_make_dws.dim_date orgname 组织机构-回访人员所属部门 one_make_dws.dim_emporg posiname 组织机构-回访人员所属岗位 one_make_dws.dim_emporg posiname 组织机构-回访人员名称 one_make_dws.dim_emporg oil_type string 油站类型 one_make_dws.dim_oilstation oil_province 油站所属省 one_make_dws.dim_oilstation oil_city string 油站所属市 one_make_dws.dim_oilstation oil_county string 油站所属区 one_make_dws.dim_oilstation customer_classify 客户类型 one_make_dws.dim_oilstation customer_province 客户所属省 one_make_dws.dim_oilstation -
分析
-
指标
- 回访工程师数量、回访人员数量、回访人员最大数量、回访人员最小数量
- 回访事务事实表
- 待派工数量、待出发数量、已完工数量、处理中数量
- 工单事务事实表
- 满意数量、不满意数量、态度满意数量、态度不满意数量、水平满意数量、水平不满意数量、响应速度满意数量、响应速度不满意数量、返修数量
- 回访事务事实表
- 回访工程师数量、回访人员数量、回访人员最大数量、回访人员最小数量
-
维度
- 日期维度：天、周、月
- 日期维度表
- 组织机构维度：人员部门、人员岗位、人员姓名
- 组织机构维度表
- 油站维度：类型、省份、城市、区域
-
客户维度：类型、省份
- 油站维度表
-
数据
-
事实表
-
fact_srv_rtn_visit：回访事务事实表
select srv_user_id,--工程师id vst_user_id,--回访人员id satisfied_num,--满意个数 unsatisfied_num,--不满意个数 srv_atu_num,--态度满意个数 srv_bad_atu_num,--态度不满意个数 srv_rpr_prof_num,--水平满意个数 srv_rpr_unprof_num,--水平不满意个数 srv_high_res_num,--响应速度满意个数 srv_low_res_num,--响应速度不满意个数 rtn_rpr_num, --返修数量 wrkodr_id, --工单id os_id,--油站id dt --日期id from fact_srv_rtn_visit; -
fact_worker_order：工单事务事实表
select wo_id,--工单id wait_dispatch_num,--待派工个数 wait_departure_num,--待出发个数 alread_complete_num,--已完成个数 processing_num --正在处理个数 from fact_worker_order;
-
-
维度表
-
dim_oilstation：油站维度表
select id,--油站id company_name,--公司名称 province_name,--省份名称 city_name,--城市名称 county_name,--区域名称 customer_classify_name,--客户名称 customer_province_name--客户省份 from dim_oilstation; -
dim_date：时间维度表
select date_id,--天 week_in_year_id,--周 year_month_id --月 from dim_date; -
dim_emporg：组织机构维度
select empid,--人员id orgname,--部门名称 posiname,--岗位名称 empname --员工名称 from dim_emporg;
-
-
-
分析
select srv_user_id,--工程师id vst_user_id,--回访人员id satisfied_num,--满意个数 unsatisfied_num,--不满意个数 srv_atu_num,--态度满意个数 srv_bad_atu_num,--态度不满意个数 srv_rpr_prof_num,--水平满意个数 srv_rpr_unprof_num,--水平不满意个数 srv_high_res_num,--响应速度满意个数 srv_low_res_num,--响应速度不满意个数 rtn_rpr_num, --返修数量 b.wait_dispatch_num,--待派工个数 b.wait_departure_num,--待出发个数 b.alread_complete_num,--已完成个数 b.processing_num, --正在处理个数 c.orgname,--部门名称 c.posiname,--岗位名称 c.empname --员工名称 wrkodr_id, --工单id os_id,--油站id dt --日期id from fact_srv_rtn_visit a join fact_worker_order b on a.wrkodr_id = b.wo_id join one_make_dws.dim_emporg c on a.srv_user_id = c.userid join one_make_dws.dim_oilstation d on a.os_id = d.id join one_make_dws.dim_date e on a.dt = e.date_id;
-
-
-
小结
- 掌握回访主题的需求分析
知识点12：服务域：回访主题实现
-
目标：实现回访主题表的维度指标构建
-
实施
-
建表
drop table if exists one_make_st.subj_rtn_visit; create table if not exists one_make_st.subj_rtn_visit( rtn_srv_num int comment '回访服务人员数量' ,vst_user int comment '回访人员数量' ,wait_dispatch_num bigint comment '待派工数量' ,wait_departure_num bigint comment '待出发数量' ,alread_complete_num bigint comment '已完工工单数量' ,processing_num bigint comment '正在处理工单数量' ,satisfied_num int comment '满意数量' ,unsatisfied_num int comment '不满意数量' ,srv_atu_num int comment '服务态度满意数量' ,srv_bad_atu_num int comment '服务态度不满意数量' ,srv_rpr_prof_num int comment '服务维修水平满意数量' ,srv_rpr_unprof_num int comment '服务维修水平不满意数量' ,srv_high_res_num int comment '服务响应速度满意数量' ,srv_low_res_num int comment '服务响应速度不满意数量' ,rtn_rpr_num int comment '返修数量' ,max_vst_user int comment '回访人员最大数量' ,min_vst_user int comment '回访人员最小数量' ,dws_day string comment '日期维度-按天' ,dws_week string comment '日期维度-按周' ,dws_month string comment '日期维度-按月' ,orgname string comment '组织机构维度-回访人员所属部门' ,posiname string comment '组织机构维度-回访人员所属岗位' ,empname string comment '组织机构维度-回访人员名称' ,oil_type string comment '油站维度-油站类型' ,oil_province string comment '油站维度-油站所属省' ,oil_city string comment '油站维度-油站所属市' ,oil_county string comment '油站维度-油站所属区' ,customer_classify string comment '客户维度-客户类型' ,customer_province string comment '客户维度-客户所属省' ) comment '回访主题表' partitioned by (month String, week String, day String) stored as orc location '/data/dw/st/one_make/subj_rtn_visit' ; -
构建
insert overwrite table one_make_st.subj_rtn_visit partition(month = '202101', week='2021W1', day='20210101') select sum(rtn_srv_num) rtn_srv_num, --回访工程师数量 sum(vst_user) vst_user, --回访人员数量 sum(wait_dispatch_sumnum) wait_dispatch_sumnum, --待派工数量 sum(wait_departure_sumnum) wait_departure_sumnum, --待出发数量 sum(alread_complete_sumnum) alread_complete_sumnum, --已完工数量 sum(processing_sumnum) processing_sumnum, --处理中数量 sum(satisfied_sumnum) satisfied_sumnum, --满意数量 sum(unsatisfied_sumnum) unsatisfied_sumnum, --不满意数量 sum(srv_atu_sumnum) srv_atu_sumnum, --态度满意数量 sum(srv_bad_atu_sumnum) srv_bad_atu_sumnum, --态度不满意数量 sum(srv_rpr_prof_sumnum) srv_rpr_prof_sumnum, --水平满意数量 sum(srv_rpr_unprof_sumnum) srv_rpr_unprof_sumnum, --水平不满意数量 sum(srv_high_res_sumnum) srv_high_res_sumnum, --响应速度满意数量 sum(srv_low_res_sumnum) srv_low_res_sumnum, --响应速度不满意数量 sum(rtn_rpr_sumnum) rtn_rpr_sumnum, --返修数量 max(vst_user) max_vst_user, --回访人员最大数量 min(vst_user) min_vst_user, --回访人员最小数量 dws_day, --日期天 dws_week, --日期周 dws_month, --日期月 orgname, --组织机构人员部门 posiname, --组织机构人员岗位 empname, --组织机构人员姓名 oil_type, --油站类型 oil_province, --油站省份 oil_city, --油站城市 oil_county, --油站区域 customer_classify, --客户类型 customer_province --客户省份 from ( --查询得到每个工单的指标 select count(fsrv.srv_user_id) rtn_srv_num, count(fsrv.vst_user_id) vst_user, sum(fwo.wait_dispatch_num) wait_dispatch_sumnum, sum(fwo.wait_departure_num) wait_departure_sumnum, sum(fwo.alread_complete_num) alread_complete_sumnum, sum(fwo.processing_num) processing_sumnum, sum(fsrv.satisfied_num) satisfied_sumnum, sum(fsrv.unsatisfied_num) unsatisfied_sumnum, sum(fsrv.srv_atu_num) srv_atu_sumnum, sum(fsrv.srv_bad_atu_num) srv_bad_atu_sumnum, sum(fsrv.srv_rpr_prof_num) srv_rpr_prof_sumnum, sum(fsrv.srv_rpr_unprof_num) srv_rpr_unprof_sumnum, sum(fsrv.srv_high_res_num) srv_high_res_sumnum, sum(fsrv.srv_low_res_num) srv_low_res_sumnum, sum(fsrv.rtn_rpr_num) rtn_rpr_sumnum, dd.date_id dws_day, dd.week_in_year_id dws_week, dd.year_month_id dws_month, emp.orgname, emp.posiname, emp.empname, dimoil.company_name oil_type, dimoil.province_name oil_province, dimoil.city_name oil_city, dimoil.county_name oil_county, dimoil.customer_classify_name customer_classify, dimoil.customer_province_name customer_province --回访事务事实表 from one_make_dwb.fact_srv_rtn_visit fsrv --关联工单事实表 left join one_make_dwb.fact_worker_order fwo on fsrv.wrkodr_id = fwo.wo_id --关联日期维度表 left join one_make_dws.dim_date dd on fsrv.dt = dd.date_id --关联油站维度表 left join one_make_dws.dim_oilstation dimoil on fsrv.os_id = dimoil.id --关联组织机构维度表 left join one_make_dws.dim_emporg emp on fsrv.vst_user_id = emp.empid where dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101' --按照分组维度聚合 group by fsrv.wrkodr_id, dd.date_id, dd.week_in_year_id, dd.year_month_id, emp.orgname, emp.posiname, emp.empname, dimoil.company_name, dimoil.province_name, dimoil.city_name, dimoil.county_name, dimoil.customer_classify_name, dimoil.customer_province_name )
group by dws_day, dws_week, dws_month, orgname, posiname, empname, oil_type, oil_province, oil_city, oil_county, customer_classify, customer_province
; -
-
小结
- 实现回访主题表的维度指标构建
知识点13：服务域：派单主题分析实现
-
目标：掌握派单主题的需求分析实现
-
路径
- step1：需求
- step2：分析
-
实施
-
需求：统计不同维度下的派单主题指标的结果
字段名称 字段说明 来源 install_sumnum 安装单数量 one_make_dwb.fact_worker_order repair_sumnum 维修单数量 one_make_dwb.fact_worker_order remould_sumnum 巡检单数量 one_make_dwb.fact_worker_order inspection_sumnum 改造单数量 one_make_dwb.fact_worker_order max_wo_num 派单数最大值 one_make_dwb.fact_worker_order min_wo_num 派单数最小值 one_make_dwb.fact_worker_order avg_wo_num 派单数平均值 one_make_dwb.fact_worker_order call_srv_user 呼叫中心派单人 one_make_dwb.fact_call_service max_dispatch_cnt 呼叫中心最大派单 one_make_dwb.fact_call_service min_dispatch_cnt 呼叫中心最小派单 one_make_dwb.fact_call_service avg_dispatch_cnt 呼叫中心平均派单 one_make_dwb.fact_call_service people_wo_num 派单平均值 one_make_dwb.fact_worker_order srv_reps_duration 派单响应时长 one_make_dwb.fact_worker_order srv_duration 服务时长 one_make_dwb.fact_worker_order pepople_sumnum 工单人数 one_make_dwb.fact_worker_order dws_day string 日期维度-按天 one_make_dws.dim_date dws_week string 日期维度-按周 one_make_dws.dim_date dws_month string 日期维度-按月 one_make_dws.dim_date orgname 组织机构-回访人员所属部门 one_make_dws.dim_emporg posiname 组织机构-回访人员所属岗位 one_make_dws.dim_emporg posiname 组织机构-回访人员名称 one_make_dws.dim_emporg oil_type string 油站类型 one_make_dws.dim_oilstation oil_province 油站所属省 one_make_dws.dim_oilstation oil_city string 油站所属市 one_make_dws.dim_oilstation oil_county string 油站所属区 one_make_dws.dim_oilstation customer_classify 客户类型 one_make_dws.dim_oilstation customer_province 客户所属省 one_make_dws.dim_oilstation -
分析
-
指标
- 安装单数量、维修单数量、改造单数量、巡检单数量
- 最大派单数、最小派单数、平均派单数
- 工单主题事实表
- 呼叫中心派单人、呼叫中心最大派单、呼叫中心最小派单、呼叫中心平均派单
- 呼叫中心
- 派单平均值、派单响应时长、服务时长、工单人数
-
维度
- 日期维度：天、周、月
- 组织机构维度：人员部门、人员岗位、人员姓名
- 油站维度：类型、省份、城市、区域
- 客户维度：类型、省份
-
数据
-
事实表
-
fact_call_service：呼叫中心事务事实表
select userid,--受理人员id dispatch_cnt,--派工数量 id,--呼叫受理id oil_station_id, --油站id dt --日期 from fact_call_service; -
fact_worker_order：工单事务事实表
select callaccept_id,--呼叫受理id install_num,--安装数量 repair_num,--维修数量 remould_num,--改造数量 inspection_num,--巡检数量 wo_num, --工单数量 people_num, --工单人数 repair_service_duration,--报修响应时长 service_total_duration --服务总时长 from fact_worker_order;
-
-
-
-
- 维度表
- dim_oilstation：油站维度表
```sql
select
id,--油站id
company_name,--公司名称
province_name,--省份名称
city_name,--城市名称
county_name,--区域名称
customer_classify_name,--客户名称
customer_province_name--客户省份
from dim_oilstation;
```
- dim_date：时间维度表
```sql
select
date_id,--天
week_in_year_id,--周
year_month_id --月
from dim_date;
```
- dim_emporg：组织机构维度
```sql
select
empid,--人员id
orgname,--部门名称
posiname,--岗位名称
empname --员工名称
from dim_emporg;
```
-
实现
-
建表
drop table if exists one_make_st.subj_dispatch; create table if not exists one_make_st.subj_dispatch( install_sumnum int comment '安装单数量' ,repair_sumnum int comment '维修单数量' ,remould_sumnum int comment '改造单数量' ,inspection_sumnum int comment '巡检单数量' ,max_wo_num int comment '派单数最大值' ,min_wo_num int comment '派单数最小值' ,avg_wo_num decimal(20, 1) comment '派单数平均值' ,call_srv_user int comment '呼叫中心派单人' ,max_dispatch_cnt int comment '呼叫中心最大派单' ,min_dispatch_cnt int comment '呼叫中心最小派单' ,avg_dispatch_cnt decimal(20, 1) comment '呼叫中心平均派单' ,people_wo_num decimal(20, 1) comment '派单平均值' ,srv_reps_duration int comment '派单响应时长' ,srv_duration int comment '服务时长' ,pepople_sumnum int comment '工单人数' ,dws_day string comment '日期维度-按天' ,dws_week string comment '日期维度-按周' ,dws_month string comment '日期维度-按月' ,orgname string comment '组织机构维度-回访人员所属部门' ,posiname string comment '组织机构维度-回访人员所属岗位' ,empname string comment '组织机构维度-回访人员名称' ,oil_type string comment '油站维度-油站类型' ,oil_province string comment '油站维度-油站所属省' ,oil_city string comment '油站维度-油站所属市' ,oil_county string comment '油站维度-油站所属区' ,customer_classify string comment '客户维度-客户类型' ,customer_province string comment '客户维度-客户所属省' ) comment '派单主题表' partitioned by (month String, week String, day String) stored as orc location '/data/dw/st/one_make/subj_dispatch' ; -
构建
insert overwrite table one_make_st.subj_dispatch partition(month = '202101', week='2021W1', day='20210101') select sum(fwo.install_num) install_sumnum, --安装单数量 sum(fwo.repair_num) repair_sumnum, --维修单数量 sum(fwo.remould_num) remould_sumnum, --改造单数量 sum(fwo.inspection_num) inspection_sumnum, --巡检单数量 max(fwo.wo_num) max_wo_num, --最大派单数 min(fwo.wo_num) min_wo_num, --最小派单数 avg(fwo.wo_num) avg_wo_num, --平均派单数 sum(fcs.userid) call_srv_user, --呼叫中心派单人 max(fcs.dispatch_cnt) max_dispatch_cnt, --呼叫中心最大派单 min(fcs.dispatch_cnt) min_dispatch_cnt, --呼叫中心最小派单 avg(fcs.dispatch_cnt) avg_dispatch_cnt, --呼叫中心平均派单 sum(fwo.wo_num) / sum(fwo.people_num) people_wo_num, --派单平均值 sum(fwo.repair_service_duration) srv_reps_duration, --派单响应时长 sum(fwo.service_total_duration) srv_duration, --服务时长 sum(fwo.people_num) pepople_sumnum, --工单人数 dd.date_id dws_day, --日期日 dd.week_in_year_id dws_week, --日期周 dd.year_month_id dws_month, --日期月 emp.orgname, --组织机构人员部门 emp.posiname, --组织机构人员岗位 emp.empname, --组织机构人员名称 dimoil.company_name oil_type, --油站类型 dimoil.province_name oil_province, --油站省份 dimoil.city_name oil_city, --油站城市 dimoil.county_name oil_county, --油站区域 dimoil.customer_classify_name customer_classify, --客户类型 dimoil.customer_province_name customer_province --客户省份 --呼叫中心事务事实表 from one_make_dwb.fact_call_service fcs --关联工单事实表 left join one_make_dwb.fact_worker_order fwo on fcs.id = fwo.callaccept_id --关联组织机构维度表 left join one_make_dws.dim_emporg emp on fcs.userid = emp.empid --关联日期维度表 left join one_make_dws.dim_date dd on fcs.dt = dd.date_id --关联油站维度表 left join one_make_dws.dim_oilstation dimoil on fcs.oil_station_id = dimoil.id where dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101' group by dd.date_id, dd.week_in_year_id, dd.year_month_id, emp.orgname, emp.posiname, emp.empname, dimoil.company_name, dimoil.province_name, dimoil.city_name, dimoil.county_name, dimoil.customer_classify_name, dimoil.customer_province_name ;
-
-
小结
- 掌握派单主题的需求分析及实现
知识点14：服务域：费用主题分析
-
目标：掌握费用主题的需求分析
-
路径
- step1：需求
- step2：分析
-
实施
-
需求:统计不同维度下的费用主题指标的结果
字段名称 字段说明 来源 install_money 安装费用 one_make_dwb.fact_srv_install max_install_money 最大安装费用 one_make_dwb.fact_srv_install min_install_money 最小安装费用 one_make_dwb.fact_srv_install avg_install_money 平均安装费用 one_make_dwb.fact_srv_install sumbiz_trip_money 外出差旅费用金额总计 one_make_dwb.fact_trvl_exp sumin_city_traffic_money 市内交通费用金额总计 one_make_dwb.fact_trvl_exp sumhotel_money 住宿费费用金额总计 one_make_dwb.fact_trvl_exp sumfars_money 车船费用金额总计 one_make_dwb.fact_trvl_exp sumsubsidy_money 补助费用金额总计 one_make_dwb.fact_trvl_exp sumroad_toll_money 过桥过路费用金额总计 one_make_dwb.fact_trvl_exp sumoil_money 油费金额总计 one_make_dwb.fact_trvl_exp exp_item_total 差旅费用扣款明细总计 one_make_dwb.fact_regular_exp actual_total_money 差旅费用总额统计 one_make_dwb.fact_trvl_exp sum_secondary_money 差旅费用二阶段扣款总计 one_make_dwb.fact_trvl_exp sum_third_money 差旅费用三阶段扣款总计 one_make_dwb.fact_trvl_exp max_secondary_money 差旅费用二阶段最大扣款总计 one_make_dwb.fact_trvl_exp max_third_money 差旅费用三阶段最大扣款总计 one_make_dwb.fact_trvl_exp sum_srv_user 报销人员总数量 one_make_dwb.fact_trvl_exp max_srv_user 报销人员最大数量 one_make_dwb.fact_trvl_exp min_srv_user 报销人员最小数量 one_make_dwb.fact_trvl_exp avg_srv_user 报销人员平均数量 one_make_dwb.fact_trvl_exp dws_day string 日期维度-按天 one_make_dws.dim_date dws_week string 日期维度-按周 one_make_dws.dim_date dws_month string 日期维度-按月 one_make_dws.dim_date oil_type string 油站类型 one_make_dws.dim_oilstation oil_province 油站所属省 one_make_dws.dim_oilstation oil_city string 油站所属市 one_make_dws.dim_oilstation oil_county string 油站所属区 one_make_dws.dim_oilstation customer_classify 客户类型 one_make_dws.dim_oilstation customer_province 客户所属省 one_make_dws.dim_oilstation -
分析
-
指标
- 安装费用、最大安装费用、最小安装费用、平均安装费用
- 外出差旅费用金额总计、市内交通费用金额总计、住宿费用金额总计、车船费用金额总计、补助费用金额总计、过桥过路费用金额总计、油费金额总计
- 差旅费用扣款明细总计、差旅费用总额统计、差旅费用二阶段扣款总计、差旅费用三阶段扣款总计、差旅费用二阶段最大扣款总计、差旅费用三阶段最大扣款总计
- 报销人员数量、报销人员最大数量、报销人员最小数量、报销人员平均数量
-
维度
- 日期维度：天、周、月
- 油站维度：类型、省份、城市、区域
- 客户维度：类型、省份
-
数据
-
事实表
-
fact_trvl_exp：差旅事务事实表
select biz_trip_money,--外出差旅费用 in_city_traffic_money,--市内交通费用 hotel_money,--住宿费用 fars_money,--车船费用 subsidy_money,--补助费用 road_toll_money,--过桥过路费用 oil_money,--油费 secondary_money,--二单补助费用总计 third_money, --三单补助费用总计 actual_total_money,--费用报销总计 ss_id,--服务网点id srv_user_id,--工程师id dt --日期 from fact_trvl_exp; -
fact_regular_exp：报销事务事实表
select ss_id,--服务网点id srv_user_id,--工程师id exp_item_name --费用项目名称 from fact_regular_exp; -
fact_srv_install：安装事务事实表
select ss_id,--服务网点id exp_device_money,--安装费用 os_id --油站id from fact_srv_install;
-
-
维度表
-
dim_oilstation：油站维度表
select id,--油站id company_name,--公司名称 province_name,--省份名称 city_name,--城市名称 county_name,--区域名称 customer_classify_name,--客户名称 customer_province_name--客户省份 from dim_oilstation; -
dim_date：时间维度表
select date_id,--天 week_in_year_id,--周 year_month_id --月 from dim_date;
-
-
-
-
-
小结
- 掌握费用主题的需求分析
知识点15：服务域：费用主题实现
-
目标：实现费用主题表的维度指标构建
-
实施
-
建表
drop table if exists one_make_st.subj_expense; create table if not exists one_make_st.subj_expense( install_money decimal(20,1) comment '安装费用' ,max_install_money decimal(20,1) comment '最大安装费用' ,min_install_money decimal(20,1) comment '最小安装费用' ,avg_install_money decimal(20,1) comment '平均安装费用' ,sumbiz_trip_money decimal(20, 1) comment '外出差旅费用金额总计' ,sumin_city_traffic_money decimal(20, 1) comment '市内交通费用金额总计' ,sumhotel_money decimal(20, 1) comment '住宿费费用金额总计' ,sumfars_money decimal(20, 1) comment '车船费用金额总计' ,sumsubsidy_money decimal(20, 1) comment '补助费用金额总计' ,sumroad_toll_money decimal(20, 1) comment '过桥过路费用金额总计' ,sumoil_money decimal(20, 1) comment '油费金额总计' ,exp_item_total int comment '差旅费用扣款明细总计' ,actual_total_money decimal(20, 1) comment '差旅费用总额统计' ,sum_secondary_money decimal(20, 1) comment '差旅费用二阶段扣款总计' ,sum_third_money decimal(20, 1) comment '差旅费用三阶段扣款总计' ,max_secondary_money decimal(20, 1) comment '差旅费用二阶段最大扣款总计' ,max_third_money decimal(20, 1) comment '差旅费用三阶段最大扣款总计' ,sum_srv_user int comment '报销人员总数量' ,max_srv_user int comment '报销人员最大数量' ,min_srv_user int comment '报销人员最小数量' ,avg_srv_user int comment '报销人员平均数量' ,dws_day string comment '日期维度-按天' ,dws_week string comment '日期维度-按周' ,dws_month string comment '日期维度-按月' ,oil_type string comment '油站维度-油站类型' ,oil_province string comment '油站维度-油站所属省' ,oil_city string comment '油站维度-油站所属市' ,oil_county string comment '油站维度-油站所属区' ,customer_classify string comment '客户维度-客户类型' ,customer_province string comment '客户维度-客户所属省' ) comment '费用主题表' partitioned by (month String, week String, day String) stored as orc location '/data/dw/st/one_make/subj_expense' ; -
构建
insert overwrite table one_make_st.subj_expense partition(month = '202101', week='2021W1', day='20210101') select sum(install.exp_device_money) install_money, --安装费用 max(install.exp_device_money) max_install_money, --最大安装费用 min(install.exp_device_money) min_install_money, --最小安装费用 avg(install.exp_device_money) avg_install_money, --平均安装费用 sum(fte.biz_trip_money) sumbiz_trip_money, --外出差旅费用金额总计 sum(fte.in_city_traffic_money) sumin_city_traffic_money, --市内交通费用金额总计 sum(fte.hotel_money) sumhotel_money, --住宿费用金额总计 sum(fte.fars_money) sumfars_money, --车船费用金额总计 sum(fte.subsidy_money) sumsubsidy_money, --补助费用金额总计 sum(fte.road_toll_money) sumroad_toll_money, --过桥过路费用金额总计 sum(fte.oil_money) sumoil_money, --油费金额总计 count(distinct fre.exp_item_name) exp_item_total, --差旅费用扣款明细总计 sum(fte.actual_total_money) actual_total_money, --差旅费用总额统计 sum(fte.secondary_money) sum_secondary_money, --差旅费用二阶段扣款总计 sum(fte.third_money) sum_third_money, --差旅费用三阶段扣款总计 max(fte.secondary_money) max_secondary_money, --差旅费用二阶段最大扣款总计 max(fte.third_money) max_third_money, --差旅费用三阶段最大扣款总计 sum(size(split(fre.srv_user_id,','))) sum_srv_user, --报销人员数量 max(size(split(fre.srv_user_id,','))) max_srv_user, --报销人员最大数量 min(size(split(fre.srv_user_id,','))) min_srv_user, --报销人员最小数量 avg(size(split(fre.srv_user_id,','))) avg_srv_user, --报销人员平均数量 dd.date_id dws_day, --日期天 dd.week_in_year_id dws_week, --日期周 dd.year_month_id dws_month, --日期月 dimoil.company_name oil_type, --油站类型 dimoil.province_name oil_province, --油站省份 dimoil.city_name oil_city, --油站城市 dimoil.county_name oil_county, --油站区域 dimoil.customer_classify_name customer_classify, --客户类型 dimoil.customer_province_name customer_province --客户省份 --差旅事务事实表 from one_make_dwb.fact_trvl_exp fte --安装事务事实表 left join one_make_dwb.fact_srv_install install on fte.ss_id = install.ss_id --报销事务事实表 left join one_make_dwb.fact_regular_exp fre on fte.srv_user_id = fre.srv_user_id --关联日期维度表 left join one_make_dws.dim_date dd on fte.dt = dd.date_id --关联油站维度表 left join one_make_dws.dim_oilstation dimoil on install.os_id = dimoil.id where dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101' group by inst_type_id, dd.date_id, dd.week_in_year_id, dd.year_month_id, dimoil.company_name, dimoil.province_name, dimoil.city_name, dimoil.county_name, dimoil.customer_classify_name, dimoil.customer_province_name ;
-
-
小结
- 实现费用主题表的维度指标构建
知识点16：客户域：客户主题分析实现
-
目标：掌握客户主题的需求分析及实现
-
路径
- step1：需求
- step2：分析
- step3：实现
-
实施
-
需求
字段名称 字段说明 来源 sum_install_num 安装数量 one_make_dwb.fact_worker_order max_install_num 安装最大数量 one_make_dwb.fact_worker_order min_install_num 安装最小数量 one_make_dwb.fact_worker_order avg_min_install_num 安装平均数量 one_make_dwb.fact_worker_order sum_repair_num 维修数量 one_make_dwb.fact_worker_order max_repair_num 维修最大数量 one_make_dwb.fact_worker_order min_repair_num 维修最小数量 one_make_dwb.fact_worker_order avg_repair_num 维修平均数量 one_make_dwb.fact_worker_order sum_wo_num 派工数量 one_make_dwb.fact_worker_order max_sum_wo_num 派工最大数量 one_make_dwb.fact_worker_order min_sum_wo_num 派工最小数量 one_make_dwb.fact_worker_order avg_wo_num 派工平均数量 one_make_dwb.fact_worker_order sum_remould_num 巡检数量 one_make_dwb.fact_worker_order max_remould_num 巡检最大数量 one_make_dwb.fact_worker_order min_remould_num 巡检最小数量 one_make_dwb.fact_worker_order avg_remould_num 巡检平均数量 one_make_dwb.fact_worker_order sum_alread_complete_num 回访数量 one_make_dwb.fact_worker_order max_alread_complete_num 回访最大数量 one_make_dwb.fact_worker_order min_alread_complete_num 回访最小数量 one_make_dwb.fact_worker_order avg_alread_complete_num 回访平均数量 one_make_dwb.fact_worker_order dws_day string 日期维度-按天 one_make_dws.dim_date dws_week string 日期维度-按周 one_make_dws.dim_date dws_month string 日期维度-按月 one_make_dws.dim_date oil_type string 油站类型 one_make_dws.dim_oilstation oil_province 油站所属省 one_make_dws.dim_oilstation oil_city string 油站所属市 one_make_dws.dim_oilstation oil_county string 油站所属区 one_make_dws.dim_oilstation customer_classify 客户类型 one_make_dws.dim_oilstation customer_province 客户所属省 one_make_dws.dim_oilstation -
分析
-
指标
- 安装数量、最大安装数量、最小安装数量、平均安装数量
- 维修数量、最大维修数量、最小维修数量、平均维修数量
- 派工数量、最大派工数量、最小派工数量、平均派工数量
- 改造数量、最大改造数量、最小改造数量、平均改造数量
- 回访数量、最大回访数量、最小回访数量、平均回访数量
-
维度
- 日期维度：天、周、月
- 油站维度：类型、省份、城市、区域
- 客户维度：类型、省份
-
数据
-
事实表
-
fact_worker_order：工单事务事实表
select install_num,--安装数 repair_num,--维修数 remould_num,--改造数 wo_num,--工单数 alread_complete_num,--完成数，已回访 oil_station_id, --油站id dt --日期 from fact_worker_order;
-
-
维度表
-
dim_oilstation：油站维度表
select id,--油站id company_name,--公司名称 province_name,--省份名称 city_name,--城市名称 county_name,--区域名称 customer_classify_name,--客户名称 customer_province_name--客户省份 from dim_oilstation; -
dim_date：时间维度表
select date_id,--天 week_in_year_id,--周 year_month_id --月 from dim_date;
-
-
-
分析
-
-
实现
-
建表
drop table if exists one_make_st.subj_customer; create table if not exists one_make_st.subj_customer( sum_install_num int comment '安装数量' ,max_install_num int comment '安装最大数量' ,min_install_num int comment '安装最小数量' ,avg_min_install_num decimal(20, 1) comment '安装平均数量' ,sum_repair_num int comment '维修数量' ,max_repair_num int comment '维修最大数量' ,min_repair_num int comment '维修最小数量' ,avg_repair_num decimal(20, 1) comment '维修平均数量' ,sum_wo_num int comment '派工数量' ,max_sum_wo_num int comment '派工最大数量' ,min_sum_wo_num int comment '派工最小数量' ,avg_sum_wo_num decimal(20, 1) comment '派工平均数量' ,sum_remould_num int comment '巡检数量' ,max_remould_num int comment '巡检最大数量' ,min_remould_num int comment '巡检最小数量' ,avg_remould_num decimal(20, 1) comment '巡检平均数量' ,sum_alread_complete_num int comment '回访数量' ,max_alread_complete_num int comment '回访最大数量' ,min_alread_complete_num int comment '回访最小数量' ,avg_alread_complete_num decimal(20, 1) comment '回访平均数量' ,dws_day string comment '日期维度-按天' ,dws_week string comment '日期维度-按周' ,dws_month string comment '日期维度-按月' ,oil_type string comment '油站维度-油站类型' ,oil_province string comment '油站维度-油站所属省' ,oil_city string comment '油站维度-油站所属市' ,oil_county string comment '油站维度-油站所属区' ,customer_classify string comment '客户维度-客户类型' ,customer_province string comment '客户维度-客户所属省' ) comment '客户主题表' partitioned by (month String, week String, day String) stored as orc location '/data/dw/st/one_make/subj_customer' ; -
构建
insert overwrite table one_make_st.subj_customer partition(month = '202101', week='2021W1', day='20210101') select sum(fwo.install_num) sum_install_num, --安装数量 max(fwo.install_num) max_install_num, --最大安装数量 min(fwo.install_num) min_install_num, --最小安装数量 avg(fwo.install_num) avg_min_install_num, --平均安装数量 sum(fwo.repair_num) sum_repair_num, --维修数量 max((fwo.repair_num)) max_repair_num, --最大维修数量 min(fwo.repair_num) min_repair_num, --最小维修数量 avg((fwo.repair_num)) avg_repair_num, --平均维修数量 sum(fwo.wo_num) sum_wo_num, --派工数量 max(fwo.wo_num) max_sum_wo_num, --最大派工数量 min(fwo.wo_num) min_sum_wo_num, --最小派工数量 avg(fwo.wo_num) avg_wo_num, --平均派工数量 sum(fwo.remould_num) sum_remould_num, --改造数量 max(fwo.remould_num) max_remould_num, --最大改造数量 min(fwo.remould_num) min_remould_num, --最小改造数量 avg(fwo.remould_num) avg_remould_num, --平均改造数量 sum(fwo.alread_complete_num) sum_alread_complete_num, --回访数量 max(fwo.alread_complete_num) max_alread_complete_num, --最大回访数量 min(fwo.alread_complete_num) min_alread_complete_num, --最小回访数量 avg(fwo.alread_complete_num) avg_alread_complete_num, --平均回访数量 dd.date_id dws_day, --日期天 dd.week_in_year_id dws_week, --日期周 dd.year_month_id dws_month, --日期月 dimoil.company_name oil_type, --油站类型 dimoil.province_name oil_province, --油站省份 dimoil.city_name oil_city, --油站城市 dimoil.county_name oil_county, --油站区域 dimoil.customer_classify_name customer_classify, --客户类型 dimoil.customer_province_name customer_province --客户省份 --工单事务事实表 from one_make_dwb.fact_worker_order fwo --日期维度表 left join one_make_dws.dim_date dd on fwo.dt = dd.date_id --油站维度表 left join one_make_dws.dim_oilstation dimoil on fwo.oil_station_id = dimoil.id where dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101' group by dd.date_id, dd.week_in_year_id, dd.year_month_id, dimoil.company_name, dimoil.province_name, dimoil.city_name, dimoil.county_name, dimoil.customer_classify_name, dimoil.customer_province_name ;
-
-
-
小结
- 掌握客户主题的需求分析及实现
知识点17：物料域：主题模型
-
目标：了解物料域主题的设计模型
-
路径
- step1：良品核销主题模型
- step2：不良品核销主题模型
-
实施
- 良品核销主题模型
- 不良品核销主题模型
-
小结
- 了解物料域主题的设计模型
知识点18：DM层：设计及运营部门主题
-
目标：掌握DM层的设计
-
路径
- step1：DM层设计
- step2：运营部门主题
-
实施
-
DM层设计
- 功能：数据集市层，用于支撑对每个部门的各种数据的需求
- 来源：对DW层的数据按照一定的部门分类进行抽取
-
运营部门主题
-
需求：统计不同维度下的运营主题指标
-
实现
-
建库
create database if not exists one_make_dm; -
建表
drop table if exists one_make_dm.mart_operation_dept; create table if not exists one_make_dm.mart_operation_dept( wo_id string comment '工单ID' ,userids string comment '工单服务用户ID' ,callaccept_id string comment '来电受理ID' ,oil_station_id string comment '油站ID' ,os_name string comment '油站名称' ,service_total_duration decimal(20,2) comment '服务工时(小时)' ,repair_num bigint comment '维修工单数量' ,wo_num bigint comment '工单数量' ,avg_wo_num int comment '平均工单' ,sum_os_online int comment '加油机在线设备总数' ,atu_num_rate decimal(5,2) comment '客户回访满意度率' ,rtn_visit_duration decimal(20,2) comment '来电受理时长(小时)' ,dws_day string comment '日期维度-按天' ,dws_week string comment '日期维度-按周' ,dws_month string comment '日期维度-按月' ) comment '运营部数据集市表' partitioned by (month String, week String, day String) stored as orc location '/data/dw/dm/one_make/mart_operation_dept'; -
构建
insert overwrite table one_make_dm.mart_operation_dept partition(month = '202101', week='2021W1', day='20210101') select fwo.wo_id --工单id , max(fwo.userids) userids --工程师id , max(fwo.callaccept_id) callaccept_id --来电受理id , max(fwo.oil_station_id) oil_station_id --油站id , max(fos.os_name) os_name --油站名称 , max(fwo.service_total_duration) service_total_duration --服务工时 , sum(fwo.wo_num) wo_num --维修工单数量 , count(fos.os_id) sum_os_num --工单数量 , avg(fwo.wo_num) avg_wo_num --平均工单 , sum(fos.valid_os_num) sum_os_online --加油机在线设备总数 , max(fsrv.srv_atu_num / (fsrv.srv_atu_num + fsrv.srv_bad_atu_num)) atu_num_rate --客户回访满意度 , max(fcs.interval / 3600.0) rtn_visit_duration --来电受理时长 , dd.date_id dws_day --日期天 , dd.week_in_year_id dws_week --日期周 , dd.year_month_id dws_month --日期月 --工单事务事实表 from one_make_dwb.fact_worker_order fwo --油站事务事实表 left join one_make_dwb.fact_oil_station fos on fwo.oil_station_id = fos.os_id --回访事务事实表 left join one_make_dwb.fact_srv_rtn_visit fsrv on fwo.wo_id = fsrv.wrkodr_id --呼叫中心事务事实表 left join one_make_dwb.fact_call_service fcs on fwo.callaccept_id = fcs.id --日期维度表 left join one_make_dws.dim_date dd on fwo.dt = dd.date_id where dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101' group by fwo.wo_id, dd.date_id, dd.week_in_year_id, dd.year_month_id;
-
-
-
-
小结
-
掌握DM层的设计
–工单id
, max(fwo.userids) userids --工程师id
, max(fwo.callaccept_id) callaccept_id --来电受理id
, max(fwo.oil_station_id) oil_station_id --油站id
, max(fos.os_name) os_name --油站名称
, max(fwo.service_total_duration) service_total_duration --服务工时
, sum(fwo.wo_num) wo_num --维修工单数量
, count(fos.os_id) sum_os_num --工单数量
, avg(fwo.wo_num) avg_wo_num --平均工单
, sum(fos.valid_os_num) sum_os_online --加油机在线设备总数
, max(fsrv.srv_atu_num / (fsrv.srv_atu_num + fsrv.srv_bad_atu_num)) atu_num_rate --客户回访满意度
, max(fcs.interval / 3600.0) rtn_visit_duration --来电受理时长
, dd.date_id dws_day --日期天
, dd.week_in_year_id dws_week --日期周
, dd.year_month_id dws_month --日期月
–工单事务事实表
from one_make_dwb.fact_worker_order fwo
–油站事务事实表
left join one_make_dwb.fact_oil_station fos on fwo.oil_station_id = fos.os_id
–回访事务事实表
left join one_make_dwb.fact_srv_rtn_visit fsrv on fwo.wo_id = fsrv.wrkodr_id
–呼叫中心事务事实表
left join one_make_dwb.fact_call_service fcs on fwo.callaccept_id = fcs.id
–日期维度表
left join one_make_dws.dim_date dd on fwo.dt = dd.date_id
where dd.year_month_id = '202101’and dd.week_in_year_id = ‘2021W1’ and dd.date_id = ‘20210101’
group by fwo.wo_id, dd.date_id, dd.week_in_year_id, dd.year_month_id;```
-
-
小结
- 掌握DM层的设计
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
