sql和fifo简单选择 [英] sql and fifo simple select

问题描述

我有这样的数据：

  Id价格数量
 1 1000 4 4000 
 2 1000 4.5 4500 
 3 1000 5 5000 
  

我会要求数据库行的价格是例如2500的总和。

我希望回答这个问题：

  Id价格数量
 1 1000 4 4000 
 2 1000 4.5 4500 
 3 500 5 2500 
  

我可以使用while循环，但我认为选择会更聪明。
我的数据库是Firebird。

我在数据库中有3行，例如购买美元。

• 首先，我为4 PLN购买了$ 1000，


• 第二次我购买了$ 1000 PLN，


• ，第三个我为5 PLN购买了$ 1000。

（PLN是波兰货币）。

现在我想卖$ 2500，当然我想知道PLN中的美元成本是多少。

在我看来，这笔交易费用：（1000 * 4）+（1000 * 4.5）+（500 * 5）= 12000 PLN。

这是非常重要的，以了解波兰会计师在PLN中的成本是多少美元。

解决方案

以下显示了如何在SQL中运行美元购买和销售的FIFO模型。我已经为MS SQL Server编写并测试了它，将其翻译成Firebird SQL并进行了测试。

所以，从$购买DollarPurchases表开始如下

  CREATE TABLE [dbo]。[DollarPurchases]（
 [ID] [ int] NULL，
 [价格] [float] NULL，
 [数量] [float] NULL，
 [成本] AS（[价格] * [数量]）
） 
  

并添加几行

  insert dollarpurchases（id，price，quantity）values（1，1000，4）
 insert dollarpurchases（id，price，quantity）values（2 ，1100,4.5）
 insert dollarpurchases（id，price，quantity）values（3，1500，5）
  

然后，我们可以创建一个包含一个正在运行的总计的视图

 创建视图vwcosts as 
 select 
 *，
 cumulativecost =（select sum（Cost）from dollarp购买p2其中p2.id< = p1.id）
从
 dollarpurchases p1 
  

视图的内容将如下所示

  ID价格数量成本累积成本
 1 1000 4 4000 4000 
 2 1100 4.5 4950 8950 
 3 1500 5 7500 16450 
  

现在，我们想要出售一定数量的美元。这样做的算法可能如下，假设ID列值反映了购买的顺序：

1. 查找最低的一行ID，其 cumulativecost 超过要出售的美元金额。




2. 标记或删除所有行ID都较低，因为他们需要全部出售才能实现美元数额。




3. 具有查找ID的行中的美元将需要全部或部分出售才能实现剩余的金额。如果需要整体，请按照步骤1删除或标记此行，如果部分更新该行以反映美元的剩余数量及其成本。

就是这样。

在下面的内容中，我不会对实时数据进行这些操作制作DollarPurchases的副本并执行此操作。我在代码中留下了一些调试语句进行检查。

   - 声明一些脚本变量使用
声明
 @dollarstosell float，
 @highestrowtosell int，
 @dollarsremaining float 
 
选择
 @dollarstosell = 8000 
 
选择*从dollarpurchases购买复制品 - 创建购买表的副本
 
 
选择@highestrowtosell =（从vwcosts中选择min（id）其中cumulativecost> @dollarstosell ）
 
选择@highestrowtosell  - 调试
 
  - 计算将在部分销售的行中剩余多少美元
 select @dollarsremaining =（select来自vwcosts的累积积分，其中id = @highestrowtosell） -  @dollarstosell 
 
选择@dollarsremaining  - 用于调试
 
  - 删除将以
销售的行$ 
从purchasecopy中删除，其中id < @highestrowtosell 
 
  - 更新将部分销售的行
 
更新purchasecopy set quantity = @dollarsremaining / price，cost = @dollarsremaining其中id = @highestrowtosell 
 
 select * from purchasescopy 
 
  - 以下是可选的，以整理
 
 drop view vwcosts 
 drop table purchasescopy 
  

这样会产生

  ID价格数量成本
 2 1100 0.86 950 
 3 1500 5 7500 
  

当然，上述仅针对最高相关行仅部分销售的情况，但如果按照第1步进行整体销售，则处理该事宜是微不足道的。

我想象一个真正的SQL专家可以在单个SQL语句中执行上述所有操作，但是我希望这个一步一步的方法可以更容易地跟踪发生了什么在$或$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $>>>> $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ code> while 循环，但也许有点像在其他答案中给出的Delphi代码示例。

将以上内容翻译成Firebird SQL如下所示。与MS SQL Server版本相比，它有两个主要更改：

• 计算成本列被重命名为 avalue （它将是值，但是命名冲突） p>




• 由于Firebird SQL不支持以TransactSQL的方式免费使用本地变量，所以我将单个行变量表。这使得一些语句有点措辞，但是从我的pov到Firebird的 EXECUTE BLOCK 。

代码：

 创建表dollarpurchases （id int，price float，quantity float，avalue calculate by（price * quantity））; 
 
创建表purchasecopy（id int，price float，quantity float）; 
 
创建视图vwDollarPurchases作为选择p1。*，（从dollarpurchases p2中选择sum（avalue），其中p2.id <= p1.id）作为来自dollarpurchases p1的累积值; 
 
创建表变量（ID int，dollarstosell float，highestrowtosell int，dollarsremaining float）; 
 
 
插入dollarpurchases（id，price，quantity）值（1，1000，4）; 
 
插入dollarpurchases（id，price，quantity）值（2,1100,4.5）; 
 
插入dollarpurchases（id，price，quantity）值（3,1500,5）; 
 
插入变量（ID，dollarstosell，highestrowtosell，dollarsremaining）
值（1，8000，0，0）; 
 
插入purchasecopy（id，price，quantity）选择id，价格，数量从dollarpurchases; 
 
更新变量set highestrowtosell =（从VWDOLLARPURCHASES中选择min（id），其中累积值> dollarstosell）其中id = 1; 
 
更新变量v1 set v1.dollarsremaining =（从VWDOLLARPURCHASES v2中选择distinct v2.cumulativevalue，其中v2.id = v1.highestrowtosell） -  v1.dollarstosell其中v1.id = 1; 
 
从purchasecopy中删除，其中id < （从id = 1的变量中选择highestrowtosell）; 
 
更新purchasecopy set quantity =（select dollarsremaining from variables where id = 1）/ price where id =（select highestrowtosell from variables where id = 1）; 
 
 select * from purchasescopy; 
  

I have data like this:

Id Price Quantity Value
1   1000    4      4000
2   1000    4.5    4500
3   1000    5      5000

and I would ask for database row when the price is the sum of for example 2500.

I expected to answer this:

Id Price Quantity Value
1  1000     4      4000
2  1000     4.5    4500
3   500     5      2500

Of could I could use while loop but I think select will be more smart. My database is Firebird.

I have 3 lines in database for example buy dollars.

• first, I bought $ 1000 for 4 PLN,
• second I bought $ 1000 for 4.5 PLN,
• and the third I bought $ 1000 for 5 PLN.

(PLN is Polish currency).

And now I would like to sell $ 2500 and of course I would like know how much is dollars cost in PLN.

In my opinion this transaction cost: (1000 * 4) + (1000 * 4.5) + (500 * 5) = 12000 PLN.

This is very important to know how much dollars is cost in PLN to Polish accountant.

解决方案

The following shows the essentials of how to operate a FIFO model of dollar purchases and sales in SQL. I've written and tested it for MS SQL Server translated it into Firebird SQL and tested it

So, start with a DollarPurchases table as follows

CREATE TABLE [dbo].[DollarPurchases](
    [ID] [int] NULL,
    [Price] [float] NULL,
    [Quantity] [float] NULL,
    [Cost]  AS ([Price]*[Quantity])
) 

and add a few rows to it

insert dollarpurchases(id, price, quantity) values (1, 1000, 4)
insert dollarpurchases(id, price, quantity) values (2, 1100, 4.5)
insert dollarpurchases(id, price, quantity) values (3, 1500, 5)

Then, we can create a view which includes a running total

create view vwcosts as
select 
  * ,
  cumulativecost = (select sum(Cost) from dollarpurchases p2 where p2.id <= p1.id)
from 
  dollarpurchases p1

The contents of the view would look like this

ID   Price      Quantity   Cost   cumulativecost
1    1000          4       4000      4000
2    1100          4.5     4950      8950
3    1500          5       7500     16450

Now, suppose we want to sell a certain amount of dollars. An algorithm to do this might be as follows, assuming the ID column value reflects the order in which the purchases were made:

1. Find the lowest ID of a row whose cumulativecost exceeds the dollar amount to sell.

2. Flag or delete all the rows with a lower ID, as they will need to be sold in their entirety to realise the dollar amount.

3. The dollars in the row with the found ID will need to be sold in whole or in part to realise the remainder of the dollar amount. If it needs to be the whole, delete or flag this row as per step 1, if in part, update the row to reflect the residual quantity of dollars and their cost.

and that's it.

In the following, instead of doing these operations on the live data, I'm going to make a copy of the DollarPurchases and perform the operations on that. I've left a few debugging statements in the code for checking purposes.

-- declare some script variables to use
declare
  @dollarstosell float,
  @highestrowtosell int,
  @dollarsremaining float

select
  @dollarstosell = 8000

select * into purchasescopy from dollarpurchases -- create copy of purchases table


select @highestrowtosell = (select min(id) from vwcosts where cumulativecost > @dollarstosell)

select @highestrowtosell -- for debugging

-- calculate how many dollars will remain in the row which will be partially sold
select @dollarsremaining = (select cumulativecost from vwcosts where id = @highestrowtosell) - @dollarstosell

select @dollarsremaining  -- for debugging

-- remove the rows which will be sold in toto

delete from purchasescopy where id < @highestrowtosell

--update the row which will be partially sold

update purchasescopy set quantity = @dollarsremaining / price, cost = @dollarsremaining where id = @highestrowtosell

select * from purchasescopy

-- following are optional to tidy up

drop view vwcosts
drop table purchasescopy  

This yields

ID  Price   Quantity    Cost
2   1100    0.86         950
3   1500    5           7500

Of course, the above only addresses the case where the highest relevant row is only partially sold, but it would be trivial to deal with it as per step 1 if it is being sold in its entirety.

I imagine that a real SQL expert could do all the above in a single SQL statement, but I hope this step-by-step approach makes it easier to follow what's going on and to debug it.

It's worth mentioning that this could all be done processing the data row-by-row using a SQL cursor in a while loop, but maybe that's a bit too much like the Delphi code example you've been given in the other answer.

A translation of the above into Firebird SQL is shown below. It has two main changes compared to the MS SQL Server version:

• Ihe computed cost column is renamed to avalue (it would have been value but for a naming conflict).

• As Firebird SQL does not support the free use of local variables in the way TransactSQL does, I replaced the variable by entries in a single-row variables table. This makes some of the statements a bit wordier but is preferable from my pov to doing it with Firebird's EXECUTE BLOCK.

The code:

create table dollarpurchases(id int, price float, quantity float, avalue computed by (price*quantity));

create table purchasescopy(id int, price float, quantity float);

create view vwDollarPurchases as select p1.*, (select sum(avalue) from dollarpurchases p2 where p2.id <= p1.id) as cumulativevalue from dollarpurchases p1;

create table variables(ID int, dollarstosell float, highestrowtosell int, dollarsremaining float);


insert into dollarpurchases(id, price, quantity) values (1, 1000, 4);

insert into dollarpurchases(id, price, quantity) values (2, 1100, 4.5);

insert into dollarpurchases(id, price, quantity) values (3, 1500, 5);

insert into variables(ID, dollarstosell, highestrowtosell, dollarsremaining)
values(1, 8000, 0, 0);

insert into purchasescopy(id, price, quantity) select id, price, quantity from dollarpurchases;

update variables set highestrowtosell = (select min(id) from VWDOLLARPURCHASES where cumulativevalue > dollarstosell) where id = 1;

update variables v1 set v1.dollarsremaining = (select distinct v2.cumulativevalue from VWDOLLARPURCHASES v2 where v2.id = v1.highestrowtosell) - v1.dollarstosell where v1.id = 1;

delete from purchasescopy where id < (select highestrowtosell from variables where id = 1);

update purchasescopy set quantity = (select dollarsremaining from variables where id = 1) / price where id = (select highestrowtosell from variables where id = 1);

select * from purchasescopy;

这篇关于sql和fifo简单选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！