使用上一个日期的期初余额计算值的总和

Question

我有下表，您也可以在此处的SQL fiddle中找到：

CREATE TABLE Flows (
Flow_Date DATE,
Product TEXT,
FlowType TEXT,
Quantity VARCHAR(255)
);
INSERT INTO Flows
(Flow_Date, Product, FlowType, Quantity)
VALUES 
("2019-05-23", "Product A", "Inbound", "400"),
("2019-05-23", "Product B", "Inbound", "500"),
("2019-05-23", "Product A", "Outbound", "300"),
("2019-05-23", "Product B", "Outbound", "200"),
("2019-05-23", "Product A", "Stock", "100"),
("2019-05-23", "Product B", "Stock", "300"),
("2019-06-19", "Product A", "Inbound", "900"),
("2019-06-19", "Product B", "Inbound", "800"),
("2019-06-19", "Product A", "Outbound", "650"),
("2019-06-19", "Product B", "Outbound", "400"),
("2019-06-19", "Product A", "Stock", "350"),
("2019-06-19", "Product B", "Stock", "700");

我使用以下查询从表中获取信息数据：

SELECT
Flow_Date,
SUM(Stock) AS Stock,
SUM(Inbound + Outbound*-1) AS Stock_Calculated,
SUM(Inbound) AS Inbound,
SUM(Outbound) AS Outbound
FROM
(SELECT 
Flow_Date, 
sum(case when FlowType = 'Stock' then Quantity else 0 end) Stock,
sum(case when FlowType = 'Inbound' then Quantity else 0 end) Inbound,
sum(case when FlowType = 'Outbound' then Quantity else 0 end) Outbound
FROM Flows
GROUP BY 1) Flows
GROUP BY 1
+------------+-------+------------------+---------+----------+
| Flow_Date  | Stock | Stock_Calculated | Inbound | Outbound |
+------------+-------+------------------+---------+----------+
| 2019-05-23 |   400 |              400 |     900 |      500 |
| 2019-06-19 |  1050 |              650 |    1700 |     1050 |
+------------+-------+------------------+---------+----------+

所有这些都完全按照我的需要工作。

---

但是，现在我的问题是线路SUM(Inbound + Outbound*-1) AS Stock_Calculated.
基本上，我想实现股票是由Inbounds和Outbounds计算的。
为此，我还必须包括股票的Opening Balance。

Opening Balance + Inbounds - Outbounds = Closing Balance (--> Opening Balance for next date)

结果应如下所示：

Flow_Date       Stock     Stock_Calculated      Inbound      Outbound
2019-05-23        400          400*               900           500
2019-06-19      1.000        1.000**            1.700         1.100
* 0 + 900 - 500 = 400
** 400 + 1.700 - 1.100 = 1.000

我必须在代码中更改什么才能使其正常工作？

Answer 1

MySQL 5+

SELECT Flow_Date, 
Stock,
@Stock := @Stock + Inbound - Outbound Stock_Calculated,
Inbound,
Outbound
FROM ( SELECT Flow_Date,
SUM(case when FlowType = 'Stock' 
then Quantity 
else 0 end) AS Stock,
SUM(case when FlowType = 'Inbound' 
then Quantity 
else 0 end) AS Inbound,
SUM(case when FlowType = 'Outbound'       
then Quantity 
else 0 end) AS Outbound
FROM Flows
GROUP BY Flow_Date ) Flows, 
( SELECT @Stock := 0 ) init_variable
ORDER BY Flow_Date;

MySQL 8+：

SELECT Flow_Date, 
Stock,
SUM(Inbound) OVER (ORDER BY Flow_Date) - SUM(Outbound) OVER (ORDER BY Flow_Date) Stock_Calculated,
Inbound,
Outbound
FROM ( SELECT Flow_Date,
SUM(case when FlowType = 'Stock' 
then Quantity 
else 0 end) AS Stock,
SUM(case when FlowType = 'Inbound' 
then Quantity 
else 0 end) AS Inbound,
SUM(case when FlowType = 'Outbound'       
then Quantity 
else 0 end) AS Outbound
FROM Flows
GROUP BY Flow_Date ) Flows;

小提琴

Answer 2

您可以混合使用窗口函数和聚合，因此：

select Flow_Date, 
sum(case when FlowType = 'Stock' then Quantity else 0 end) as Stock,
sum(case when FlowType = 'Inbound' then Quantity else 0 end) as Inbound,
sum(case when FlowType = 'Outbound' then Quantity else 0 end) as Outbound,
sum(case when FlowType = 'Inbound' then Quantity
when FlowType = 'Outbound' then -Quantity
else 0
end) over (order by date) as calculated_stock
from Flows
group by 1;