SQL Server价格表加权购买价格

Question

我有3个表:

<表类> ID 项目 数量 价格 tbody><<tr>11001101.0021001102.0031001103.0041002102.0051002101.0061003101.0071004101.0081004102.00

在比较股票和买入合计时使用条件聚合，最后应用回退

select t.item, (s + t.qf * f.price) s, stock, (s + t.qf * f.price) / stock  price
from (
select s.Item, s.Stock, 
sum(coalesce(case when b.qe <= Stock then b.qty else Stock - b.qs end * b.price, 0)) s,
--  qty for fallback
min(case when Stock > coalesce(b.qe,0) then Stock - coalesce(b.qe,0) else 0 end) qf
from Stock s
left join (
select Item, qty, price, ID,
sum(qty) over(partition by Item order by ID desc) - qty qs, -- starting runnig total
sum(qty) over(partition by Item order by ID desc) qe  -- ending runnig total
from Buys
) b on s.Item = b.Item and s.Stock > b.qs
group by s.Item, s.Stock
) t
join Fallback f on f.Item = t.Item;
order by t.Item;

如果某项可能缺少回退，则需要进行一些小调整。

select t.item, (s + t.qf * coalesce(f.price, 0)) s, stock, (s + t.qf * coalesce(f.price, 0)) / stock  price
from (
select s.Item, s.Stock, 
sum(coalesce(case when b.qe <= Stock then b.qty else Stock - b.qs end * b.price, 0)) s,
--  qty for fallback
min(case when Stock > coalesce(b.qe,0) then Stock - coalesce(b.qe,0) else 0 end) qf
from Stock s
left join (
select Item, qty, price, ID,
sum(qty) over(partition by Item order by ID desc) - qty qs, -- starting runnig total
sum(qty) over(partition by Item order by ID desc) qe  -- ending runnig total
from Buys
) b on s.Item = b.Item and s.Stock > b.qs
group by s.Item, s.Stock
) t
left join Fallback f on f.Item = t.Item
where t.qf = 0 or f.item is not null
order by t.Item;

如果需要回退但缺少回退，则查询将不返回行。否则返回该行。

,db&lt的在小提琴

您需要创建Buys的总数量，并在此基础上计算价格。

这有点复杂，因为您可能在Buys中有太多或不够的行来满足库存。

SELECT
s.Item,
s.stock,
(
ISNULL(b.FoundStockPrice, 0)
+ CASE WHEN s.stock > ISNULL(b.FoundStock, 0)
THEN s.stock - ISNULL(b.FoundStock, 0)
ELSE 0 END * f.price
) / s.stock
FROM Stock s
JOIN Fallback f ON f.Item = s.Item
OUTER APPLY (
SELECT
FoundStock = SUM(b.qty),
FoundStockPrice = SUM(
CASE WHEN b.FullStock > b.RunningSum THEN b.qty
ELSE b.FullStock - (b.RunningSum - b.qty) END
* b.price)
FROM (
SELECT *,
RunningSum = SUM(b.qty) OVER (PARTITION BY b.Item
ORDER BY b.ID DESC ROWS UNBOUNDED PRECEDING),
FullStock = s.stock
FROM Buys b
WHERE b.Item = s.Item
) b
WHERE b.RunningSum - b.qty < s.stock
) b;

步骤如下:

• 对于每个Stock取所有相关的Buys行。
• 计算qty的运行和，然后过滤到运行和包含最终stock的行(换句话说，它必须达到之前的运行和)。
• 将这些Buys行乘以它们的price，考虑到我们需要在必要的stock上减去任何东西。也取总数量。
• 最终价格是:之前计算的总价格，加上任何剩余的未找到的stock乘以fallback.price，全部除以总stock。

db<在小提琴>