从业务问题到第一张数据表

这门课只做一个项目：为“青岚生活馆”完成一份经营分析。我们会沿用同一套客户、订单、商品和订单明细数据，最后把 SQL 的结果放进 Excel，做成可以复查的经营看板。

先把目标说具体。管理者想知道销售是否增长、哪些品类贡献收入、哪些客户值得维护，以及客户买过一次之后还会不会回来。SQL 负责从明细中算出可信的结果，Excel 负责复核、展示和交付。

先认识分析对象

一笔订单可能包含多件商品，所以我们不能把所有内容塞进一张表。这个项目使用四张原始表：

表	一行代表什么	主要用途
`customers_raw`	一位客户	注册时间、城市、渠道、会员等级
`products`	一件商品	商品名称、品类、成本和标价
`orders_raw`	一笔订单	下单时间、状态和支付方式
`order_items_raw`	一笔订单中的一种商品	数量、成交单价和折扣

“一行代表什么”叫作数据粒度。后面只要结果不对，我们就先回到粒度检查。比如直接在订单明细表里数行数，得到的是明细行数，不是订单数。

建立操作环境

课程统一使用 SQLite。它把整个数据库放在一个文件里，不需要额外启动数据库服务，SQL 也足以覆盖本课用到的连接、CTE 和窗口函数。

在一个空目录中准备 setup.sql，然后执行：

shell

sqlite3 greenlife.db

进入 SQLite 后打开外键检查，并创建四张表：

sql

PRAGMA foreign_keys = ON;
 
CREATE TABLE customers_raw (
    customer_id INTEGER,
    signup_date TEXT,
    city TEXT,
    channel TEXT,
    member_level TEXT
);
 
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT NOT NULL,
    unit_cost REAL NOT NULL,
    list_price REAL NOT NULL
);
 
CREATE TABLE orders_raw (
    order_id INTEGER,
    customer_id INTEGER,
    order_date TEXT,
    status TEXT,
    payment_method TEXT
);
 
CREATE TABLE order_items_raw (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    unit_price REAL,
    discount_rate REAL
);

orders_raw 暂时没有主键，这是有意保留的。原始导入层要如实接住源数据，重复订单应该先被发现，再由清洗逻辑处理。如果一开始就加主键，第二条重复记录会导入失败，我们反而看不到源数据的问题。

生成一套可以反复练习的数据

先插入 12 件商品。成本和标价分开存放，后面才能计算毛利。

sql

INSERT INTO products VALUES
    (1, '晴雨伞', '出行', 36.00, 69.00),
    (2, '帆布袋', '出行', 18.00, 39.00),
    (3, '随行杯', '饮水', 42.00, 89.00),
    (4, '玻璃水瓶', '饮水', 28.00, 59.00),
    (5, '手冲咖啡壶', '餐厨', 96.00, 189.00),
    (6, '陶瓷马克杯', '餐厨', 24.00, 49.00),
    (7, '香薰蜡烛', '居家', 38.00, 79.00),
    (8, '亚麻抱枕', '居家', 55.00, 109.00),
    (9, '无线夜灯', '数码', 68.00, 139.00),
    (10, '桌面风扇', '数码', 82.00, 169.00),
    (11, '便携餐具', '餐厨', 16.00, 35.00),
    (12, '收纳盒', '居家', 22.00, 45.00);

下面的递归 CTE 生成 120 位客户。数据里故意保留了前导空格、两种北京写法和少量空渠道，这些问题会在下一节清洗。

sql

WITH RECURSIVE n(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM n WHERE x < 120
)
INSERT INTO customers_raw
SELECT
    x,
    date('2023-07-01', printf('+%d days', (x * 11) % 365)),
    CASE x % 7
        WHEN 0 THEN ' 上海'
        WHEN 1 THEN '上海'
        WHEN 2 THEN '北京市'
        WHEN 3 THEN '北京'
        WHEN 4 THEN '广州'
        WHEN 5 THEN '深圳'
        ELSE '杭州'
    END,
    CASE
        WHEN x % 19 = 0 THEN NULL
        WHEN x % 4 = 0 THEN '自然搜索'
        WHEN x % 4 = 1 THEN '信息流广告'
        WHEN x % 4 = 2 THEN '社交推荐'
        ELSE '线下活动'
    END,
    CASE
        WHEN x % 10 = 0 THEN '金卡'
        WHEN x % 3 = 0 THEN '银卡'
        ELSE '普通'
    END
FROM n;

再生成 540 笔订单，时间范围是 2024 年 1 月到 2025 年 6 月。订单状态包括完成、取消和退货。

sql

WITH RECURSIVE n(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM n WHERE x < 540
)
INSERT INTO orders_raw
SELECT
    x,
    ((x * 37) % 120) + 1,
    date('2024-01-01', printf('+%d days', (x * 17) % 547)),
    CASE
        WHEN x % 29 = 0 THEN 'CANCELLED'
        WHEN x % 41 = 0 THEN 'RETURNED'
        ELSE 'COMPLETED'
    END,
    CASE x % 3
        WHEN 0 THEN '支付宝'
        WHEN 1 THEN '微信支付'
        ELSE '银行卡'
    END
FROM n;
 
INSERT INTO orders_raw
SELECT * FROM orders_raw WHERE order_id = 113;

最后一条语句模拟重复导入，所以表里有 541 行，但只有 540 个不同的订单编号。

做第一次验收

不要在建表后立刻开始分析。先用最小查询确认数据是否进入了预期的表。

sql

SELECT 'customers_raw' AS table_name, COUNT(*) AS row_count
FROM customers_raw
UNION ALL
SELECT 'products', COUNT(*) FROM products
UNION ALL
SELECT 'orders_raw', COUNT(*) FROM orders_raw;

结果应该是 120 位客户、12 件商品和 541 行订单原始记录。再检查重复订单：

sql

SELECT
    COUNT(*) AS rows_in_table,
    COUNT(DISTINCT order_id) AS distinct_orders
FROM orders_raw;

rows_in_table	distinct_orders
541	540

这两个数字不同不是报错，而是下一节要处理的数据质量问题。

为什么先保留原始表

很多初学者会问：既然知道城市有空格、订单有重复，为什么不在导入时直接改掉？因为分析首先要能解释“源数据原来是什么”。

我们把数据分成三层：

层级	本项目对象	允许做什么	主要作用
原始层	`customers_raw`、`orders_raw`、`order_items_raw`	尽量原样保存	追查源数据问题
清洗层	下一节创建的 `v_*_clean`	去重、统一类别、处理空值	统一分析口径
分析层	`v_sales_detail` 和各指标查询	连接、聚合、计算指标	回答业务问题

如果一开始就在 orders_raw 上删除重复行，后面看到销售额异常时，我们无法确认重复来自源系统还是清洗脚本。保留原始层后，任何清洗规则都可以重新执行，也能比较清洗前后少了多少记录。

原始层并不表示“什么都不管”。字段数量、基本类型和文件是否完整仍要检查，只是不在这里悄悄改变业务内容。

把粒度真正弄明白

你可以用 1 号订单想象四张表的关系。客户表中只有一行客户，订单表中只有一行 1 号订单，但订单明细里可能有三行：晴雨伞、陶瓷马克杯和便携餐具。

连接订单与明细后，1 号订单会出现三行。这不是重复，而是一对多关系展开后的正常结果。于是下面三个计数回答不同问题：

sql

SELECT
    COUNT(*) AS detail_rows,
    COUNT(DISTINCT order_id) AS orders,
    SUM(quantity) AS units
FROM order_items_raw;

COUNT(*) 数明细行。
COUNT(DISTINCT order_id) 数订单。
SUM(quantity) 数商品件数。

后面只要指标不对，先问一句“一行代表什么”。这比先改公式更有效，因为许多销售额翻倍、订单数偏大和客单价偏低，根源都是粒度混了。

主键与外键在这里解决什么

products.product_id 是主键，一件商品只能有一个稳定编号。订单明细中的 product_id 指向它，是连接商品名称、品类和成本的外键。

orders_raw 暂时不加主键，是为了让重复记录进入原始层。清洗后我们会让每个 order_id 只保留一条，再把它当作订单的稳定标识。换句话说，原始层可以暴露问题，分析层必须有明确的唯一性。

看懂递归 CTE 在生成什么

生成客户和订单时使用了递归 CTE。以客户编号为例：

sql

WITH RECURSIVE n(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM n WHERE x < 120
)
SELECT x FROM n;

第一行产生 1，递归部分不断把上一行加 1，直到 120。它相当于临时生成一张 1 到 120 的数字表。

后面的取模表达式负责把编号映射到日期、城市和渠道。例如 x % 4 的结果只会是 0、1、2、3，因此可以稳定分配四种渠道。我们使用确定性规则，而不是随机函数，是为了让每次重建数据库都得到相同结果。教程中的查询结果才能逐行复核。

真实项目不会用递归 CTE 伪造客户，这里只是用它准备一套可反复练习的数据。分析方法仍然与导入真实 CSV 后相同。

按顺序完成本节操作

建议把建表和插入语句保存为 setup.sql，然后一次执行：

shell

sqlite3 greenlife.db < setup.sql

执行后按这个顺序检查：

.tables 能看到四张原始表。
PRAGMA table_info(products); 能看到商品字段与类型。
客户数为 120，商品数为 12。
订单原始行数为 541，不同订单数为 540。
最早订单不早于 2024-01-01，最晚订单不晚于 2025-06-30。

日期范围可以这样查：

sql

SELECT
    MIN(order_date) AS first_order_date,
    MAX(order_date) AS last_order_date
FROM orders_raw;

行数对上但日期范围不对，说明数据生成表达式或导入格式仍有问题。不要因为表里“有数据”就直接开始分析。

本节常见错误

重新执行脚本时报表已存在

说明旧数据库还在。练习时可以删除课程生成的数据库文件后重建；正式环境则应该写迁移脚本，不能随意删除业务数据。

中文变成乱码

先确认 setup.sql 保存为 UTF-8。终端能正确显示中文后再继续。乱码一旦进入表，后面的 Excel 导出也会继续错误。

订单数写成 541

541 是原始行数，540 才是不同订单编号数。现阶段两个数字都要保留：前者说明导入了多少行，后者暴露重复问题。

看到重复就立刻删除

先查询重复记录的所有字段，确认它们是否完全相同。若金额、状态或更新时间不同，保留哪一条需要业务规则，不能只按编号随便删除。

接下来我们会补齐订单明细，检查重复、空值和类别写法，再建立干净的数据视图。清洗后的每一个变化都会有清洗前后的数量对照。

从业务问题到第一张数据表

先认识分析对象

一笔订单可能包含多件商品，所以我们不能把所有内容塞进一张表。这个项目使用四张原始表：

表	一行代表什么	主要用途
`customers_raw`	一位客户	注册时间、城市、渠道、会员等级
`products`	一件商品	商品名称、品类、成本和标价
`orders_raw`	一笔订单	下单时间、状态和支付方式
`order_items_raw`	一笔订单中的一种商品	数量、成交单价和折扣

“一行代表什么”叫作数据粒度。后面只要结果不对，我们就先回到粒度检查。比如直接在订单明细表里数行数，得到的是明细行数，不是订单数。

建立操作环境

课程统一使用 SQLite。它把整个数据库放在一个文件里，不需要额外启动数据库服务，SQL 也足以覆盖本课用到的连接、CTE 和窗口函数。

在一个空目录中准备 setup.sql，然后执行：

shell

sqlite3 greenlife.db

进入 SQLite 后打开外键检查，并创建四张表：

sql

PRAGMA foreign_keys = ON;
 
CREATE TABLE customers_raw (
    customer_id INTEGER,
    signup_date TEXT,
    city TEXT,
    channel TEXT,
    member_level TEXT
);
 
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT NOT NULL,
    unit_cost REAL NOT NULL,
    list_price REAL NOT NULL
);
 
CREATE TABLE orders_raw (
    order_id INTEGER,
    customer_id INTEGER,
    order_date TEXT,
    status TEXT,
    payment_method TEXT
);
 
CREATE TABLE order_items_raw (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    unit_price REAL,
    discount_rate REAL
);

生成一套可以反复练习的数据

先插入 12 件商品。成本和标价分开存放，后面才能计算毛利。

sql

INSERT INTO products VALUES
    (1, '晴雨伞', '出行', 36.00, 69.00),
    (2, '帆布袋', '出行', 18.00, 39.00),
    (3, '随行杯', '饮水', 42.00, 89.00),
    (4, '玻璃水瓶', '饮水', 28.00, 59.00),
    (5, '手冲咖啡壶', '餐厨', 96.00, 189.00),
    (6, '陶瓷马克杯', '餐厨', 24.00, 49.00),
    (7, '香薰蜡烛', '居家', 38.00, 79.00),
    (8, '亚麻抱枕', '居家', 55.00, 109.00),
    (9, '无线夜灯', '数码', 68.00, 139.00),
    (10, '桌面风扇', '数码', 82.00, 169.00),
    (11, '便携餐具', '餐厨', 16.00, 35.00),
    (12, '收纳盒', '居家', 22.00, 45.00);

下面的递归 CTE 生成 120 位客户。数据里故意保留了前导空格、两种北京写法和少量空渠道，这些问题会在下一节清洗。

sql

WITH RECURSIVE n(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM n WHERE x < 120
)
INSERT INTO customers_raw
SELECT
    x,
    date('2023-07-01', printf('+%d days', (x * 11) % 365)),
    CASE x % 7
        WHEN 0 THEN ' 上海'
        WHEN 1 THEN '上海'
        WHEN 2 THEN '北京市'
        WHEN 3 THEN '北京'
        WHEN 4 THEN '广州'
        WHEN 5 THEN '深圳'
        ELSE '杭州'
    END,
    CASE
        WHEN x % 19 = 0 THEN NULL
        WHEN x % 4 = 0 THEN '自然搜索'
        WHEN x % 4 = 1 THEN '信息流广告'
        WHEN x % 4 = 2 THEN '社交推荐'
        ELSE '线下活动'
    END,
    CASE
        WHEN x % 10 = 0 THEN '金卡'
        WHEN x % 3 = 0 THEN '银卡'
        ELSE '普通'
    END
FROM n;

再生成 540 笔订单，时间范围是 2024 年 1 月到 2025 年 6 月。订单状态包括完成、取消和退货。

sql

WITH RECURSIVE n(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM n WHERE x < 540
)
INSERT INTO orders_raw
SELECT
    x,
    ((x * 37) % 120) + 1,
    date('2024-01-01', printf('+%d days', (x * 17) % 547)),
    CASE
        WHEN x % 29 = 0 THEN 'CANCELLED'
        WHEN x % 41 = 0 THEN 'RETURNED'
        ELSE 'COMPLETED'
    END,
    CASE x % 3
        WHEN 0 THEN '支付宝'
        WHEN 1 THEN '微信支付'
        ELSE '银行卡'
    END
FROM n;
 
INSERT INTO orders_raw
SELECT * FROM orders_raw WHERE order_id = 113;

最后一条语句模拟重复导入，所以表里有 541 行，但只有 540 个不同的订单编号。

做第一次验收

不要在建表后立刻开始分析。先用最小查询确认数据是否进入了预期的表。

sql

SELECT 'customers_raw' AS table_name, COUNT(*) AS row_count
FROM customers_raw
UNION ALL
SELECT 'products', COUNT(*) FROM products
UNION ALL
SELECT 'orders_raw', COUNT(*) FROM orders_raw;

结果应该是 120 位客户、12 件商品和 541 行订单原始记录。再检查重复订单：

sql

SELECT
    COUNT(*) AS rows_in_table,
    COUNT(DISTINCT order_id) AS distinct_orders
FROM orders_raw;

rows_in_table	distinct_orders
541	540

这两个数字不同不是报错，而是下一节要处理的数据质量问题。

为什么先保留原始表

很多初学者会问：既然知道城市有空格、订单有重复，为什么不在导入时直接改掉？因为分析首先要能解释“源数据原来是什么”。

我们把数据分成三层：

层级	本项目对象	允许做什么	主要作用
原始层	`customers_raw`、`orders_raw`、`order_items_raw`	尽量原样保存	追查源数据问题
清洗层	下一节创建的 `v_*_clean`	去重、统一类别、处理空值	统一分析口径
分析层	`v_sales_detail` 和各指标查询	连接、聚合、计算指标	回答业务问题

原始层并不表示“什么都不管”。字段数量、基本类型和文件是否完整仍要检查，只是不在这里悄悄改变业务内容。

把粒度真正弄明白

连接订单与明细后，1 号订单会出现三行。这不是重复，而是一对多关系展开后的正常结果。于是下面三个计数回答不同问题：

sql

SELECT
    COUNT(*) AS detail_rows,
    COUNT(DISTINCT order_id) AS orders,
    SUM(quantity) AS units
FROM order_items_raw;

COUNT(*) 数明细行。
COUNT(DISTINCT order_id) 数订单。
SUM(quantity) 数商品件数。

后面只要指标不对，先问一句“一行代表什么”。这比先改公式更有效，因为许多销售额翻倍、订单数偏大和客单价偏低，根源都是粒度混了。

主键与外键在这里解决什么

products.product_id 是主键，一件商品只能有一个稳定编号。订单明细中的 product_id 指向它，是连接商品名称、品类和成本的外键。

看懂递归 CTE 在生成什么

生成客户和订单时使用了递归 CTE。以客户编号为例：

sql

WITH RECURSIVE n(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM n WHERE x < 120
)
SELECT x FROM n;

第一行产生 1，递归部分不断把上一行加 1，直到 120。它相当于临时生成一张 1 到 120 的数字表。

真实项目不会用递归 CTE 伪造客户，这里只是用它准备一套可反复练习的数据。分析方法仍然与导入真实 CSV 后相同。

按顺序完成本节操作

建议把建表和插入语句保存为 setup.sql，然后一次执行：

shell

sqlite3 greenlife.db < setup.sql

执行后按这个顺序检查：

.tables 能看到四张原始表。
PRAGMA table_info(products); 能看到商品字段与类型。
客户数为 120，商品数为 12。
订单原始行数为 541，不同订单数为 540。
最早订单不早于 2024-01-01，最晚订单不晚于 2025-06-30。

日期范围可以这样查：

sql

SELECT
    MIN(order_date) AS first_order_date,
    MAX(order_date) AS last_order_date
FROM orders_raw;

行数对上但日期范围不对，说明数据生成表达式或导入格式仍有问题。不要因为表里“有数据”就直接开始分析。

本节常见错误

重新执行脚本时报表已存在

说明旧数据库还在。练习时可以删除课程生成的数据库文件后重建；正式环境则应该写迁移脚本，不能随意删除业务数据。

中文变成乱码

先确认 setup.sql 保存为 UTF-8。终端能正确显示中文后再继续。乱码一旦进入表，后面的 Excel 导出也会继续错误。

订单数写成 541

541 是原始行数，540 才是不同订单编号数。现阶段两个数字都要保留：前者说明导入了多少行，后者暴露重复问题。

看到重复就立刻删除

先查询重复记录的所有字段，确认它们是否完全相同。若金额、状态或更新时间不同，保留哪一条需要业务规则，不能只按编号随便删除。

接下来我们会补齐订单明细，检查重复、空值和类别写法，再建立干净的数据视图。清洗后的每一个变化都会有清洗前后的数量对照。