SQL -计算产品的累计平均成本价格。
我想知道是否有人能至少指出我在这个问题上的正确方向。
我需要计算库存的平均成本，但要注意的是，它只应计算在最新的产品采购，与我们的库存有关。
为了更好地解释x产品，我们从不同的供应商那里以不同的价格购买了1200台。我们只剩下150个单元的库存，因此，要获得平均购买价格和价值的其余项目，我们需要得到的产品的平均成本，从最新购买的150个单位只购买。
我的看法如下：
ProductId (int) - PK
ProductName (varchar(150))
StockInHand (int)相关表如下(为便于使用而截断)
PurchaseOrder
PurchaseOrderId (int) - PK
PurchaseOrderDate (DateTime)
PurchaseOrderStatus (int) - FK采购订单状态-1=打开，2=签入
PurchaseOrderLine
PurchaseOrderLineId (int) - PK
Qty (int)
UnitPrice (decimal(18,2))
ProductId (int) - FK
PurchaseOrderId (int) - FK更新
我在这里创建了一个sql小提琴：
http://sqlfiddle.com/#!18/69288/1
你会发现我有三份定购单：
1: Qty 20 @20 40 20
2: Qty 20 @20 30 20
3: Qty 10 @10 25 10
我有两份相同货物的订单：
1: Qty 10
2:第15次
从这里，我可以得到手头的股票：25
我需要我剩下的股票的平均购买价格。
我还剩25台，所以我需要从最新的定购单中倒转，才能得到价值。
我将从定购单3取10张，从定购单2取15张：
10 *25 25=25 250
15 *£30 =£450
28 700/ 25 =28 28
我希望这能让我的问题更清楚！
谢谢
更新2
非常感谢你，Sticky Bit抽出时间发表了我的问题的解决方案，并对此进行了深思熟虑的解释。
我已经在我的dev DB上尝试过了，并且似乎有一个问题。
我有4份相同产品的定购单(同一天有两份)。
我还没有想出如何在这个文本编辑器中优雅地格式化表格，所以请耐心地听我说：
PurchaseOrderId / PurchaseOrderDate
2/ 2018-07-28
3/ 2018-07-29
4/ 2018-07-30
5/ 2018-07-30
我有下面的PurchaseOrderLine
PurchaseOrderLineId / PurchaseOrderId / ProductId / Qty / UnitPrice
3/2/8/ 20 / 400.00
4/3/8/ 40 / 420.00
5/4/8/ 25 / 500.00
6/5/8/1/ 200.00
运行以下命令：
SELECT pol.productid,
po.purchaseorderdate,
sum(pol.qty) qty,
avg(pol.unitprice) unitprice
FROM purchaseorder po
INNER JOIN purchaseorderline pol
ON pol.purchaseorderid = po.purchaseorderid
GROUP BY pol.productid,
po.purchaseorderdate;给我这些结果：
8 2018-07-28 00:00:00.000 20 400.000000
2018-07-29 00:00:00.000 40 420.000000
8 2018-07-30 00:00:00.000 26 350.000000
您会注意到，7月30日购买的产品的平均成本已经降低(这是考虑到两种价格之间的平均价格，而没有考虑到质量-我不确定这是否出于设计？)
如果然后运行以下命令：
SELECT po.productid,
po.purchaseorderdate,
sum(po.qty) OVER (PARTITION BY po.productid
ORDER BY po.purchaseorderdate) qty,
sum(po.unitprice) OVER (PARTITION BY po.productid
ORDER BY po.purchaseorderdate) unitprice
FROM (SELECT pol.productid,
po.purchaseorderdate,
sum(pol.qty) qty,
avg(pol.unitprice) unitprice
FROM purchaseorder po
INNER JOIN purchaseorderline pol
ON pol.purchaseorderid = po.purchaseorderid
GROUP BY pol.productid,
po.purchaseorderdate) po;我得到以下结果：
8 2018-07-28 00:00:00.000 20 400.000000
8 2018-07-29 00:00:00.000 60 820.000000
8 2018-07-30 00:00:00.000 86 1170.000000
再一次，这里似乎出现了一些与单位价格有关的问题。
任何帮助都非常感谢！
亲切的问候
回答 2
Stack Overflow用户
发布于 2018-07-30 18:46:13
- 首先，我们需要找到一个查询，为每个产品提供当前的库存水平。为此，我们将join
purchaseorderline和orderline留给product，并计算每一行的差异。由于一个产品可以是多个订单，我们额外地汇总结果，以得到整体差异--目前的库存水平--对每个产品。 从产品p中选择p.productid，p.productname，sum(pol.qty，0) - coalesce(ol.qty，0) qty； - 接下来，我们需要数量，这是为每个产品和天(的
purchaseorders)库存。为了实现这一点，我们内部加入了purchaseorder和purchaseorderline。同样，为了说明可能的情况，我们再次总结，同一天为同一产品下了多个订单。 选择pol.productid，po.purchaseorderdate，sum(pol.qty) qty，sum(pol.qty * pol.unitprice)单价，从购买订单po内部加入pol.purchaseorderid ON pol.purchaseorderid= po.purchaseorderid组的pol.purchaseorderid，po.purchaseorderdate； 现在，我们可以使用前面的结果和窗口函数，以获得的数量和平均价格的产品，直至每天。 选择po.productid，po.purchaseorderdate，sum(po.qty) BY ( po.productid ORDER BY po.purchaseorderdate) qty，sum(po.unitprice) OVER ( po.productid ORDER BY po.purchaseorderdate) / sum(po.qty) OVER ( po.productid ORDER BY po.purchaseorderdate)单价从(选择pol.productid，po.purchaseorderdate，sum(pol.qty) qty，sum(pol.qty * pol.unitprice)单价，从购买订单po内部加入pol.purchaseorderid = po.purchaseorderid集团的购买者订购单价格pol.productid，po.purchaseorderdate) po；
现在，我们使用OUTER APPLY将1和2的结果放在一起。对于每一种产品，我们选择的TOP 1结果是2.按日降订单--即先下一批--库存的数量大于或等于目前库存的数量。
SELECT p.productid,
p.productname,
po.unitprice
FROM (SELECT p.productid,
p.productname,
sum(coalesce(pol.qty, 0) - coalesce(ol.qty, 0)) qty
FROM product p
LEFT JOIN purchaseorderline pol
ON pol.productid = p.productid
LEFT JOIN orderline ol
ON ol.productid = p.productid
GROUP BY p.productid,
p.productname) p
OUTER APPLY (SELECT TOP 1
po.unitprice
FROM (SELECT po.productid,
po.purchaseorderdate,
sum(po.qty) OVER (PARTITION BY po.productid
ORDER BY po.purchaseorderdate) qty,
sum(po.unitprice) OVER (PARTITION BY po.productid
ORDER BY po.purchaseorderdate)
/
sum(po.qty) OVER (PARTITION BY po.productid
ORDER BY po.purchaseorderdate) unitprice
FROM (SELECT pol.productid,
po.purchaseorderdate,
sum(pol.qty) qty,
sum(pol.qty * pol.unitprice) unitprice
FROM purchaseorder po
INNER JOIN purchaseorderline pol
ON pol.purchaseorderid = po.purchaseorderid
GROUP BY pol.productid,
po.purchaseorderdate) po) po
WHERE po.productid = p.productid
AND po.qty >= p.qty
ORDER BY po.purchaseorderdate DESC) po;Stack Overflow用户
发布于 2018-07-30 12:16:28
我没有看到'UnitCost‘，所以我将在代码示例中使用'UnitPrice’作为成本。
我不知道这是不是完美，但它可能会帮助你朝着正确的方向。
SELECT (SUM(UnitPrice)/150) FROM OrderDetails
WHERE PurchaseOrderId IS BETWEEN 1050 AND 1200
GOhttps://stackoverflow.com/questions/51593603
复制相似问题
腾讯云开发者
