数据库设计与开发

Appearance

📘OLAP 与数据仓库课程笔记（完整详细版） ​

一、概述 ​

1. OLTP 与 OLAP 的区别 ​

特性OLTP（在线事务处理）OLAP（在线分析处理）
查询类型短小频繁的事务查询少量但复杂、耗时长的分析查询
数据更新实时更新定期批量更新
应用场景收银系统、机票预订市场趋势分析、销售预测
数据来源单个业务系统多个系统的集成数据

二、数据仓库（Data Warehouse） ​

1. 定义 ​

  • 数据仓库是多个数据源的数据集成，集中存储，供分析使用。
  • 通常定期从源数据库复制数据，如每天或每周一次。

2. 构建方式 ​

  • ETL（Extract, Transform, Load）：
    • 提取：从各个业务数据库中提取数据
    • 转换：清洗、格式统一、标准化等
    • 加载：将数据加载到数据仓库中

3. 用途 ​

  • 支持复杂的分析型查询（OLAP）
  • 避免对 OLTP 系统造成性能压力

三、星型模式（Star Schema） ​

1. 组成结构 ​

✅ 事实表（Fact Table） ​

  • 包含度量值（如销售额）和维度键
  • 示例：Sales(bar, beer, drinker, day, time, price)

✅ 维度表（Dimension Tables） ​

  • 描述事实表中的实体信息
  • 示例：
    sql
    Bars(bar, addr, license)
Beers(beer, manf)
Drinkers(drinker, addr, phone)

2. 可视化图示 ​

           +-----------+
           | Dimension |
           | Tables    |
     +-----+----+  +---+----+  +-----+----+
     | Beers   |  | Bars   |  | Drinkers |
     +---------+  +--------+  +----------+
         \          |           /
          \         |          /
           \        v         /
            +--------------+
            | Fact Table   |
            | Sales        |
            +--------------+

3. 维度属性 vs 依赖属性 ​

  • 维度属性：对应维度表的主键，如 bar, beer, drinker, time
  • 依赖属性：由维度属性决定的值，如 price

四、ROLAP 与 MOLAP ​

类型全称描述
ROLAPRelational OLAP使用关系型数据库支持星型模式，适合大数据量
MOLAPMultidimensional OLAP使用多维数据库，以“数据立方体”形式组织数据，适合快速聚合查询

五、数据立方体（Data Cube） ​

1. 概念 ​

  • 维度 = 维度表的主键（如 bar, beer, drinker, time）
  • 点 = 一个具体的事实（如某个时间在某酒吧购买某啤酒的价格）
  • 边界（Marginals）= 对某些维度进行聚合的结果（如 SUM(price) over bar）

2. 示例 ​

text
cube[bar="Joe's Bar", beer="Bud", drinker="Jim", time="*"] 
→ 表示 Jim 在 Joe’s Bar 所有时间购买 Bud 的总价格

3. 聚合可视化 ​

             price
               /|\
              / | \
             bar beer drinker

六、操作：上卷（Roll-Up）与下钻（Drill-Down） ​

1. 上卷（Roll-Up） ​

  • 合并维度，向上汇总
  • 示例：按“酒吧”维度上卷 → 得到每个饮者在所有酒吧购买 Bud 的总价

2. 下钻（Drill-Down） ​

  • 分解维度，向下细化
  • 示例：发现 Joe’s Bar 销售 A-B 啤酒较少，进一步查看具体哪种 A-B 啤酒销量低

✅ 示例对比： ​

初始数据（按酒吧、饮者分组） ​

BarJimBobMary
Joe’s Bar453330
Nut-House503642
Blue Chalk383140

上卷（按酒吧） ​

DrinkerTotal (A-B)
Jim133
Bob100
Mary112

下钻（按啤酒） ​

BeerJimBobMary
Bud402940
Miller453137
Bud Light484035

七、数据挖掘（Data Mining） ​

1. 定义 ​

  • 从海量数据中发现有用的信息和模式
  • 不是简单统计，而是发现隐藏的关联、聚类、趋势等

2. 应用场景 ​

  • 用户行为聚类（如网页主题分类）
  • 检测异常行为（如信用卡欺诈）
  • 推荐系统（如亚马逊推荐商品）

八、购物篮分析（Market Basket Analysis） ​

1. 原理 ​

  • 寻找经常一起出现的商品组合（频繁项集）
  • 支持度（Support）：项集出现在多少个购物篮中

2. SQL 实现（寻找频繁商品对） ​

sql
SELECT b1.item, b2.item
FROM Baskets b1, Baskets b2
WHERE b1.basket = b2.basket AND b1.item < b2.item
GROUP BY b1.item, b2.item
HAVING COUNT(*) >= s;

3. Apriori 算法优化 ​

步骤： ​

  1. 先找出所有高频单品（单个商品出现次数 ≥ s）
  2. 仅基于这些高频单品构建候选商品对，减少计算量

优点： ​

  • 减少连接的数据量
  • 显著提升性能

✅ 示例： ​

假设超市有 10,000 种商品，平均每个购物篮 10 件，支持度阈值为 1%

  • 最多只有 1/10 的商品可能是高频项
  • 可节省大量计算资源

九、总结对比表 ​

概念说明
OLTP短小、频繁、实时事务查询
OLAP复杂、分析型、批量处理
数据仓库整合多个源系统的只读数据库
星型模式事实表 + 维度表
ROLAP基于关系型 DBMS 的 OLAP 实现
MOLAP基于多维模型的 OLAP 实现
数据立方体多维数据结构，支持聚合与切片
Roll-Up向上汇总，减少维度
Drill-Down向下细化，增加维度
数据挖掘发现隐藏模式和趋势
购物篮分析寻找商品组合规律，用于营销策略

十、关键概念总结 ​

  1. OLAP 是数据分析的核心技术，适用于大规模聚合查询。
  2. 数据仓库是 OLAP 的基础，通过 ETL 流程整合数据。
  3. 星型模式是数据仓库中最常见的结构，包含事实表和维度表。
  4. 数据立方体提供多维视角，便于聚合分析。
  5. Roll-Up 和 Drill-Down是 OLAP 中常用的两种操作方式。
  6. 数据挖掘是从大数据中发现价值的过程，常用于市场分析、欺诈检测等。
  7. Apriori 算法能高效找出购物篮中的频繁项集，优化 SQL 查询性能。