sql - Using MODEL clause in Oracle and returning previous values
问题描述
I have a query that should return V * previous C when MOD (rn, 2)! = 0, and should return V / previous C when MOD (rn, 2) = 0 (for each row sorted by rn).
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;
This is example of valid output:
ROWNUM | V | C |
---|---|---|
1 | 1 | 1 |
2 | 2 | 0.5 |
3 | 4 | 2 |
4 | 2 | 1 |
5 | 3 | 3 |
But my query return this (incorrect output):
ROWNUM | V | C |
---|---|---|
1 | 1 | 1 |
2 | 2 | 0.5 |
3 | 4 | 0 |
4 | 2 | 0 |
5 | 3 | 0 |
Please, help me fix my query.
解决方案
You can put the MOD
ulo filter in a CASE
expression on the right-hand side of the assignment rather than trying to have odd- and even-row rules:
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;
Which outputs:
RN V C 1 1 1 2 2 .5 3 4 2 4 2 1 5 3 3
db<>fiddle here
Update
Your query works if you use AUTOMATIC ORDER
(rather than the default 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<>fiddle here
推荐阅读
- c# - 如何在我的代码中访问 System.Configuration 参考?
- python - “marque_id”列中的空值违反非空约束 - Django 2.1.7
- javascript - Javascript音乐播放器表现怪异
- python - MemoryError cross_val_score Jupyter Notebook
- javascript - 如何将 OSRM 中的 geojson 数据保存到文本文件中
- javascript - JavaScript:函数返回最接近的值
- cmake - 使用 cmake 构建 klee
- c# - 使用 wpf 构建 Excel 工作表的集合
- java - 如何在没有任何安全性的情况下使一个休息端点可访问,同时在 Spring Boot 应用程序中实现 spring-security
- python - 将整数保存到文件并检索它