问题描述
在一个典型的销售数据模型中，我们可能拥有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可能涉及不同的币种，我们需要更精细的预聚合。理想的方案是：
- 确定聚合维度：我们需要按sale的交易币种（currency_items_sold_in）来汇总。
-
独立预聚合：
- sale_lines：按sale_id聚合price_paid。
- cash_transactions（收到金额）：按sale_id和received_currency_id聚合received_amount。
- cash_transactions（转换金额）：按sale_id和converted_currency_id聚合converted_amount。
- 最终连接：将这些预聚合的结果连接回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; -- 将聚合结果连接到货币表查询结果示例：
| currency | total_price_paid | total_received_amount | total_converted_amount |
|---|---|---|---|
| DKK | 500 | 300 | 700 |
| SEK | 200 | 400 | 0 |
| NOK | 0 | 150 | 0 |
结果解读：
-
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。
这个结果准确地反映了在不同币种上下文下的聚合总和，避免了数据重复导致的错误。
注意事项与总结
- 预聚合原则：当处理多个“一对多”关系时，始终优先考虑在JOIN到主表之前对子表进行预聚合。这可以有效避免数据膨胀问题，确保聚合结果的准确性。
- CTE的优势：使用公共表表达式（CTE）可以提高查询的可读性和模块化，尤其是在复杂的查询中。它允许你定义临时的、命名的结果集，供后续查询引用。
- 多币种处理：对于像cash_transactions这样可能涉及多种币种的字段，需要根据其上下文（例如received_currency_id和converted_currency_id）进行独立的聚合，以确保每个聚合结果都具有明确的币种含义。
- COALESCE函数：在进行LEFT JOIN时，如果某个货币没有对应的销售明细或现金交易，聚合子查询将不会返回该货币的行。使用COALESCE(column_name, 0)可以确保这些情况下返回0而不是NULL，使结果更清晰。
- 性能考虑：对于非常大的数据集，过多的子查询或CTE可能会对性能产生影响。数据库查询优化器通常能够很好地处理这些结构，但在极端情况下，可能需要评估索引策略或考虑物化视图等优化手段。
- 业务逻辑优先：在设计聚合逻辑时，始终要清晰地理解业务需求。例如，是需要按销售发生的币种聚合，还是按收到金额的币种聚合，这决定了GROUP BY的字段选择。
通过采用这种预聚合的方法，我们能够有效地解决SQL深度关联数据聚合中的“扇出”问题，尤其是在涉及复杂的多币种财务数据时，确保了数据分析的准确性和可靠性。
