Home » SQL & PL/SQL » SQL & PL/SQL » Getting the Prior Dimension Value
Getting the Prior Dimension Value [message #381603] Sun, 18 January 2009 22:42 Go to next message
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 by
SELECT 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 by
SELECT 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 Go to previous message
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

Previous Topic: Problem in handling Sysdate
Next Topic: Proble with hierarchy query (merged 3)
Goto Forum:
  


Current Time: Thu Feb 13 03:54:48 CST 2025