处理复杂多对多关系中SQL聚合函数的挑战：以多币种销售数据为例

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 product (
  id SERIAL PRIMARY KEY,
  name CHARACTER VARYING(12),
  current_price INTEGER
);
INSERT INTO product(id,name,current_price) VALUES (1,'icecream',200), (2,'sunglasses',300);

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明细1
(2, 1, 2, 300, 1.0), -- 销售1明细2

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

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交易1: DKK -> DKK
(2, 1, '752', '208', 400, 300), -- 销售1交易2: SEK -> DKK (收到SEK 400，转换为DKK 300)

(3, 2, '572', '208', 150, 100), -- 销售2交易1: NOK -> DKK (收到NOK 150，转换为DKK 100)
(4, 2, '208', '208', 100, 100)  -- 销售2交易2: 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;

-- 子查询预聚合方法 (部分解决，但仍有币种问题)
SELECT 
  s.currency_items_sold_in, 
  SUM(sale_line_aggregates.price_paid) as "total_price_paid",
  SUM(cash_transaction_aggregates.converted_amount) as "total_converted_amount",
  SUM(cash_transaction_aggregates.received_amount) as "total_received_amount"
FROM sale s
LEFT JOIN (
  SELECT 
    sale_id,
    SUM(price_paid) AS price_paid
  FROM sale_lines
  GROUP BY sale_id
) AS sale_line_aggregates ON sale_line_aggregates.sale_id = s.id
LEFT JOIN (
  SELECT
    sale_id,
    SUM(converted_amount) as converted_amount,
    SUM(received_amount) as received_amount
  FROM cash_transactions
  GROUP BY sale_id
) AS cash_transaction_aggregates ON cash_transaction_aggregates.sale_id = s.id
GROUP BY s.currency_items_sold_in;

WITH CTE_SALE AS (
  -- 定义一个CTE来获取所有销售的主ID和销售币种
  SELECT
    id AS sale_id, 
    currency_items_sold_in AS iso_number
  FROM sale
)
SELECT 
  curr.iso_code AS currency,
  COALESCE(line.price_paid, 0) AS total_price_paid,
  COALESCE(received.amount, 0) AS total_received_amount,
  COALESCE(converted.amount, 0) AS total_converted_amount
FROM currency AS curr
LEFT JOIN (
  -- 聚合销售明细金额，按销售主币种分组
  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
  GROUP BY s.iso_number
) AS line 
  ON line.iso_number = curr.iso_number
LEFT JOIN (
  -- 聚合收到的金额，按收到的币种分组
  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
  GROUP BY tr.received_currency_id
) AS received
  ON received.iso_number = curr.iso_number
LEFT JOIN (
  -- 聚合转换后的金额，按转换后的币种分组
  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
  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