對具有多個連接的不同行求和

September 10, 2021

架構：
CREATE TABLE "items" (
 "id"            SERIAL                   NOT NULL PRIMARY KEY,
 "country"       VARCHAR(2)               NOT NULL,
 "created"       TIMESTAMP WITH TIME ZONE NOT NULL,
 "price"         NUMERIC(11, 2)           NOT NULL
);
CREATE TABLE "payments" (
 "id"      SERIAL                   NOT NULL PRIMARY KEY,
 "created" TIMESTAMP WITH TIME ZONE NOT NULL,
 "amount"  NUMERIC(11, 2)           NOT NULL,
 "item_id" INTEGER                  NULL
);
CREATE TABLE "extras" (
 "id"      SERIAL                   NOT NULL PRIMARY KEY,
 "created" TIMESTAMP WITH TIME ZONE NOT NULL,
 "amount"  NUMERIC(11, 2)           NOT NULL,
 "item_id" INTEGER                  NULL
);
資料：
INSERT INTO items VALUES
 (1, 'CZ', '2016-11-01', 100),
 (2, 'CZ', '2016-11-02', 100),
 (3, 'PL', '2016-11-03', 20),
 (4, 'CZ', '2016-11-04', 150)
;
INSERT INTO payments VALUES
 (1, '2016-11-01', 60, 1),
 (2, '2016-11-01', 60, 1),
 (3, '2016-11-02', 100, 2),
 (4, '2016-11-03', 25, 3),
 (5, '2016-11-04', 150, 4)
;
INSERT INTO extras VALUES
 (1, '2016-11-01', 5, 1),
 (2, '2016-11-02', 1, 2),
 (3, '2016-11-03', 2, 3),
 (4, '2016-11-03', 3, 3),
 (5, '2016-11-04', 5, 4)
;
所以，我們有：
CZ 中 3 項 PL 中 1 項
在 CZ 中獲得 370，在 PL 中獲得 25
CZ 350 成本和 PL 20
在 CZ 中額外獲得 11 個額外收入，在 PL 中額外獲得 5 個額外收入
現在我想得到以下問題的答案：
上個月我們在每個國家/地區有多少物品？
每個國家/地區的總收入（payments.amounts 的總和）是多少？
每個國家的總成本（items.price 的總和）是多少？
每個國家/地區的總額外收入（extras.amount 的總和）是多少？
使用以下查詢（SQLFiddle）：
SELECT
 country                  AS "group_by",
 COUNT(DISTINCT items.id) AS "item_count",
 SUM(items.price)         AS "cost",
 SUM(payments.amount)     AS "earned",
 SUM(extras.amount)       AS "extra_earned"
FROM items
 LEFT OUTER JOIN payments ON (items.id = payments.item_id)
 LEFT OUTER JOIN extras ON (items.id = extras.item_id)
GROUP BY 1;
結果是錯誤的：
group_by | item_count |  cost  | earned | extra_earned
----------+------------+--------+--------+--------------
CZ       |          3 | 450.00 | 370.00 |        16.00
PL       |          1 |  40.00 |  50.00 |         5.00
CZ 的 Cost 和 extra_earned 無效 - 450 代替 350 和 16 代替 11。 PL 的 Cost 和 Earned 也無效 - 它們加倍。
我了解，如果LEFT OUTER JOINitems.id = 1 的項目將有 2 行（其他匹配項以此類推），但我不知道如何建構正確的查詢。
問題：
如何避免在多個表的查詢中聚合錯誤結果？
計算不同值總和的最佳方法是什麼（在這種情況下為 item.id）？
PostgreSQL 版本：9.6.1

由於per可以有多個payments和多個，因此您會在這兩個表之間遇到**“代理交叉連接” 。**在加入之前聚合行，它應該都是正確的：extras``item``item_id item
SELECT i.country         AS group_by
    , COUNT(*)          AS item_count
    , SUM(i.price)      AS cost
    , SUM(p.sum_amount) AS earned
    , SUM(e.sum_amount) AS extra_earned
FROM  items i
LEFT  JOIN (
  SELECT item_id, SUM(amount) AS sum_amount
  FROM   payments
  GROUP  BY 1
  ) p ON p.item_id = i.id
LEFT  JOIN (
  SELECT item_id, SUM(amount) AS sum_amount
  FROM   extras
  GROUP  BY 1
  ) e ON e.item_id = i.id
GROUP BY 1;
考慮“魚市”的例子：
兩個 SQL LEFT JOINS 產生不正確的結果
準確地說，SUM(i.price)在加入單個 n 表後將是不正確的，該表將每個價格乘以相關行的數量。做兩次只會讓事情變得更糟——而且計算成本也可能很高。
哦，因為我們現在不乘以行items，所以我們可以使用更便宜的count(*)而不是count(DISTINCT i.id). （id存在NOT NULL PRIMARY KEY。）
db<>fiddle here
舊sqlfiddle
但是如果我想過濾items.created？
處理您的評論。
這取決於。我們可以對和應用相同的過濾器payments.created嗎extras.created？
如果是，那麼只需在子查詢中添加過濾器。（在這種情況下似乎不太可能。）
如果不是，但我們仍在選擇大多數項目，則上述查詢仍然是最有效的。連接中消除了子查詢中的一些聚合，但這仍然比更複雜的查詢便宜。
如果不是，並且我們正在選擇一小部分項目，我建議使用相關子查詢或LATERAL連接。例子：
單個查詢中的多個 array_agg() 呼叫