数据仓库整理

数仓

olap vs oltp

• OLTP主要用于支持日常的业务操作，如银行交易、电子商务等，强调数据的准确性、实时性和并发性。
• OLAP主要用于支持复杂的数据分析，如数据仓库、决策支持等，强调数据的维度、聚合和可视化。

将OLTP数据库的数据转移到OLAP数据库的过程一般包括以下几个步骤：

• 数据抽取：从OLTP数据库中提取需要分析的数据，可以使用SQL语句、ETL工具或者其他方法。
• 数据清洗：对抽取出来的数据进行质量检查和修正，去除重复、错误或者不一致的数据，保证数据的完整性和有效性。
• 数据转换：根据OLAP数据库的结构和需求，对数据进行适当的变换和加工，如进行聚合、分组、排序等操作，生成事实表和维度表。
• 数据加载：将转换后的数据加载到OLAP数据库中，可以使用批量加载、增量加载或者实时加载等方式。
• 数据刷新：定期或者根据事件触发，对OLAP数据库中的数据进行更新和同步，保证数据的时效性和一致性。

数据仓库

源数据层是数据仓库的基础层，它负责从各种数据源抽取、清洗、转换和加载原始数据，保证数据的完整性、准确性和一致性。模型层是数据仓库的核心层，它负责对源数据层的数据进行进一步的加工、聚合和建模，使得数据更加适合分析和查询。模型层通常采用维度建模，将数据划分为事实表和维度表，事实表存储业务过程中发生的事件，维度表存储事件的属性。指标层是数据仓库的最上层，负责对模型层的数据进行最终的应用和呈现，提供给用户或系统使用。分层优点：复杂问题简单化、清晰数据结构(方便管理)、增加数据的复用性、隔离原始数据(解耦)
ods 原始数据层 存放原始数据，保持原貌不做处理
dwd 明细数据层 对ods层数据清洗（去除空值，脏数据，超过极限范围的数据）
dws 服务数据层 轻度聚合
ads 应用数据层 具体需求

ODS（原始数据库）(Operation Data Store)

保持数据原貌，不做任何修改；压缩采用 LZO，压缩比是 100g 数据压缩完 10g 左右。创建分区表

DWD（明细数据层）(Data Warehouse Detail)

1. 数据清洗
   （1）空值去除
   （2）过滤核心字段无意义的数据，比如订单表中订单 id 为 null，支付表中支付 id 为空
   （3）将用户行为宽表和业务表进行数据一致性处理

2. 清洗的手段
   Sql、mr、rdd、kettle、Python等等

3. 清洗掉多少数据算合理
   1 万条数据清洗掉 1 条。

4. 脱敏
   对手机号、身份证号等敏感数据脱敏

5. 维度退化
   对业务数据传过来的表进行维度退化和降维。（商品一级二级三级、省市县、年月日）

6. LZO压缩

7. 列式存储 parquet

DWS（服务数据层）(Data Warehouse Summary)

DWS 层有 3-5 张宽表（处理 100-200 个指标 70%以上的需
求）
具体宽表名称：用户行为宽表，用户购买商品明细行为宽表，商品宽表，购物车宽表，物流宽表、登录注册、售后等。

哪个宽表最宽？大概有多少个字段？
最宽的是用户行为宽表。大概有 60-100 个字段

具体用户行为宽表字段名称
评论、打赏、收藏、关注–商品、关注–人、点赞、分享、好价爆料、文章发布、活跃、签到、补签卡、幸运屋、礼品、金币、电商点击、gmv

DWT（主题数据层）(Data Warehouse Topic)

分析过的指标
日活、月活、周活、留存、留存率、新增（日、周、年）、转化率、流失、回流、七天内连续 3 天登录（点赞、收藏、评价、购买、加购、下单、活动）、连续 3 周（月）登录、GMV、复购率、复购率排行、点赞、评论、收藏、领优惠价人数、使用优惠价、沉默、值不值得买、退款人数、退款率 topn 热门商品

留转 G 复活指标
（1）活跃
日活：100 万 ；月活：是日活的 2-3 倍 300 万
总注册的用户多少？1000 万-3000 万之间
（2）GMV
GMV：每天 10 万订单 （50 – 100 元） 500 万-1000 万
10%-20% 100 万-200 万
（3）复购率
某日常商品复购；（手纸、面膜、牙膏）10%-20%
电脑、显示器、手表 1%
（4）转化率
商品详情 =》 加购物车 =》下单 =》 支付
5%-10% 60-70% 90%-95%
（5）留存率
1/2/3、周留存、月留存
搞活动： 10-20%

ADS（应用数据层）(Application Data Store)

如何分析用户活跃？
在启动日志中统计不同设备 id 出现次数。

如何分析用户新增?
用活跃用户表 left join 用户新增表，用户新增表中 mid 为空的即为用户新增。

如何分析用户 1 天留存？
留存用户=前一天新增 join 今天活跃
用户留存率=留存用户/前一天新增

如何分析沉默用户？
(登录时间为 7 天前,且只出现过一次)
按照设备 id 对日活表分组，登录次数为 1，且是在一周前登录。

如何分析本周回流用户？
本周活跃 left join 本周新增 left join 上周活跃，且本周新增 id 和上周活跃 id 都为 null。

如何分析流失用户？
(登录时间为 7 天前)
按照设备 id 对日活表分组，且七天内没有登录过。

如何分析最近连续 3 周活跃用户数？
按照设备 id 对周活进行分组，统计次数大于 3 次。

如何分析最近七天内连续三天活跃用户数？
1）查询出最近 7 天的活跃用户，并对用户活跃日期进行排名
2）计算用户活跃日期及排名之间的差值
3）对同用户及差值分组，统计差值个数
4）将差值相同个数大于等于 3 的数据取出，然后去重，即为连续 3 天及以上活跃的用户
7 天连续收藏、点赞、购买、加购、付款、浏览、商品点击、退货
1 个月连续 7 天
连续两周

数据仓库建模的方法

• ER模型是Inmon提出的，这个模型是符合3范式的，他的出发点就是整合数据，将各个系统中的数据以整个企业角度按主题进行分类，但是不能直接用于分析决策
• 维度模型是Kimball提出的，这个人和Inmon算是数仓的两个流派，他的出发点就是分析决策，为分析需求服务，而现在多数的数仓的搭建都是基于维度模型进行搭建的。
• 区别：ER模型冗余更少，但是在大规模数据跨表分析中，会造成多表关联，这会大大降低执行效率

维度模型

维度模型主要由事实表和维度表组成，事实表存储可度量的指标，维度表存储描述性的属性。维度模型有三种典型的形式：星型模型、雪花模型和星座模型。维度模型简单直观，适合业务变化快速的行业，能够快速交付，提高查询性能。

• 星型模型：星型模型是由一个事实表和多个维度表组成的，每个维度表都直接与事实表相连接，形成一个类似星星的结构。星型模型的优点是简单直观，易于理解和使用，查询性能高，适合快速交付和变化频繁的业务需求。星型模型的缺点是数据冗余度高，可能导致数据不一致和存储空间浪费，维度表的层次结构不明显，不利于分析细节。
• 雪花模型：雪花模型是对星型模型的扩展，它将一些维度表进一步分解为更小的维度表，形成一个类似雪花的结构。雪花模型的优点是数据冗余度低，数据一致性和质量高，维度表的层次结构清晰，有利于分析复杂的业务逻辑。雪花模型的缺点是设计和实现较复杂，查询性能低，需要多次连接操作，不利于快速响应和变更。

维度建模中表的类型

• 维度表：一张维度表就表示对一个对象的一些描述信息。每个维度表都包含单一的主键列，和一些对该主键的描述信息，通常维度表会很宽。比如 乘客信息表，司机信息表，城市首都表
• 事实表：一张事实表就表示对业务过程的描述，比如播单，下单，支付。每个事实表都包含若干维度外键，若干退化维度（维度属性存储到事实表中，减少关联），和数值型的度量值，通常事实表会比较大。

事实表有哪几种类型

a)事务事实表：每一行数据表示一个事务，数据一旦插入就不会修改播单
b)周期快照事实表：不会保存所有时间的数据，只会保留固定时间间隔的数据，比如购物车，每时每刻都会增加或者减少，但是我们更加关心的是一天结束的时候购物车中有几件商品，就会采用周期性快照事实表（汇总事实表） 历史至今快照事实表
c)累积快照事实表：用于追踪事实的变化过程，比如从买家下单到支付的时长，买家支付到卖家发货的时长等（数据会变更）

事实表的设计过程

a)一共有五步，分别是选择业务过程，声明粒度，确定维度，确定事实，冗余维度
a)选择业务过程 就是对业务的整个生命周期进行分析，然后选择与需求有关的业务过程，比如打车呼单的整个过程，乘客呼单，平台播单，司机抢单，司机接驾，完成订单，（买家下单，买家付款，卖家发货，买家确认收货）然后就是根据我们的需求去选择对应的过程
b)声明粒度 ，粒度就是用于确定事实表中一行所表示的业务的细节层次，通常在设计事实表的时候，粒度定义的越细越好，比如订单明细表的粒度就是 订单级别
c)确定维度，选择描述清楚业务过程所处环境的维度信息中，比如订单明细表中 出发城市，到达城市，产品线，司机，订单状态等（支付事实表，买家，买家，商品，收货人信息，业务类型，订单时间）
d)确定事实，事实就是分析业务过程中的度量值，比如订单金额，订单次数等
e)冗余维度，在事实表中冗余一些下游用户需要使用的常用维度，减少多表之间的关联。

维度表的设计过程

a)第一步，选择维度，比如商品维度
b)第二步，确定主维表，一般就是业务系统中商品表同步到ods层的表，就是主维表
c)第三步，确定相关维表，因为不同业务系统或者同一业务系统中的不同表之间都会存在关联性，根据对业务的梳理，确定哪些表和主维表存在关联关系，比如商品会与 类目，spu，卖家，店铺等维度存在关联。
d)第四步，确定维度属性，分为两步，就是从主维表中选择维度属性或者生成新的维度属性，还有就是从相关维表

数据仓库开发包含几步

a)第一步，进行数据调研；包括了业务调研和需求调研，业务调研就是要弄清楚公司有哪些业务，以及每个业务有包括哪些业务线，一般每个业务会独自建设数据仓库。
b)第二步，进行架构设计；包括了数据域划分和构建总线矩阵，数据域就是指 将业务过程或者维度进行抽象的集合，在划分数据域的时候，应该尽可能保证当前划分的能够覆盖所有的业务需求，又能在新业务进入时无影响的被包含到已有的数据域中或者扩展新的数据域，国际化数仓里面的数据域包括司机域，乘客域，交易域，客服域，安全域等等，阿里巴巴就会有 商品域 会员域 店铺域 交易域 日志域等等。构建总线矩阵，就需要明确每个数据域下有哪些业务过程，业务过程与哪些维度相关。
c)第三步，进行规范定义，主要包括定义维度属性和定义指标体系，包括原子指标和派生指标（原子指标+时间周期+修饰词）
d)第四步，进行模型设计，包括明细层DIM和DWD，和汇总层DWS和DM层，以及面向分析人员的app层。
e)第五步，进行代码开发和上线生成调度任务，进行周期运行。