Getting the Prior Dimension Value [message #381603] |
Sun, 18 January 2009 22:42  |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
I was wondering if there was any way to get the prior dimension value from within a MODEL clause. I can obviously get the current dimension value bySELECT seq_num
,reading
,prev_seq_num
FROM (SELECT t.*
,0 prev_seq_num
,seq_num seq_num_m
FROM test t)
MODEL
RETURN UPDATED ROWS
DIMENSION BY (seq_num)
MEASURES (reading, prev_seq_num, seq_num_m)
RULES (prev_seq_num[ANY] = cv(seq_num)
)
ORDER BY seq_num;
SEQ_NUM READING PREV_SEQ_NUM
---------- ---------- ------------
1 10 1
2 20 2
4 30 4 I can get the previous dimension value bySELECT seq_num
,reading
,prev_seq_num
FROM (SELECT t.*
,0 prev_seq_num
,seq_num seq_num_m
FROM test t)
MODEL
RETURN UPDATED ROWS
DIMENSION BY (seq_num)
MEASURES (reading, prev_seq_num, seq_num_m)
RULES (prev_seq_num[ANY] = MAX(seq_num_m)[seq_num < cv()])
ORDER BY seq_num;
SEQ_NUM READING PREV_SEQ_NUM
---------- ---------- ------------
1 10
2 20 1
4 30 2 But something about that seems inefficient from a process standpoint and quite inelegant. It doesn't seem like I should have to add mirror column, seq_num_m, and it seems like it would be expensive as I started to get fairly high in the sequence numbers. Is there anything like a cv(seq_num).prior feature that I am just not seeing in the documentation?
And before somebody suggests it, I know I can do this with analytic functions. My current code is doing that. The problem is that there are five analytic functions in the statement, each doing their own partitioning, sorting, and calculating and it doesn't appear to be all the efficient. I wanted to test a query using the MODEL clause against what I already have and compare the performance.
CREATE TABLE test(
seq_num NUMBER
,reading NUMBER
);
INSERT INTO test VALUES(1, 10);
INSERT INTO test VALUES(2, 20);
INSERT INTO test VALUES(4, 30);
|
|
|
Re: Getting the Prior Dimension Value [message #381635 is a reply to message #381603] |
Mon, 19 January 2009 01:07  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I'm not sure this is what you want but anyway...
If you want to play with dimension number it should be consecutive. As you have not consecutive seq_num you must create a new number column:
SQL> SELECT seq_num
2 ,reading
3 ,prev_seq_num
4 FROM (SELECT t.*,
5 row_number() over (order by seq_num) rn
6 FROM test t)
7 MODEL
8 DIMENSION BY (rn)
9 MEASURES (seq_num, reading, 0 as prev_seq_num)
10 RULES (prev_seq_num[ANY] = seq_num[cv()-1])
11 /
SEQ_NUM READING PREV_SEQ_NUM
---------- ---------- ------------
1 10
2 20 1
4 30 2
(I think this is a study case otherwise LAG function should be far better than MODEL cause for this.)
Regards
Michel
[Updated on: Mon, 19 January 2009 01:07] Report message to a moderator
|
|
|