logo标签列表

SQL分区求和

sqlsql-serversql-server-2008t-sql
3

我有6000件存货，库存被分成了两个批次，一个包含4000件，另一个包含2000件。

基于销售订单，我正在尝试预测哪个批次的存货将履行每个订单。

比如：

SELECT product, order_qty, price, date_required FROM orders Where product = 'X'

这将生成以下表格：
product | order_qty | price |  date_required
------------------------------------------------------
 X      | 300       | 1.01  |   01/07/2018
 X      | 500       | 1.00  |   03/07/2018
 X      | 700       | 1.05  |   02/07/2018
 X      | 1000      | 1.00  |   01/08/2018
 X      | 2000      | 1.00  |   20/07/2018
 X      | 600       | 1.06  |   10/07/2018
 X      | 500       | 1.10  |   11/07/2018
 X      | 400       | 1.00  |   10/08/2018

然后我有另一个例子：

SELECT batch, product, qty, date_received FROM batches where product = 'X'

这将产生一个表格，类似于这样：
 batch  |product| qty   |  date_received
-------------------------------------------
 ABC123 | X     | 2000  |   01/04/2018
 987ZYX | X     | 4000  |   01/01/2018

使用这两个表，我想预测哪一批库存订单会被取走。库存采用FIFO系统，因此根据表格，它必须先使用批次代码为987ZYX的批次来履行订单。我正在寻找一个查询将其作为表格呈现：

product | order_qty | price |  date_required|   batch
------------------------------------------------------------------
 X      | 300       | 1.01  |   01/07/2018  |   987ZYX
 X      | 500       | 1.00  |   03/07/2018  |   987ZYX  
 X      | 700       | 1.05  |   02/07/2018  |   987ZYX
 X      | 1000      | 1.00  |   01/08/2018  |   ABC123
 X      | 2000      | 1.00  |   20/07/2018  |   987ZYX & ABC123
 X      | 600       | 1.06  |   10/07/2018  |   987ZYX
 X      | 500       | 1.10  |   11/07/2018  |   987ZYX
 X      | 400       | 1.00  |   10/08/2018  |   ABC123

我已根据所需日期选择上述批次，例如第一个需要的订单是1/7/18，这将从987ZYX批次中移除300件，然后我按此方法继续，直到必须完成来自下一批次的订单。
我知道可以使用SUM over方法，但如何确保它不使用比批次中可用的库存更多的库存呢？如上所示，如果需要从两个批次中履行，则尽可能显示两个批次。
3
这是一个复杂的查询。逻辑需要累加总和。虽然这可以在SQL Server 2008中实现，但在SQL Server 2012+中会更简单。 - Gordon Linoff
嗨@GordonLinoff，不幸的是我被限制在SQL 2008上。 - Richard Thompson
如果按日期排序，这将使跟进变得更加容易。 - paparazzo
1个回答
1
首先，我讨厌日期（部分原因是我的生日在2月29日，部分原因是我的“d”键无法正常工作），因此我为每个订单和每个批次创建了索引，并假设应按照最小可能批次索引的递增顺序处理订单。我也不检查下单时物品是否有库存，以及许多其他事情。
正如@GordonLinoff建议的那样，我们将使用累积总和。我为表＃orders和＃batch创建了新表格，其中包含累积数量（我们只需将所有ID为当前的订单/批次的数量相加）。然后我们递归计算批次...好吧，我们以某种方式找到必要的批次。我想我们不需要递归，但我刚学会在SQL中使用它，所以我很自豪地在不必要的地方使用它。哦，我还忘记检查批次和订单的产品是否相同...
drop table if exists #orders, #batch, #orders_cumulative, #batch_cumulative

create table #orders (id int, product varchar(10), order_qty int, price float, date_required date)
insert into #orders VALUES
(1, 'x', 300, 1.01, '20180107'),
(2, 'y', 500, 1, '20180307'),
(3, 'x', 700, 1.05, '20180207'),
(4, 'x', 1000, 1, '20180108'),
(5, 'x', 2000, 1, '20180402'),
(6, 'x', 600, 1.06, '20180302'),
(7, 'y', 100, 1, '20180203'),
(8, 'x', 100, 1, '20180402')

create table #batch (id int, batch varchar(10), product varchar(10), qty int)
insert into #batch VALUES
(1, 'abc', 'x', 1000),
(2, 'zxc', 'x', 1000),
(3, 'sd', 'x', 2000),
(4, 'eiso', 'y', 10000)

SELECT  o.*
        ,(select sum(order_qty) from #orders where id <= o.id and product = o.product) cumulative_qty
INTO    #orders_cumulative
from    #orders o

select  b.*
        ,isnull((select sum(qty) from #batch where id < b.id and product = b.product), 0) cumulative_ex_qty
        ,(select sum(qty) from #batch where id <= b.id and product = b.product) cumulative_qty
into    #batch_cumulative
FROM    #batch b

select top 10 * from #orders_cumulative
select top 10 * from #batch_cumulative

select  oc.*
        ,case when bc.cumulative_ex_qty > oc.cumulative_qty - oc.order_qty then convert(varchar(10), isnull(b1.batch, '') + ', ' + b2.batch) else b2.batch end batch
from    #orders_cumulative oc 
join    #batch_cumulative bc on oc.cumulative_qty between bc.cumulative_ex_qty and bc.cumulative_qty and oc.product = bc.product
join    #batch b2 on b2.id = bc.id 
left JOIN   #batch b1 on b1.id = bc.id - 1

编辑：我纠正了主要问题（为每个产品分别计算，消除愚蠢的递归），除了将日期时间更改为ID之外。

谢谢，稍作修改后这个方案完美解决了问题。我不知道为什么没有想到使用临时表。 - Richard Thompson
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接