SQL 数据处理全链路：从数据清洗、ETL 到零售关联分析

在日常开发中，我们经常把 SQL 理解成“增删改查”的工具。但如果把视角从单个业务系统拉高到整条数据链路，会发现 SQL 实际上贯穿了数据的产生、清洗、集成、分析和展示。

一条比较完整的数据处理链路可以概括为：

业务系统（OLTP）
      │
      ▼
数据抽取 Extract
      │
      ▼
数据清洗 / 转换 Transform
      │
      ▼
数据加载 Load
      │
      ▼
数据仓库（OLAP）
      │
      ├── SQL 聚合分析
      ├── Excel / BI 可视化
      └── SQL + Python 数据挖掘

本文结合几篇 SQL 数据处理相关资料，对其中的知识重新组织，重点梳理以下几个问题：

OLTP、OLAP 在整条数据链路中分别负责什么；
如何使用 SQL 做数据质量检查和数据清洗；
ETL 的 Extract、Transform、Load 分别在做什么；
Kettle 中 Transformation 与 Job 的关系；
SQL 如何与 Python 配合完成零售购物篮关联分析；
SQLite、Redis 等数据库在数据处理体系中分别适合什么场景。

1. 先建立整体认知：OLTP 与 OLAP

SQL 的使用场景大体可以分为两类：OLTP 和 OLAP。

1.1 OLTP：服务在线业务

OLTP（Online Transaction Processing，联机事务处理）更关注实时性，典型工作包括：

新增、删除、修改、查询业务数据；
事务处理；
SQL 查询优化；
高并发读写；
通过主从等架构提升可用性。

对于互联网业务系统来说，订单、库存、会员、支付、财务流水等数据，首先都是在 OLTP 系统中产生的。

1.2 OLAP：面向分析和决策

OLAP（Online Analytical Processing，联机分析处理）更关注分析能力。

它通常不会像交易系统一样要求毫秒级写入，而是更关注：

大规模数据聚合；
报表生成；
趋势分析；
数据挖掘；
为业务决策提供依据。

问题在于：业务系统中的原始数据并不天然适合分析。

数据可能存在空值、重复、单位不统一、字段类型错误、不同系统字段命名不一致等问题。因此，从 OLTP 到 OLAP，中间必须经过清洗、转换和集成。

这也是 ETL 出现的原因。

2. 数据质量决定分析上限

数据分析中有一个很重要的事实：

模型只能尽可能挖掘已有数据中的信息，但无法凭空修复低质量数据。

因此，在做报表、机器学习或者数据挖掘之前，第一件事通常不是“选什么算法”，而是先确认数据质量。

资料中把数据清洗需要关注的问题归纳为四个方面，可以记成“完全合一”：

完整性：数据是否存在缺失；
全面性：字段类型、格式、单位等是否符合实际语义；
合法性：字段值是否处于合理范围；
唯一性：是否存在不应该出现的重复数据。

下面分别来看。

3. 完整性：先把 NULL 找出来

数据清洗最常遇到的问题就是缺失值。

假设有一张 titanic_train 表，可以先统计单个字段的 NULL 数量：

1
2
3

SELECT COUNT(*) AS num
FROM titanic_train
WHERE Age IS NULL;

如果要一次检查多个字段，可以使用 SUM + CASE WHEN：

SELECT
    SUM(CASE WHEN Age IS NULL THEN 1 ELSE 0 END)   AS age_null_num,
    SUM(CASE WHEN Cabin IS NULL THEN 1 ELSE 0 END) AS cabin_null_num,
    SUM(CASE WHEN Embarked IS NULL THEN 1 ELSE 0 END) AS embarked_null_num
FROM titanic_train;

这里之所以使用 SUM，本质上是在做布尔计数：

1
2
3

字段为空     -> 1
字段不为空   -> 0
最终 SUM     -> NULL 总数

如果字段数量很多，手工写几十个表达式显然不现实。资料中给出的思路是利用 information_schema.COLUMNS 获取字段列表，再通过存储过程和动态 SQL 对每一列逐个检查。

这类方法的核心不是某一段具体存储过程，而是一个通用思路：

当数据质量规则需要对大量字段重复执行时，把规则程序化，而不是人工复制 SQL。

4. 缺失值到底应该怎么处理

发现 NULL 之后，并不是一律填 0。

资料中总结了三种基本处理方式：

删除缺失记录；
使用均值填充；
使用高频值填充。

真正的选择标准应该取决于字段含义。

4.1 均值填充：适合连续数值

例如 Age 是年龄，可以使用平均年龄进行填充：

UPDATE titanic_train
SET Age = (
    SELECT ROUND(AVG(Age), 1)
    FROM titanic_train2
)
WHERE Age IS NULL;

资料中的例子额外复制了一张临时表 titanic_train2，原因是某些 MySQL 场景下，直接在更新目标表时又从同一目标表子查询，可能触发：

1	`You can't specify target table ... for update in FROM clause`

4.2 高频值填充：适合离散分类字段

例如登船港口 Embarked 只有少量离散值，统计发现 S 出现频率最高，可以用它填充缺失值：

1
2
3

UPDATE titanic_train
SET Embarked = 'S'
WHERE Embarked IS NULL;

4.3 有些缺失值不应该强行填

Cabin 表示船舱位置，取值分布非常分散，而且缺失数量很大。

这种字段如果没有可靠的推断依据，使用平均值或者最高频值都没有业务意义。此时保留 NULL，反而比“为了完整而完整”更合理。

所以，数据清洗的一个核心原则是：

缺失值处理不是 SQL 技巧问题，而是字段语义问题。

5. CSV 导入时，要特别小心“空字符串”和 NULL

资料中的实践还暴露出一个非常容易踩坑的问题：CSV 中的空值进入 MySQL 后，不一定会自动变成真正的 NULL。

例如年龄为空时，如果目标字段是数值类型：

严格模式下可能直接导入失败；
非严格模式下可能被转换成 0；
字符字段则可能变成空字符串 ''。

这会让后续数据清洗产生误判。

更稳妥的导入方式，是在 LOAD DATA 时显式使用用户变量和 NULLIF：

LOAD DATA INFILE '/path/train.csv'
INTO TABLE titanic_train
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
    passenger_id,
    survived,
    pclass,
    name,
    sex,
    @age,
    sibsp,
    parch,
    ticket,
    fare,
    @cabin,
    embarked
)
SET
    age = NULLIF(@age, ''),
    cabin = NULLIF(@cabin, '');

这里：

1	`NULLIF(@age, '')`

表示如果读取到的是空字符串，就转换为真正的 NULL。

这个细节非常重要，因为：

1 2	`0 != NULL '' != NULL`

一旦导入阶段把空值“污染”成 0，后面的平均值、最小值、分布统计都会被影响。

6. 全面性：字段类型也属于数据质量

通过 CSV 工具直接导入数据库时，有一种常见现象：所有字段都被创建成 VARCHAR(255)。

但从业务含义上看：

PassengerId 应该是整数；
Survived 应该是整数或离散标识；
Pclass 应该是整数；
Age、Fare 应该是数值类型。

因此清洗不仅要修改“数据值”，还应该修复表结构。

例如：

ALTER TABLE titanic_train
    MODIFY PassengerId INT NOT NULL;

ALTER TABLE titanic_train
    MODIFY Survived INT NOT NULL;

ALTER TABLE titanic_train
    MODIFY Pclass INT NOT NULL;

ALTER TABLE titanic_train
    MODIFY Age DECIMAL(5,2) NOT NULL;

ALTER TABLE titanic_train
    MODIFY Fare DECIMAL(7,4) NOT NULL;

如果某个字段在业务上不允许为空，也应该增加 NOT NULL 约束。

数据库约束的价值在于：

不要只在清洗阶段修数据，还要让数据库结构阻止同类脏数据再次进入。

7. 合法性与唯一性

7.1 合法性

合法性检查关注的是“这个值虽然不为空，但它是不是合理”。

典型检查包括：

数值是否越界；
日期是否合理；
金额是否出现非法负值；
枚举字段是否出现未知值；
单位是否统一；
格式是否符合约定。

例如年龄字段即使全部非空，也仍然可能出现 -3、500 这样的非法数据。

7.2 唯一性

唯一性关注重复记录。

如果存在天然业务主键，例如乘客 ID、订单 ID、交易 ID，可以通过主键或者唯一索引把重复数据挡在数据库层。

1 2	`ALTER TABLE titanic_train ADD PRIMARY KEY (PassengerId);`

如果无法添加主键，至少也应该使用聚合检查：

SELECT PassengerId, COUNT(*) AS cnt
FROM titanic_train
GROUP BY PassengerId
HAVING COUNT(*) > 1;

8. 从数据清洗继续向前：ETL

当数据来自多个系统时，问题会比单表清洗更复杂。

不同数据源可能：

使用不同 DBMS；
字段命名不同；
字段类型不同；
数据存在冗余；
同一个实体在多个系统重复出现；
输出格式要求不同。

因此需要数据集成，而数据集成中最经典的过程就是 ETL。

ETL 分别表示：

1
2
3

E = Extract    抽取
T = Transform  转换
L = Load       加载

整个过程可以理解为：

OLTP 数据源
   │
   ├── Extract：把数据取出来
   │
   ├── Transform：清洗、映射、验证、过滤
   │
   └── Load：写入目标系统
          │
          ▼
      OLAP / 数据仓库

9. Extract：全量抽取与增量抽取

抽取阶段首先要搞清楚数据在哪里。

需要确认：

数据源使用什么 DBMS；
是结构化还是非结构化数据；
表结构如何；
数据规模如何；
数据变化频率如何。

资料中区分了两种抽取方式。

全量抽取

每次把整个数据集重新读取一遍。

优点是逻辑简单，缺点是数据量大时成本很高。

增量抽取

只获取上一次同步之后发生变化的数据。

其价值在于动态捕捉数据源变化，并将变化持续同步到目标系统。

因此，在持续运行的数据集成任务中，增量抽取通常更重要。

10. Transform：真正的数据加工区

Transform 是 ETL 中最“重”的部分。

常见工作包括：

字段映射；
类型转换；
数据清洗；
数据验证；
数据过滤；
多表关联；
衍生字段计算；
编码转换；
去重。

可以把它理解成数据流水线：

原始数据
   │
   ▼
字段映射
   │
   ▼
清洗
   │
   ▼
验证
   │
   ▼
过滤 / 关联 / 衍生
   │
   ▼
标准数据

ETL 的目的并不只是“搬数据”，而是把不同来源、不同质量、不同规范的数据转换成统一标准。

11. Load：把转换结果送到目标系统

数据转换完成之后，需要加载到目标位置。

如果目标是关系型数据库，可以：

直接执行 SQL 插入；
批量加载；
根据主键进行插入或更新。

如果目标是文件，也可以输出为 TXT、CSV 等格式。

因此，Load 的目标并不一定是数据库，也可能是下游分析系统需要的文件。

12. Kettle：把 ETL 做成可视化数据流

使用 Kettle 演示 ETL。

Kettle 由 Java 开发，可以通过可视化方式搭建数据转换流程。

其中有三个重要组件：

组件	作用
Spoon	图形化设计转换和作业
Pan	命令行执行 Transformation
Kitchen	命令行执行 Job

最容易混淆的是 Transformation 和 Job。

Transformation：数据转换

Transformation 对应 .ktr 文件。

它关注一条具体的数据处理流水线，例如：

1	`表输入 -> 字段转换 -> 过滤 -> 插入/更新`

Job：工作流编排

Job 对应 .kjb 文件。

它负责更高层的流程控制，可以组织多个 Transformation。

可以简单理解为：

Job
├── Transformation A
├── Transformation B
└── Transformation C

也就是说：

Transformation 解决“数据怎么变”，Job 解决“整个任务怎么跑”。

13. Kettle 示例一：两个 MySQL 数据库之间同步表

1	`test1.heros ---> test2.heros`

目标库中已经存在表结构，只需要同步数据。

核心流程只有两个组件：

表输入
  │
  ▼
插入 / 更新

第一步：表输入

从 test1 查询：

1 2	`SELECT * FROM heros;`

第二步：插入 / 更新

目标连接指向 test2，目标表为 heros。

以 id 作为匹配条件：

1	`source.id = target.id`

然后配置需要更新的字段。

这样，当目标表中不存在对应 id 时可以插入，存在时则进行更新。

这个案例本质上就是最基础的数据库同步模型：

1	`Source Query -> Key Match -> Insert / Update`

14. Kettle 示例二：把交易流水加工成业务语义

第二个例子更有代表性。

有两张表：

1 2	`account：账户信息 trade：交易流水`

trade 中只有：

打款账户 account_id1；
收款账户 account_id2；
转账金额 amount。

而输出文件还需要增加一列 value：

1 2	`收款账户是个人 -> 对私客户发生的交易收款账户是公司 -> 对公客户发生的交易`

整个数据流可以抽象为：

trade 表输入
     │
     ▼
account 表查询
     │
     ▼
获取 customer_type
     │
     ▼
过滤记录
   ┌─┴───────────┐
   │             │
公司账户       个人账户
   │             │
增加常量       增加常量
   │             │
   └──────┬──────┘
          ▼
      文本文件输出

这正是 Transform 的典型工作：

原始数据库里并没有最终业务字段，但可以通过关联、判断和转换得到新的业务语义。

例如最终输出：

1
2
3

account_id1;account_id2;amount;value
322202020312335;622202020312337;200.0;对私客户发生的交易
322202020312335;322202020312336;100.0;对公客户发生的交易

15. 清洗、集成完成之后，才真正进入分析阶段

到这里，数据已经经历了：

1	`采集 -> 清洗 -> 集成 -> 标准化`

接下来才是 OLAP 分析。

五种通过 SQL 进入数据分析或机器学习的方式：

SQL Server + Analysis Services；
PostgreSQL + MADlib；
BigQuery ML；
SQLFlow；
SQL + Python。

这些方式背后其实可以分成两种思路。

思路一：把机器学习能力集成进数据平台

例如通过 SQL 直接调用模型训练、分类、聚类、关联规则等能力。

优点是数据无需频繁搬运，SQL 使用者也可以直接进入分析流程。

思路二：SQL 与算法层解耦

也就是：

1 2	`SQL 负责取数 Python 负责分析`

更推荐用这个方式完成复杂分析，因为 Python 在算法调参、数据预处理、模型组合方面更灵活。

16. 零售分析：什么是购物篮关联分析

使用了一个面包店零售数据集来说明关联分析。

数据包含：

Date：日期；
Time：时间；
Transaction：交易 ID；
Item：商品名称。

数据一共有 21293 条记录。

原始数据中还存在典型的数据质量问题：

交易 ID 并不完全连续；
同一交易中商品可能重复；
Item 存在 NONE；
商品大小写格式不统一。

这恰好说明了一件事：

数据分析算法之前，仍然需要数据清洗。

17. Apriori 的两个核心指标：支持度与置信度

购物篮分析的目标，是寻找经常一起出现的商品组合。

使用 Apriori 算法查找频繁项集。

17.1 支持度 Support

支持度描述某个商品组合在所有订单中出现的频率。

例如有 5 笔订单，其中牛奶出现 4 次：

1	`Support(牛奶) = 4 / 5 = 0.8`

如果“牛奶 + 面包”同时出现 3 次：

1	`Support(牛奶, 面包) = 3 / 5 = 0.6`

当项集支持度大于等于设定的最小支持度时，就可以称为频繁项集。

17.2 置信度 Confidence

置信度是一个条件概率概念。

例如：

1 2	`牛奶出现 4 次牛奶 + 啤酒同时出现 2 次`

则：

1	`Confidence(牛奶 -> 啤酒) = 2 / 4 = 0.5`

表示购买牛奶的订单中，有 50% 同时购买了啤酒。

反过来，如果啤酒出现 3 次：

1	`Confidence(啤酒 -> 牛奶) = 2 / 3 ≈ 0.67`

注意：

1	`Confidence(A -> B) != Confidence(B -> A)`

因为分母不同。

18. SQL + Python 完成关联分析

整体实现可以拆成三个阶段：

MySQL
  │
  ▼
SQLAlchemy 查询
  │
  ▼
Pandas 数据预处理
  │
  ▼
efficient_apriori
  │
  ▼
频繁项集 + 关联规则

18.1 从数据库加载数据

import sqlalchemy as sql
import pandas as pd

engine = sql.create_engine(
    'mysql+pymysql://root:password@localhost/wucai'
)

data = pd.read_sql_query(
    'SELECT * FROM bread_basket',
    engine
)

18.2 清洗 Item

统一为小写：

1	`data['Item'] = data['Item'].str.lower()`

删除无效的 none：

1
2
3

data = data.drop(
    data[data.Item == 'none'].index
)

18.3 按交易聚合，并在订单内去重

购物篮算法需要的输入是：

1
2
3

订单1 -> {bread, coffee}
订单2 -> {cake, coffee, tea}
订单3 -> {bread, pastry}

因此可以按 Transaction 分组，把每笔订单的商品转换成集合：

transactions = list(
    data.groupby('Transaction')
        .agg(lambda x: set(x.Item.values))['Item']
)

这里使用 set 非常关键，因为同一订单中重复出现同一个商品，并不应该被当成多个独立商品参与项集计算。

18.4 执行 Apriori

from efficient_apriori import apriori

itemsets, rules = apriori(
    transactions,
    min_support=0.02,
    min_confidence=0.5
)

print('频繁项集：', itemsets)
print('关联规则：', rules)

资料中的参数是：

1 2	`最小支持度 0.02 最小置信度 0.5`

在示例结果中：

1 个商品组成的频繁项集有 19 种；
2 个商品组成的频繁项集有 14 种；
得到 8 条关联规则；
多条规则都指向 coffee，例如 cake -> coffee、cookies -> coffee 等。

这就是从数据库中的交易流水，逐步得到可用于商品组合分析的关联规则。

19. SQLite：轻量数据库同样可以成为分析数据源

示例：读取本地聊天记录并生成词云。

流程可以抽象成：

SQLite
  │
  ▼
读取聊天表
  │
  ▼
提取 Message
  │
  ▼
移除 HTML 标签
  │
  ▼
移除停用词
  │
  ▼
jieba 分词
  │
  ▼
WordCloud

核心查询思路是先找出聊天相关数据表：

SELECT name
FROM sqlite_master
WHERE type = 'table'
  AND name LIKE 'Chat%';

然后遍历每张表，读取消息字段。

这个示例说明：

SQL 并不只服务于大型 MySQL、PostgreSQL 数据库，本地 SQLite 文件同样可以成为 Python 数据分析的数据入口。

20. Redis：它在数据体系中的位置不是“替代 MySQL”

Redis 与 MongoDB、MySQL 的定位。

Redis 的特点是：

Key-Value 模型；
数据主要在内存中操作；
读写速度高；
支持字符串、哈希、列表、集合、有序集合等数据结构；
支持持久化，但通常更常见的定位仍然是缓存和高性能数据结构服务。

而 MongoDB 更偏向文档数据库，承担长期数据存储的职责。

在典型业务系统中，可以把 MySQL 和 Redis 的关系理解为：

          高频访问
客户端  -----------> Redis
                    │
                    │ miss
                    ▼
                  MySQL

Redis 适合保存：

热点数据；
实时排行榜；
高并发计数；
临时状态。

MySQL 更适合保存：

完整业务数据；
持久化实体；
需要事务和复杂查询的数据。

Redis DECR 的原子计数例子

资料还给出了一个抢票思路：用 Redis 的 DECR 对库存做原子减一。

逻辑非常简单：

DECR ticket_count
      │
      ├── 结果 >= 0：抢票成功
      └── 结果 < 0 ：票已售罄

Python 伪代码：

temp = redis_client.decr('ticket_count')

if temp >= 0:
    print('抢票成功')
else:
    print('抢票失败')

这个例子体现了 Redis 的另一个价值：

某些高并发场景不需要复杂关系模型，只需要一个足够快并且具备原子语义的数据操作。

21. 把四部分知识串起来

把数据清洗、数据集成、数据分析和 DBMS 选型放在一起后，可以得到一条完整的数据处理思路。

第一阶段：业务系统产生数据

1 2	`OLTP MySQL / PostgreSQL / 业务数据库`

重点是：

正确性；
实时性；
事务；
高并发。

第二阶段：数据清洗

按照“完全合一”检查：

完整性 -> NULL / 缺失值
全面性 -> 类型 / 格式 / 单位
合法性 -> 范围 / 业务规则
唯一性 -> 重复记录

第三阶段：数据集成

通过 ETL：

1	`Extract -> Transform -> Load`

把不同数据库、不同格式的数据统一起来。

第四阶段：OLAP 分析

SQL 聚合
BI 报表
SQL + Python
机器学习 / 数据挖掘

第五阶段：展示和反馈

可以通过：

Excel 数据透视表；
数据透视图；
Python 可视化；
BI 工具；
数据挖掘结果。

最终让数据重新服务业务决策。

22. 一套可复用的数据处理 Checklist

以后面对一份陌生数据，可以按下面的顺序快速检查。

数据源

数据来自哪个系统？
OLTP 还是分析库？
是 MySQL、PostgreSQL、SQLite 还是其他 DBMS？
数据量多大？
需要全量还是增量抽取？

数据质量

NULL 有多少？
空字符串有没有被误当成有效值？
字段类型是否合理？
单位是否统一？
枚举值是否合法？
是否存在重复记录？
是否有业务主键？

数据转换

是否需要字段映射？
是否需要关联维表？
是否需要衍生字段？
是否需要过滤无效数据？
是否需要统一编码和格式？

数据加载

目标是数据库、数据仓库还是文件？
是 Insert、Update 还是 Upsert？
是否需要周期性调度？

数据分析

SQL 聚合是否足够？
是否需要 Python？
是否需要关联分析、分类、聚类等算法？
分析前是否已经再次确认数据质量？

23. 总结

看似分别在讲 SQLite、Redis、数据清洗、Kettle 和 Apriori，但把它们串起来后，本质上讲的是同一件事：数据如何从业务系统一步步变成可用于决策的信息。

可以把整套思路压缩成一句话：

OLTP 负责产生可靠的业务数据，
SQL 负责查询和清洗，
ETL 负责集成和转换，
OLAP 负责聚合分析，
Python 负责更灵活的数据挖掘，
Redis / SQLite 等 DBMS 则根据场景承担各自最擅长的角色。

真正值得掌握的并不是某一条 SQL 或某一个 ETL 工具，而是这条完整的数据链路。

当遇到一份数据时，不要急着问“应该用什么算法”，更应该先问：

数据从哪里来？质量怎么样？如何统一？最终要回答什么业务问题？

这四个问题想清楚之后，SQL 才真正从“查询语言”变成数据工程和数据分析之间的连接器。

数据库

#Redis #SQL #SQLite #OLTP #OLAP #数据清洗 #ETL #Kettle #Python #Apriori

SQL 数据处理全链路：从数据清洗、ETL 到零售关联分析

https://allendericdalexander.github.io/2026/08/11/db/53sql/08sql-data-pipeline-cleaning-etl-analysis/

作者

AtLuoFu

发布于

2026年8月11日

许可协议

RFC 1 带读：Host Software——互联网协议协作从这里开始上一篇

Redis 从入门到实战：高性能原理、数据结构、事务、Lua 与排行榜下一篇