我想獲得 STOCK 和加權平均單位成本WAUC。
表 T1：
| 排 | 物品 | 在 | 出去 | 價格 | 股票 | WAUC |
|---|---|---|---|---|---|---|
| 1 | 一個 | 1000 | —— | 20 | 1000 | 20 |
| 2 | 一個 | 2000年 | —— | 25 | —— | —— |
| 3 | 一個 | 1500 | —— | 15 | —— | —— |
| 4 | 一個 | 500 | —— | 20 | —— | —— |
我有前 5 列和最后兩列的前兩條記錄，并希望獲得最后兩列的其余部分STOCK和WAUC.
WAUC = ((PREVIOUS_PRICE * PREVIOUS_STOCK) (CURRENT_IN * CURRENT_PRICE)) / CURRENT_STOCK
所以，我寫了查詢：
SELECT ROW,
SUM(IN - OUT) OVER(ORDER BY ROW) STOCK,
((LAG(STOCK * WAUC) OVER (ORDER BY ROW)) (IN * PRICE)) / STOCK AS WAUC
FROM T1
我想要的是：
| 排 | 物品 | 在 | 出去 | 價格 | 股票 | WAUC |
|---|---|---|---|---|---|---|
| 1 | 一個 | 1000 | —— | 20 | 1000 | 20 |
| 2 | 一個 | 2000年 | —— | 25 | 3000 | 23.33 |
| 3 | 一個 | 1500 | —— | 15 | 4500 | 20.55 |
| 4 | 一個 | 500 | —— | 20 | 5000 | 20.49 |
換句話說，我想LAG在計算資料中使用結果。
uj5u.com熱心網友回復：
你的公式應該是：
WAUC = (
PREVIOUS_WAUC * PREVIOUS_STOCK
(CURRENT_IN - CURRENT_OUT) * CURRENT_PRICE
)
/ CURRENT_STOCK
您可以使用MODEL子句（通過一些額外的測量使計算更簡單）：
SELECT "ROW", item, "IN", "OUT", price, stock, wauc
FROM t1
MODEL
DIMENSION BY ("ROW")
MEASURES (item, "IN", "OUT", price, 0 AS change, 0 AS stock, 0 AS total, 0 AS wauc)
RULES (
change["ROW"] = COALESCE("IN"[cv()], 0) - COALESCE("OUT"[cv()], 0),
stock["ROW"] = change[cv()] COALESCE(stock[cv()-1], 0),
total["ROW"] = change[cv()] * price[cv()] COALESCE(total[cv()-1], 0),
wauc["ROW"] = total[cv()] / stock[cv()]
);
或者，從 Oracle 12 開始，使用MATCH_RECOGNIZE：
SELECT "ROW",
item,
"IN",
"OUT",
price,
total_stock AS stock,
total_cost / total_stock AS wauc
FROM t1
MATCH_RECOGNIZE(
ORDER BY "ROW"
MEASURES
SUM(COALESCE("IN", 0) - COALESCE("OUT", 0)) AS total_stock,
SUM((COALESCE("IN", 0) - COALESCE("OUT", 0))*price) AS total_cost
ALL ROWS PER MATCH
PATTERN (all_rows )
DEFINE
all_rows AS 1 = 1
)
或決議函式：
SELECT "ROW",
item,
"IN",
"OUT",
price,
SUM(COALESCE("IN",0) - COALESCE("OUT", 0)) OVER (ORDER BY "ROW")
AS stock,
SUM((COALESCE("IN",0) - COALESCE("OUT", 0))*price) OVER (ORDER BY "ROW")
/ SUM(COALESCE("IN",0) - COALESCE("OUT", 0)) OVER (ORDER BY "ROW")
AS wauc
FROM t1
其中，對于樣本資料：
CREATE TABLE t1 ("ROW", ITEM, "IN", "OUT", PRICE, STOCK, WAUC) AS
SELECT 1, 'A', 1000, CAST(NULL AS NUMBER), 20, CAST(NULL AS NUMBER), CAST(NULL AS NUMBER) FROM DUAL UNION ALL
SELECT 2, 'A', 2000, NULL, 25, NULL, NULL FROM DUAL UNION ALL
SELECT 3, 'A', 1500, NULL, 15, NULL, NULL FROM DUAL UNION ALL
SELECT 4, 'A', 500, NULL, 20, NULL, NULL FROM DUAL;
所有輸出：
排 物品 在 出去 價格 股票 WAUC 1 一個 1000 20 1000 20 2 一個 2000年 25 3000 23.333333333333333333333333333333333333333 3 一個 1500 15 4500 20.5555555555555555555555555555555555556 4 一個 500 20 5000 20.5
注意：ROW,IN和OUT是關鍵字，您不應該將它們用作識別符號，因為您必須在它們出現的任何地方使用帶引號的識別符號。
db<>在這里擺弄
uj5u.com熱心網友回復：
您的問題與“滯后”無關。
使用MT0的樣本資料：
select "ROW", item, "IN", "OUT", price,
sum(nvl("IN", 0) - nvl("OUT", 0))
over (partition by item order by "ROW") as stock,
round(sum((nvl("IN", 0) - nvl("OUT", 0)) * price)
over (partition by item order by "ROW")
/ sum(nvl("IN", 0) - nvl("OUT", 0))
over (partition by item order by "ROW"), 2 ) as wauc
from t1
;
ROW ITEM IN OUT PRICE STOCK WAUC
------ ---- ------ ------ ------ ------ ------
1 A 1000 20 1000 20
2 A 2000 25 3000 23.33
3 A 1500 15 4500 20.56
4 A 500 20 5000 20.5
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/381363.html
上一篇：決議括號括起來的某些字串值
下一篇：在同一查詢中搜索多列