多币种销售聚合陷阱及解决方法

问题描述

在一个典型的销售数据模型中，我们可能拥有currency（货币）、product（产品）、sale（销售）、sale_lines（销售明细）和cash_transactions（现金交易）等表。其中，sale表记录了销售的主信息及其交易币种，sale_lines记录了销售包含的产品明细及其价格和数量，通常其币种与sale表一致。然而，cash_transactions表则记录了具体的现金交易，它可能包含客户支付的原始币种（received_currency_id）和系统内部转换后的币种（converted_currency_id），这两种币种都可能与sale表的交易币种不同。

当我们需要汇总特定销售（例如，按销售发生的币种分组）的总销售额、收到的总金额和转换后的总金额时，问题就出现了。由于sale与sale_lines之间是“一对多”关系，sale与cash_transactions之间也是“一对多”关系，如果直接将这些表连接起来进行聚合，数据行会在JOIN操作中被“扇出”（fan-out），导致聚合函数（如SUM）对重复的数据行进行累加，从而产生不准确的结果。

例如，一个销售（sale）可能有多个销售明细（sale_lines）和多个现金交易（cash_transactions）。如果直接LEFT JOIN sale_lines和LEFT JOIN cash_transactions，那么sale表中的每一行都可能因为sale_lines和cash_transactions的交叉组合而重复多次。在这种情况下，对sale_lines.price_paid或cash_transactions.received_amount进行SUM操作，会因为数据行的重复而得到远超实际值的总和。

更复杂的是，cash_transactions中的received_amount和converted_amount分别对应不同的币种上下文，直接对它们求和可能将不同币种的金额混淆，导致汇总结果失去实际意义。

聚合陷阱分析

SQL聚合陷阱的核心在于，当一个主表（例如sale）通过多个“一对多”关系连接到多个子表（例如sale_lines和cash_transactions）时，如果子表中的行数不一致，那么在JOIN操作后，主表的每一行可能会被复制多次，形成笛卡尔积的子集。随后的GROUP BY操作虽然可以确保按主键进行分组，但SUM等聚合函数会作用于这些已膨胀的数据行上，从而导致不正确的总和。

考虑以下简化的数据结构和场景：

表结构示例

CREATE TABLE currency (
  iso_number CHARACTER VARYING(3) PRIMARY KEY,
  iso_code CHARACTER VARYING(3)
);

INSERT INTO currency(iso_number, iso_code) VALUES ('208','DKK'), ('752','SEK'), ('572','NOK');

CREATE TABLE sale (
  id SERIAL PRIMARY KEY,
  time_of_sale TIMESTAMP,
  currency_items_sold_in CHARACTER VARYING(3) -- 销售主要币种
);

INSERT INTO sale(id, time_of_sale, currency_items_sold_in) 
VALUES 
(1, CURRENT_TIMESTAMP, '208'), -- 销售1，以DKK计价
(2, CURRENT_TIMESTAMP, '752')  -- 销售2，以SEK计价
;

CREATE TABLE sale_lines (
  id SERIAL PRIMARY KEY,
  sale_id INTEGER,
  product_id INTEGER,
  price_paid INTEGER,
  quantity FLOAT
);

INSERT INTO sale_lines(id, sale_id, product_id, price_paid, quantity)
VALUES 
(1, 1, 1, 200, 1.0), -- 销售1有2条明细
(2, 1, 2, 300, 1.0),

(3, 2, 1, 100, 1.0), -- 销售2有2条明细
(4, 2, 1, 100, 1.0)
;

CREATE TABLE cash_transactions (
  id SERIAL PRIMARY KEY,
  sale_id INTEGER,
  received_currency_id CHARACTER VARYING(3), -- 收到金额的币种
  converted_currency_id CHARACTER VARYING(3), -- 转换后金额的币种
  received_amount INTEGER,
  converted_amount INTEGER
);

INSERT INTO cash_transactions(id, sale_id, received_currency_id, converted_currency_id, received_amount, converted_amount)
VALUES
(1, 1, '208', '208', 200, 200), -- 销售1有2条交易，第一笔DKK->DKK
(2, 1, '752', '208', 400, 300), -- 第二笔SEK->DKK

(3, 2, '572', '208', 150, 100), -- 销售2有2条交易，第一笔NOK->DKK
(4, 2, '208', '208', 100, 100)  -- 第二笔DKK->DKK
;

如果尝试直接聚合：

SELECT 
  s.currency_items_sold_in, 
  SUM(sl.price_paid) as "price_paid",
  SUM(ct.received_amount) as "total_received_amount",
  SUM(ct.converted_amount) as "total_converted_amount"
FROM sale s
LEFT JOIN sale_lines sl ON sl.sale_id = s.id
LEFT JOIN cash_transactions ct ON ct.sale_id = s.id
GROUP BY s.currency_items_sold_in;

上述查询将产生错误的结果，因为sale_lines和cash_transactions的行数不一致，导致s.currency_items_sold_in下的每一组内部数据行被重复计算。例如，销售1（DKK）有2条销售明细和2条现金交易，直接JOIN后，每个销售明细会与每个现金交易组合，导致sale的DKK行被复制4次，SUM(sl.price_paid)和SUM(ct.received_amount)都会是实际值的2倍。

解决方案：基于CTE的预聚合

解决此类问题的关键在于“预聚合”。我们应该在将不同“一对多”分支连接到主表之前，分别对每个分支的数据进行聚合。这样可以确保每个子表在连接到主表时，每组只有一个聚合结果行，从而避免数据膨胀。

对于本场景，由于cash_transactions的received_amount和converted_amount可能涉及不同的币种，我们需要更精细的预聚合。理想的方案是：

1. 确定聚合维度：我们需要按sale的交易币种（currency_items_sold_in）来汇总。
2. 独立预聚合：
   • sale_lines：按sale_id聚合price_paid。
   • cash_transactions（收到金额）：按sale_id和received_currency_id聚合received_amount。
   • cash_transactions（转换金额）：按sale_id和converted_currency_id聚合converted_amount。
3. 最终连接：将这些预聚合的结果连接回sale表或直接连接到currency表，并进行最终的按币种分组。

使用公共表表达式（CTE）可以使查询结构更清晰、逻辑更易于理解。

详细步骤与代码示例

我们将使用一个CTE来获取所有销售的sale_id及其对应的currency_items_sold_in，作为后续子查询的统一基础。然后，针对sale_lines和cash_transactions，分别创建子查询进行预聚合。

WITH CTE_SALE AS (
  -- 定义一个CTE，用于获取所有销售的ID及其销售币种
  SELECT
    id as sale_id, 
    currency_items_sold_in AS iso_number -- 将销售币种作为ISO编号，便于后续JOIN
  FROM sale
)
SELECT 
  curr.iso_code AS currency, -- 最终显示货币代码
  COALESCE(line.price_paid, 0)  as total_price_paid, -- 销售明细总价，若无则为0
  COALESCE(received.amount, 0)  as total_received_amount, -- 收到的总金额，若无则为0
  COALESCE(converted.amount, 0) as total_converted_amount -- 转换后的总金额，若无则为0
FROM currency AS curr -- 从货币表开始，确保所有已知货币都被考虑
LEFT JOIN (
  -- 子查询1: 聚合销售明细的总价
  SELECT 
    s.iso_number, -- 按销售币种分组
    SUM(sl.price_paid) AS price_paid
  FROM sale_lines sl
  JOIN CTE_SALE s ON s.sale_id = sl.sale_id -- 通过CTE_SALE关联到销售币种
  GROUP BY s.iso_number -- 按销售币种聚合
) AS line 
  ON line.iso_number = curr.iso_number -- 将聚合结果连接到货币表

LEFT JOIN (
  -- 子查询2: 聚合收到的总金额
  SELECT 
    tr.received_currency_id as iso_number, -- 按收到的币种分组
    SUM(tr.received_amount) AS amount
  FROM cash_transactions tr
  JOIN CTE_SALE s ON s.sale_id = tr.sale_id -- 通过CTE_SALE关联到销售
  GROUP BY tr.received_currency_id -- 按收到的币种聚合
) AS received
  ON received.iso_number = curr.iso_number -- 将聚合结果连接到货币表

LEFT JOIN (
  -- 子查询3: 聚合转换后的总金额
  SELECT 
    tr.converted_currency_id as iso_number, -- 按转换后的币种分组
    SUM(tr.converted_amount) AS amount
  FROM cash_transactions AS tr
  JOIN CTE_SALE s ON s.sale_id = tr.sale_id -- 通过CTE_SALE关联到销售
  GROUP BY tr.converted_currency_id -- 按转换后的币种聚合
) AS converted
  ON converted.iso_number = curr.iso_number; -- 将聚合结果连接到货币表

查询结果示例：

结果解读：

• DKK (丹麦克朗):
  • total_price_paid为500：来自销售1（DKK）的销售明细总价 (200 + 300 = 500)。
  • total_received_amount为300：来自销售1的第一笔交易 (200 DKK) + 销售2的第二笔交易 (100 DKK)。
  • total_converted_amount为700：来自销售1的两笔交易转换后 (200 DKK + 300 DKK) + 销售2的两笔交易转换后 (100 DKK + 100 DKK)。
• SEK (瑞典克朗):
  • total_price_paid为200：来自销售2（SEK）的销售明细总价 (100 + 100 = 200)。
  • total_received_amount为400：来自销售1的第二笔交易 (400 SEK)。
  • total_converted_amount为0：没有交易转换为SEK。
• NOK (挪威克朗):
  • total_price_paid为0：没有销售以NOK计价。
  • total_received_amount为150：来自销售2的第一笔交易 (150 NOK)。
  • total_converted_amount为0：没有交易转换为NOK。

这个结果准确地反映了在不同币种上下文下的聚合总和，避免了数据重复导致的错误。

注意事项与总结

1. 预聚合原则：当处理多个“一对多”关系时，始终优先考虑在JOIN到主表之前对子表进行预聚合。这可以有效避免数据膨胀问题，确保聚合结果的准确性。
2. CTE的优势：使用公共表表达式（CTE）可以提高查询的可读性和模块化，尤其是在复杂的查询中。它允许你定义临时的、命名的结果集，供后续查询引用。
3. 多币种处理：对于像cash_transactions这样可能涉及多种币种的字段，需要根据其上下文（例如received_currency_id和converted_currency_id）进行独立的聚合，以确保每个聚合结果都具有明确的币种含义。
4. COALESCE函数：在进行LEFT JOIN时，如果某个货币没有对应的销售明细或现金交易，聚合子查询将不会返回该货币的行。使用COALESCE(column_name, 0)可以确保这些情况下返回0而不是NULL，使结果更清晰。
5. 性能考虑：对于非常大的数据集，过多的子查询或CTE可能会对性能产生影响。数据库查询优化器通常能够很好地处理这些结构，但在极端情况下，可能需要评估索引策略或考虑物化视图等优化手段。
6. 业务逻辑优先：在设计聚合逻辑时，始终要清晰地理解业务需求。例如，是需要按销售发生的币种聚合，还是按收到金额的币种聚合，这决定了GROUP BY的字段选择。

通过采用这种预聚合的方法，我们能够有效地解决SQL深度关联数据聚合中的“扇出”问题，尤其是在涉及复杂的多币种财务数据时，确保了数据分析的准确性和可靠性。