助力工业物联网,工业大数据之安装事实指标需求分析【二十一】

简介: 助力工业物联网,工业大数据之安装事实指标需求分析【二十一】

1:安装事实指标需求分析

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

2:安装事实指标构建

  • 目标:实现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层安装事实指标表的构建


目录
相关文章
|
1月前
|
SQL 分布式计算 大数据
请问本地安装了大数据计算MaxCompute studio,如何验证联通性及基本DDL操作呢?
请问本地安装了大数据计算MaxCompute studio,如何验证联通性及基本DDL操作呢?
32 0
|
4天前
|
分布式计算 Hadoop 大数据
【大数据】Hadoop下载安装及伪分布式集群搭建教程
【大数据】Hadoop下载安装及伪分布式集群搭建教程
12 0
|
21天前
|
存储 数据挖掘 大数据
大数据数仓建模基础理论【维度表、事实表、数仓分层及示例】
数据仓库建模是组织和设计数据以支持数据分析的过程,包括ER模型和维度建模。ER模型通过实体和关系描述数据结构,遵循三范式减少冗余。维度建模,特别是Kimball方法,用于数据仓库设计,便于分析和报告。事实表存储业务度量,如销售数据,分为累积、快照、事务和周期性快照类型。维度表提供描述性信息,如时间、产品、地点和客户详情。数仓通常分层为ODS(源数据)、DWD(明细数据)、DIM(公共维度)、DWS(数据汇总)和ADS(应用数据),以优化数据管理、质量、查询性能和适应性。
|
27天前
|
大数据 Linux 虚拟化
大数据软件基础(3) —— 在VMware上安装Linux集群
大数据软件基础(3) —— 在VMware上安装Linux集群
41 0
|
1月前
|
大数据 Docker 容器
大数据 安装指南-----利用docker
大数据 安装指南-----利用docker
50 0
|
1月前
|
分布式计算 Hadoop 大数据
[大数据] mac 史上最简单 hadoop 安装过程
[大数据] mac 史上最简单 hadoop 安装过程
|
1月前
|
监控 物联网 大数据
智慧工地管理平台系统源码基于物联网、云计算、大数据等技术
智慧工地平台APP通过对施工过程人机料法环的全面感知、互联互通、智能协同,提高施工现场的生产效率、管理水平和决策能力,实现施工管理的数字化、智能化、精益化。
69 0
|
1月前
|
分布式计算 大数据 Hadoop
Python大数据之PySpark(二)PySpark安装
Python大数据之PySpark(二)PySpark安装
279 0
|
1月前
|
NoSQL 物联网 大数据
【补充】助力工业物联网,工业大数据之AirFlow安装
【补充】助力工业物联网,工业大数据之AirFlow安装
63 1
|
1月前
|
Prometheus 数据可视化 Cloud Native
助力工业物联网,工业大数据之服务域:可视化工具Grafana介绍【三十八】
助力工业物联网,工业大数据之服务域:可视化工具Grafana介绍【三十八】
113 1

热门文章

最新文章