把四张表连接成销售明细

订单表告诉我们“谁在什么时候下了单”，订单明细告诉我们“买了什么”，商品表提供成本，客户表提供城市和渠道。要计算销售额与毛利，四张表缺一不可。

先画清连接关系

连接条件来自字段之间的业务关系：

text

v_orders_clean.order_id
        │
        ├── v_order_items_clean.order_id
        │              │
        │              └── products.product_id
        │
        └── v_customers_clean.customer_id

订单到明细是一对多关系。连接后，一笔订单会展开成多行，这是正确结果。客户到订单、商品到明细也都是一对多关系。

从两张表的连接开始

sql

SELECT
    o.order_id,
    o.order_date,
    i.product_id,
    i.quantity,
    i.unit_price,
    i.discount_rate
FROM v_orders_clean AS o
JOIN v_order_items_clean AS i
  ON o.order_id = i.order_id
WHERE o.status = 'COMPLETED'
ORDER BY o.order_id
LIMIT 10;

JOIN 默认是内连接，只保留两边都能匹配的记录。销售分析需要有效订单和有效明细同时存在，所以这里使用内连接。

检查是否有“有订单、无明细”的情况，可以换成左连接：

sql

SELECT COUNT(*) AS orders_without_items
FROM v_orders_clean AS o
LEFT JOIN v_order_items_clean AS i
  ON o.order_id = i.order_id
WHERE i.order_id IS NULL;

结果应该是 0。左连接会保留左表全部记录，匹配不到的右表字段显示为 NULL，很适合做完整性检查。

明确定义销售额和毛利

本课使用下面两个公式：

\text{销售额}=\text{数量}\times\text{成交单价}\times(1-\text{折扣率})

\text{毛利}=\text{销售额}-\text{数量}\times\text{单位成本}

折扣率 0.15 表示打 85 折。销售额使用成交单价而不是商品标价，因为订单明细已经记录了交易时的价格。

建立后续共用的销售视图

sql

CREATE VIEW v_sales_detail AS
SELECT
    o.order_id,
    o.order_date,
    strftime('%Y-%m', o.order_date) AS order_month,
    o.customer_id,
    c.city,
    c.channel,
    c.member_level,
    i.product_id,

USING (order_id) 是两边连接列同名时的简写。视图只保留完成订单，后面的经营指标便不需要重复写状态条件。

防止一对多连接把订单数算错

sql

SELECT
    COUNT(*) AS detail_rows,
    COUNT(DISTINCT order_id) AS orders,
    COUNT(DISTINCT customer_id) AS customers
FROM v_sales_detail;

detail_rows	orders	customers
1271	509	120

1271 是商品明细行数，509 才是完成订单数。只要分析对象是订单，就使用 COUNT(DISTINCT order_id)，或先聚合到一行一订单。

下面把 1 号订单聚合回订单粒度：

sql

SELECT
    order_id,
    COUNT(*) AS item_lines,
    SUM(quantity) AS units,
    ROUND(SUM(revenue), 2) AS order_revenue,
    ROUND(SUM(gross_profit), 2) AS order_profit
FROM v_sales_detail
WHERE order_id = 1
GROUP BY order_id;

看到一行结果后，再确认 item_lines 与原始明细数量一致。

连接前先预测行数会怎样变化

连接不是把两张表“横着拼起来”这么简单。连接后的行数由匹配关系决定。

订单表一行一订单，明细表一行一种商品。509 笔完成订单连接明细后得到 1271 行，平均每笔订单约 2.5 行明细。这个增加是预期的一对多展开。

如果订单表本身仍有重复，一条重复订单会匹配它的全部明细。假设 113 号订单有三行明细，订单重复一次，连接结果就会额外多三行，销售额也会多算一次。因此去重必须发生在连接前。

连接前可以先写出预期：

阶段	预期粒度	预期数量
`v_orders_clean` 中完成订单	一行一订单	509
`v_order_items_clean`	一行一商品明细	约 1350
`v_sales_detail`	一行一完成订单商品明细	1271

数量与预期相差很大时，先停下来检查连接条件，不要继续计算总额。

用匹配率验证连接是否漏数据

内连接会丢掉无法匹配的记录。销售分析使用内连接没有问题，前提是我们知道丢了什么。

检查订单是否缺少客户：

sql

SELECT COUNT(*) AS orders_without_customer
FROM v_orders_clean AS o
LEFT JOIN v_customers_clean AS c
  ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

检查明细是否缺少商品：

sql

SELECT COUNT(*) AS items_without_product
FROM v_order_items_clean AS i
LEFT JOIN products AS p
  ON i.product_id = p.product_id
WHERE p.product_id IS NULL;

两个结果都应为 0。若不是 0，直接使用内连接会让这些订单或明细悄悄消失。此时需要补主数据、标记未知商品，或在指标口径中说明排除数量。

为什么在明细层保存收入和成本

v_sales_detail 为每行商品明细计算收入、成本和毛利。这样做让后续任何维度都能汇总：按月、品类、城市、渠道或客户分组时，公式保持一致。

以一行明细为例，数量 2、成交单价 89、折扣率 0.10、单位成本 42：

\text{销售额}=2\times 89\times(1-0.10)=160.20

\text{成本}=2\times 42=84.00

\text{毛利}=160.20-84.00=76.20

折扣只影响收入，不改变本项目的单位成本。真实业务若有运费、平台佣金、退款或优惠券分摊，毛利公式需要扩展，不能把这些成本默认藏在商品成本里。

检查金额是否因四舍五入产生差异

本项目在明细层把收入和毛利保留两位小数，再进行汇总。这与“先汇总原始小数、最后四舍五入”可能产生几分钱差异。

两种方法都可以使用，但要保持一致。财务报表通常根据实际记账粒度决定何时四舍五入。本课程选择明细层两位小数，是因为每行代表一次真实商品交易金额，Excel 也能逐行复核。

可以比较两种结果：

sql

SELECT
    ROUND(SUM(revenue), 2) AS sum_of_rounded_lines,
    ROUND(SUM(
        quantity * unit_price * (1 - discount_rate)
    ), 2) AS rounded_after_sum
FROM v_sales_detail;

若两者不同，不要随意把差异抹掉，应在指标定义中写清采用哪种口径。

用三个单元测试保护销售视图

建立视图后固定运行这三项检查：

sql

SELECT COUNT(*) AS invalid_amount_rows
FROM v_sales_detail
WHERE revenue <= 0
   OR cost < 0;

sql

SELECT COUNT(*) AS profit_formula_errors
FROM v_sales_detail
WHERE ABS(gross_profit - (revenue - cost)) > 0.01;

sql

SELECT
    COUNT(DISTINCT order_id) AS completed_orders,
    ROUND(SUM(revenue), 2) AS revenue
FROM v_sales_detail;

前两项应为 0，最后一项应返回 509 笔订单和 229909.45 销售额。这三条查询以后会成为 Excel 对账的源头。

下一节将继续使用这个销售视图，建立一套不会重复计算的经营指标。我们会先定义每个指标的粒度和分母，再看数字。

把四张表连接成销售明细 | 自在学