计算具有上一日期期初余额的值的总和 [英] Calculate sum of values with opening balance from previous date

问题描述

我有下表，您也可以在 SQL fiddle 此处:

I have the following table which you can also find in the SQL fiddle here:

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");

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

I use the following query to get information data from the table:

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 |
+------------+-------+------------------+---------+----------+

所有这些都完全符合我的需要.

All this works exactly how I need it.

但是，现在我的问题是 SUM(Inbound + Outbound*-1) AS Stock_Calculated 行.
基本上，我想实现库存是由 Inbounds 和 Outbounds 计算的.
为此，我还必须包含股票的期初余额.

However, now my issue is the line SUM(Inbound + Outbound*-1) AS Stock_Calculated.
Basically, I want to achive that the Stock is calculated by the Inbounds and Outbounds.
For this I also have to include an Opening Balance for the stock.

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

我需要对代码进行哪些更改才能使其正常工作?

What do I have to change in my code to make it work?

推荐答案

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

You can mix window functions and aggregations, so:

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;

这篇关于计算具有上一日期期初余额的值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！