维度设计
-
功能：基于组合维度来更加细化我们的指标，来更加精确的发现问题
- 指标如果不基于组合维度进行分析得到，指标的结果是没有意义的
-
实现：开发中维度就是指标聚合时的分组字段
-
特点
- 数据量小
- 很少发生变化
-
采集方式：全量
-
常见维度
- 时间维度：年、季度、月、周、天、小时
- 地区维度：国家、省份、城市
- 平台维度：网站、APP、小程序、H5
- 操作系统维度：Windows、Mac OS、Android、Linux、IOS
- ……
维度设计常用模型
-
星型模型
- 设计：所有维度表直接关联事实表
- 优点：每次查询时候，直接获取对应的数据结果，不用关联其他的维度子表，可以提高性能
- 缺点：数据冗余度相比雪花模型较高
-
星座模型
- 星座模型：基于星型模型的演变，多个事实共同使用一个维度表
一站制造业务维度设计
-
日期时间维度（未实现）
- 年维度、季度维度、月维度、周维度、日维度
- 日环比、周环比、月环比、日同比、周同比、月同比
- 环比：同一个周期内的比较
- 同比：上个个周期的比较
-
行政地区维度
- 地区级别：国家维度、省份维度、城市维度、县区维度、乡镇维度
-
服务网点维度
- 网点名称、网点编号、省份、城市、县区、所属机构
-
油站维度
- 油站类型、油站名称、油站编号、客户编号、客户名称、省份、城市、县区、油站状态、所属公司
-
组织机构维度
- 人员编号、人员名称、岗位编号、岗位名称、部门编号、部门名称
行政地区维度设计
构建行政地区维度表，得到所有省份、城市、县区及乡镇维度信息
--获取所有乡镇的所有信息
select id area_id,areaname area,parentid from one_make_dwd.ciss_base_areas where rank = 4
--获取所有县区的信息
select id area_id,areaname area,parentid from one_make_dwd.ciss_base_areas where rank = 3
--获取所有市区的所有信息
select id area_id,areaname area,parentid from one_make_dwd.ciss_base_areas where rank = 2
--获取所有省份的信息
select id area_id,areaname area,parentid from one_make_dwd.ciss_base_areas where rank = 1
实现SQL
--获取每个镇的所有行政地区信息 area_id = parentid
--省份id 省份名称 城市id 城市名称 县区id 县区名称 乡镇id 乡镇名称
SELECT
-- 省ID、省名称
province.area_id as provinceId,province.area as provincename,
-- 市区ID、市区名称
city.area_id as cityId,city.area as cityname,
-- 县ID、县区名称
county.area_id as countyId,county.area as countyname,
-- 镇ID、镇名称
town.area_id as townId,town.area as townname
FROM
--获取所有乡镇的所有信息
(select id area_id,areaname area,parentid from one_make_dwd.ciss_base_areas where rank = 4) AS town
JOIN
--获取所有县区的信息
(select id area_id,areaname area,parentid from one_make_dwd.ciss_base_areas where rank = 3) AS county
ON town.parentid = county.area_id
JOIN
--获取所有市区的所有信息
(select id area_id,areaname area,parentid from one_make_dwd.ciss_base_areas where rank = 2) AS city
ON county.parentid = city.area_id
JOIN
--获取所有省份的信息
(select id area_id,areaname area,parentid from one_make_dwd.ciss_base_areas where rank = 1) AS province
ON city.parentid = province.area_id
服务网点维度设计
构建服务网点维度表，得到服务网点id、网点名称、网点所属的地理区域、服务网点状态等
--ciss_base_servicestation：服务网点信息表
--id |name |code |province|city|region|status|org_id|org_name |
----------------------------------+----------------+------+--------+----+------+------+------+----------------+
--40284afa5bc4f47e015bc8760a7c0814|太原***** |W14167|14 |1401| | 1|1026 |太原***** |
--40284afa5ce73d1c015d0c8c81d16e7a|吉尔**** |W41242|41 |4101|410102| 1|1141 |吉尔***** |
select
-- 服务网点ID、名称、编号
id,name,code,
-- 省份ID、缺省份名称
province as province_ID,pro.province_name
-- 城市ID、缺城市名称
city as city_ID,city.city_name
-- 所属县区ID、缺所属县区名称
region as region_ID,region.region_name
-- 使用状态编号（1 | 2）、使用状态名称
status,b.dictname
-- 部门ID，部门名称
org_id,org_name
from ONE_MAKE_DWD.ciss_base_servicestation;
eos_dict_type：字典状态类别表 与 eos_dict_entry：字典状态明细表 结合获取 状态编号、状态名称
--eos_dict_type：字典状态类别表，记录所有需要使用字典标记的表
--字典类别表：字典类型id和字典类型名称
--dicttypeid |dicttypename|
-----------------------------+------------+
--BUSS_SERVICE_STATION_STATUS|服务网点使用状态 |
select dicttypeid,dicttypename from ONE_MAKE_DWD.eos_dict_type where dicttypename = '服务网点使用状态';
--eos_dict_entry：字典状态明细表，记录所有具体的状态或者类别信息
--字典明细表：字典类型id、具体的编号和值
--dicttypeid |dictid|dictname|
-----------------------------+------+--------+
--BUSS_SERVICE_STATION_STATUS|2 |停用 |
--BUSS_SERVICE_STATION_STATUS|1 |使用 |
select dicttypeid,dictid,dictname from ONE_MAKE_DWD.eos_dict_entry where dicttypeid = 'BUSS_SERVICE_STATION_STATUS';
--dicttypeid |dictid|dictname|
-----------------------------+------+--------+
--BUSS_SERVICE_STATION_STATUS|2 |停用 |
--BUSS_SERVICE_STATION_STATUS|1 |使用 |
SELECT
a.dicttypeid,dictid,dictname
FROM
(select dicttypeid,dicttypename from ONE_MAKE_DWD.eos_dict_type where dicttypename = '服务网点使用状态') AS a
JOIN
(select dicttypeid,dictid,dictname from ONE_MAKE_DWD.eos_dict_entry where dicttypeid = 'BUSS_SERVICE_STATION_STATUS') AS b
ON a.dicttypeid = b.dicttypeid
ciss_base_areas：行政地区信息表
- 通过具体的id关联所有地区信息（这里只选取了 省、市、县）
--获取所有县区的信息
select id area_id,areaname area from one_make_dwd.ciss_base_areas where rank = 3
--获取所有市区的所有信息
select id area_id,areaname area from one_make_dwd.ciss_base_areas where rank = 2
--获取所有省份的信息
select id area_id,areaname area from one_make_dwd.ciss_base_areas where rank = 1
实现SQL
/*
id |name |code |province_ID|province_name|city_ID|city_name |region_ID|region_name|status|dictname|org_id|org_name |
--------------------------------+----------------+------+-----------+-------------+-------+----------+---------+-----------+------+--------+------+----------------+
40284afa672b330d0169bd3277895aa0|蓟县*** |W12424|12 |天津市 |1202 |市辖县 |120225 |蓟县 | 1|使用 |1428 |蓟县*** |
40284afa6aa022f4016ab92695615539|承德*** |W13501|13 |河北省 |1308 |承德市 |130802 |双桥区 | 1|使用 |1541 |承德*** |
*/
select
-- 服务网点ID、名称、编号
id,name,code,
-- 省份ID、缺省份名称
province as province_ID,pro.province_name,
-- 城市ID、缺城市名称
city as city_ID,city.city_name,
-- 所属县区ID、缺所属县区名称
region as region_ID,region.region_name,
-- 使用状态编号（1 | 2）、使用状态名称
status,b.dictname,
-- 部门ID，部门名称
org_id,org_name
from ONE_MAKE_DWD.ciss_base_servicestation AS a
join
-- 添加 使用状态名称
(
-- dictid|dictname|
-- ------+--------+
-- 2 |停用 |
-- 1 |使用 |
SELECT
dictid,dictname
FROM
(select dicttypeid,dicttypename from ONE_MAKE_DWD.eos_dict_type where dicttypename = '服务网点使用状态') AS a
JOIN
(select dicttypeid,dictid,dictname from ONE_MAKE_DWD.eos_dict_entry where dicttypeid = 'BUSS_SERVICE_STATION_STATUS') AS b
ON a.dicttypeid = b.dicttypeid) AS b
ON a.status = b.dictid
JOIN
-- 添加省份名称
(select id province_id,areaname province_name from one_make_dwd.ciss_base_areas where rank = 1) AS pro
ON a.province = pro.province_id
JOIN
-- 添加城市名称
(select id city_id,areaname city_name from one_make_dwd.ciss_base_areas where rank = 2) AS city
ON a.city = city.city_id
JOIN
-- 添加县区名称
(select id region_id,areaname region_name from one_make_dwd.ciss_base_areas where rank = 3) AS region
ON a.region = region.region_id
油站维度设计
构建油站维度表，得到油站id、油站名称、油站所属的地理区域、所属公司、油站状态等
/*
id |name |code |customer_id|customer_name |province_name|city_name|region_name|township_name|status|customer_classify|dt |
--------------------------------+--------------+--------+-----------+-----------------------+-------------+---------+-----------+-------------+------+-----------------+--------+
10011414 |中石**** |10011414|S0166 |中国********* |22 |2203 |220301 | | 1|0002 |20210101|
10011415 |中石***** |10011415|S0166 |中国********* |22 |2203 |220301 | | 1|0002 |20210101|
*/
SELECT
-- 油站ID、油站名称、油站编码
id, name, code,
-- 客户ID、客户名称
-- 缺客户所属公司ID、所属公司名称、客户所在省份id和省份名称
customer_id, customer_name,
-- 油站所属省份ID、城市ID、县区ID、乡镇ID
-- 缺所属省份名称、城市名称、县区名称、乡镇名称
province AS province_name, city AS city_name, region AS region_name, township AS township_name,
-- 状态（停用、正常）
-- 缺状态名称
status,
-- 客户分类ID
customer_classify,
-- 分区（按年月日）
dt
from one_make_dwd.ciss_base_oilstation
where id != '' and name is not null and name != 'null' and customer_id is not null;
获取 油站状态
--eos_dict_type：字典状态类别表，记录所有需要使用字典标记的表
/*DICTTYPEID |DICTTYPENAME|RANK|PARENTID|SEQNO |dt |
----------------------+------------+----+--------+------------------------+--------+
BUSS_OILSTATION_STATUS|油站状态 | 1| |.BUSS_OILSTATION_STATUS.|20210101|*/
select * from ONE_MAKE_DWD.eos_dict_type where dicttypename = '油站状态';
--eos_dict_entry：字典状态明细表，记录所有具体的状态或者类别信息
/*DICTTYPEID |DICTID|DICTNAME|STATUS|SORTNO|RANK|PARENTID|SEQNO|FILTER1|FILTER2|dt |
----------------------+------+--------+------+------+----+--------+-----+-------+-------+--------+
BUSS_OILSTATION_STATUS|2 |停用 | 1| 2| 1| |.2. | | |20210101|
BUSS_OILSTATION_STATUS|1 |正常 | 1| 1| 1| |.1. | | |20210101|*/
select * from ONE_MAKE_DWD.eos_dict_entry where dicttypeid = 'BUSS_OILSTATION_STATUS';
/*DICTID|DICTNAME|
------+--------+
2 |停用 |
1 |正常 |*/
SELECT
DICTID,
DICTNAME
FROM
(select * from ONE_MAKE_DWD.eos_dict_type where dicttypename = '油站状态') AS a
JOIN
(select * from ONE_MAKE_DWD.eos_dict_entry where dicttypeid = 'BUSS_OILSTATION_STATUS') AS b
ON a.DICTTYPEID = b.DICTTYPEID;
获取 客户id、公司名称、客户所属省份、客户所属公司ID
--ciss_base_baseinfo：客户公司信息表【公司ID、公司名称】
--ygcode|companyname|
--------+-----------+
--17 |一站制造** |
--13 |一站制造** |
--11 |一站制造** |
select ygcode, companyname from one_make_dwd.ciss_base_baseinfo group by ygcode, companyname;
--ciss_base_customer：客户信息表【客户id、客户省份名称、所属公司ID】
--code |province|company|
-------+--------+-------+
--S0201|江苏省 |11 |
--S0202| |13 |
select code, province, company from one_make_dwd.ciss_base_customer;
/*companyname|code |province|company|
-----------+-----+--------+-------+
一站制造** |S1684|浙江省 |17 |
一站制造** |S1684|浙江省 |17 |*/
SELECT
code, -- 客户id
companyname, -- 公司名称
province, -- 客户省份名称
company -- 所属公司ID
FROM
(select ygcode, companyname from one_make_dwd.ciss_base_baseinfo group by ygcode, companyname) AS a
JOIN
(select code, province, company from one_make_dwd.ciss_base_customer) AS b
ON a.ygcode = b.company
实现SQL
SELECT
-- 油站ID、油站名称、油站编码
id, name, cio.code,
-- 客户ID、客户名称
-- 缺客户所属公司ID、所属公司名称、客户所在省份id和省份名称
customer_id, customer_name,
customer.companyname,customer.company,customer.province,
-- 油站所属省份ID、城市ID、县区ID、乡镇ID
-- 缺所属省份名称、城市名称、县区名称、乡镇名称
cio.province AS province_ID, city AS city_ID, region AS region_ID, township AS township_ID,
province_name,city_name,region_name,township_name,
-- 状态（停用、正常）
-- 缺状态名称
status, DICTNAME
-- 客户分类ID
customer_classify,
-- 分区（按年月日）
dt
from
one_make_dwd.ciss_base_oilstation AS cio
JOIN
(SELECT
code, -- 客户id
companyname, -- 公司名称
province, -- 客户省份名称
company -- 所属公司ID
FROM
(select ygcode, companyname from one_make_dwd.ciss_base_baseinfo group by ygcode, companyname) AS a
JOIN
(select code, province, company from one_make_dwd.ciss_base_customer) AS b
ON a.ygcode = b.company) AS customer
ON cio.customer_id = customer.code
JOIN
-- 油站状态
(SELECT
DICTID,
DICTNAME
FROM
(select * from ONE_MAKE_DWD.eos_dict_type where dicttypename = '油站状态') AS a
JOIN
(select * from ONE_MAKE_DWD.eos_dict_entry where dicttypeid = 'BUSS_OILSTATION_STATUS') AS b
ON a.DICTTYPEID = b.DICTTYPEID) AS status
ON cio.status = status.DICTID
JOIN
--获取所有省份的信息
(select id province_id,areaname province_name from one_make_dwd.ciss_base_areas where rank = 1) AS province
ON cio.province = province.province_id
JOIN
-- 添加城市名称
(select id city_id,areaname city_name from one_make_dwd.ciss_base_areas where rank = 2) AS city
ON cio.city = city.city_id
JOIN
-- 添加县区名称
(select id region_id,areaname region_name from one_make_dwd.ciss_base_areas where rank = 3) AS region
ON cio.region = region.region_id
JOIN
-- 添加乡镇名称
(select id township_id,areaname township_name from one_make_dwd.ciss_base_areas where rank = 4) AS township
ON cio.township = township.township_id
组织机构维度设计
实现组织机构维度表的构建，得到每个工程师对应的组织机构信息
--org_employee：员工信息表【员工id、员工编码、员工名称、用户系统id】
SELECT *FROM ONE_MAKE_DWD.ORG_EMPLOYEE OE ;
SELECT
EMPID, -- 员工id
EMPCODE, -- 员工编码
EMPNAME, -- 员工名称
USERID -- 用户系统id
FROM ONE_MAKE_DWD.ORG_EMPLOYEE OEE ;
DESC ONE_MAKE_DWD.ORG_EMPLOYEE
--org_empposition：员工岗位信息表【员工id、岗位id】
SELECT
EMPID, -- 员工id
POSITIONID -- 岗位id
FROM ONE_MAKE_DWD.ORG_EMPPOSITION OEN ;
--org_position：岗位信息表【岗位id、岗位编码、岗位名称、部门id】
SELECT
POSITIONID, -- 岗位id
POSICODE, -- 岗位编码
POSINAME, -- 岗位名称
ORGID -- 部门id
FROM ONE_MAKE_DWD.ORG_POSITION OPN ;
--org_organization：部门信息表【部门id、部门编码、部门名称】
SELECT
ORGID, -- 部门id
ORGCODE, -- 部门编码
ORGNAME -- 部门名称
FROM ONE_MAKE_DWD.ORG_ORGANIZATION OON ;
实现SQL
SELECT
OEE.EMPID AS `员工id`,EMPCODE AS `员工编码`,EMPNAME AS `员工名称`,USERID AS `用户系统id`,
OEN.POSITIONID AS `岗位id`,
POSICODE AS `岗位编码`,POSINAME AS `岗位名称`,
OON.ORGID AS `部门id`,OON.ORGCODE AS `部门编码`,OON.ORGNAME AS `部门名称`
FROM
(SELECT
EMPID, -- 员工id
EMPCODE, -- 员工编码
EMPNAME, -- 员工名称
USERID -- 用户系统id
FROM ONE_MAKE_DWD.ORG_EMPLOYEE) AS OEE
JOIN
(SELECT
EMPID, -- 员工id
POSITIONID -- 岗位id
FROM ONE_MAKE_DWD.ORG_EMPPOSITION) AS OEN
ON OEE.EMPID = OEN.EMPID
JOIN
(SELECT
POSITIONID, -- 岗位id
POSICODE, -- 岗位编码
POSINAME, -- 岗位名称
ORGID -- 部门id
FROM ONE_MAKE_DWD.ORG_POSITION) AS OPN
ON OEN.POSITIONID = OPN.POSITIONID
JOIN
(SELECT
ORGID, -- 部门id
ORGCODE, -- 部门编码
ORGNAME -- 部门名称
FROM ONE_MAKE_DWD.ORG_ORGANIZATION) AS OON
ON OPN.ORGID = OON.ORGID
