我有一個查詢應該在 MOD (rn, 2) 時回傳 V * previous C！= 0，并且當 MOD (rn, 2) = 0（對于按 rn 排序的每一行）時，應回傳 V / 前一個 C。
select *
FROM (
Select 1 rn, 1 V from dual UNION ALL
Select 2 rn, 2 V from dual UNION ALL
Select 3 rn, 4 V from dual UNION ALL
Select 4 rn, 2 V from dual UNION ALL
Select 5 rn, 3 V from dual
)
MODEL
DIMENSION BY (rn)
MEASURES ( V, 0 c)
RULES (
c[MOD(rn, 2) != 0] = nvl(c[cv()-1],1)*V[cv()]
,c[MOD(rn, 2) = 0] = c[cv()-1] /V[cv()]
-- c[any] = nvl(c[cv()-1],1) *B_VOL[cv()]
) order by 1;
這是有效輸出的示例：
| 行號 | 伏 | C |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 0.5 |
| 3 | 4 | 2 |
| 4 | 2 | 1 |
| 5 | 3 | 3 |
但我的查詢回傳這個（錯誤的輸出）：
| 行號 | 伏 | C |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 0.5 |
| 3 | 4 | 0 |
| 4 | 2 | 0 |
| 5 | 3 | 0 |
請幫我解決我的查詢。
uj5u.com熱心網友回復：
您可以將MODulo 過濾器放在CASE賦值右側的運算式中，而不是嘗試使用奇數和偶數行規則：
select *
FROM (
SELECT 1 rn, 1 V from dual UNION ALL
Select 2 rn, 2 V from dual UNION ALL
Select 3 rn, 4 V from dual UNION ALL
Select 4 rn, 2 V from dual UNION ALL
Select 5 rn, 3 V from dual
)
MODEL
DIMENSION BY (rn)
MEASURES ( V, 0 AS c)
RULES (
c[1] = V[1],
c[rn>1] = CASE MOD(cv(rn), 2)
WHEN 0
THEN c[cv()-1] / V[cv()]
ELSE c[cv()-1] * V[cv()]
END
)
order by rn;
哪些輸出：
注冊護士 伏 C 1 1 1 2 2 .5 3 4 2 4 2 1 5 3 3
db<>在這里擺弄
更新
如果您使用AUTOMATIC ORDER（而不是默認值SEQUENTIAL ORDER），則您的查詢有效：
select *
FROM (
Select 1 rn, 1 V from dual UNION ALL
Select 2 rn, 2 V from dual UNION ALL
Select 3 rn, 4 V from dual UNION ALL
Select 4 rn, 2 V from dual UNION ALL
Select 5 rn, 3 V from dual
)
MODEL
DIMENSION BY (rn)
MEASURES ( V, 0 c)
RULES AUTOMATIC ORDER (
c[MOD(rn, 2) != 0] = nvl(c[cv(rn)-1],1)*V[cv(rn)]
,c[MOD(rn, 2) = 0] = c[cv(rn)-1] /V[cv(rn)]
)
order by 1;
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/346915.html