项目数据字典

本章目标：拿到项目里的任意一张表，能说出字段、主键、和谁关联；能把 GMV、AOV、复购率等核心指标算到标准答案。 前置知识：完成项目 00 章的数据准备，读过 SQL 基础 的前几节。 用途：只服务项目线，做项目、对口径、面试前回查时使用。理论章节不依赖这张字典。

1. 为什么需要数据字典

数据字典回答三个问题：这张表存什么、每个字段什么意思、表之间怎么关联。没有它，你看到 order_delivered_customer_date 为空时，不知道该删还是该留；看到 customer_unique_id 和 customer_id 时，不知道哪个才是用户主键。

数据开发不仅要会写 SQL，还要能维护口径。同一个“GMV”，含不含运费、算不算取消单、按订单去重还是按明细求和，结果可能差很多。口径必须写下来，代码才能复查，面试才讲得清。

2. 核心概念

2.1 八张表一览

合计 550,759 行。数据来自 Olist 巴西电商公开数据集，时间范围 2016-09-04 ~ 2018-10-17，分析窗口取 2017-01 ~ 2018-08。

2.2 表关系与关联键

customers 1 ───< orders 1 ───< order_items >─── 1 products
                   │                │
                   │                └────────── 1 sellers
                   ├──< payments
                   └──< order_reviews

products.product_category_name ─── 1 category_translation

orders.customer_id            → customers.customer_id
order_items.order_id          → orders.order_id
order_items.product_id        → products.product_id
order_items.seller_id         → sellers.seller_id
payments.order_id             → orders.order_id
order_reviews.order_id        → orders.order_id
products.product_category_name → category_translation.product_category_name

orders 和 order_items 是一对多；payments、order_reviews 都可能一单多行，直接 JOIN 明细会 fan-out。customers.customer_unique_id 才是自然人 ID，一个自然人可能对应多个 customer_id（多个收货地址），复购必须用它。

2.3 字段清单

2.4 真实脏数据

order_delivered_customer_date 为空多数不是脏数据。它表示订单还没签收，要和“delivered 状态却没有签收时间”的 8 条分开处理。

2.5 清洗口径与规模

标准清洗顺序：

1. 订单：排除 canceled 和 unavailable，得到有效订单。
2. 明细：只保留属于有效订单的 order_items 行。
3. 关联：LEFT JOIN 客户、商品、品类翻译、卖家，补上可读维度。
4. 评价：先按 order_id 聚合均分，再 JOIN 明细，避免 fan-out。
5. 时间：字符串 CAST 成 DATE / TIMESTAMP，派生 order_date、is_late、delivery_days。

2.6 核心指标标准答案

以 data/metrics.json 为唯一基线。

品类 GMV Top5（窗口内，含运费）：health_beauty 1,432,395.00、watches_gifts 1,294,824.43、bed_bath_table 1,239,779.55、sports_leisure 1,146,420.59、computers_accessories 1,049,201.49。Top10 累计占比 62.37%。

AOV 的分母容易错：不是 99,441（原始订单），不是 98,207（有效订单），也不是 112,101（明细行），而是 98,199（有明细的有效订单）。

2.7 签收时间空值

orders 有 2,965 条 order_delivered_customer_date 为空，其中大部分是未签收订单，属于正常业务空值；只有 8 条 order_status = 'delivered' 却没有签收时间，才是异常。

判断空值是否脏，看三点：业务上是否允许为空；空值是否和业务状态一致；会不会影响当前指标。计算 GMV 不需要该字段，做履约分析时只取签收和预计时间都不为空的有效订单。

3. 动手示例

先跑数仓和体检：

python project/build_warehouse.py
python scripts/check_data.py

构建完成后，用 DuckDB 查询数仓文件：

python - <<'PY'
import duckdb
con = duckdb.connect('project/warehouse.duckdb', read_only=True)
print(con.sql("""
    SELECT
        COUNT(*) AS total_orders,
        COUNT(*) FILTER (WHERE order_delivered_customer_date IS NULL) AS null_delivered,
        COUNT(*) FILTER (
            WHERE order_status = 'delivered'
              AND order_delivered_customer_date IS NULL
        ) AS delivered_missing_date
    FROM ods_orders
""").df())
PY

预期结果：

total_orders  null_delivered  delivered_missing_date
99441         2965            8

空值数量和业务状态对得上，说明缺失主要来自未签收；8 条 delivered 缺签收时间才是异常。再用 check_data.py 的输出对照 98,207 / 112,101 / 98,199 和 GMV，能对上就说明清洗口径正确。

4. 常见坑

5. 在线练习

自测：

• 能用 LEFT JOIN 和 NOT EXISTS 各写一条“没有下过有效订单的客户”。
• 能在固定时间窗口内按品类聚合 GMV，并说清分子和分母。
• 能用窗口函数算 7 日移动平均，并解释为什么要先固定日期范围。

6. 面试延伸

面试题：什么是数据口径？为什么数据开发要负责口径？

是什么：口径是指标的业务定义和计算规则，包括统计对象、过滤条件、时间范围、分母和单位。

解决什么问题：同一份数据，不同口径会算出不同结果。把口径写进数据字典和 SQL 注释，报表、分析和财务才能对得上；出问题时也能定位是数据错还是定义不同。

我的实操边界：我在项目里统一定义了有效订单、GMV 含运费、AOV 分母、复购 ID 和迟到判定；没有在公司级指标平台上维护过跨部门口径。

面试题：空值和脏数据有什么区别？

是什么：空值是字段没有值；脏数据是值不符合业务规则，例如重复评价 ID、支付与明细不一致、时间倒挂。

解决什么问题：空值可能合法也可能非法，要看业务含义。未签收订单没有签收时间合法，delivered 状态却没有签收时间才异常。清洗前先分类，避免误删正常数据。

我的实操边界：我在项目里保留未签收订单的空时间，只把 8 条 delivered 缺时间标为异常；商品缺失品类归为 unknown；没有处理过线上数据质量告警。

7. 本章验收

• 能不看文档说出 8 张表各存什么、行数大概多少。
• 能画出表关系图，写出 7 条关联键。
• 能说出 5 类真实脏数据的数量和处理方式，并解释签收时间空值为什么多数不算脏数据。
• 能复述 GMV、AOV、复购率、迟到率的标准答案和各自口径。
• 能运行本章 SQL，验证 99,441 / 2,965 / 8 和 98,207 / 112,101 / 98,199。

重新下载数据：python scripts/fetch_olist.py；重新体检：python scripts/check_data.py。下一章：速查表。