我有 3 張桌子：
購買
| ID | 物品 | 數量 | 價格 |
|---|---|---|---|
| 1 | 1001 | 10 | 1.00 |
| 2 | 1001 | 10 | 2.00 |
| 3 | 1001 | 10 | 3.00 |
| 4 | 1002 | 10 | 2.00 |
| 5 | 1002 | 10 | 1.00 |
| 6 | 1003 | 10 | 1.00 |
倒退
| 物品 | 價格 |
|---|---|
| 1001 | 3.00 |
| 1002 | 3.00 |
| 1003 | 4.00 |
庫存
| 物品 | 股票 |
|---|---|
| 1001 | 15 |
| 1002 | 5 |
| 1003 | 25 |
我必須計算每件商品的實際價格。為此，我必須檢查表“購買”從最大 ID 到最小 ID 的每一行，并在庫存充足的情況下獲取所有價格。如果表中沒有足夠的購買，我必須使用部分庫存的后備價格，我沒有第一表中的價格。
所以對于專案號。1001，庫存為 15。在 ID 3 中找到 10 件的價格（3.00 美元）；第 2 行剩余 5 件的價格（2.00 美元）。所以正確的實際股價是 2.66 美元。
對于專案編號。1002，庫存為 5。在第 5 行，數量超過 5 的最新購買價格為 1.00 美元。所以實際正確的庫存價格為 1.00 美元。
對于專案編號。1003，庫存為 25。第 6 行中只有一個條目，10 件，每件 1.00 美元。因此缺少 15 件的價格必須從備用表 4.00 美元中獲取。所以正確的實際股價是 2.80 美元。
結果應該是這樣的：
| 物品 | 股票 | 價值 |
|---|---|---|
| 1001 | 15 | 2.66 |
| 1002 | 5 | 1.00 |
| 1003 | 25 | 2.80 |
但我不知道它是如何作業的。非常感謝您的幫助。
uj5u.com熱心網友回復：
在比較股票和買入總數時使用條件聚合，最后應用回退
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;
db<>小提琴
uj5u.com熱心網友回復：
您需要創建數量的運行總和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<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/482487.html
上一篇：PostgreSQL-具有最小/最大值的連續天-表包含多個相等的天
下一篇：如何根據日期計算訪問次數？