對深度層次結構的資料進行分組時如何處理SQL聚合函式-有解無憂

鑒于以下場景：

我有一個包含 5 個表的資料庫：

貨幣（iso_number，iso_code），
產品（ID，名稱，current_price），
銷售（id，time_of_sale，currency_items_sold_in），
sale_lines (id, sale_id, product_id, price_paid, 數量),
現金交易（id、sale_id、receed_currency_id、converted_currency_id、receive_amount、converted_amount）

該設定允許存盤客戶最初提供的貨幣種類，內部兌換的貨幣以及原始金額和兌換（轉換）金額。

我希望能夠找到符合特定條件（時間段、賣家、商店）等的所有銷售（（為簡單起見省略））。

對于所有這些銷售，我將加入相關資料，即銷售線和現金交易。現在，sale_lines 上的貨幣總是與相關銷售上的貨幣相匹配。但是，對于 cash_transactions， received_amount/received_currency 可能與銷售中的貨幣不同。盡管converted_currency/converted_amount 存盤在cash_transaction 行中，但它應該跟隨銷售。

當我嘗試對某些欄位執行 SUM 時出現問題，當您開始加入一對多關系然后執行像 SUM 這樣的聚合函式時，即使您在幕后指定了正確的 GROUP BY，SQL Server 仍然對重復的行進行求和如果我們不使用 GROUP BY，將需要顯示資料。

這里也描述了這個問題：https : //wikido.isoftdata.com/index.php/The_GROUPing_pitfall

按照上面文章中的解決方案，在我的情況下，我應該將每次銷售的匯總結果左連接到外部查詢上。

但是，當 sale_lines 貨幣與銷售匹配，但 cash_transactions 貨幣可能與銷售不同時，我該怎么辦？

我嘗試創建以下 SQL Fiddle，它插入一些測驗資料并突出顯示問題：http : //sqlfiddle.com/#!17/54a7b/15

在小提琴中，我創建了 2 個銷售，其中的商品以 DKK(208) 和 752(SEK) 出售。第一次銷售有 2 條銷售線，有 2 筆現金交易，第一筆交易直接是 DKK => DKK，第二筆交易是 SEK => DKK。

在第二次銷售中也有 2 條銷售線，2 筆現金交易，第一筆交易是挪威克朗 => 丹麥克朗，第二筆交易直接是丹麥克朗 => 丹麥克朗。

在 fiddle 的最后一個查詢中，可以觀察到 total_received_amount 是偽造的，因為它是 DKK、SEK 和 NOK 的混合，沒有提供太多價值。

我想要有關如何正確獲取資料的建議，我不在乎是否必須在服務器端 (PHP) 上執行額外的“邏輯”，以便在總和正確的情況下對某些資料進行重復資料洗掉。

任何建議都非常感謝。

來自小提琴的 DDL

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'),
(2, CURRENT_TIMESTAMP, '752')
;

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),
(2, 1, 2, 300, 1.0),

(3, 2, 1, 100, 1.0),
(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),
(2, 1, '752', '208', 400, 300),

(3, 2, '572', '208', 150, 100),
(4, 2, '208', '208', 100, 100)
;

來自 Fiddle 的查詢

--SELECT * FROM currency;
--SELECT * FROM product;
--SELECT * FROM sale;
--SELECT * FROM sale_lines;
--SELECT * FROM cash_transactions;


--- Showing the sales with duplicated lines to 
--- fit joined data for OneToMany SaleLines, and OneToMany cash transactions.
SELECT *
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;



--- Grouping the data by important identifier "currency_items_sold_in".
--- The SUM of sl.price_paid is wrong as it SUMS the duplicated lines as well.
SELECT 
  s.currency_items_sold_in, 
  SUM(sl.price_paid) as "price_paid"
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;

--- To solve this the SUM can be joined via the "Monkey-Poop" method.
--- Here the problem arises, the SUMS for cash_transaction.received_amount and cash_transaction.converted_amount cannot be relied upon
--- As those fields themselves rely on cash_transaction.received_currency_id and cash_transaction.converted_currency_id
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;

uj5u.com熱心網友回復：

您可以計算子查詢中按貨幣分組的每個金額。然后加入他們的貨幣。

使用 CTE，您可以確保每個子查詢都使用相同的銷售額。

WITH CTE_SALE AS (
  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;

貨幣 | total_price_paid | total_received_amount | total_converted_amount
:------- | ---------------: | --------------------: | ---------------------：
丹麥克朗 | 500 | 300 | 700
瑞典克朗 200 | 400 | 0
挪威克朗 0 | 150 | 0

db<>在這里擺弄

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/358087.html

標籤：php sql PostgreSQL的总计的聚合函数

上一篇：將SQL查詢轉換為PySpark資料幀，以用于連接資料幀的情況

下一篇：獲取最高值ID