数据基础：OLTP 与 OLAP 数据建模的异同

OLTP 与 OLAP：常见工作流

联机分析处理 (OLAP) 和联机事务处理 (OLTP) 是两种主要的数据处理系统。两者之间存在多种差异。

OLTP 系统旨在处理来自多个用户的多个事务，它们通常用于许多应用程序的后端。例如，在线商务网站将使用 OLTP 系统来收集和显示用户/商品信息。当用户登录时，客户端会从 OLTP 系统获取用户的数据以显示在前端。当用户购买某件商品时，OLTP 系统中的订单表将添加一行。当商家更改其某件商品的信息时，新信息将在后端的商品表中更新。因此，这些系统针对每次对少量行执行 CRUD 操作进行了优化，尽可能减少延迟，以提升用户体验。

另一方面，OLAP 系统旨在支持一次对数百到数百万行进行复杂查询。OLAP 系统与 OLTP 系统的另一个不同之处在于，最终用户是数据分析师/数据科学家等。因此，虽然 OLTP 适合回答有关单个用户的查询，但 OLAP 更适合涉及数百万用户的查询。

它们之间有 ETL/ELT，其中 E 代表提取，T 代表转换，L 代表加载。它们共同构成一个流程，从 OLTP 中提取数据并进行转换，以便将其加载到 OLAP 系统中。

由于 OLAP 和 OLTP 之间存在这些差异，因此两者的数据建模涉及不同的技术。虽然 OLTP 倾向于数据规范化以节省空间、查询时间并保持数据完整性（想象一下，如果我们在多个表中拥有一种商品的信息，更新其信息会很麻烦，而且容易出错，数据不匹配），但 OLAP 鼓励重复以优先考虑查询性能（如果两个表中的两列经常通过连接一起显示，那么也许它们应该放在同一个表中，因为连接的成本可能很高，等等）。

一 OLTP：数据规范化

您经常会在 OLTP 系统中看到数据规范化，这种系统非常注重节省存储并避免数据重复。这种类型的建模将数据组织到通过键连接的表中。

1NF、2NF 和 3NF 描述了数据库必须满足的条件才能达到适当的规范化水平。每个条件都建立在另一个条件之上；也就是说，要达到 2NF，必须达到 1NF。

键类型

# 表：用户表：用户
user_id|    姓名       | 年龄|      地址                                   | plan_id  
 1        | Zac  Smith |   18 | 123  St，X  City，CA，90000  | 1
 2        | Eli  Evans  |   22 | 234  St，Y  City，TX，60000   | 2             
 3        | Lil  Smitt   |   33 | 345  St，Z  City，NV，70000  | 1

计划 ID |  成本  
 1        | 10  
 2        | 20      
 3        | 30

超级键

可以唯一标识一行的属性集称为超级键。例如，在下面的 Users 表中，user_id 可以是超级键，但 (user_id, name) 也可以。以下是超级键的更多特征：

• 它们可以包含 NULL 值。

• 向候选键添加零个或多个属性会生成超键。

候选键

能够唯一标识一行的最小属性集称为候选键。对于上表，user_id 是候选键，但不是 (user_id, name)。以下是候选键的几个特征：

• 它们可以包含 NULL 值。

• 一个表必须有≥1个候选键。

• 它们可以是简单的（1 个属性），也可以是复合的（多个属性）。

主键

表中可以有≥1个候选键，从中可以选出一个作为主键。以下是主键的几个特征：

• 它们不能包含 NULL 值。

• 一个表只能有一个主键。

• 它们可以是简单的（1 个属性），也可以是复合的（多个属性）。

外键

它们用于表示两个表之间的关系。在上面的两个表中，users 表中的 plan_id 列是外键，它引用 plans 表中的 plan_id 列。

第一范式——1NF

对于符合 1NF 的表，它必须满足以下条件：

• 单个单元格仅包含一个值。最初，此规则规定为“任何表列都不能将表作为值”。但规则的含义在于简化用于从表中获取数据的查询语言。此规则通常被解释为“不允许使用复杂的数据类型”。但是，较新版本的 SQL 为构造和映射等开辟了可能性。因此，我对此持保留态度。

• 没有重复的行，这可以通过主键列来实现。

第二范式——2NF

如果满足以下条件，则认为表符合 2NF：

• 它已经符合 1NF 了。

• 其所有非主属性（非键）列都完全依赖于主键。当涉及到组合键时，这一点尤其棘手，因为此规则意味着属性列必须依赖于组合键下的所有列，而不仅仅是其中的几个列。

考虑下面的表，其中主键是复合键：[制造商，型号]。制造商国家/地区依赖于制造商，但不依赖于型号。因此，此设计不属于 2NF。要使设计符合 2NF，请将表分成 2 个，其中一个表包含制造商和型号列，而另一个表包含制造商和制造商国家/地区列。

制造商        | 型号       | 制造商国家
 Forte        | X-Prime    | 意大利                
 Forte        | Ultraclean   | 意大利                
 Dent-o-Fresh | EZbrush    | 美国                  
 Brushmaster  | SuperBrush | 美国                  
 Kobayashi    | ST -60     | 日本                
 Hoch        | Toothmaster | 德国              
 Hoch        | X-Prime    | 德国

从功能上讲，这是消除冗余的步骤，以便为给定的数据提供单一真实来源，从而节省空间并方便以后更新。例如，考虑会计系统中的客户地址。客户表需要该地址，订单、发货、发票、应收账款和收款表也需要该地址。不要将客户地址作为单独的条目存储在每个表中，而是将其存储在一个位置，即客户表或单独的地址表中。

第三范式——3NF

如果一个表符合 3NF，则该表符合以下条件：

• 它已经符合2NF了。

• 任何非主属性都不依赖于另一个非主属性。

考虑下面的表格，其中主键是复合键：[锦标赛，年份]。虽然获胜者列依赖于这两个列，但获胜者出生日期列也依赖于获胜者列，这使得表格容易出现逻辑不一致，因为没有什么可以阻止同一个人在不同的记录中显示不同的出生日期。为了符合 3NF，我们可以将此表分成两个表，其中一个表仅包含给定锦标赛和年份的获胜者姓名，称为 Winner，而另一个表包含有关获胜者的信息，称为 WinnerInfo。

锦标赛         | 年份   | 冠军              | 冠军出生日期
印第安纳邀请赛 | 1998 年| 艾尔·弗雷德里克森 | 1975 年7 月21 日

克利夫兰公开赛 | 1999 年| 鲍勃·阿尔伯森 | 1968 年9 月28 日

得梅因大师赛   | 1999 年| 艾尔·弗雷德里克森 | 1975 年7 月21 日

印第安纳邀请赛 | 1999 年| 奇普·马斯特森 | 1977 年3 月14 日          
例外：遵守第三范式虽然在理论上是可取的，但并不总是可行的。如果您有一个 Customers 表，并且想要消除所有可能的字段间依赖关系，则必须为城市、邮政编码、销售代表、客户类别以及可能在多个记录中重复的任何其他因素创建单独的表。理论上，规范化是值得追求的。但是，许多小表可能会降低性能或超出打开文件和内存容量。

案例研究：拼车应用

假设我们正在为共享乘车应用构建数据库，该应用需要存储乘客、司机和乘车等相关实体的数据。以下是数据的逻辑模型。

1NF 合规性

下面的模型已经是 1NF 了，因为每个表都有一个唯一标识行的 id 列，并且列中的每个单元格都是尽可能原子的。对于 Rider 和 Driver 表，其他超键是 username 和 phone_number，因为它们通常对某个人是唯一的。因此，从技术上讲，这些列中的任何一个都可以用作主键，用于唯一标识 rider/drive 表中的行。但是，如果用户更改了他们的电子邮件或电话号码，甚至是用户名，该怎么办？那么我们必须更新 Ride 表中的相应行，这是一个巨大的麻烦。因此，为每个新用户/司机创建一个严格增加的 id，然后在其他表中使用该 id 作为外键，这样更方便。

2NF 合规性

为了使模型符合 2NF，让我们尝试尽可能地消除冗余。这里最明显的冗余来源是地理位置：所有三个表都具有相同的列集！我们可以将这些列分成一个单独的表，并使用位置 ID 引用该表。接下来，如果用户既是乘客又是司机怎么办？我们可以通过创建一个存储有关人员信息的用户表来消除这种冗余，但仍保留乘客和司机表来存储与这些角色相关的信息。

3NF 合规性

为了符合 3NF，考虑一下这样的场景：乘客预订了行程，但始终没有与司机匹配，或者在行程完成之前以某种方式取消了行程。因此，我们可以将带有“预订”的任何内容分离到单独的预订表中。此外，我们可以将汽车和电话列分组到单独的表中，因为多个人可以拥有相同类型的电话/汽车。

小结

从历史上看，数据规范化与关系数据库密切相关，后者是大多数后端数据库使用的方式。随着 NoSQL 数据库的兴起，它允许水平扩展以更好地服务客户，规范化可能不再是数据建模方面最大的问题。

二 OLAP：数据非规范化和 STAR 模式

OLAP 数据建模有很多种技术，每种技术都有各自的优点和缺点。维度建模/星型模式是其中一种比较流行的技术。

建模步骤

1. 选择业务流程：业务流程是组织执行的操作活动，例如接受订单、处理保险索赔、为学生注册课程或每月为每个帐户创建快照每个业务流程一个事实表；每个度量都是一行。如何识别业务流程：它们通常由业务系统（例如计费/采购系统）支持；它们生成/捕获性能指标；它们由输入触发并产生输出指标。

2. 确定粒度：粒度明确确定单个事实表代表什么。必须在选择维度或事实之前确定粒度，因为每个候选维度或事实都必须与粒度一致。原子粒度指的是给定业务流程捕获数据的最低级别。建议从关注原子粒度数据开始，因为它可以抵御不可预测的用户查询的挑战；汇总粒度对于性能调优很重要，但它们预先假设了业务的常见问题。每个提议的事实表粒度产生一个单独的物理表。粒度定义示例：杂货收据上的行项目、银行帐户对帐单的每月快照、一天购买的单张机票。

3. 确定维度：维度提供围绕业务流程事件的“谁、什么、哪里、何时、为什么和如何”上下文。维度表包含 BI 应用程序用于筛选和分组事实的描述性属性。例如，营销分析应用程序的维度表可能包括时间段、营销区域和产品类型（注意：每个都是一个表，而不仅仅是一列）。

4. 识别事实：事实是业务流程事件产生的测量结果，几乎总是数字。单个事实表行与事实表粒度描述的测量事件具有一对一关系。在事实表中，只允许与确定的粒度一致的事实。例如，在零售销售交易中，销售的产品数量及其扩展价格是有效事实，而商店经理的工资则不允许。

键类型

在这里，键的语言与 OLTP 建模略有不同。虽然主键和外键仍然非常流行，但人们自然会更加关注这些键的内容，因为它们将用于连接表。

自然键

自然键具有上下文或业务含义（例如股票代码 — MSFT、APPL 和 GOOGL）。自然键可能不够唯一，无法用作主键，因此通常需要手动生成。

代理键

代理键不具有上下文/业务含义。它是“人为”制造的，仅用于数据分析。代理键最常用的版本是递增的连续整数或“计数器”值（即 1、2、3）。代理键还可以包括当前系统日期/时间戳或随机字母数字字符串。

持久键

尽管 OLTP 系统只是通过覆盖来更新行，并且大多数时候只关心某个实体的最新信息，但 OLAP 系统对历史数据更加谨慎。假设我们有一个包含员工信息的表，如果一名员工离职然后又回来，会发生什么？我们应该给他们一个新的 ID 还是相同的 ID？这是持久键发挥作用的地方，它与代理键协同工作以捕获我们需要的所有信息。答案是，当员工第一次加入公司时，我们会给他们一个持久键和一个代理键。持久键不会改变，并且始终指向同一个人。所以如果他们辞职后又回来，我们会给他们一个新的代理键，但不会是新的持久键。

事实/事实表

事实表包含现实世界中的测量数值结果、其每个相关维度的外键以及退化维度键和时间戳。事实表是查询产生的计算和聚合的主要目标。

事实表的类型

• 事务事实表：始终包含每个维度的外键，以及可选的时间戳和退化维度键。

• 定期快照事实表：一行总结了标准时间段（例如一天、一周或一个月）内发生的许多测量事件。粒度是时间段。如果在此期间没有发生任何活动，通常会插入一行，其中包含每个事实的零或空值。

• 累积快照事实表：一行总结了在流程开始和结束之间可预测的步骤中发生的测量事件。适用于建模管道/工作流。流程中的每个里程碑都有一个日期外键。在创建订单行时最初插入一行，然后随着进度的发生而更新。它们通常包括里程碑完成计数器。

• 无事实事实表：事件可能仅记录在某一时刻聚集在一起的一组维度实体。例如，学生在某一天上课的事件可能没有记录的数字事实，但带有日历日、学生、老师、地点和班级外键的事实行是明确定义的。

• 聚合事实表或多维数据集：聚合事实表是原子事实表数据的简单数字汇总，专门用于加速查询性能。一组设计合理的聚合应该像数据库索引一样运行，它们可以加速查询性能，但 BI 应用程序或业务用户不会直接遇到它们。聚合事实表包含缩小的一致维度的外键，以及通过汇总更多原子事实表中的度量而创建的聚合事实。

• 合并事实表：如果多个流程中的事实能够以相同的粒度表达，那么将它们合并到单个合并事实表中通常很方便。例如，可以将实际销售数据与销售预测数据合并到单个事实表中，从而使分析实际数据与预测数据的任务变得简单而快速。

事实表中的键

• 代理键在 ETL 加载过程中按顺序分配，并用作 1) 事实表的单列主键；2) 用作事实表行的直接标识符，而无需为 ETL 目的浏览多个维度；3) 允许中断的加载过程退出或恢复；4) 允许将事实表更新操作分解为风险较低的插入和删除。

• 避免使用Centipede 事实表（例如，将日期、月份、季度和年份作为单独的外键存储在单个事实表中，这是不好的做法）。

事实表中的事实

• 避免事实表的外键为空。相反，关联的维度表必须有默认的键（和代理键）表示未知/NA。

• 一致的事实：如果相同的测量出现在单独的事实表中，并且具有相同的定义，那么它们必须具有相同的名称。

• 多个当前事实：确保有 3 列，1 列表示原始货币的数值，1 列表示原始货币（tex（“USD”等），1 列表示标准货币的数值。

• 多种计量单位事实：许多事实对单位有许多不同的要求，将所有内容存储在一组标准单位中，然后将对话因素存储在其他地方并在报告中使用它们。

• 事实表中的时间跨度跟踪：有时使用类型 2 技术来处理事实表中缓慢变化的维度很有用（例如，缓慢变化的库存余额，频繁的定期快照会在每个快照中加载相同的行）。

维度/维度表

维度表包含一个主键列，用于匹配事实表上的外键和属性，这些属性是查询中的约束和分组规范的主要目标。报告上的描述性标签通常是维度属性域值。

维度类型

• 杂项维度：交易业务流程通常会产生大量杂乱的低基数标志和指示符。您无需为每个标志和属性创建单独的维度，而是可以创建一个将它们组合在一起的杂项维度。该维度在模式中经常被标记为事务配置文件维度，不需要是所有属性的可能值的笛卡尔乘积，而应该只包含源数据中实际出现的值的组合。

• 扩展维度：维度可以包含对另一个维度表的引用。例如，银行账户维度可以引用表示开户日期的单独维度。这些次级维度引用称为扩展维度。扩展维度可以使用，但必须谨慎。

• 多值维度：事实表所附的每个维度都应具有单个值。但有时，维度确实具有多值，可以使用桥接表来处理（如下所示）。

使用桥接表捕获多值维度的演示

• 审计维度：当通过 ETL 创建事实表行时，创建包含当时已知的 ETL 处理元数据的审计维度是有帮助的。有用的审计维度属性：描述 ETL 代码版本的环境变量、时间戳、数据质量的基本指标等。

• 一致的维度：当不同维度表中的属性具有相同的列名和域内容时，维度表是一致的。不同的事实表可以通过与每个表相关联的一致维度进行组合。

• 退化维度：事实表中的维度键没有自己的维度表，因为所有有趣的属性都已放置在分析维度中。

维度表中的键

• 代理键：避免使用自然键（因为它们可能不够唯一、业务环境发生变化、手动生成很麻烦等）。使用维度代理键，它们是简单的整数，按顺序分配，从值 1 开始，每次需要新键时。

• 持久密钥：虽然多个代理密钥可能随着时间的推移与一个项目相关联，但持久密钥永远不会改变。它们应该独立于业务环境，因此生成代理密钥的方式与此相同。

缓慢改变的维度

有时，我们需要对维度进行更新。例如，用户是一个常见的维度，其属性一直在变化（从电话号码到地址等）。我们将这些维度称为“缓慢变化的维度”，下面是处理它们的几种方法。

• 类型 0：保留原始，因为值永远不会改变，适用于标记为原始的任何属性。

• 类型 1：覆盖维度行中的属性，但这意味着必须重新计算受影响的聚合事实表。

• 类型 2：在维度表中添加具有新的主代理键的新行，并将其用作事实表中后续行的外键。需要 3 个额外的列：start_date、end_date、current_row（布尔指示符）。这是处理维度变化的最常用方法。

类型 2 缓慢变化维度的演示。左表是建模的第一步，而右表可以解释随时间变化的属性。请注意，添加了 sid 列作为主键来唯一标识一行。product_id 列现在是持久键。通常先将结束日期设置为非常大的值，然后在发生变化时覆盖该值。

• 类型 3：添加具有original_val、current_val、effective_date模式的新列。当值发生变化时，像类型 1 一样更新current_val和effective_date。不常用。

• 类型 4或类型 5：与类型 1 类似，但将当前值保存在一个表中，并将参考历史记录保存在单独的表中。

• 类型 6：结合类型 1、2 和 3。每次数据发生变化时添加新行，但也维护当前列，以便事实表中的行可以按两个时间点进行过滤和分组。

维度和维度表的一般准则

非规范化扁平维度：尝试将多对一固定深度层次结构非规范化为扁平维度行上的单独属性。另一个极端是雪花模式，它是星型模式的扩展，其中维度表被分解为子维度；应避免使用这种模式，因为它很难理解并且会降低查询性能。

维度中的空属性：用空值代替“不可用”，因为不同的数据库对空值的分组和约束处理不一致。

标志和指示符作为文本维度属性：在维度表中，应使用单独查看时有意义的全文词语来补充神秘的缩写、真/假标志和操作指示符。

维度表大小：维度表通常较浅（行不多）且较宽（列多）。

维度之间的关系：在大多数情况下，维度之间的关联应该降级到事实表，其中两个维度都表示为单独的外键。

维度中的多个层次结构：许多维度包含多个自然层次结构。例如，日历日期维度可能具有从日到周到财务期的层次结构，以及从日到月到年的层次结构。位置密集型维度可能具有多个地理层次结构。在所有这些情况下，单独的层次结构都可以在同一维度表中优雅地共存。

• 固定深度位置层次结构：一系列多对一关系（例如，产品到品牌到类别到部门）。每个层次结构级别都应作为维度表中的单独位置属性出现。

• 略微不规则/可变深度层次结构：没有固定的层级数，但深度范围较小（即地理层次结构，美国有州，但大多数国家没有）。通过创建最大层级数的列，强制将这些层级放入固定深度层次结构中。

• 不规则/可变深度层次结构：深度不确定。由于固定模式，关系数据库很难处理。有两种方法可以处理它们：递归或桥接表（见下文）。

延迟的事实与延迟的维度

• 延迟到达事实：如果新事实行的最新维度上下文与传入行不匹配，则事实行延迟到达。当事实行延迟时会发生这种情况。在这种情况下，必须搜索相关维度（想想类型 2 缓慢变化的维度）以找到在延迟到达测量事件发生时有效的维度键。

• 延迟维度：当事实在维度上下文之前到达时，向维度表添加一个虚拟行，然后稍后覆盖（类型 1）。如果 etl 管道使用类型 2，则当信息到达维度表时，首先添加虚拟行，然后添加另一行，并使用新的代理键更新事实表。

案例研究：拼车应用

为了展示根据系统（OLTP 与 OLAP）对数据建模的方式之间的差异，让我们再次使用具有相同属性的拼车应用程序示例。

选择业务流程

每次乘客请求乘车时，都会触发此处的业务流程。

确定粒度

让我们坚持最直观的思路：事务性。事实表中的每一行都对应一次行程。

确定维度

• 对象：乘客、司机

• 什么：骑行状态。

• Where：地点（预订、取车、还车）

• 原因：也许我们可以在这里添加机场、餐厅等地点的地标。

• 如何：汽车、电话设备

• 时间：时间戳（预订、取车、还车）

确认事实

• 我们在这里得到的唯一数值是付款细节。

拼合模型

小结

• 我们不再将用户与其角色分开，因此如果某人既是司机又是乘客，那么他们将在两个表中拥有一行，且 ID 不同。虽然这增加了更新过程的复杂性，但它简化了分析师/科学家的查询结果。

• 尽管大多数列都很相似，但我们仍将乘客和司机分开，因为它们在概念上是独立的实体，并且它允许更细粒度的连接（如果我只关心司机，那么我就不必筛选非司机了。）

• 请注意，汽车和设备表是分开的，并通过行程（事实）表与相应的乘客/司机相关联。这遵循了构建事实表的指导原则之一，即避免使用扩展表并通过事实表降低维度之间的任何关系。

往期推荐