黑马一站制造数仓实战6

- 项目中有哪些主题域？
  - 服务域：工单主题、安装主题
  - 客户域：客户事主题
  - 仓储域：物料主题
  - 运营域：工时主题
  - 市场域：工单主题
- 项目中有哪些核心维度？
  - 时间维度
  - 地区维度
  - 油站维度
  - 服务站点维度
  - 组织机构维度
  - 物流维度
  - 仓库维度
  - ……
- 行政地区维度中有哪些核心字段？

省份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：文件：上传时候文件是不对的
  - 语法 + 函数 + 数据关系
    - 语法 + 函数 ：计算

- 整体目标：构建数仓中的DWB：主题事务事实表
  核心的主题事实的构建：SQL实现 + 主题的指标

o001        userid1        2021-01-01    200.00

主题事务事实数据【DWB】：订单主题

o001        userid1        2021-01-01    订单总金额:200        订单总个数:1

主题周期快照事实表：数据应用层【ST：维度【DWS】 + 事实指标【DWB】】

2021-01-01    订单总金额：xxxx        订单总个数:xxxx

一站制造项目分层设计

- ODS层 ：原始数据层：101张表：AVRO
- DWD层：明细数据层：101张表：ORC
- DWS层：维度数据层：维度表
- DWB层：轻度汇总层：Join + 构建基础指标

DWB层的设计

实施

- 功能：存储每个事实主题需要的事务事实数据以及轻度聚合的结果，供ST层基于DWS层进行统计聚合得到最终每个主题的指标
  - 关联：将事实主题需要的字段进行关联合并到一张事实表中，构建基于主题的事实
  - 聚合：对常用的基础指标基于细粒度实现轻度聚合
- 来源：对DWD层的数据进行关联或者轻度聚合
- 需求：按照一站制造的业务主题的划分需求，构建每个主题的DWB层的数据

- 业务主题划分及主题指标的设计
- 实施
  - 基本流程
    - 油站：有安装、维修、巡检、改造需求
    - 呼叫：打电话到呼叫中心下工单
      - 呼叫中心可以直接解决：不会有新的工单产生：咨询类寻求
      - 呼叫中心不能直接解决：构建工单
    - 网点：呼叫中心将工单分派给网点
    - 工单：分配工程师确认工单
    - 油站：实施具体的工单需求
    - 仓储物料：申请仓库调度零配件，有零配件费用
    - 差旅费用：交通费用、住宿费用、加油费用、补贴费用
    - 回访：呼叫中心将完成的订单进行电话回访
  - 呼叫中心事实指标：来电受理次数、分派工单次数
  - 油站事实指标：油站个数、停用油站个数、油站设备个数
  - 工单事实指标：安装工单个数、维修工单个- 数
- 安装事实指标：安装个数、安装费用
- 维修事实指标：维度个数、维修费用
- 客户回访事实指标：满意个数、不满意个数、态度满意个数、响应速度的满意个数、技术满意个数
- 费用事实指标：报销费用、差率费用、补贴费用
- 差旅事实指标：油费、住宿费用、交通费用
- 网点物料事实指标：零配件的个数
- ……

DWB层呼叫中心事实指标表的需求

实施

- 目标需求：基于基础的时间、受理方式、来电类型等事实维度统计工单数量、电话数量、回访数量、投诉数量等

数据来源

- ciss_service_callaccept：客服中心来电详情表

 eos_dict_type：字典状态类别表，记录所有需要使用字典标记的表

select * from eos_dict_type where dicttypename = '来电类型';
select * from eos_dict_type where dicttypename = '来电受理单--处理方式';

eos_dict_entry：字典状态明细表，记录所有具体的状态或者类别信息

select * from eos_dict_entry where dicttypeid = 'BUSS_CALL_TYPE';
select * from eos_dict_entry where dicttypeid = 'BUSS_PROCESS_WAY';

ciss_service_workorder：工单状态明细表

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层呼叫中心事实指标表的构建

实施

建库

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层油站事实指标表的需求分析

目标需求：基于油站信息及设备数据构建油站主题事实的油站个数、停用个数、新增个数、设备个数等

油站数量：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层油站事实指标表的构建
- 实施
  - 建表

-- 创建油站事实表
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层工单事实指标表的需求分析

目标需求：基于工单信息统计等待分配工单数量、完成工单数量、处理工单数量、响应时长、服务时长等指标

数据来源

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_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
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层工单事实指标表的构建
- 实施
  - 建表

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层安装事实指标表的需求分析

目标需求：基于设备安装信息统计安装设备个数、收费安装个数、审核安装个数等指标

全新安装数量：install_type = 1
联调安装数量：install_way = 2
产生维修数量：is_repair = 1
额外收费数量：is_pay = 1
安装设备数量：与服务单关联，统计设备的id个数
安装费用：通过工单id从报销单信息中关联得到报销金额
审核完成工单个数

数据来源
ciss_service_install：安装单信息表