如何在对深度隐藏的数据进行分组时处理SQL聚合函数 [英] How to deal with SQL Aggregate Functions when Grouping deeply hiearched data

问题描述

假设有以下情况：

我有一个包含5个表的数据库：

1. 币种(iso_number，iso_code)，
2. 产品(id，名称，Current_Price)，
3. Sales(id，Time_of_Sales，Currency_Items_Sold_in)，
4. Sales_Line(id，Sales_id，product_id，Price_Payed，Quantity)，
5. Cash_Transaction(id，Sales_id，Receied_Currency_id，Converted_Currency_id，Receired_Amount，Converted_Amount)

该设置允许存储客户最初提供的货币类型、内部兑换的货币以及原始金额和兑换(转换)金额。

我希望能够找到符合特定条件(时间段、卖家、门店)等的所有销售((为简单起见))。

对于所有这些销售，我将连接相关数据，即Sales_Lines和Cash_Transaction。现在，SALEL_LINES上的货币始终与相关销售上的货币匹配。 但是，对于CASH_TRANSACTIONS，RECEIVED_AMOUNT/RECEIVED_CURRENT可能不同于销售中的货币。尽管Converted_Currency/Converted_Amount存储在CASH_TRANSACTION行上，但它应该位于销售之后。

当我尝试执行某些字段的求和时，当您开始联接一对多关系，然后执行诸如SUM之类的聚合函数时，即使您在后台指定了正确的GROUP BY，SQL Server仍会对显示数据所需的重复行求和，如果我们没有使用GROUP BY的话。

此处还介绍了该问题： https://wikido.isoftdata.com/index.php/The_GROUPing_pitfall

按照上面文章中的解决方案，在我的例子中，我应该将每次销售的聚合结果连接到外部查询。

但是，如果SALEL_LINES币种与销售匹配，但CASH_TRANSACTIONS币种可能与销售不同，我该怎么办？

我尝试创建以下SQL文件，它插入一些测试数据并突出显示问题：http://sqlfiddle.com/#!17/54a7b/15

在小提琴中，我创建了2个Sales，其中项目以丹麦克朗(208)和752丹麦克朗(瑞典克朗)销售。 在第一次销售中，有两个销售行和两笔现金交易，第一笔交易直接为DKK=>；DKK，第二笔交易为SEK=>；DKK。

在第二次销售中，还有两笔销售行和两笔现金交易，第一笔交易为NOK=>；DKK，第二笔交易直接为DKK=>；DKK。

在小提琴的最后一次查询中，可以观察到TOTAL_RECEIVED_AMOUNT是假的，因为它是DKK、SEK和NOK的组合，没有提供太多值。

我需要有关如何正确获取数据的建议，我不在乎是否必须在服务器端(PHP)上执行额外的逻辑和操作来消除某些数据的重复数据，只要总和正确即可。

我们非常感谢您的建议。

小提琴

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;

推荐答案

您可以计算子查询中按货币分组的每个金额。 然后在货币上加入他们的行列。

使用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;

currency | total_price_paid | total_received_amount | total_converted_amount
:------- | ---------------: | --------------------: | ---------------------:
DKK      |              500 |                   300 |                    700
SEK      |              200 |                   400 |                      0
NOK      |                0 |                   150 |                      0

小提琴here

这篇关于如何在对深度隐藏的数据进行分组时处理SQL聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！