17. 实战示例-统计分析

-- 建库
    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 '/test/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;

-- 建表
-- 创建油站事实表
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 '/test/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;

-- 建表
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 '/test/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;

-- 建表
-- 创建安装单事实表
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 '/test/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';

-- 建表
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 '/test/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';

-- 建表
-- 创建客户回访实时表
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 '/test/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';

-- 建表
    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 '/test/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;

-- 建表
    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 '/test/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;

-- 建表
    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 '/test/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;