助力工业物联网，工业大数据之服务域：安装主题分析实现【二十七】
* 安装方式* 日期维度：天、周、月
* 油站维度：类型、省份、城市、地区
* 客户维度：类型、省份
- **数据**
* 事实表
+ fact\_srv\_install：安装事务事实表
```
select
inst_id, --安装单id
inst_type_id, --安装方式id 1-设备安装，2-设备联调
exp_device_money, --安装费用
dt,--日期
os_id --油站id
from one_make_dwb.fact_srv_install;
```
- 维度表
* dim\_oilstation：油站维度表
```
select
id,--油站id
company_name,--公司名称
province_name,--省份名称
city_name,--城市名称
county_name,--区域名称
customer_classify_name,--客户名称
customer_province_name--客户省份
from dim_oilstation;
```
+ dim\_date：时间维度表
```
select
date_id,--天
week_in_year_id,--周
year_month_id --月
from dim_date;
```
!(https://img-blog.csdnimg.cn/img_convert/f1e91fbae0463c2053d47c99b989616a.png)
+ **实现**
- **建表**
```
create table if not exists one_make_st.subj_install(
install_way string comment '安装方式'
,install_sum bigint comment '安装数量'
,sum_money int comment '支付费用'
,dws_day string comment '日期维度-按天'
,dws_week string comment '日期维度-按周'
,dws_month string comment '日期维度-按月'
,oil_type string comment '油站维度-油站类型'
,oil_province string comment '油站维度-油站所属省'
,oil_city string comment '油站维度-油站所属市'
,oil_county string comment '油站维度-油站所属区'
,customer_classify string comment '客户维度-客户类型'
,customer_province string comment '客户维度-客户所属省'
) comment '安装主题表'
partitioned by (month string, week string, day string)
stored as orc
location '/data/dw/st/one\_make/subj\_install';
```
[*]构建
insert overwrite table one_make_st.subj_install partition(month = '202101', week='2021W1', day='20210101')
select
--安装方式
max(case when install.inst_type_id = 1 then '设备安装' when install.inst_type_id = 2 then '设备联调' else '未知' end) install_way
, count(install.inst_id) install_sum --安装数量
, sum(install.exp_device_money) sum_money --支付金额
, dd.date_id dws_day --日期天
, dd.week_in_year_id dws_week --日期周
, dd.year_month_id dws_month --日期月
, dimoil.company_name oil_type --油站类型
, dimoil.province_name oil_province --油站省份
, dimoil.city_name oil_city --油站城市
, dimoil.county_name oil_county --油站地区
, dimoil.customer_classify_name customer_classify --客户类型
, dimoil.customer_province_name customer_province --客户省份
--安装事务事实表
from one_make_dwb.fact_srv_install install
--关联日期维度表
left join one_make_dws.dim_date dd on install.dt = dd.date_id
--关联油站维度表
left join one_make_dws.dim_oilstation dimoil on install.os_id = dimoil.id
where dd.year_month_id = '202101' and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101'
--按照维度分组
group by
inst_type_id,
dd.date_id, dd.week_in_year_id, dd.year_month_id,
dimoil.company_name, dimoil.province_name, dimoil.city_name, dimoil.county_name,
dimoil.customer_classify_name, dimoil.customer_province_name
;
[*] 小结
[*]把握安装主题的需求分析及实现
10：服务域：维修主题分析实现
[*] 目标：把握维修主题的需求分析及实现
[*] 路径
[*]step1：需求
[*]step2：分析
[*]step3：实现
[*] 实施
[*]需求：统计差异维度下的维修主题指标的结果
https://img-blog.csdnimg.cn/img_convert/05cbfb47f9ec649ce4ae355e09b5f7f8.png
[*] 分析
[*] 指标
[*]付出费用、工时费用、零部件费用、交通费用
[*]故障总数、最大数目、均匀数目
[*] 维度
[*]日期维度：天、周、月
[*]油站维度：类型、省份、城市、地区
[*]客户维度：类型、省份
[*]物流公司
[*] 数据
[*] 事实表
[*]fact_srv_repair：维修事务事实表
select
hour_money,--工时费用
parts_money,--配件物料费用
fars_money,--交通费用
fault_type_ids, --故障id集合
dt,--日期
os_id,--油站id
ss_id --服务网点id
from fact_srv_repair;
[*] fact_srv_stn_ma：网点物料事务事实表
select
ss_id,--服务网点id
logi_cmp_id --物流公司id
from fact_srv_stn_ma;
[*] 维度表
[*]dim_oilstation：油站维度表
select
id,--油站id
company_name,--公司名称
province_name,--省份名称
city_name,--城市名称
county_name,--区域名称
customer_classify_name,--客户名称
customer_province_name--客户省份
from dim_oilstation;
[*]dim_date：时间维度表
select
date_id,--天
week_in_year_id,--周
year_month_id --月
from dim_date;
[*]dim_logistics：物流维度表
select
type_id,--物流公司id
type_name --物流公司名称
from one_make_dws.dim_logistics where prop_name = '物流公司';
[*] 实现
[*]建表
drop table if exists one_make_st.subj_repair;
create table if not exists one_make_st.subj_repair(
sum_pay_money decimal(20,1) comment '支付费用'
,sum_hour_money decimal(20,1) comment '小时费用'
,sum_parts_money decimal(20,1) comment '零部件费用'
,sum_fars_money decimal(20,1) comment '交通费用'
,sum_faulttype_num bigint comment '故障类型总数'
,max_faulttype_num int comment '故障类型最大数量'
,avg_faulttype_num int comment '故障类型平均数量'
,dws_day string comment '日期维度-按天'
,dws_week string comment '日期维度-按周'
,dws_month string comment '日期维度-按月'
,oil_type string comment '油站维度-油站类型'
,oil_province string comment '油站维度-油站所属省'
,oil_city string comment '油站维度-油站所属市'
,oil_county string comment '油站维度-油站所属区'
,customer_classify string comment '客户维度-客户类型'
,customer_province string comment '客户维度-客户所属省'
,logi_company string comment '物流公司维度-物流公司名称'
) comment '维修主题表'
partitioned by (month String, week String, day String)
stored as orc
location '/data/dw/st/one\_make/subj\_repair';
[*] 构建
insert overwrite table one_make_st.subj_repair partition(month = '202101', week='2021W1', day='20210101')
select
sum(pay_money) sum_pay_money, --支付费用
sum(hour_money) sum_hour_money, --工时费用
sum(parts_money) sum_parts_money, --物料费用
sum(fars_money) sum_fars_money, --交通费用
sum(fault_type_num) sum_faulttype_num, --故障类型总数
max(fault_type_num) max_faulttype_num, --最大故障数量
avg(fault_type_num) avg_faulttype_num, --平均故障数量
dws_day, --日期天
dws_week, --日期周
dws_month, --日期月
oil_type, --油站类型
oil_province, --油站省份
oil_city, --油站城市
oil_county, --油站区域
customer_classify, --客户类型
customer_province, --客户省份
logi_company --物流公司
from (
select
(hour_money + parts_money+fars_money) pay_money,
hour_money,
parts_money,
fars_money,
case when (size(split(fault_type_ids, ','))) <= 0 then 0 else (size(split(fault_type_ids, ','))) end fault_type_num,
dd.date_id dws_day,
dd.week_in_year_id dws_week,
dd.year_month_id dws_month,
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]