视图需要相互依赖的逻辑:如果没有MODEL，可能吗? [英] View Requires Interdependence Logic: Possible without MODEL?
问题描述
我正在尝试编写一些Oracle 11g SQL，但是我遇到了一些麻烦的问题.我正在寻找类似电子表格的行为.我找到了一个确实使用Oracle的MODEL子句的解决方案，但是性能不是很好.所以我想知道"non- MODEL"解决方案在技术上是否可行.
I'm trying to write some Oracle 11g SQL, but I'm running into a bit of a chicken-and-egg problem. I'm looking for spreadsheet-like behavior. I've found a solution that does use Oracle's MODEL clause, but the performance isn't great. So I'm wondering if a "non-MODEL" solution is even technically feasible.
这是一个玩具示例，演示了我正在尝试做的事情.鉴于此表:
Here's a toy example that demonstrates what I'm trying to do. Given this table:
CREATE TABLE t (id NUMBER PRIMARY KEY, n NUMBER);
INSERT INTO t (id, n) VALUES (2, 0);
INSERT INTO t (id, n) VALUES (3, 1);
INSERT INTO t (id, n) VALUES (5, 1);
INSERT INTO t (id, n) VALUES (7, 2);
INSERT INTO t (id, n) VALUES (11, 3);
INSERT INTO t (id, n) VALUES (13, 5);
INSERT INTO t (id, n) VALUES (17, 8);
INSERT INTO t (id, n) VALUES (19, 13);
我想计算另外两个派生的列，分别称为X和Y.
I want to compute two additional, derived columns, call them X and Y.
以下是计算X和Y的规则:
X: 对于第一行(由ID的最小值定义)，将
X设置为N. 对于所有后续行，X的值应比前一个Y的值小1(按ID排序).
X: For the very first row, as defined by the minimum value of ID, set
XtoN. For all subsequent rows, the value ofXshould be one less than the value of the previousY, as sorted byID.
Y:
两次N加X.
接下来的几个步骤显示了如果我要手动执行此操作，我将如何填写所需的视图.首先，给定数据的前几行:
These next few steps show how I'd fill out my desired view if I were to do this by hand. First, the first few rows of the given data:
ID N X Y
--- --- --- ---
2 0
3 1
5 1
7 2
....
由于我们位于第一行，因此X应该设置为N或0. Y应该是2 * N + X或0.
Since we're in the first row, X should be set to N, or 0. Y should be 2 * N + X, or 0.
ID N X Y
--- --- --- ---
2 0 0 0
3 1
5 1
7 2
....
现在，由于我们不再位于第一行，因此从现在开始，X应该始终比上一行的Y小一个.在第二行中，这表示X =(上一个Y)-1 = 0 - 1 = -1.第二行的Y将是2 * N + X或2 * (1) + (-1) = 1.
Now, since we're not in the first row any longer, X should always be one less than the previous row's Y from here on out. Here in the second row, that means X = (previous Y) - 1 = 0 - 1 = -1. And the second row's Y will be 2 * N + X, or 2 * (1) + (-1) = 1.
ID N X Y
--- --- --- ---
2 0 0 0
3 1 -1 1
5 1
7 2
....
如果您继续进行数学运算，这是期望的结果:
If you continue with the math, here's the desired outcome:
ID N X Y
--- --- --- ---
2 0 0 0
3 1 -1 1
5 1 0 2
7 2 1 5
11 3 4 10
13 5 9 19
17 8 18 34
19 13 33 59
给定X和Y的计算规则，是否有可能在不必诉诸MODEL子句的情况下获得此结果?
Given the rules for how X and Y are computed, is it possible to get this outcome without having to resort to the MODEL clause?
我不是在基于这个特定示例寻找数学上的简化；这只是我想出的一个玩具例子，展示了我在实际问题中面临的那种相互依存关系.
I'm not looking for a mathematical simplification based on this particular example; this is just a toy example I came up with that demonstrates the kind of interdependence I'm facing in my actual problem.
P.S .:这是一个MODEL示例，我能够将其拼凑在一起并产生此输出；也许有可能进行改进以提高性能?
P.S.: Here's a MODEL example I was able to cobble together that does generate this output; maybe there are modifications possible to improve performance?
SQL> WITH u AS (
2 SELECT ROW_NUMBER() OVER (ORDER BY t.id) r
3 , t.id
4 , t.n
5 FROM t
6 )
7 SELECT r
8 , id
9 , n
10 , x
11 , y
12 FROM u
13 MODEL
14 DIMENSION BY (r)
15 MEASURES (id
16 , n
17 , CAST(NULL AS NUMBER) x
18 , CAST(NULL AS NUMBER) y) RULES AUTOMATIC ORDER
19 ( x[1] = n[cv()]
20 , y[r] = 2 * n[cv()] + x[cv()]
21 , x[r > 1] ORDER BY r = y[cv() - 1] - 1
22 )
23 ;
R ID N X Y
---------- ---------- ---------- ---------- ----------
1 2 0 0 0
2 3 1 -1 1
3 5 1 0 2
4 7 2 1 5
5 11 3 4 10
6 13 5 9 19
7 17 8 18 34
8 19 13 33 59
8 rows selected.
SQL>
谢谢.
推荐答案
You could use recursive subquery factoring (also known as a recursive CTE):
with tmp as (
select t.*,
row_number() over (order by t.id) as rn
from t
),
r (id, n, x, y, rn) as (
select id, n, 0, 0, rn
from tmp
where rn = 1
union all
select tmp.id, tmp.n, r.y - 1, (tmp.n * 2) + r.y - 1, tmp.rn
from r
join tmp on tmp.rn = r.rn + 1
)
select id, n, x, y
from r
order by rn;
ID N X Y
---------- ---------- ---------- ----------
2 0 0 0
3 1 -1 1
5 1 0 2
7 2 1 5
11 3 4 10
13 5 9 19
17 8 18 34
19 13 33 59
基本上，这是您手动操作的所有步骤.锚点成员是您的第一步，将第一行的x和y都设置为零.然后，递归成员将执行您指定的计算. (在计算该行的y时不能引用新计算的x值，因此必须将其重复为(tmp.n * 2) + r.y - 1). rn只是保持ID的行顺序，同时使查找下一行更加容易-因此，您可以查找rn + 1而不是直接查找下一个最高ID值.
It's basically walking through your manual steps. The anchor member is your first manual step, setting x and y both to zero for the first row. The recursive member then does the calculation you specified. (You can't refer to the new-calculated x value when calculating that row's y, so you have to repeat that as (tmp.n * 2) + r.y - 1). The rn is just to keep the rows orders by ID while making it easier to find the next row - so you can look for rn + 1 instead of find the next highest ID value directly.
示例数据的性能没有显着差异，但是添加一千行后，model子句大约需要5秒，而递归CTE大约需要1秒；使用另外一千行模型需要约20秒，而CTE需要约3秒；另外一千行模型花费了约40秒，CTE花费了约6秒；而另外一千行(总共4,008行)的模型则花费了约75秒，而CTE花费了约10秒. (我很无聊地等待更多版本的模型版本；五分钟之内用10,000杀死了它).我真的不能说这将如何处理您的真实数据，但是在此基础上，可能值得尝试.
There's no significant performance difference with your sample data, but with a thousand rows added the model clause takes about 5 seconds and the recursive CTE takes about 1 second; with another thousand rows model takes ~20 seconds and the CTE takes ~3 seconds; with another thousand rows model took ~40 seconds and CTE took ~6 seconds; and with another thousand rows (so 4,008 in total) model took ~75 seconds and CTE took ~10 seconds. (I got bored waiting for the model version with more rows than that; killed it after a five minutes with 10,000). I can't really say how this will perform with your real data, but on that basis, it's probably worth trying.
这篇关于视图需要相互依赖的逻辑:如果没有MODEL，可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
