知识点01：课程回顾
-
项目中有哪些主题域？
- 服务域：工单主题、安装主题
- 客户域：客户事主题
- 仓储域：物料主题
- 运营域：工时主题
- 市场域：工单主题
-
项目中有哪些核心维度？
- 时间维度
- 地区维度
- 油站维度
- 服务站点维度
- 组织机构维度
- 物流维度
- 仓库维度
- ……
-
行政地区维度中有哪些核心字段？
省份id 省份名称 城市id 城市名称 县区id 县区名称 乡镇id 乡镇名称- 定期或者按照变化，全量同步到数据仓库中
-
时间维度中有哪些核心字段？
年 季度 月 周 日 年的第几天 周的第几天 工作日 节假日 周日- 每一年提前将下一年的时间维度信息生成，增量放入数据仓库中
-
服务网点维度中有哪些核心字段？
服务网点id 编码 名称 省份 城市 县区 组织机构id 组织机构名称 -
油站维度中有哪些核心字段？
油站id 油站编码 油站名称 省份 城市 县区 乡镇 客户id 客户名称 公司id 公司名称 -
组织机构维度中有哪些核心字段？
工程师id 工程师名称 岗位id 岗位名称 部门id 部门名称 -
集中问题
- DG连接不上：YARN的进程故障，导致ThriftServer无法运行
- Hadoop：NameNode、DataNode、ResourceManager、NodeManager
- Hive：Metastore、Hiveserver2
- Spark：ThriftServer
- 异常：ProtocolBuffer 不匹配：dim_date
- 数据文件与表的定义是不匹配的
- step1：检查建表语法
- step2：文件：上传时候文件是不对的
- 语法 + 函数 + 数据关系
- 语法 + 函数 ：计算
- 数据关系：逻辑
- DG连接不上：YARN的进程故障，导致ThriftServer无法运行
知识点02：课程目标
-
整体目标：构建数仓中的DWB：主题事务事实表
-
核心的主题事实的构建：SQL实现 + 主题的指标
-
原始事务事实数据【DWD】：订单数据
o001 userid1 2021-01-01 200.00 -
主题事务事实数据【DWB】：订单主题
o001 userid1 2021-01-01 订单总金额:200 订单总个数:1 -
主题周期快照事实表：数据应用层【ST：维度【DWS】 + 事实指标【DWB】】
2021-01-01 订单总金额：xxxx 订单总个数:xxxx
-
-
-
重点内容：SQL以及数据关系
知识点03：分层回顾
-
目标：回顾一站制造项目分层设计
-
实施
-
ODS层 ：原始数据层：101张表：AVRO
-
DWD层：明细数据层：101张表：ORC
-
DWS层：维度数据层：维度表
-
DWB层：轻度汇总层：Join + 构建基础指标
-
小结
- 回顾一站制造项目分层设计
知识点04：DWB层的设计
-
目标：掌握DWB层的设计
-
路径
- step1：功能
- step2：来源
- step3：需求
-
实施
- 功能：存储每个事实主题需要的事务事实数据以及轻度聚合的结果，供ST层基于DWS层进行统计聚合得到最终每个主题的指标
- 关联：将事实主题需要的字段进行关联合并到一张事实表中，构建基于主题的事实
- 聚合：对常用的基础指标基于细粒度实现轻度聚合
- 来源：对DWD层的数据进行关联或者轻度聚合
- 需求：按照一站制造的业务主题的划分需求，构建每个主题的DWB层的数据
- 功能：存储每个事实主题需要的事务事实数据以及轻度聚合的结果，供ST层基于DWS层进行统计聚合得到最终每个主题的指标
-
小结
- 掌握DWB层的设计
知识点05：事实主题指标划分
-
目标：掌握一站制造中的业务主题划分及主题指标的设计
-
实施
- 基本流程
- 油站：有安装、维修、巡检、改造需求
- 呼叫：打电话到呼叫中心下工单
- 呼叫中心可以直接解决：不会有新的工单产生：咨询类寻求
- 呼叫中心不能直接解决：构建工单
- 网点：呼叫中心将工单分派给网点
- 工单：分配工程师确认工单
- 油站：实施具体的工单需求
- 仓储物料：申请仓库调度零配件，有零配件费用
- 差旅费用：交通费用、住宿费用、加油费用、补贴费用
- 回访：呼叫中心将完成的订单进行电话回访
- 呼叫中心事实指标：来电受理次数、分派工单次数
- 油站事实指标：油站个数、停用油站个数、油站设备个数
- 工单事实指标：安装工单个数、维修工单个数
- 安装事实指标：安装个数、安装费用
- 维修事实指标：维度个数、维修费用
- 客户回访事实指标：满意个数、不满意个数、态度满意个数、响应速度的满意个数、技术满意个数
- 费用事实指标：报销费用、差率费用、补贴费用
- 差旅事实指标：油费、住宿费用、交通费用
- 网点物料事实指标：零配件的个数
- ……
- 基本流程
-
小结
- 掌握一站制造中的业务主题划分及主题指标的设计
知识点06：呼叫中心事实指标需求分析
-
目标：掌握DWB层呼叫中心事实指标表的需求
-
路径
- step1：目标需求
- step2：数据来源
-
实施
- 目标需求：基于基础的时间、受理方式、来电类型等事实维度统计工单数量、电话数量、回访数量、投诉数量等
-
数据来源
- ciss_service_callaccept：客服中心来电详情表
- **eos_dict_type**：字典状态类别表，记录所有需要使用字典标记的表
```sql
select * from eos_dict_type where dicttypename = '来电类型';
select * from eos_dict_type where dicttypename = '来电受理单--处理方式';
```
- **eos_dict_entry**：字典状态明细表，记录所有具体的状态或者类别信息
```sql
select * from eos_dict_entry where dicttypeid = 'BUSS_CALL_TYPE';
select * from eos_dict_entry where dicttypeid = 'BUSS_PROCESS_WAY';
```
- **ciss_service_workorder**：工单状态明细表
```sql
select callaccept_id,status from ciss_service_workorder;
-- 查看每个状态的含义
select * from eos_dict_type where dicttypename = '派工单状态';
select * from eos_dict_entry where dicttypeid = 'BUSS_WORKORDER_STATUS';
```
-
小结
- 掌握DWB层呼叫中心事实指标表的需求
知识点07：呼叫中心事实指标构建
-
目标：实现DWB层呼叫中心事实指标表的构建
-
实施
-
建库
create database if not exists one_make_dwb; -
建表
-- 创建呼叫中心 | 来电受理事实表 drop table if exists one_make_dwb.fact_call_service; create table if not exists one_make_dwb.fact_call_service( id string comment '受理id（唯一标识）' , code string comment '受理单唯一编码' , call_date string comment '来电日期（日期id）' , call_hour int comment '来电时间（小时）（事实维度）' , call_type_id string comment '来电类型（事实维度）' , call_type_name string comment '来电类型名称（事实维度）' , process_way_id string comment '受理方式（事实维度）' , process_way_name string comment '受理方式（事实维度）' , oil_station_id string comment '油站id' , userid string comment '受理人员id' , cnt int comment '单据数量（指标列）' , dispatch_cnt int comment '派工数量' , cancellation_cnt int comment '派工单作废数量' , chargeback_cnt int comment '派工单退单数量' , interval int comment '受理时长（单位：秒）' , tel_spt_cnt int comment '电话支持数量' , on_site_spt_cnt int comment '现场安装、维修、改造、巡检数量' , custm_visit_cnt int comment '回访单据数量' , complain_cnt int comment '投诉单据数量' , other_cnt int comment '其他业务单据数量') partitioned by (dt string) stored as orc location '/data/dw/dwb/one_make/fact_call_service'; -
构建数据字典表【每次都要两张表关联，比较麻烦，合并为一张表】
create table if not exists one_make_dwb.tmp_dict stored as orc as select dict_t.dicttypename -- 类型名称 , dict_e.dictid -- 字典编号 , dict_e.dictname -- 字典名称 from one_make_dwd.eos_dict_type dict_t left join one_make_dwd.eos_dict_entry dict_e on dict_t.dt = '20210101' and dict_e.dt = '20210101' and dict_t.dicttypeid = dict_e.dicttypeid order by dict_t.dicttypename, dict_e.dictid;select * from one_make_dwb.tmp_dict where dicttypename = '来电类型'; select * from one_make_dwb.tmp_dict where dicttypename = '来电受理单--处理方式'; -
抽取
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) -- 受理时长（单位：秒） , 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; -
-
小结
- 实现DWB层呼叫中心事实指标表的构建
知识点08：油站事实指标需求分析
-
目标：掌握DWB层油站事实指标表的需求分析
-
路径
- step1：目标需求
- step2：数据来源
-
实施
- 目标需求：基于油站信息及设备数据构建油站主题事实的油站个数、停用个数、新增个数、设备个数等
- 油站数量：1个油站就是一条数据，这个值默认就为1
- 已停用油站数量：停用状态，判断油站的状态是什么状态
- 有效油站数量：使用状态，判断油站的状态是什么状态
- 当日新增油站：判断之前有没有这个油站
- 历史记录表：oil_history：记录了当前所有油站的信息
- id、name
- 今日新数据：oil_current：记录了今天所有油站的信息
- id、name
- left join
```
oil_current a left join oil_history b on a.id = b.id
where b.id is null
```
- 当日停用油站：判断当日状态
- 油站设备数量：得到这个油站的所有设备信息，按照油站id分组统计设备个数
-
数据来源
-
ciss_base_oilstation：油站信息表
select id os_id --油站id , name os_name --油站名称 , code os_code --油站编码 , province province_id --油站省份 , city city_id --油站城市 , region county_id --油站区域 , status status_id --油站状态 , customer_classify cstm_type_id --客户分类id , 1 os_num --油站数量：默认为1 , case when status = 2 then 1 else 0 end invalid_os_num --停用油站数量：1-停用，0-启用 , case when status = 1 then 1 else 0 end valid_os_num --有效油站数量：1-有效，0-无效 from ciss_base_oilstation; -
ciss_base_oilstation_history：油站历史记录表
-
模拟油站历史记录
create table if not exists one_make_dwd.ciss_base_oilstation_history stored as orc as select * from one_make_dwd.ciss_base_oilstation where dt < '20210102'; -
查询历史油站信息
--获取当前的油站是否是一个新增油站 select oil.id , case when oil.id = his.id then 0 else 1 end current_new_os_num --今日油站数据表 from one_make_dwd.ciss_base_oilstation oil --历史油站数据表
left outer join one_make_dwd.ciss_base_oilstation_history his
on oil.id = his.id where oil.dt = ‘20210101’; -
-
ciss_base_device_detail：油站设备信息表
-- 设备信息表中按照油站id分组聚合设备id：每个油站的设备个数 select oil.id, count(dev.id) device_num from one_make_dwd.ciss_base_oilstation oil
left join one_make_dwd.ciss_base_device_detail dev on oil.id = dev.oilstation_id
where oil.dt = ‘20210101’
group by oil.id; -
-
小结
- 掌握DWB层油站事实指标表的需求分析
知识点09：油站事实指标构建
-
目标：实现DWB层油站事实指标表的构建
-
实施
-
建表
-- 创建油站事实表 drop table if exists one_make_dwb.fact_oil_station; create table if not exists one_make_dwb.fact_oil_station( os_id string comment '油站id' , os_name string comment '油站名称' , os_code string comment '油站编码' , province_id string comment '省份id' , city_id string comment '城市id' , county_id string comment '县id' , status_id int comment '状态id' , cstm_type_id int comment '客户分类id' , os_num int comment '油站数量 默认为1' , invalid_os_num int comment '已停用油站数量（状态为已停用为1，否则为0）' , valid_os_num int comment '有效油站数量（状态为启用为1，否则为0）' , current_new_os_num int comment '当日新增油站（新增油站为1，老油站为0）' , current_invalid_os_num int comment '当日停用油站（当天停用的油站数量）' , device_num int comment '油站设备数量' ) comment "油站事实表" partitioned by (dt string) stored as orc location '/data/dw/dwb/one_make/fact_oil_station'; -
抽取
insert overwrite table one_make_dwb.fact_oil_station partition(dt = '20210101') select oil.id os_id --油站id , name os_name --油站名称 , code os_code --油站编码 , province province_id --油站省份 , city city_id --油站城市 , region county_id --油站区域 , status status_id --油站状态 , customer_classify cstm_type_id --客户分类id , 1 os_num --油站数量：默认为1 , case when status = 2 then 1 else 0 end invalid_os_num --停用油站数量：1-停用，0-启用 , case when status = 1 then 1 else 0 end valid_os_num --有效油站数量：1-有效，0-无效 , current_new_os_num --当日新增油站数量，1-新增，0-老油站 , case when current_invalid_os_num is null then 0 else current_invalid_os_num end current_invalid_os_num --当日停用油站数量 , device_num --油站设备数量 --油站信息表 from one_make_dwd.ciss_base_oilstation oil left join ( --关联历史油站表，判断是否为新增油站 select oil.id , case when oil.id = his.id then 0 else 1 end current_new_os_num from one_make_dwd.ciss_base_oilstation oil left outer join one_make_dwd.ciss_base_oilstation_history his on oil.id = his.id where oil.dt = '20210101' ) oilnewhis on oil.id = oilnewhis.id left join ( --关联停用油站数据，统计今日停用油站个数 select oil.id, count(oil.id) current_invalid_os_num from one_make_dwd.ciss_base_oilstation oil where oil.dt = '20210101' and oil.status = 2 group by oil.id ) invalidos on oil.id = invalidos.id left join ( --关联油站设备信息表，统计油站设备个数 select oil.id, count(dev.id) device_num from one_make_dwd.ciss_base_oilstation oil left join one_make_dwd.ciss_base_device_detail dev on oil.id = dev.oilstation_id where oil.dt = '20210101' group by oil.id ) devinfo on oil.id = devinfo.id;
-
-
小结
- 实现DWB层油站事实指标表的构建
知识点10：工单事实指标需求分析
-
目标：掌握DWB层工单事实指标表的需求分析
-
路径
- step1：目标需求
- step2：数据来源
-
实施
-
目标需求：基于工单信息统计等待分配工单数量、完成工单数量、处理工单数量、响应时长、服务时长等指标
字段名 说明 数据来源 wo_id 工单id one_make_dwd.ciss_service_workorder callaccept_id 来电受理单id one_make_dwd.ciss_service_workorder oil_station_id 油站id one_make_dwd.ciss_service_workorder userids 服务该工单用户id(注意：可能会有多个，以逗号分隔) one_make_dwd.ciss_service_workorder wo_num 工单单据数量 one_make_dwd.ciss_service_workorder back_num 退回工单数量（如果工单没有被退回，数量是0） one_make_dwd.ciss_service_workorder、ciss_service_workorder_back abolished_num 已作废工单数量 one_make_dwd.ciss_service_workorder wait_dispatch_num 待派工数量 one_make_dwd.ciss_service_workorder、eos_dict_type、eos_dict_entry派工单状态：待派工（status=4） alread_complete_num 已完工工单数量（已完工、已回访） 派工单状态：已完工、已回访（status=5 || 6） processing_num 正在处理工单数量（待离站、待完工） 派工单状态：待离站、待完工（status=3 || 4） people_num 工单人数数量（一个工单由多人完成） one_make_dwd.ciss_service_workorder、ciss_service_workorder_user默认为1数据预处理；工单用户id是否为空；工单用户id是否为空 service_total_duration 服务总时长（按小时）->从出发到完工时间（leave_time - start_time） one_make_dwd.ciss_service_workorder repair_service_duration 报修响应时长（按小时）->呼叫中心受理到出发时间（start_time-submit_time） one_make_dwd.ciss_service_workorder customer_repair_num 客户报修工单数量 one_make_dwd.ciss_service_workorder；is_customer_repairs字段 charg_num 收费工单数量 one_make_dwd.ciss_service_workorder；is_charg字段 repair_device_num 维修设备数量 ciss_service_order、ciss_service_order_device；状态：维修（type=2） install_device_num 安装设备数据量 ciss_service_order、ciss_service_order_device；状态：安装(type=1) install_num 安装单数量（以下四个单据的数量有可能会有重叠，例如：一个工单有可能有巡检、也有可能有维修） one_make_dwd.ciss_service_install repair_num 维修单数量 ciss4.ciss_service_repair remould_num 巡检单数量 ciss4.ciss_service_remould inspection_num 改造单数量 ciss4.ciss_service_inspection workorder_trvl_exp 工单差旅费(通过工单id与ciss4.ciss_service_trvl_exp_dtl关联，取submoney5即可) ciss4.ciss_service_trvl_exp_dtl -
数据来源
-
ciss_service_workorder：工单详情事实表
select id,--工单id callaccept_id,--来电受理id oil_station_id, --油站id service_userid,--工程师id status,--工单状态 submit_time,--提交时间 start_time,--开始时间 leave_time,--离开时间 is_customer_repairs,--是否为报修工单 is_charg --是否为收费工单 from ciss_service_workorder; -
ciss_service_workorder_back：回退工单信息表
select id, --回退id workorder_id --工单id from ciss_service_workorder_back; -
ciss_service_workorder_user：工程师信息表
select workorder_id, --工单id userid, --工程师id username --工程师姓名 from ciss_service_workorder_user; -
ciss_service_trvl_exp_dtl：差旅费用信息表
select work_order_id, --工单id submoney5 --应收会计扣款金额 from ciss_service_trvl_exp_dtl; -
ciss_service_order：服务单信息表
select id, --服务单id workorder_id, --工单id type --工单类型,1-安装，2-维修，3-巡检 from ciss_service_order; -
ciss_service_order_device：服务单设备信息表
select id, --设备id service_order_id --服务单id from ciss_service_order_device; -
工单类型合并表
- ciss_service_install：设备安装信息表
- 服务单id、安装工单id
- ciss_service_repair：设备维修信息表
- 服务单id、维修工单id
- ciss_service_install：设备安装信息表
-
-
ciss_service_remould：设备改造信息表
- 服务单id、改造工单id- ciss_service_inspection：设备巡检信息表
- 服务单id、巡检工单id
select so.id, --服务单id so.workorder_id, --工单id install.id installid, --安装单id repair.id repairid, --维修单id remould.id remouldid, --改造单id inspection.id inspectionid --巡检单id --服务单信息表 from one_make_dwd.ciss_service_order so left join one_make_dwd.ciss_service_install install on so.id = install.service_id - ciss_service_inspection：设备巡检信息表
left join one_make_dwd.ciss_service_repair repair on so.id = repair.service_id
left join one_make_dwd.ciss_service_remould remould on so.id = remould.service_id
left join one_make_dwd.ciss_service_inspection inspection on so.id = inspection.service_id
where so.dt = ‘20210101’;
``` -
-
小结
- 掌握DWB层呼叫中心事实指标表的需求分析
知识点11：工单事实指标构建
-
目标：实现DWB层工单事实指标表的构建
-
实施
-
建表
drop table if exists one_make_dwb.fact_worker_order; create table if not exists one_make_dwb.fact_worker_order( wo_id string comment '工单id' , callaccept_id string comment '来电受理单id' , oil_station_id string comment '油站id' , userids string comment '服务该工单用户id(注意：可能会有多个，以逗号分隔)' , wo_num bigint comment '工单单据数量' , back_num bigint comment '退回工单数量，默认为0' , abolished_num bigint comment '已作废工单数量' , wait_dispatch_num bigint comment '待派工数量' , wait_departure_num bigint comment '待出发数量' , alread_complete_num bigint comment '已完工工单数量（已完工、已回访）' , processing_num bigint comment '正在处理工单数量（待离站、待完工）' , people_num int comment '工单人数数量（一个工单由多人完成）' , service_total_duration int comment '服务总时长（按小时），(leave_time - start_time)' , repair_service_duration int comment '报修响应时长（按小时），(start_time-submit_time)' , customer_repair_num bigint comment '客户报修工单数量' , charg_num bigint comment '收费工单数量' , repair_device_num bigint comment '维修设备数量' , install_device_num bigint comment '安装设备数据量' , install_num bigint comment '安装单数量' , repair_num bigint comment '维修单数量' , remould_num bigint comment '改造单数量' , inspection_num bigint comment '巡检单数量' , workorder_trvl_exp decimal(20,1) comment '工单差旅费' ) partitioned by (dt string) stored as orc location '/data/dw/dwb/one_make/fact_worker_order' ; -
抽取
insert overwrite table one_make_dwb.fact_worker_order partition(dt = '20210101') select --工单id wo.id wo_id --来电受理单id , max(callaccept_id) callaccept_id --油站id , max(oil_station_id) oil_station_id --工程师id , max(case when wo.service_userids is not null then concat_ws(',', wo.service_userid, wo.service_userids) else wo.service_userid end) userids --工单单据数量：安装单、维修单…… , count(wo.id) wo_num --退回工单数量 , count(wob.id) back_num --已作废工单数量 , sum(case when status = '-1' then 1 else 0 end) abolished_num --待派发工单数量 , sum(case when status = '4' then 1 else 0 end) wait_dispatch_num --待出发工单数量 , sum(case when status = '2' then 1 else 0 end) wait_departure_num --已完工工单数量 , sum(case when status = '5' then 1 when status = '6' then 1 else 0 end) alread_complete_num --处理中工单数量 , sum(case when status = '3' then 1 when status = '4' then 1 else 0 end) processing_num --工单人数 , case when count(usr.id) = 0 then 1 else count(usr.id) end people_num --服务总时长 , max((wo.leave_time - wo.start_time) / 3600000) service_total_duration --报修响应时长 , max((wo.start_time - wo.submit_time) / 3600000) repair_service_duration --客户报修工单数量 , sum(case when wo.is_customer_repairs = '2' then 1 else 0 end) customer_repairs --收费工单数量 , sum(case when wo.is_charg = '1' then 1 else 0 end) charg_num --维修设备数量 , max(case when sod.repair_device_num = 0 then 1 when sod.repair_device_num is null then 0 else sod.repair_device_num end) repair_device_num --安装设备数量 , max(case when sod2.install_device_num = 0 then 1 when sod2.install_device_num is null then 0 else sod2.install_device_num end) install_device_num --安装单数量 , sum(case when sertype.installid is not null then 1 else 0 end) install_num --维修单数量 , sum(case when sertype.repairid is not null then 1 else 0 end) repair_num --改造单数量 , sum(case when sertype.remouldid is not null then 1 else 0 end) remould_num --巡检单数量 , sum(case when sertype.inspectionid is not null then 1 else 0 end) inspection_num --工单差旅费 , max(case when ed.submoney5 is null then 0.0 else ed.submoney5 end) workorder_trvl_exp -- 工单信息表 from one_make_dwd.ciss_service_workorder wo --关联回退工单：回退工单个数 left join one_make_dwd.ciss_service_workorder_back wob on wo.id = wob.workorder_id --关联工程师信息表：工程师人数 left join one_make_dwd.ciss_service_workorder_user usr on wo.id = usr.workorder_id --关联差旅费用信息表：工单差旅费用 left join one_make_dwd.ciss_service_trvl_exp_dtl ed on wo.id = ed.work_order_id --关联维修设备个数信息 left join ( --统计每个工单的维修设备个数 select so.workorder_id, count(sod.id) repair_device_num --服务单表关联设备表：每个工单对应的设备id from one_make_dwd.ciss_service_order so left join one_make_dwd.ciss_service_order_device sod on so.id = sod.service_order_id where so.type = '2' and so.dt='20210101' group by so.workorder_id ) sod on wo.id = sod.workorder_id --关联安装设备个数信息 left join ( --统计每个工单的安装设备个数 select so.workorder_id, count(sod.id) install_device_num from one_make_dwd.ciss_service_order so left join one_make_dwd.ciss_service_order_device sod on so.id = sod.service_order_id --过滤服务单的类型为安装类型的服务单 where so.type = '1' and so.dt='20210101' group by so.workorder_id ) sod2 on wo.id = sod2.workorder_id --工单类型合并表：安装、维修、改造、巡检单id left join ( select so.id, so.workorder_id, install.id installid, repair.id repairid, remould.id remouldid, inspection.id inspectionid from one_make_dwd.ciss_service_order so left join one_make_dwd.ciss_service_install install on so.id = install.service_id left join one_make_dwd.ciss_service_repair repair on so.id = repair.service_id left join one_make_dwd.ciss_service_remould remould on so.id = remould.service_id left join one_make_dwd.ciss_service_inspection inspection on so.id = inspection.service_id where so.dt = '20210101' ) sertype on wo.id = sertype.workorder_id where wo.dt='20210101'
group by wo.id
; -
-
小结
- 实现DWB层呼叫中心事实指标表的构建
知识点12：安装事实指标需求分析
-
目标：掌握DWB层安装事实指标表的需求分析
-
路径
- step1：目标需求
- step2：数据来源
-
实施
- 目标需求：基于设备安装信息统计安装设备个数、收费安装个数、审核安装个数等指标
- 全新安装数量：install_type = 1
- 联调安装数量：install_way = 2
- 产生维修数量：is_repair = 1
- 额外收费数量：is_pay = 1
- 安装设备数量：与服务单关联，统计设备的id个数
- 安装费用：通过工单id从报销单信息中关联得到报销金额
- 审核完成工单个数：
-
数据来源
-
ciss_service_install：安装单信息表
select id,--安装单id code,--安装单号 install_way, --安装方式 service_id --服务单id from ciss_service_install;
-
-
ciss_service_workorder：工单详情事实表
select service_userid,--工程师id service_station_id,--服务站点id oil_station_id,--油站id create_time --创建时间 from ciss_service_workorder; -
ciss_service_order：服务单信息表
select id, --服务单id workorder_id, --工单id type --工单类型,1-安装，2-维修，3-巡检 from ciss_service_order;
-
ciss_service_order_device：服务单设备信息表
select id, --设备id service_order_id --服务单id from ciss_service_order_device; -
ciss_s_install_exp_rep_02_dtl：报销单明细表
select id, --报销ID workorder_id,--工单id money5 --报销金额 from ciss_s_install_exp_rep_02_dtl; -
ciss_service_install_validate：设备安装审核信息表
select id, --审核ID workorder_id,--工单id has_validate --审核状态，1-已审核，0-未审核 from ciss_service_install_validate; -
小结
- 掌握DWB层安装事实指标表的需求分析
知识点13：安装事实指标构建
-
目标：实现DWB层安装事实指标表的构建
-
实施
-
建表
-- 创建安装单事实表 drop table if exists one_make_dwb.fact_srv_install; create table if not exists one_make_dwb.fact_srv_install( inst_id string comment '安装单id' , inst_code string comment '安装单编码' , inst_type_id string comment '安装方式id' , srv_user_id string comment '服务人员用户id' , ss_id string comment '服务网点id' , os_id string comment '油站id' , date_id string comment '日期id' , new_inst_num int comment '全新安装数量' , debug_inst_num int comment '设备联调安装数量' , repair_num int comment '产生维修安装单数量' , ext_exp_num int comment '额外收费安装单数量' , inst_device_num int comment '安装设备数量' , exp_device_money int comment '安装费用' , validated_inst_num int comment '审核安装单数量' ) comment '安装单事实表' partitioned by (dt string) stored as orc location '/data/dw/dwb/one_make/fact_srv_install'; -
抽取
insert overwrite table one_make_dwb.fact_srv_install partition(dt = '20210101') select sinstall.id inst_id --安装单id , sinstall.code inst_code --安装单号 , sinstall.install_way inst_type_id --安装方式 , 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 --创建时间 , new_inst_num --全新安装数量 , debug_inst_num --设备联调安装数量 , repair_num --产生维修安装数量 , ext_exp_num --额外收费安装数量 , inst_device_num --安装设备数量 , exp_device_money --安装费用 , validated_inst_num --已审核安装单数量 --安装信息表 from one_make_dwd.ciss_service_install sinstall --服务单表 left join one_make_dwd.ciss_service_order sorder on sinstall.service_id = sorder.id --工单表 left join one_make_dwd.ciss_service_workorder swo on sorder.workorder_id = swo.id --获取全新、联调、维度、收费的安装数量 left join ( select id, case when install_type = 1 then 1 else 0 end new_inst_num, case when install_way = 2 then 1 else 0 end debug_inst_num, case when is_repair = 1 then 1 else 0 end repair_num, case when is_pay = 1 then 1 else 0 end ext_exp_num from one_make_dwd.ciss_service_install ) installtype on sinstall.id = installtype.id --获取每个服务单的安装设备数量 left join ( select sorder.id, count(sodevice.id) inst_device_num from one_make_dwd.ciss_service_order sorder left join one_make_dwd.ciss_service_order_device sodevice on sorder.id = sodevice.service_order_id group by sorder.id ) sodev on sorder.id = sodev.id --获取每个工单的报销总金额 left join ( select swo.id, sum(dtl.money5) exp_device_money from one_make_dwd.ciss_service_workorder swo left join one_make_dwd.ciss_s_install_exp_rep_02_dtl dtl on swo.id = dtl.workorder_id where dtl.dt = '20210101' and dtl.money5 is not null group by swo.id ) dtl on swo.id = dtl.id --获取每个安装工单的审核状态 left join ( select swo.id, case when ivalida.has_validate = 1 then 1 else 0 end validated_inst_num from one_make_dwd.ciss_service_workorder swo left join one_make_dwd.ciss_service_install_validate ivalida on swo.id = ivalida.workorder_id ) validate on swo.id = validate.id where swo.service_userid is not null and sinstall.dt = '20210101';
-
-
小结
- 实现DWB层安装事实指标表的构建
知识点14：维修事实指标需求分析
-
目标：掌握DWB层维修事实指标表的构建需求分析
-
路径
- step1：目标需求
- step2：数据来源
-
实施
- 目标需求：基于维修信息数据统计维修设备个数、维修、更换、升级配件数量、工时费用、配件费用等指标
-
数据来源
-
ciss_service_repair：维修信息表
select id,--维修单id code,--维修单号 service_id,--服务单id is_pay,--是否收费 1-收费，0-免费 hour_charge,--工时费用 parts_charge,--配件费用 fares_charge --车船费用 from ciss_service_repair; -
ciss_service_order：服务单信息表
select id, --服务单id workorder_id, --工单id type --工单类型,1-安装，2-维修，3-巡检 from ciss_service_order; -
ciss_service_workorder：工单详情事实表
select id,--工单id service_userid,--工程师id service_station_id,--服务站点id oil_station_id,--油站id create_time --创建时间 from ciss_service_workorder; -
ciss_service_order_device：服务单设备信息表
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层维修事实指标表的需求分析
知识点15：维修事实指标构建
-
目标：实现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' ;
-
-
小结
- 实现DWB层维修事实指标表的构建
知识点16：客户回访事实指标需求分析
-
目标：掌握DWB层客户回访事实指标表的需求分析
-
路径
- step1：目标需求
- step2：数据来源
-
实施
- 目标需求：基于客户回访数据统计工单满意数量、不满意数量、返修数量等指标
-
数据来源
-
ciss_service_return_visit：回访信息表
select id,--回访id code,--回访编号 workorder_id,--工单id create_userid, --回访人员id service_attitude,--服务态度 response_speed,--响应速度 repair_level,--服务维修水平 is_repair --是否返修 from ciss_service_return_visit;- 1：满意
- 0：不满意
-
ciss_service_workorder：服务工单信息表
select id,--工单id service_userid,--工程师id service_station_id,--服务站点id oil_station_id --油站id from ciss_service_workorder;
-
-
小结
- 掌握DWB层客户回访事实指标表的需求分析
知识点17：客户回访事实指标
-
目标：实现DWB层客户回访事实指标表的构建
-
实施
-
建表
-- 创建客户回访实时表 drop table if exists one_make_dwb.fact_srv_rtn_visit; create table if not exists one_make_dwb.fact_srv_rtn_visit( vst_id string comment '回访id' , vst_code string comment '回访编号' , wrkodr_id string comment '工单id' , srv_user_id string comment '服务人员用户id' , os_id string comment '油站id' , ss_id string comment '服务网点id' , vst_user_id string comment '回访人员id' , 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 '返修数量' ) comment '客户回访事实表' partitioned by (dt string) stored as orc location '/data/dw/dwb/one_make/fact_srv_rtn_visit'; -
抽取
insert overwrite table one_make_dwb.fact_srv_rtn_visit partition(dt = '20210101') select visit.id vst_id --回访id , visit.code vst_code --回访编号 , visit.workorder_id wrkodr_id --工单id , swo.service_userid srv_user_id --工程师id , swo.oil_station_id os_id --油站id , swo.service_station_id ss_id --服务网点id , visit.create_userid 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 --返修数量 --回访信息表 from one_make_dwd.ciss_service_return_visit visit --工单信息表 left join one_make_dwd.ciss_service_workorder swo on visit.workorder_id = swo.id --获取满意与不满意个数 left join ( select visit.workorder_id, sum(case when visit.service_attitude = 1 and visit.response_speed = 1 and visit.repair_level = 1 then 1 else 0 end) satisfied_num, sum(case when visit.service_attitude = 0 then 1 when visit.response_speed = 0 then 1 when visit.repair_level = 0 then 1 when visit.yawp_problem_type = 0 then 1 else 0 end) unsatisfied_num, sum(case when visit.service_attitude = 1 then 1 else 0 end) srv_atu_num, sum(case when visit.service_attitude = 0 then 1 else 0 end) srv_bad_atu_num, sum(case when visit.repair_level = 1 then 1 else 0 end) srv_rpr_prof_num, sum(case when visit.repair_level = 0 then 1 else 0 end) srv_rpr_unprof_num, sum(case when visit.response_speed = 1 then 1 else 0 end) srv_high_res_num, sum(case when visit.response_speed = 0 then 1 else 0 end) srv_low_res_num, sum(case when visit.is_repair = 1 then 1 else 0 end) rtn_rpr_num from one_make_dwd.ciss_service_return_visit visit left join one_make_dwd.ciss_service_workorder swo on visit.workorder_id = swo.id where visit.dt = '20210101' group by visit.workorder_id ) vstswo on visit.workorder_id = vstswo.workorder_id where visit.dt = '20210101' ;
-
-
小结
- 实现DWB层客户回访事实指标表的构建
知识点18：费用事实指标分析及实现
-
目标：实现DWB层费用报销事实指标表的构建
-
路径
- 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层费用报销事实指标表的构建
知识点19：差旅事实指标分析及实现
-
目标：实现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 ;
-
-
小结
- 实现DWB层差旅报销事实指标表的构建
知识点20：网点物料事实指标分析及实现
-
目标：实现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 ;
-
-
小结
- 实现DWB层网点物料事实指标表的构建
附录一：在线教育项目回顾
01：在线教育项目需求
-
目标：掌握在线教育项目需求
-
实施
- 常规的需求：通过对数据进行数据分析处理，得到一些指标，来反映一些事实，支撑运营决策
- 行业：在线教育行业
- 产品：课程
- 需求：提高学员报名的转换率，实现可持续化的运营发展
- 需求1：分析学员从访问到报名每个环节的留存率和流失率，发现每个环节存在的问题，解决问题，提高报名率
- 访问分析
- 咨询分析
- 意向分析
- 报名分析
- 通过各个环节的分析，来发现每个环节流失原因，解决问题，实现提高每一步转化率
- 需求2：持续化发展需要构建良好的产品口碑，把控学员学习质量：通过对考试、考勤、作业做管理和把控
- 考勤分析
- 需求1：分析学员从访问到报名每个环节的留存率和流失率，发现每个环节存在的问题，解决问题，提高报名率
-
小结
- 掌握在线教育项目需求
-
面试：项目介绍
02：需求主题划分
- 目标：掌握在线教育中需求主题的划分
- 实施
- 数据仓库的数据管理划分
- 数据仓库【DW】：存储了整个公司所有数据
- 数据集市/主题域【DM】：按照一定的业务需求进行划分：部门、业务、需求
- 主题：每一个主题就面向最终的一个业务分析需求
- 数据集市/主题域【DM】：按照一定的业务需求进行划分：部门、业务、需求
- 数据仓库【DW】：存储了整个公司所有数据
- 在线教育中的需求主题
- 数据仓库：业务系统数据【客服系统、CRM系统、学员管理系统】
- 业务数据仓库：结构化数据
- 数据集市/主题域
- 运营管理集市/运营域
- 销售管理集市/销售域
- 学员管理集市/用户域
- 产品管理集市/产品域
- 广告域
- ……
- 数据主题
- 来源分析主题、访问分析主题、咨询分析主题
- 销售分析主题、线索分析主题、意向分析主题、报名分析主题
- 考勤分析主题、考试分析主题、作业分析主题
- 产品访问主题、产品销售主题、产品付费主题
- 表名：层 _ 【域】 _ 主题 _ 维度表
- 数据仓库：业务系统数据【客服系统、CRM系统、学员管理系统】
- 数据仓库的数据管理划分
- 小结
- 掌握在线教育中需求主题的划分
- 面试：项目中划分了哪些主题域以及有哪些主题?
03：数据来源
- 目标：掌握在线教育平台的数据来源
- 实施
- 访问分析主题、咨询分析主题
- 客服系统：客服系统数据库
- 需求：统计不同维度下的访问用户数、咨询用户数
- 指标：UV、PV、IP、Session、跳出率、二跳率
- 维度：时间、地区、来源渠道、搜索来源、来源页面
- web_chat_ems
- web_chat_text_ems
- 线索分析主题、意向分析主题、报名分析主题
- CRM系统：营销系统数据库
- 需求：统计不同维度下意向用户个数、报名用户个数、有效线索个数
- 维度：时间、地区、来源渠道、线上线下、新老学员、校区、学科、销售部门
- customer_relationship：意向与报名信息表
- customer_clue：线索信息表
- customer：学员信息表
- itcast_school：校区信息表
- itcast_subject：学科信息表
- employee：员工信息表
- scrm_deparment：部门信息表
- itcast_clazz：报名班级信息表
- 考勤分析主题
- 数据来源：学员管理系统
- 需求：统计不同维度下学员考勤指标：出勤人数、出勤率、迟到、请假、旷课
- tbh_student_signin_record：学员打卡信息表
- student_leave_apply：学员请假信息表
- tbh_class_time_table：班级作息时间表
- course_table_upload_detail：班级排课表
- class_studying_student_count：班级总人数表
- 访问分析主题、咨询分析主题
- 小结
- 记住核心的表与字段
- 面试：数据来源是什么？
04：数仓设计
-
目标：掌握业务分析主题中每个主题数仓的实现流程
-
实施
-
访问分析主题
- ODS：web_chat_ems、web_chat_text_ems
- DWD：将两张表进行合并，并且实现ETL
- DWS：基于不同维度统计所有访问数据的用户个数、会话个数、Ip个数
-
咨询分析主题
- ODS：web_chat_ems、web_chat_text_ems
- DWD：直接复用了访问分析的DWD
- DWS：基于不同维度统计所有咨询【msg_count > 0】数据的用户个数、会话个数、Ip个数
-
意向分析主题
- ODS：customer_relationship、customer_clue
- DIM：customer、employee、scrm_department、itcast_shcool、itcast_subject
- DWD：对customer_relationship实现ETL
- DWM：实现所有表的关联，将所有维度和事实字段放在一张表中
- DWS：实现基于不同维度的聚合得到意向人数
-
报名分析主题
- ODS：customer_relationship
- DIM：customer、employee、scrm_department、itcast_clazz
- DWD：对customer_relationship实现ETL并且过滤报名数据
- DWM：实现四张表的关联，将所有维度和事实字段放在一张表中
- DWS：基于小时维度对其他组合维度进行聚合得到指标
- APP：基于小时的结果累加得到天、月、年维度下的事实的结果
-
考勤管理主题
- ODS：tbh_student_signin_record、student_leave_apply
- DIM：tbh_class_time_table、course_table_upload_detail、class_studying_student_count
- DWD：没有
- DWM
- 学员出勤状态表：基于学员打卡信息表
- 班级出勤状态表：基于学员出勤状态表
- 班级请假信息表：基于请假信息表得到的
- 班级旷课信息表：总人数 - 出勤人数 - 请假人数
- DWS：基于天构建天+班级维度下的出勤指标：24个
- APP：基于人次进行Sum累加重新计算月、年的出勤指标
-
小结
- 掌握业务分析主题中每个主题数仓的实现流程
- 面试：分层怎么设计的？
- ODS：原始数据层：存储原始数据
- DWD：明细数据层：ETL以后的明细数据
- DWM：轻度汇总层：对主题的事务事实进行构建，关联所有事实表获取主题事实，构建一些基础指标
- DWS：汇总数据层： 构建整个主题域的事实和维度的宽表
- APP：拆分每个主题不同维度的子表
- DIM ：维度数据层：所有维度表
05：技术架构
-
目标：掌握整个项目的技术架构
-
实施
-
数据源：MySQL数据库
-
数据采集：Sqoop
-
数据存储：Hive：离线数据仓库
-
数据处理：HiveSQL【MapReduce】 =》 以后简历中要改为SparkSQL等工具来实现
-
数据结果：MySQL
-
数据报表：FineBI
-
协调服务：Zookeeper
-
可视化交互：Hue
-
任务流调度：Oozie
-
集群管理监控：Cloudera Manager
-
项目版本管理：Git
-
小结
- 掌握整个项目的技术架构
- 面试：项目介绍或者项目的技术架构？
06：项目优化
-
目标：掌握Hive的常见优化
-
实施
-
属性优化
-
本地模式
hive.exec.mode.local.auto=true; -
JVM重用
mapreduce.job.jvm.numtasks=10 -
推测执行
mapreduce.map.speculative=true mapreduce.reduce.speculative=true hive.mapred.reduce.tasks.speculative.execution=true -
Fetch抓取
hive.fetch.task.conversion=more -
并行执行
hive.exec.parallel=true hive.exec.parallel.thread.number=16 -
压缩
hive.exec.compress.intermediate=true hive.exec.orc.compression.strategy=COMPRESSION mapreduce.map.output.compress=true mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.DefaultCodec -
矢量化查询
hive.vectorized.execution.enabled = true; hive.vectorized.execution.reduce.enabled = true; -
零拷贝
hive.exec.orc.zerocopy=true; -
关联优化
hive.optimize.correlation=true; -
CBO优化器
hive.cbo.enable=true; hive.compute.query.using.stats=true; hive.stats.fetch.column.stats=true; hive.stats.fetch.partition.stats=true; -
小文件处理
#设置Hive中底层MapReduce读取数据的输入类：将所有文件合并为一个大文件作为输入 hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; #如果hive的程序，只有maptask，将MapTask产生的所有小文件进行合并 hive.merge.mapfiles=true; hive.merge.mapredfiles=true; hive.merge.size.per.task=256000000; hive.merge.smallfiles.avgsize=16000000; -
索引优化
hive.optimize.index.filter=true -
谓词下推PPD
hive.optimize.ppd=true;
-
-
- Inner Join和Full outer Join，条件写在on后面，还是where后面，性能上面没有区别
- Left outer Join时 ，右侧的表写在on后面，左侧的表写在where后面，性能上有提高
- Right outer Join时，左侧的表写在on后面、右侧的表写在where后面，性能上有提高
- 如果SQL语句中出现不确定结果的函数，也无法实现下推
- Map Join
```properties
hive.auto.convert.join=true
hive.auto.convert.join.noconditionaltask.size=512000000
```
- Bucket Join
```properties
hive.optimize.bucketmapjoin = true;
hive.auto.convert.sortmerge.join=true;
hive.optimize.bucketmapjoin.sortedmerge = true;
hive.auto.convert.sortmerge.join.noconditionaltask=true;
```
- Task内存
```properties
mapreduce.map.java.opts=-Xmx6000m;
mapreduce.map.memory.mb=6096;
mapreduce.reduce.java.opts=-Xmx6000m;
mapreduce.reduce.memory.mb=6096;
```
- 缓冲区大小
```properties
mapreduce.task.io.sort.mb=100
```
- Spill阈值
```properties
mapreduce.map.sort.spill.percent=0.8
```
- Merge线程
```properties
mapreduce.task.io.sort.factor=10
```
- Reduce拉取并行度
```properties
mapreduce.reduce.shuffle.parallelcopies=8
mapreduce.reduce.shuffle.read.timeout=180000
```
-
SQL优化
-
核心思想：先过滤后处理
- where和having使用
- join中on和where使用
- 将大表过滤成为小表再join
-
-
设计优化
-
分区表：减少了MapReduce输入，避免不需要的过滤
-
分桶表：减少了比较次数，实现数据分类，大数据拆分，构建Map Join
-
文件存储：优先选用列式存储：parquet、orc
-
-
小结
- 熟练掌握Hive中的优化
- 面试：项目中做了哪些优化？Hive做了哪些优化？
07：项目问题
-
目标：掌握Hive的常见优化
-
实施
-
内存问题：现象程序运行失败
- OOM：out of memory
-
- 堆内存不足：给Task进程分配更多的内存
```
mapreduce.map.java.opts=-Xmx6000m;
mapreduce.map.memory.mb=6096;
mapreduce.reduce.java.opts=-Xmx6000m;
mapreduce.reduce.memory.mb=6096;
```
- 物理内存不足
- 允许NodeManager使用更多的内存
- 硬件资源可以扩充：扩充物理内存
- 调整代码：基于分区处理、避免Map Join
- 虚拟内存不足：调整虚拟内存的比例，默认为2.1
- 数据倾斜问题：程序运行时间长，一直卡在99%或者100%
- **现象**
- 运行一个程序，这个程序的某一个Task一直在运行，其他的Task都运行结束了，进度卡在99%或者100%
- **基本原因**
- 基本原因：这个ReduceTask的负载要比其他Task的负载要高
- ReduceTask的数据分配不均衡
- **根本原因**：分区的规则
- 默认分区：根据K2的Hash值取余reduce的个数
- 优点：相同的K2会由同一个reduce处理
- 缺点：可能导致数据倾斜
- **数据倾斜的场景**
- group by / count(distinct)
- join
- **解决方案**
- group by / count(distinct)
- 开启Combiner
```
hive.map.aggr=true
```
- 随机分区
- 方式一：开启参数
```
hive.groupby.skewindata=true
```
- 开启这个参数以后，底层会自动走两个MapReduce
- 第一个MapReduce自动实现随机分区
- 第二个MapReduce做最终的聚合
- 方式二：手动指定
```
distribute by rand()：将数据写入随机的分区中
```
```
distribute by 1 ：将数据都写入一个分区
```
- join
- 方案一：尽量避免走Reduce Join
- Map Join：尽量将不需要参加Join的数据过滤，将大表转换为小表
- 构建分桶Bucket Map Join
- 方案二：skewjoin：避免数据倾斜的Reduce Join过程
```sql
--开启运行过程中skewjoin
set hive.optimize.skewjoin=true;
--如果这个key的出现的次数超过这个范围
set hive.skewjoin.key=100000;
--在编译时判断是否会产生数据倾斜
set hive.optimize.skewjoin.compiletime=true;
--不合并，提升性能
set hive.optimize.union.remove=true;
--如果Hive的底层走的是MapReduce，必须开启这个属性，才能实现不合并
set mapreduce.input.fileinputformat.input.dir.recursive=true;
```
-
小结
- 掌握Hive中常见的内存溢出及数据倾斜问题
- 面试：数据倾斜怎么解决？
- 调大分区个数：重分区
- Join时候， 可以将小的数据实现广播
- 自定义分区规则:RDD五大特性：对于二元组类型的RDD，可以指定分区器
- reduceByKey(partitionClass = HashPartition)
-
技术面试：理论为主
-
Hadoop：HDFS读写原理，YARN中程序运行流程、端口号、哪些进程、MapReduce运行过程
-
Hive：SQL语句，函数应用
- 字符串函数、日期函数、判断函数、窗口函数
-
附录二：一站制造项目回顾
01：项目需求
-
目标：掌握项目业务需求
- 这个项目属于哪个行业？
- 为什么要做这个项目？
- 这个项目的目的是什么？
-
实施
-
项目行业：工业互联网大数据
-
项目名称：加油站服务商数据运营管理平台
- 中石化，中石油，中海油、壳牌，道达尔……
-
整体需求
基于加油站的设备安装、维修、巡检、改造等数据进行统计分析 支撑加油站站点的设备维护需求以及售后服务的呼叫中心数据分析 提高服务商服务加油站的服务质量 保障零部件的仓储物流及供应链的需求 实现服务商的所有成本运营核算 -
具体需求
- 运营分析：呼叫中心服务单数、设备工单数、参与服务工程师个数、零部件消耗与供应指标等
- 设备分析：设备油量监控、设备运行状态监控、安装个数、巡检次数、维修次数、改造次数
- 呼叫中心：呼叫次数、工单总数、派单总数、完工总数、核单次数
- 员工分析：人员个数、接单次数、评价次数、出差次数
- 报销统计分析、仓库物料管理分析、用户分析
-
报表
-
-
小结
- 掌握项目业务需求
- 提高公司的服务质量
- 促进公司的运营成本管理
- 掌握项目业务需求
02：业务流程
-
目标：掌握加油站设备维护的主要业务流程
-
实施
- step1：加油站服务商联系呼叫中心，申请服务：安装/巡检/维修/改造加油机
- step2：呼叫中心联系对应服务站点，分派工单：联系站点主管，站点主管分配服务人员
- step3：服务人员确认工单和加油站点信息
- step4：服务人员在指定日期到达加油站，进行设备检修
- step5：如果为安装或者巡检服务，安装或者巡检成功，则服务完成
- step6：如果为维修或者改造服务，需要向服务站点申请物料，物料到达，实施结束，则服务完成
- step7：服务完成，与加油站站点服务商确认服务结束，完成订单核验
- step8：工程师报销过程中产生的费用
- step9：呼叫中心会定期对该工单中的工程师的服务做回访
-
小结
- 掌握加油站设备维护的主要业务流程
- 工单分析、费用分析、物料分析、回访分析
03：技术选型
-
目标：掌握加油站服务商数据运营平台的技术选型
-
实施
-
数据生成：业务数据库系统
- Oracle：工单数据、物料数据、服务商数据、报销数据等
- ERP：企业资源管理系统
- 呼叫中心：所有来电系统业务系统
- CISS：业务系统：客户、工单
-
数据采集
- Sqoop：离线数据库采集
-
数据存储
- Hive【HDFS】：离线数据仓库【表】
-
数据计算
- SparkSQL：类HiveSQL开发方式【面向表】
- 对数据仓库中的结构化数据做处理分析
- 场景：统计分析
- 开发方式
- DSL：使用函数【DSL函数 + RDD函数】
- SQL：使用SQL语句对表的进行处理
- 功能：离线计算 + 实时计算
- 注意：SparkSQL可以解决所有场景的分布式计算，离线计算的选型不仅仅是SparkSQL
- SparkSQL/Impala/Presto
- 使用方式
- Python/Jar：spark-submit
- ETL
- ThriftServer：SparkSQL用于接收SQL请求的服务端，类似于Hive的Hiveserver2
- PyHive :Python连接SparkSQL的服务端，提交SQL语句
- JDBC：Java连接SparkSQL的服务端，提交SQL语句
- spark-sql -f ：运行SQL文件，类似于hive -f
- beeline：交互式命令行，一般用于测试
- Python/Jar：spark-submit
- SparkSQL：类HiveSQL开发方式【面向表】
-
数据应用
- MySQL：结果存储
- Grafana：数据可视化工具
-
监控工具
- Prometheus：服务器性能指标监控工具
-
调度工具
- AirFlow：任务流调度工具：Python
-
技术架构
-
-
小结
- 掌握加油站服务商数据运营平台的技术选型
04：分层整体设计
-
目标：掌握油站分析项目中的分层整体设计
-
实施
-
ODS：原始数据层：最接近于原始数据的层次，直接采集写入层次：原始事务事实表
-
DWD：明细数据层：对ODS层的数据根据业务需求实现ETL以后的结果：ETL以后事务事实表
-
DWB：轻度汇总层：类似于以前讲解的DWM，轻度聚合
- 关联：将主题事实的表进行关联，所有与这个主题相关的字段合并到一张表
- 聚合：基于主题的事务事实构建基础指标
- 主题事务事实表
-
ST：数据应用层：类似于以前讲解的APP，存储每个主题基于维度分析聚合的结果：周期快照事实表
- 供数据分析的报表
-
DM：数据集市：按照不同部门的数据需求，将暂时没有实际主题需求的数据存储
- 做部门数据归档，方便以后新的业务需求的迭代开发
-
DWS：维度数据层：类似于以前讲解的DIM：存储维度数据表
-
数据仓库设计方案
- 从上到下：在线教育：先明确需求和主题，然后基于主题的需求采集数据，处理数据
- 场景：数据应用比较少，需求比较简单
- 上下到上：一站制造：将整个公司所有数据统一化在数据仓库中存储准备，根据以后的需求，动态直接获取数据
- 场景：数据应用比较多，业务比较复杂
- 从上到下：在线教育：先明确需求和主题，然后基于主题的需求采集数据，处理数据
-
小结
- 掌握油站分析项目中的分层整体设计
- ODS：原始数据层
- DWD：明细数据层
- DWB：轻度汇总层
- ST：数据应用层
- DM：数据集市层
- DWS：维度数据层
- 掌握油站分析项目中的分层整体设计
05：分层具体功能
- 目标：掌握油站分析的每层的具体功能
- 实施
- ODS
- 数据内容：存储所有原始业务数据，基本与Oracle数据库中的业务数据保持一致
- 数据来源：使用Sqoop从Oracle中同步采集
- 存储设计：Hive分区表，avro文件格式存储，保留3个月
- DWD
- 数据内容：存储所有业务数据的明细数据
- 数据来源：对ODS层的数据进行ETL扁平化处理得到
- 存储设计：Hive分区表，orc文件格式存储，保留所有数据
- DWB
- 数据内容：存储所有事实与维度的基本关联、基本事实指标等数据
- 数据来源：对DWD层的数据进行清洗过滤、轻度聚合以后的数据
- 存储设计：Hive分区表，orc文件格式存储，保留所有数据
- ST
- 数据内容：存储所有报表分析的事实数据
- 数据来源：基于DWB和DWS层，通过对不同维度的统计聚合得到所有报表事实的指标
- DM
- 数据内容：存储不同部门所需要的不同主题的数据
- 数据来源：对DW层的数据进行聚合统计按照不同部门划分
- DWS
- 数据内容：存储所有业务的维度数据：日期、地区、油站、呼叫中心、仓库等维度表
- 数据来源：对DWD的明细数据中抽取维度数据
- 存储设计：Hive普通表，orc文件 + Snappy压缩
- 特点：数量小、很少发生变化、全量采集
- ODS
- 小结
- 掌握油站分析的每层的具体功能
06：业务系统结构
-
目标：了解一站制造中的业务系统结构
-
实施
-
数据来源
-
业务流程
-
油站站点联系呼叫中心，申请工单
- 呼叫中心分派工单给工程师
-
工程师完成工单
- 工程师费用报销
- 呼叫中心回访工单
-
ERP系统：企业资源管理系统，存储整个公司所有资源的信息
- 所有的工程师、物品、设备产品供应链、生产、销售、财务的信息都在ERP系统中
-
CISS系统：客户服务管理系统，存储所有用户、运营数据
- 工单信息、用户信息
-
呼叫中心系统：负责实现所有客户的需求申请、调度、回访等
- 呼叫信息、分配信息、回访信息
-
-
组织结构
- 运营部（编制人数300人）
- 负责服务策略制定和实施，对服务网络运营过程管理。部门职能包括物料管理、技术支持、服务效率管理、服务质量控制、服务标准化和可视化实施等工作。承担公司基础服务管理方面具体目标责任
- 综合管理部（编制人数280人）
- 下属部门有呼叫中心、信息运维、人事行政、绩效考核与培训、企划部等部门。负责公司市场部、运营部、财务部等专业业务以外的所有职能类工作，包括行政后勤管理、劳动关系、绩效考核与培训、企划宣传、采购需求管理、信息建设及数据分析、公司整体目标和绩效管理等工作。
- 市场部（编制人数50人）
- 负责客户需求开发、服务产品开发、市场拓展与销售管理工作，执行销售策略、承担公司市场、销售方面具体目标责任。
- 财务部（编制人数10人）
- 负责服务公司财务收支、费用报销、报表统计、财务分析等财务管理工作
- 市场销售服务中心（编制人数4000人）
- 负责服务产品销售，设备的安装、维护、修理、改造等工作，严格按照公司管理标准实施日常服务工作
- 运营部（编制人数300人）
-
业务流程
-
-
小结
- 了解一站制造中的业务系统结构
07：业务系统数据
-
目标：熟悉业务系统核心数据表
-
实施
-
切换查看数据库
-
查看数据表
- CISS_BASE：基础数据表
- 报销项目核算、地区信息、服务商信息、设备信息、故障分类、出差补助信息、油站基础信息等
- CISS_SERVICE、CISS_S：服务数据表
- 来电受理单信息、改派记录信息、故障更换材料明细信息、综合报销信息、服务单信息、安装单、维修单、改造单信息
- CISS_MATERIAL、CISS_M：仓储物料表
- 物料申明明细信息、网点物料调配申请等
- ORG：组织机构数据
- 部门信息、员工信息等
- EOS：字典信息表
- 存放不同状态标识的字典
- CISS_BASE：基础数据表
-
核心数据表
-
- 运营分析
- 工单分析、安装分析、维修分析、巡检分析、改造分析、来电受理分析
- 提高服务质量
- 回访分析
- 运营成本核算
- 收入、支持分析
-
小结
- 熟悉业务系统核心数据表
08：一站制造业务主题划分
- 目标：掌握一站制造的主题域及主题的划分
- 实施
- 来源
- 主题域划分：业务或者部门划分
- 业务：客户域、广告域、运营域……
- 部门：运维域、财务域、销售域……
- 数据需求来划分主题
- 运营域：访问分析报表、转化分析报表、用户属性分析报表、订单分析报表
- 主题域划分：业务或者部门划分
- 服务域
- 安装主题：安装方式、支付费用、安装类型
- 工单主题：派工方式、工单总数、派工类型、完工总数、
- 维修主题：支付费用、零部件费用、故障类型
- 派单主题：派单数、派单平均值、派单响应时间
- 费用主题：差旅费、安装费、报销人员统计
- 回访主题：回访人员数、回访工单状态
- 油站主题：油站总数量、油站新增数量
- 客户域
- 客户主题：安装数量、维修数量、巡检数量、回访数量
- 仓储域
- 保内良品核销主题：核销数量、配件金额
- 保内不良品核销主题：核销配件数、核销配件金额
- 送修主题：送修申请、送修物料数量、送修类型
- 调拨主题：调拨状态、调拨数量、调拨设备类型
- 消耗品核销：核销总数、核销设备类型
- 服务商域
- 工单主题：派工方式、工单总数、工单类型、客户类型
- 服务商油站主题：油站数量、油站新增数量
- 运营域
- 运营主题：服务人员工时、维修站分析、平均工单、网点分布
- 市场域
- 市场主题：工单统计、完工明细、订单统计
- 来源
- 小结
- 掌握一站制造的主题域及主题的划分
09：一站制造业务维度设计
-
目标：掌握一站制造业务维度设计
-
实施
- 日期时间维度
- 年维度、季度维度、月维度、周维度、日维度
- 日环比、周环比、月环比、日同比、周同比、月同比
- 环比：同一个周期内的比较
- 同比：上个个周期的比较
- 行政地区维度
- 地区级别：国家维度、省份维度、城市维度、县区维度、乡镇维度
- 服务网点维度
- 网点名称、网点编号、省份、城市、县区、所属机构
- 油站维度
- 油站类型、油站名称、油站编号、客户编号、客户名称、省份、城市、县区、油站状态、所属公司
- 组织机构维度
- 人员编号、人员名称、岗位编号、岗位名称、部门编号、部门名称
- 服务类型维度
- 类型编号、类型名称
- 设备维度
- 设备类型、设备编号、设备名称、油枪数量、泵类型、软件类型
- 故障类型维度
- 一级故障编号、一级故障名称、二级故障编号、二级故障名称
- 物流公司维度
- 物流公司编号、物流公司名称
- ……
- 日期时间维度
-
小结
- 掌握一站制造业务维度设计
10：一站制造业务主题维度矩阵
-
目标：了解一站制造业务主题的维度矩阵
-
实施
-
小结
- 了解一站制造业务主题的维度矩阵
编制人数280人）
- 下属部门有呼叫中心、信息运维、人事行政、绩效考核与培训、企划部等部门。负责公司市场部、运营部、财务部等专业业务以外的所有职能类工作，包括行政后勤管理、劳动关系、绩效考核与培训、企划宣传、采购需求管理、信息建设及数据分析、公司整体目标和绩效管理等工作。
- 市场部（编制人数50人）
- 负责客户需求开发、服务产品开发、市场拓展与销售管理工作，执行销售策略、承担公司市场、销售方面具体目标责任。
- 财务部（编制人数10人）
- 负责服务公司财务收支、费用报销、报表统计、财务分析等财务管理工作
- 市场销售服务中心（编制人数4000人）
- 负责服务产品销售，设备的安装、维护、修理、改造等工作，严格按照公司管理标准实施日常服务工作
-
业务流程
-
小结
- 了解一站制造中的业务系统结构
07：业务系统数据
-
目标：熟悉业务系统核心数据表
-
实施
-
切换查看数据库
[外链图片转存中…(img-yl5a3G6j-1671762017103)]
-
查看数据表
- CISS_BASE：基础数据表
- 报销项目核算、地区信息、服务商信息、设备信息、故障分类、出差补助信息、油站基础信息等
- CISS_SERVICE、CISS_S：服务数据表
- 来电受理单信息、改派记录信息、故障更换材料明细信息、综合报销信息、服务单信息、安装单、维修单、改造单信息
- CISS_MATERIAL、CISS_M：仓储物料表
- 物料申明明细信息、网点物料调配申请等
- ORG：组织机构数据
- 部门信息、员工信息等
- EOS：字典信息表
- 存放不同状态标识的字典
- CISS_BASE：基础数据表
-
核心数据表
- 运营分析
- 工单分析、安装分析、维修分析、巡检分析、改造分析、来电受理分析
- 提高服务质量
- 回访分析
- 运营成本核算
- 收入、支持分析
- 运营分析
-
-
小结
- 熟悉业务系统核心数据表
08：一站制造业务主题划分
- 目标：掌握一站制造的主题域及主题的划分
- 实施
- 来源
- 主题域划分：业务或者部门划分
- 业务：客户域、广告域、运营域……
- 部门：运维域、财务域、销售域……
- 数据需求来划分主题
- 运营域：访问分析报表、转化分析报表、用户属性分析报表、订单分析报表
- 主题域划分：业务或者部门划分
- 服务域
- 安装主题：安装方式、支付费用、安装类型
- 工单主题：派工方式、工单总数、派工类型、完工总数、
- 维修主题：支付费用、零部件费用、故障类型
- 派单主题：派单数、派单平均值、派单响应时间
- 费用主题：差旅费、安装费、报销人员统计
- 回访主题：回访人员数、回访工单状态
- 油站主题：油站总数量、油站新增数量
- 客户域
- 客户主题：安装数量、维修数量、巡检数量、回访数量
- 仓储域
- 保内良品核销主题：核销数量、配件金额
- 保内不良品核销主题：核销配件数、核销配件金额
- 送修主题：送修申请、送修物料数量、送修类型
- 调拨主题：调拨状态、调拨数量、调拨设备类型
- 消耗品核销：核销总数、核销设备类型
- 服务商域
- 工单主题：派工方式、工单总数、工单类型、客户类型
- 服务商油站主题：油站数量、油站新增数量
- 运营域
- 运营主题：服务人员工时、维修站分析、平均工单、网点分布
- 市场域
- 市场主题：工单统计、完工明细、订单统计
- 来源
- 小结
- 掌握一站制造的主题域及主题的划分
09：一站制造业务维度设计
-
目标：掌握一站制造业务维度设计
-
实施
- 日期时间维度
- 年维度、季度维度、月维度、周维度、日维度
- 日环比、周环比、月环比、日同比、周同比、月同比
- 环比：同一个周期内的比较
- 同比：上个个周期的比较
- 行政地区维度
- 地区级别：国家维度、省份维度、城市维度、县区维度、乡镇维度
- 服务网点维度
- 网点名称、网点编号、省份、城市、县区、所属机构
- 油站维度
- 油站类型、油站名称、油站编号、客户编号、客户名称、省份、城市、县区、油站状态、所属公司
- 组织机构维度
- 人员编号、人员名称、岗位编号、岗位名称、部门编号、部门名称
- 服务类型维度
- 类型编号、类型名称
- 设备维度
- 设备类型、设备编号、设备名称、油枪数量、泵类型、软件类型
- 故障类型维度
- 一级故障编号、一级故障名称、二级故障编号、二级故障名称
- 物流公司维度
- 物流公司编号、物流公司名称
- ……
- 日期时间维度
-
小结
- 掌握一站制造业务维度设计
10：一站制造业务主题维度矩阵
-
目标：了解一站制造业务主题的维度矩阵
-
实施
-
小结
- 了解一站制造业务主题的维度矩阵
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
