TapCub 正式上线:统计、分析、客服一个平台免费用
数据分析

用 SQL 做 ETL:一个可以直接运行的提取、转换、加载示例

用 SQL 做 ETL,就是用 SELECT 提取、用表达式和窗口函数转换、用 INSERT ... SELECT 和 upsert 加载。附一个完整可运行的例子,行数和金额逐项核对,并能安全重跑。

许安 发布于 2026 年 11 月 20 日 11 分钟阅读 数据分析
示例数据:12 行原始数据去掉重复的 E003 后剩 11 行,再拒绝 E007(13 月)和 E008(没有用户)后得到 9 行合格数据。被拒绝的行连同原因放在单独的表里。示例数据

用 SQL 做 ETL,就是用 SELECT 提取行,用表达式、连接和窗口函数转换,再用 INSERT ... SELECT 加载,并用 upsert 让重跑也安全。下面是一个可以在 SQLite 里直接运行的完整例子:一张 12 行的事件表,被清洗、去重、加载进三张表,并核对到每一行、每一分钱。

读完你会知道

  • 把所有清洗规则放进一个视图,加载之前先检查结果
  • 用主键和 ON CONFLICT 让加载幂等,并通过运行两遍来证明
  • 核对行数和金额:提取 12 = 重复 1 + 拒绝 2 + 加载 9,购买金额 466.00 − 被拒绝的 45.00 = 421.00

用 SQL 做 ETL 是什么意思

ETL 是提取(extract)、转换(transform)、加载(load)。亚马逊的描述最直白:从源数据库取出相关记录,整理成更适合分析的形态,再把处理好的数据写进目标数据库。SQL 对这三步都适用:提取是一条 SELECT(或一次导出),转换是 SELECT 里的表达式、连接和窗口函数,加载是 INSERT ... SELECT 写入目标表。

只用 SQL 做 ETL,适合源表和目标表能在同一个数据库会话里访问、数据量在几千到几百万行的场景。需要定时调度、多个数据源或多种文件格式、失败告警时它就不够了,最后一节给出判断规则。

如果先加载再转换,顺序就叫 ELT。ETL 与 ELT 的区别做了比较,ETL 术语页有简短定义。本文是 SQL 本身的一个完整、可运行的例子。下面的内容在 SQLite 命令行、DB Browser for SQLite 或 Python 的 sqlite3 模块里原样可跑(需要 SQLite 3.25 或更新版本,因为用到了窗口函数和 upsert)。文中展示的计数是在 SQLite 3.51.0 上运行得到的实际输出。

源表和它的问题

示例数据:从一家商店的事件日志导出的 12 行,带有真实导出常见的各种问题。

-- 原始导出表:全部是文本,没有任何约束,和真实导出一样
CREATE TABLE raw_events (
  event_id TEXT,        -- 事件编号,本应不重复,但导出里有重复
  occurred_at TEXT,     -- 发生时间,格式不统一
  user_email TEXT,      -- 用户邮箱,大小写和空格混乱,可能为空
  event_name TEXT,      -- 事件名,大小写混乱
  amount_cents INTEGER, -- 金额,单位是分
  page TEXT             -- 页面路径
);
-- 12 行示例数据:E003 出现两次,E007 的月份是 13,E008 没有用户
INSERT INTO raw_events VALUES
 ('E001','2026-11-01 09:15:00','Ann@Example.com ','Purchase', 4500,'/checkout/done'),   -- 邮箱有大写和末尾空格
 ('E002','2026-11-01T10:02:30','bob@example.com','purchase',12900,'/checkout/done'),   -- 时间用 T 分隔,和其他行不一致
 ('E003','2026-11-01 11:40:00','ann@example.com','page_view',NULL,'/pricing'),   -- E003 的原始行
 ('E003','2026-11-01 11:40:00','ann@example.com','page_view',NULL,'/pricing'),   -- E003 的重复副本
 ('E004','2026-11-01 23:59:59','cy@example.com','PURCHASE ', 2500,'/checkout/done'),   -- 事件名全大写,末尾有空格
 ('E005','2026-11-02 08:00:00','bob@example.com','page_view',NULL,'/blog'),
 ('E006','2026-11-02 08:05:00','dee@example.com','purchase', 7800,'/checkout/done'),
 -- E007 的月份是 13,整行时间无效
 ('E007','2026-13-02 09:00:00','eve@example.com','purchase', 3000,'/checkout/done'),
 -- E008 没有用户邮箱
 ('E008','2026-11-02 14:30:00',NULL,'purchase', 1500,'/checkout/done'),
 ('E009','2026-11-03 10:00:00','ann@example.com','purchase', 4500,'/checkout/done'),
 ('E010','2026-11-03 12:12:00','bob@example.com','page_view',NULL,'/pricing'),
 ('E011','2026-11-03 18:45:00','cy@example.com','purchase', 9900,'/checkout/done');
问题涉及的行处理规则
同一个 event_id 出现两次E003(2 份)每个 event_id 只留一行
时间分隔符不统一(空格和 T)E002统一成 YYYY-MM-DD HH:MM:SS
不可能的日期(13 月)E007带原因拒绝,不加载
缺少用户E008带原因拒绝,不加载
大小写混用、末尾空格E001、E004lower(trim(...))
金额是整数分全部购买行除以 100

目标是三张表:events_clean 放合格的行,events_rejected 放没通过规则的行,daily_summary 每天一行。

-- 目标表 1:合格事件,event_id 做主键,防止重复加载
CREATE TABLE events_clean (
  event_id TEXT PRIMARY KEY,   -- 事件编号,主键,不允许重复
  occurred_at TEXT NOT NULL,   -- 统一格式后的时间,不允许为空
  user_id TEXT NOT NULL,       -- 清洗后的用户标识,不允许为空
  event_name TEXT NOT NULL,    -- 小写、去空格后的事件名
  amount REAL,                 -- 金额,单位元
  page TEXT);                  -- 页面路径
-- 目标表 2:被拒绝的行,加上拒绝原因,方便以后追查
CREATE TABLE events_rejected (event_id TEXT, reason TEXT);
-- 目标表 3:每天一行的汇总,day 做主键,方便 upsert
CREATE TABLE daily_summary (
  day TEXT PRIMARY KEY,   -- 日期,主键,每天只有一行
  events INTEGER,         -- 当天事件总数
  buyers INTEGER,         -- 当天购买的去重用户数
  revenue REAL);          -- 当天购买金额合计

提取:只取需要的

真实流程里,提取是对另一个系统的查询或一次文件导出。两个习惯可以让它保持轻量。只选目标表需要的列;并且增量提取:记下一个水位线,也就是已加载数据中最大的时间戳,下次只取更新的行。

-- 增量提取:只取比水位线更新的行,:last_loaded_at 是上次加载到的时间
SELECT event_id, occurred_at, user_email, event_name, amount_cents, page
FROM raw_events
WHERE occurred_at > :last_loaded_at;

第一次运行时水位线是空字符串,全部数据都会取出。有两点要小心。比较的列要用源系统可靠写入的列,比如插入时间,并且每次多回看几分钟来捕捉迟到的行:这样做只有在后面的加载步骤幂等时才安全。另外不要对自己不控制的表用 SELECT *,源端新增一列会悄悄改变目标表。

本例的原始行已经在 raw_events 里,所以提取就是视图里的 FROM 子句,水位线留作你自己流程里的注释。

转换:在一个视图里清洗、去重、标准化

把所有规则放进一个视图,这样加载之前就能先检查结果。

-- 暂存视图:所有清洗规则都放在这里,加载前可以先查看结果
CREATE VIEW v_stage AS
WITH norm AS (
  SELECT event_id,
         datetime(replace(occurred_at,'T',' '))          AS occurred_at,   -- 统一时间格式,非法日期变成 NULL
         lower(trim(user_email))                         AS user_id,       -- 去空格、转小写
         lower(trim(event_name))                         AS event_name,    -- 事件名也要统一
         amount_cents / 100.0                            AS amount,        -- 分换成元,用 100.0 避免整数除法
         page,
         row_number() OVER (PARTITION BY event_id ORDER BY rowid) AS rn   -- 同一事件编号的副本依次编号
  FROM raw_events)
SELECT * FROM norm WHERE rn = 1;   -- 每个事件编号只留编号为 1 的那份
  • replace(occurred_at,'T',' ') 把 2026-11-01T10:02:30 变成空格分隔的形式。SQLite 的 datetime() 两种写法都接受,按其文档,无法解析的输入返回 NULL。这让 E007 可以被检测出来:datetime('2026-13-02 09:00:00') 是 NULL,你可以判断它,而不是让加载崩溃。
  • lower(trim(...)) 让 Ann@Example.com 和 ann@example.com 成为同一个用户。不做这一步,一个买家在所有计数里会变成两个人。
  • row_number() OVER (PARTITION BY event_id ORDER BY rowid) 给每个事件的各份副本编号,只留 rn = 1 就去掉了重复的 E003。窗口函数从 SQLite 3.25.0(2018-09-15)开始支持。
  • 除以 100.0 而不是 100,避免整数除法。

加载前先查视图确认:SELECT count(*) FROM v_stage 返回 11(12 行原始数据减去 1 条重复),两条坏行的时间戳或用户列显示 NULL。

加载:拒绝行、合格行和可重复运行的汇总

三条语句,按这个顺序。

-- 1. 拒绝行:先写入拒绝表,带上以后能读懂的原因
INSERT INTO events_rejected
SELECT event_id,
       CASE WHEN occurred_at IS NULL THEN 'bad timestamp' ELSE 'missing user' END
FROM v_stage
WHERE occurred_at IS NULL OR user_id IS NULL;   -- 时间无效或没有用户的行

-- 2. 合格的行:时间和用户都有效才加载,重复的主键直接跳过
INSERT INTO events_clean
SELECT event_id, occurred_at, user_id, event_name, amount, page
FROM v_stage
WHERE occurred_at IS NOT NULL AND user_id IS NOT NULL   -- 两个必填字段都有效
ON CONFLICT(event_id) DO NOTHING;                        -- 已加载过的事件编号不再重复写入

-- 3. 每日汇总:按天聚合,已有的那一天用新数字覆盖,所以可以放心再跑一次
INSERT INTO daily_summary (day, events, buyers, revenue)
SELECT date(occurred_at), count(*),                         -- 按天取日期、事件数
       count(DISTINCT CASE WHEN event_name='purchase' THEN user_id END),   -- 买家数,按用户去重
       coalesce(sum(CASE WHEN event_name='purchase' THEN amount END),0)   -- 收入,没有购买时记 0
FROM events_clean
WHERE true                    -- 避免 ON 被解析成连接条件
GROUP BY date(occurred_at)
ON CONFLICT(day) DO UPDATE SET   -- 当天已有记录时,用新算的数字覆盖
  events=excluded.events, buyers=excluded.buyers, revenue=excluded.revenue;

有两个细节。主键上的 ON CONFLICT ... DO NOTHING 保证重跑不会把同一个 event_id 插入两次。第 3 步里带 excluded. 前缀的 DO UPDATE 会用新算出的值覆盖当天那一行,所以运行两次也只有每天一行、数字相同。upsert 语法从 SQLite 3.24.0(2018-06-04)开始支持;PostgreSQL 文档里有同样的 ON CONFLICT 写法。当 upsert 的值来自一条 SELECT 时,SQLite 文档说这条 SELECT 应该始终带一个 WHERE 子句,哪怕只是 WHERE true,免得解析器把 ON 当成连接条件,这就是第 3 步里有 WHERE true 的原因。

实际运行的结果:

日期事件数买家数收入
2026-11-0143199.00
2026-11-022178.00
2026-11-0332144.00
app.tapcub.com

从提取到加载的行数

12 行示例

提取

12

去重后

11

加载为合格行

9

去掉 1 条重复;拒绝 2 行,一行是不可能的日期,一行缺少用户。
示例数据
示例数据:12 行原始数据去掉重复的 E003 后剩 11 行,再拒绝 E007(13 月)和 E008(没有用户)后得到 9 行合格数据。被拒绝的行连同原因放在单独的表里。

核对计数,并证明它能重复运行

说不清每一行去了哪里的流程,不算做完。核对两个等式。

行数: 提取 12 = 去掉重复 1 + 拒绝 2 + 加载 9。查询如下:

-- 行数核对:提取数 = 重复数 + 拒绝数 + 加载数
SELECT (SELECT count(*) FROM raw_events) AS extracted,
       (SELECT count(*) FROM raw_events) - (SELECT count(*) FROM v_stage) AS dup,   -- 被去掉的重复行
       (SELECT count(*) FROM events_rejected) AS rej,                               -- 被拒绝的行
       (SELECT count(*) FROM events_clean) AS loaded;                               -- 已加载的合格行

返回 12、1、2、9,而 1 + 2 + 9 = 12。

金额: 原始表里的购买金额除以 100 后合计 466.00(45 + 129 + 25 + 78 + 30 + 15 + 45 + 99)。被拒绝的购买是 E007(30.00)和 E008(15.00),共 45.00。所以合格购买应当合计 466.00 − 45.00 = 421.00,SELECT sum(amount) FROM events_clean WHERE event_name='purchase' 返回的正好是 421.0。

重跑测试: 先清空 events_rejected,再把三条加载语句运行第二遍。events_clean 仍是 9 行,daily_summary 仍是同样的 3 行,拒绝行是 2 条,因为拒绝表事先清空了。如果重跑改变了任何计数,说明加载不幂等,一次失败的运行就可能弄坏目标表。

核对项预期结果
行数:提取 = 重复 + 拒绝 + 加载12 = 1 + 2 + 912、1、2、9
拒绝后的购买金额合计466.00 − 45.00 = 421.00421.0
重跑:events_clean 的行数99
重跑:daily_summary 的行数33
重跑:无法解释的差异00

示例数据。每项核对都在真实运行中通过;如果重跑改变了任何计数,说明加载不是幂等的。

SQL 单独不够用的时候

SQL 是大多数数据流程里的转换语言,但它周围的粘合部分不是 SQL。对照信号和对策:

信号说明什么要补什么
任务必须每晚 02:00 运行需要调度器cron、工作流工具或数据库自带的任务功能
源和目标是不同的系统单个会话读不到两边用导出文件或连接器做提取和加载
原始表有几亿行每次全表重扫太慢基于水位线的增量加载、分区、数据仓库
规则经常变手改视图定义会漂移把 SQL 放进版本控制并评审改动
出错要有人知道没有东西在盯着任务像上面那样的行数核对,再接上告警

如果先把原始数据加载进数据仓库,再在仓库里转换,就是 ELT,见 ETL 与 ELT 的区别。本文的 SQL 可以原样搬到那种做法里,只是步骤顺序不同。

怎样把结果送进 TapCub

拿上面的 events_clean,把它作为事件导入 TapCub。

  1. 导出可直接加载的形态。 选出 TapCub 的 CSV 向导能映射的列,时间用 ISO 文本:SELECT occurred_at AS time, event_name AS event, user_id, page FROM events_clean。user_id 用内部用户 ID,不要用邮箱;演示里用邮箱只是为了展示转小写。避免写入个人数据说明了哪些内容不该放进去。
  2. 一次性导入文件。 在 全端分析 › 数据管理 › 数据导入 点 导入文件。向导会识别类型、显示前 20 行,并按 time、event_name、user_id 这类列名猜测映射。一个文件最多 100,000 行,更大的导出要拆分。导入的事件平台为 import,可以按它筛选。
  3. 持续加载用服务端事件接口。 数据导入页面用于迁移,不是持续采集渠道;定时任务请从你的流程里通过服务端事件接口发送,见发送服务端事件。
  4. 再查回来。 全端分析 › 行为分析 › 行为分析 里的 SQL 标签页接受一条 SELECT 或 WITH,只能查项目的 v_events、v_sessions、v_persons 和 v_autocapture 视图,最多返回 50,000 行,30 秒超时。写操作会被拒绝,所以 TapCub 的 SQL 用来读取你加载的内容,不能做加载本身。见用 SQL 查询数据。

TapCub 不是 ETL 工具:它不调度也不运行你的转换。它接收清洗好的事件,让你用和脚本采集的事件同样的报表去分析。

常见问题

SQL 里的 ETL 是什么?

就是用 SQL 语句完成的提取、转换和加载。提取是一条 SELECT 或一次导出,转换是 SELECT 里的清洗和整理,加载是 INSERT ... SELECT 写入目标表。它适合数据量不大、源和目标在同一个数据库会话里能访问的场景,重复运行需要调度器。

只用 SQL 能做 ETL 吗?

一次性或中等规模的任务可以:上面的例子只用 SQL 就完成了提取、清洗、去重、加载和核对。但定时运行、在不同系统间搬数据、核对失败时告警,还是要 SQL 之外的东西。这些部分和转换本身相比很小。

怎样让 SQL 加载可以安全地重复运行?

给目标表加主键并使用 upsert:永不变化的事实数据用 ON CONFLICT ... DO NOTHING,汇总表用 DO UPDATE SET col = excluded.col。这样重跑得到相同的行。测试方法是把加载运行两遍,比较行数,就像核对一节里那样。

SQL 里的 ETL 和 ELT 有什么区别?

ETL 先转换再加载;ELT 先加载原始数据,再用 SQL 在目标内转换。SQL 本身是一样的。ELT 保留了原始表,所以改了规则可以重建,目标是计算资源充足的数据仓库时更方便。

ETL 最常用到哪些 SQL 功能?

清洗函数如 trim、lower 和日期解析;表达规则的 CASE;做汇总的 GROUP BY;去重用的 row_number() 这类窗口函数;以及安全重载用的 upsert。这些覆盖了大部分转换,剩下的工作是核对计数。

资料来源

  • Amazon Web Services,What is ETL?:提取、转换、加载的定义,以及 ELT 如何调换顺序。核对于 2026-11-20。
  • SQLite,UPSERT:ON CONFLICT 语法、excluded. 限定符、3.24.0 版本,以及 INSERT ... SELECT 的 WHERE 子句说明。核对于 2026-11-20。
  • SQLite,日期和时间函数:接受的时间格式,以及无法解析的输入返回 NULL。核对于 2026-11-20。
  • PostgreSQL,INSERT:ON CONFLICT DO NOTHING 和 DO UPDATE 子句。核对于 2026-11-20。
  • SQLite,窗口函数:3.25.0(2018-09-15)加入,包含 row_number()。核对于 2026-11-20。
归档:数据分析

这篇文章对你有帮助吗?

我们会根据反馈修改和补充这篇内容。

许安

客户体验顾问

关注在线客服、访客沟通与自助服务,分享常见问题的处理思路和可复用流程,让每一次沟通更清晰有效。

把清洗好的事件加载到一处

导入事件 CSV,用脚本持续发送事件流,并用只读 SQL 查询项目里的事件。

看清数据,找到增长。

每一次点击,都有数据依据。装好一行代码,1 分钟看到第一批数据。

无需信用卡 · 统计与客服都有免费版 · 统计无 Cookie