Home » SQL & PL/SQL » SQL & PL/SQL » Calculating a row value from the previous row value in a query statement (Oracle 9.2)
Calculating a row value from the previous row value in a query statement [message #350586] Thu, 25 September 2008 08:48 Go to next message
milaan66
Messages: 12
Registered: March 2007
Junior Member
I HAVE A NEED TO CALCULATE A NEXT ROW VALUE FROM THE PREVIOUS ROW VALUE IN A QUERY STATEMENT. IF THERE ARE 10 ROWS RETURNED FROM A QUERY STATEMENT, THE 2ND ROW VALUE IS CALCULATED BASED ON THE VALUE OF 1ST ROW, 3RD ROW VALUE IS CALCULATED BASED ON THE VALUE OF 2ND ROW AND SO ON….THE LAST ROW VALUE IS CALCULATED ON THE VALUE OF SECOND LAST ROW. HOW TO WRITE A QUERY FOR THIS?

For example:
ROW VALUE
R1 N1
R2 N2(N*N1)
R3 N3(N*N2)
.
.
.
R10 N10(N*N9)
Re: Calculating a row value from the previous row value in a query statement [message #350587 is a reply to message #350586] Thu, 25 September 2008 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't post in UPPER case.
Read OraFAQ Forum Guide
Post what you tried (read how to format your post in guide).
Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #350591 is a reply to message #350586] Thu, 25 September 2008 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just for my fun and with my apologies to those that I lectured for posting complete solution:
SQL> create table t (id integer, val integer);

Table created.

SQL> insert into t 
  2  select level, round(dbms_random.value(2,10)) 
  3  from dual connect by level <= 10
  4  /

10 rows created.

SQL> select * from t order by 1;
        ID        VAL
---------- ----------
         1          8
         2          9
         3          9
         4          2
         5          9
         6         10
         7         10
         8          8
         9          3
        10          5

10 rows selected.

SQL> select id, val, 
  2         (select round(exp(sum(ln(val))))
  3          from t b
  4          connect by id = prior id-1
  5          start with id = a.id) mul
  6  from t a
  7  order by id
  8  /
        ID        VAL        MUL
---------- ---------- ----------
         1          8          8
         2          9         72
         3          9        648
         4          2       1296
         5          9      11664
         6         10     116640
         7         10    1166400
         8          8    9331200
         9          3   27993600
        10          5  139968000

10 rows selected.

Now it's open to post the many other solutions that exist. Wink

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #350610 is a reply to message #350586] Thu, 25 September 2008 10:38 Go to previous messageGo to next message
milaan66
Messages: 12
Registered: March 2007
Junior Member
My appologies for not following the forum guidelines.

Here is what I want to do?

Table:

SQL> Create t1 (a varchar2(10), x number);

Table created.

SQL> insert into t1 values ('abc', 5);

1 row created.

SQL> insert into t1 values ('abcd', 6);

1 row created.

SQL> insert into t1 values ('xyz', 8);

1 row created.

SQL> insert into t1 values ('yze', 1);

1 row created.

SQL> select t1.a, t1.x, 0.3*t1.x+(1-0.3)*t.avg ewma
  2  from (select avg(x) avg from t1) t, t1;


        A  X EWMA
--------- -- ----
      abc  5    5
     abcd  6  5.3
      xyz  8  5.9
      yze  1  3.8

4 rows selected.

What I want is to apply different formula for EWMA from row # 2 onwards. The formula would be 0.3*t1.x+(1-0.3)*ewma (of previous row. Multiply by EWMA of previous row instead by AVG.

Expected result set:

        A  X  EWMA
--------- -- -----
      abc  5     5
     abcd  6   5.3
      xyz  8  6.11
      yze  1 4.577

Hope I have explained well now.

Thanks,
Milaan

[Edit MC: add code tags, do it yourself next time, check with Preview button]

[Updated on: Thu, 25 September 2008 10:48] by Moderator

Report message to a moderator

Re: Calculating a row value from the previous row value in a query statement [message #350621 is a reply to message #350610] Thu, 25 September 2008 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Afaik, you can't do it in pure SQL in 9.2.

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #350628 is a reply to message #350610] Thu, 25 September 2008 12:46 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Why hasn't anyone asked:

What is row number 2? There is no such thing as row numbers in a relational database unless you specify an order with an ORDER BY clause?
Re: Calculating a row value from the previous row value in a query statement [message #350669 is a reply to message #350628] Thu, 25 September 2008 19:07 Go to previous messageGo to next message
milaan66
Messages: 12
Registered: March 2007
Junior Member
Yes, it would have a order by clause in the statement.

Milaan
Re: Calculating a row value from the previous row value in a query statement [message #350776 is a reply to message #350586] Fri, 26 September 2008 05:36 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Take a look at Lag and Lead Analytic Functions.

Rajy
Re: Calculating a row value from the previous row value in a query statement [message #350781 is a reply to message #350776] Fri, 26 September 2008 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will not help as there is a recursive formula.
Current value does not depend on a stored previous one but on a calculated one based on all the previous values.

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #350782 is a reply to message #350781] Fri, 26 September 2008 06:01 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
if the problem grows more complicated, you could have a look at the MODEL CLAUSE.
Re: Calculating a row value from the previous row value in a query statement [message #350783 is a reply to message #350586] Fri, 26 September 2008 06:02 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
He may see them, if they dont help, then he learnt something new . . .
That's what i have finally got:
A          X        EWMA
abc        5           5
abcd       6         5.3
xyz        8        6.11
yze        1        4.43

Rajy
Re: Calculating a row value from the previous row value in a query statement [message #350787 is a reply to message #350782] Fri, 26 September 2008 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
_jum wrote on Fri, 26 September 2008 13:01
if the problem grows more complicated, you could have a look at the MODEL CLAUSE.

Which does not exist in 9.2 as OP stated it is his version.

Of course it is easy to do it with model clause, it is built for this.

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #350788 is a reply to message #350783] Fri, 26 September 2008 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the last line is wrong.
Post what you did.

Regards
Michel

[Updated on: Fri, 26 September 2008 06:23]

Report message to a moderator

Re: Calculating a row value from the previous row value in a query statement [message #350799 is a reply to message #350586] Fri, 26 September 2008 06:52 Go to previous messageGo to next message
milaan66
Messages: 12
Registered: March 2007
Junior Member
Thanks guys. I know it can be done by MODEL clause in 10g (though I haven't tried yet). But I am stuck with 9.2. Still looking...

Milaan
Re: Calculating a row value from the previous row value in a query statement [message #350807 is a reply to message #350610] Fri, 26 September 2008 08:08 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Could a custom aggregate function do what are you looking for?

It should work on 9i too.

Processing ...
select id,val,0.3*avg*product(val) over (order by id) as ewma
from (
		select t.*,avg(val) over() as avg
		from t
	)
Query finished, retrieving results...
   ID     VAL     EWMA                  
--------- ------- --------------------- 
        1       4                   6,6 
        2       7                  46,2 
        3       8                 369,6 
        4       4                1478,4 
        5       8               11827,2 
        6       8               94617,6 
        7       8              756940,8 
        8       3             2270822,4 
        9       2             4541644,8 
       10       3            13624934,4 

10 row(s) retrieved



And this is what you would need

CREATE OR REPLACE 
TYPE T_PRODUCT_AGG AS OBJECT (
	g_number  number,
	STATIC FUNCTION ODCIAggregateInitialize (
		sctx  IN OUT  T_PRODUCT_AGG
	)
    RETURN NUMBER,

	MEMBER FUNCTION ODCIAggregateIterate (
		self   IN OUT  T_PRODUCT_AGG,
		value  IN      number
	)
	RETURN NUMBER,

	MEMBER FUNCTION ODCIAggregateTerminate (
		self         IN   T_PRODUCT_AGG,
		returnValue  OUT  number,
		flags        IN   NUMBER
	)
	RETURN NUMBER,

	MEMBER FUNCTION ODCIAggregateMerge (
		self  IN OUT  T_PRODUCT_AGG,
		ctx2  IN      T_PRODUCT_AGG
	)
	RETURN NUMBER
);
/

CREATE OR REPLACE 
TYPE BODY T_PRODUCT_AGG IS
	STATIC FUNCTION ODCIAggregateInitialize (
		sctx  IN OUT  T_PRODUCT_AGG
	)
	RETURN NUMBER IS
		temp clob;
	BEGIN
		sctx := T_PRODUCT_AGG(1);
		RETURN ODCIConst.Success;
	END;

	MEMBER FUNCTION ODCIAggregateIterate (
		self   IN OUT  T_PRODUCT_AGG,
		value  IN      number
	)
	RETURN NUMBER IS
	BEGIN
		g_number := SELF.g_number * nvl(value,1);
		RETURN ODCIConst.Success;
	END;

	MEMBER FUNCTION ODCIAggregateTerminate (
		self         IN   T_PRODUCT_AGG,
		returnValue  OUT  number,
		flags        IN   NUMBER
	)
    RETURN NUMBER IS
	BEGIN
		returnValue := SELF.g_number;
		RETURN ODCIConst.Success;
	END;

	MEMBER FUNCTION ODCIAggregateMerge (
		self  IN OUT  T_PRODUCT_AGG,
		ctx2  IN      T_PRODUCT_AGG
	)
	RETURN NUMBER IS
	BEGIN
		g_number := self.g_number * ctx2.g_number;
		RETURN ODCIConst.Success;
	END;
END;
/

CREATE OR REPLACE FUNCTION product(
	x number
) RETURN number
DETERMINISTIC AGGREGATE USING T_PRODUCT_AGG;
/


For explanations:
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm#1004572

Bye Alessandro

[Updated on: Fri, 26 September 2008 08:13]

Report message to a moderator

Re: Calculating a row value from the previous row value in a query statement [message #350839 is a reply to message #350807] Fri, 26 September 2008 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Custom aggregate function will indeed do it but this is PL/SQL. Wink

I don't see the relation between OP problem (the final result) and your query.

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #350858 is a reply to message #350839] Fri, 26 September 2008 14:18 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Michel Cadot wrote on Fri, 26 September 2008 19:16

I don't see the relation between OP problem (the final result) and your query.



Quote:
I want is to apply different formula for EWMA from row # 2 onwards. The formula would be 0.3*t1.x+(1-0.3)*ewma (of previous row. Multiply by EWMA of previous row instead by AVG.


I forgot to add 1-0.3 but, if it wasn't there (as I was thinking before coming back here to read it better) that formula would be
first_ewma = 0.3 * t1.x
ewma = 0.3*t1.x*prev_ewma

ewma^1 = 0.3 * t1.x^1
ewma^2 = 0.3 * t1.x^2 * ewma^1
...
ewma^n = 0.3 * t1.x^n * ewma^(n-1) = 0.3 * t1.x^n * [0.3*t1.x^(n-1)]*....*[0.3 * t1.x^1] = product(0.3*t1.x) 


And I missed to put 0.3 inside the function.

But now, considering that missed (1-0.3). This can't be done as easily as before and I don't have enough time to analyze it.

If the OP would clarify his requirements giving us the exact recursive formula in mathematical terms. Something like first values and formulas to calculate successive ones(base steps and recursive steps). With that it would be possible to analyze it and see if there is an equivalent numerical succession.

Bye Alessandro

[Updated on: Fri, 26 September 2008 14:23]

Report message to a moderator

Re: Calculating a row value from the previous row value in a query statement [message #350862 is a reply to message #350858] Fri, 26 September 2008 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But now, considering that missed (1-0.3). This can't be done as easily as before and I don't have enough time to analyze it.

Of course otherwise it was just a variant of my first exemple (with 0.3 instead of implicit 1).

Quote:
If the OP would clarify his requirements giving us the exact recursive formula in mathematical terms. Something like first values and formulas to calculate successive ones(base steps and recursive steps).

He did I think:
ewma[n] = 0.3*x[n] + (1-0.3)*ewma[n-1]
with ewma[0] = 0

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #350867 is a reply to message #350858] Fri, 26 September 2008 14:48 Go to previous messageGo to next message
milaan66
Messages: 12
Registered: March 2007
Junior Member
Thank you all for your effort. It has given a good insight but am still struggling... Here is the formula I want to apply:

For row#1 : lambda*column1+(1-lambda)*(average of column1) = EWMA of row#1
For row#2 : lambda*column1+(1-lambda)*(row#1 ewma)
For row#3 : lambda*column1+(1-lambda)*(row#2 ewma)
.
.
For row#N : lambda*column1+(1-lambda)*(row#n-1 ewma)


Where -
lambda is some constant value.
EWMA is the result of this calculation for that row.

Hope this is clear.

thank you Milaan

[Updated on: Fri, 26 September 2008 14:48]

Report message to a moderator

Re: Calculating a row value from the previous row value in a query statement [message #350890 is a reply to message #350867] Fri, 26 September 2008 19:44 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
I probably found a way.

This one took more than an hour of concentration, I had to remember a techique I didn't use since the time of the math course at the university.

Let's begin then.
If we consider the average as ewma#0 and as value#0 too, we can say:
ewma#n=lambda*value#n+(1-lambda)*ewma#(n-1)

so
ewma#n-(lambda*value#n)=(1-lambda)*ewma#(n-1)=
  =(1-lambda)*(ewma#(n-1)-(lambda*value#(n-1))+(lambda*value#(n-1))) =
  =(1-lambda)*((1-lambda)*ewma#(n-2)+(lambda*value#(n-1))) =
  ....
  =(1-lambda)*(...(1-lambda)*ewma#0+(lambda*value#1)...) =
  =(1-lambda)^n*ewma#0+(1-lambda)^(n-1)*value#1+...+(1-lambda)*value#(n-1) =
  =(1-lambda)^n*value#0+(1-lambda)^(n-1)*value#1+...+(1-lambda)*value#(n-1)

and finally
ewma#n=(1-lambda)^n*ewma#0+(1-lambda)^(n-1)*value#1+...+(1-lambda)^2*value#(n-2)+(1-lambda)*value#(n-1)+(lambda*value#n) =
  =lambda*value#i+summatory(for i,from 0,to n,of lambda*value#(i-1)*(1-lambda)^(n-i)) 


That should be evaluated as (Not tested)

with comp as(
    selct 0 as id,avg(val) as val
    from t
    union all
		select id,val
		from t
	) 
select a.id,a.val,sum(:lambda*b.val*pow(1-:lambda),a.id-b.id)+a.val
from comp a 
  join comp b on (
    a.id < b.id
  ) 
group by a.id,a.val
/


Bye Alessadro

[Updated on: Fri, 26 September 2008 19:59]

Report message to a moderator

Re: Calculating a row value from the previous row value in a query statement [message #350960 is a reply to message #350890] Sun, 28 September 2008 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice analysis.
Modifying your query (for instance, first val (id 0) is not average which is first ewma...):
SQL> create table t (id integer, val integer);

Table created.

SQL> insert into t 
  2  select level, round(dbms_random.value(2,10)) 
  3  from dual connect by level <= 10
  4  /

10 rows created.

SQL> select * from t order by 1;
        ID        VAL
---------- ----------
         1          3
         2          3
         3          4
         4          9
         5          5
         6          6
         7          7
         8          4
         9          4
        10          8

10 rows selected.

SQL> select avg(val) from t;
  AVG(VAL)
----------
       5.3

1 row selected.

SQL> var lambda number
SQL> exec :lambda := 0.3

PL/SQL procedure successfully completed.

SQL> col ewma format 990.00

SQL> with 
  2    data as (
  3      select 0 id, avg(val)/:lambda val from t
  4      union all
  5      select id, val from t
  6    ) 
  7  select a.id, a.val,
  8         sum(:lambda * power(1-:lambda,a.id-b.id) * b.val) ewma
  9  from data a, data b
 10  where b.id <= a.id
 11    and a.id > 0
 12  group by a.id, a.val
 13  order by a.id
 14  /
        ID        VAL    EWMA
---------- ---------- -------
         1          3    4.61
         2          3    4.13
         3          4    4.09
         4          9    5.56
         5          5    5.39
         6          6    5.58
         7          7    6.00
         8          4    5.40
         9          4    4.98
        10          8    5.89

10 rows selected.

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #351073 is a reply to message #350960] Mon, 29 September 2008 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just by curiosity, I also made it with custom aggregate function.
First I create a type containing the current value, lambda value (which might be different for each row) and the ewma initial value (id=0 in the previous query).
SQL> create or replace type ewma_value as object (
  2    value  number,
  3    lambda number,
  4    first  number
  5    )
  6  /

Type created.

Then I create the aggregate function:
SQL> Create or replace type ewma_type as object (
  2    ewma   number, 
  3    coef   number,
  4    first  number,
  5  
  6    static function ODCIAggregateInitialize (sctx IN OUT ewma_type) return number, 
  7  
  8    member function ODCIAggregateIterate (
  9      self  IN OUT ewma_type, 
 10      value IN     ewma_value) 
 11    return number, 
 12  
 13    member function ODCIAggregateTerminate (
 14      self        IN  ewma_type, 
 15      returnValue OUT number, 
 16      flags       IN  number) 
 17    return number, 
 18  
 19    member function ODCIAggregateMerge (
 20      self IN OUT ewma_type, 
 21      ctx2 IN     ewma_type) 
 22    return number 
 23  
 24  );
 25  /

Type created.

SQL> Create or replace type body ewma_type is 
  2  
  3    static function ODCIAggregateInitialize (sctx IN OUT ewma_type) 
  4    return number 
  5    is 
  6    begin 
  7      sctx := ewma_type (0,1,null); 
  8    return ODCIConst.Success; 
  9    end; 
 10  
 11    member function ODCIAggregateIterate (
 12      self  IN OUT ewma_type, 
 13      value IN     ewma_value) 
 14    return number 
 15    is 
 16    begin 
 17      if self.first is null then self.first := value.first; end if;
 18      self.ewma := value.lambda * value.value + (1-value.lambda) * self.ewma;
 19      self.coef := self.coef * (1-value.lambda);
 20      return ODCIConst.Success; 
 21    end; 
 22  
 23    member function ODCIAggregateTerminate (
 24      self        IN  ewma_type, 
 25      returnValue OUT number, 
 26      flags       IN  number) 
 27    return number 
 28    is 
 29    begin 
 30      returnValue := ewma + nvl(self.first,0) * self.coef; 
 31      return ODCIConst.Success; 
 32    end;
 33  
 34    member function ODCIAggregateMerge (
 35      self IN OUT ewma_type, 
 36      ctx2 IN ewma_type) 
 37    return number 
 38    is 
 39    begin 
 40      raise_application_error (-20001, 'Not supported');
 41      return ODCIConst.Error; 
 42    end; 
 43  
 44  end;
 45  /

Type body created.

SQL> Create or replace function ewma (input ewma_value) 
  2  return number 
  3  aggregate using ewma_type; 
  4  /

Function created.

Finally I use it:
SQL> with ave as (select avg(val) ave from t)
  2  select id, val, 
  3         ewma(ewma_value(val,0.3,ave)) over (order by id) ewma
  4  from t, ave
  5  order by id
  6  /
        ID        VAL    EWMA
---------- ---------- -------
         1          3    4.61
         2          3    4.13
         3          4    4.09
         4          9    5.56
         5          5    5.39
         6          6    5.58
         7          7    6.00
         8          4    5.40
         9          4    4.98
        10          8    5.89

10 rows selected.


Now I want to compare performances between the 2 ways with a table of 1000 rows (I executed several times each query and post here the fastest ones):
SQL> truncate table t;

Table truncated.

SQL> insert into t 
  2  select level, round(dbms_random.value(2,10)) 
  3  from dual connect by level <= 1000
  4  /

1000 rows created.

SQL> select count(*) from t;
  COUNT(*)
----------
      1000

1 row selected.

SQL> var lambda number
SQL> exec :lambda := 0.3

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly
SQL> set timing on
SQL> with 
  2    data as (
  3      select 0 id, avg(val)/:lambda val from t
  4      union all
  5      select id, val from t
  6    ) 
  7  select a.id, a.val,
  8         sum(:lambda * power(1-:lambda,a.id-b.id) * b.val) ewma
  9  from data a, data b
 10  where b.id <= a.id
 11    and a.id > 0
 12  group by a.id, a.val
 13  order by a.id
 14  /

1000 rows selected.

Elapsed: 00:00:01.75

Execution Plan
----------------------------------------------------------
Plan hash value: 2686348104

--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |     1 |    52 |    15  (54)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   2 |   LOAD AS SELECT           |                           |       |       |            |          |
|   3 |    UNION-ALL               |                           |       |       |            |          |
|   4 |     SORT AGGREGATE         |                           |     1 |    13 |            |          |
|   5 |      TABLE ACCESS FULL     | T                         |  1000 | 13000 |     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | T                         |  1000 | 26000 |     3   (0)| 00:00:01 |
|   7 |   SORT GROUP BY            |                           |     1 |    52 |     9  (56)| 00:00:01 |
|   8 |    MERGE JOIN              |                           | 50100 |  2544K|     6  (34)| 00:00:01 |
|   9 |     SORT JOIN              |                           |  1001 | 26026 |     3  (34)| 00:00:01 |
|* 10 |      VIEW                  |                           |  1001 | 26026 |     2   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6618_116BB3 |  1001 | 26026 |     2   (0)| 00:00:01 |
|* 12 |     SORT JOIN              |                           |  1001 | 26026 |     3  (34)| 00:00:01 |
|  13 |      VIEW                  |                           |  1001 | 26026 |     2   (0)| 00:00:01 |
|  14 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6618_116BB3 |  1001 | 26026 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  10 - filter("A"."ID">0)
  12 - access(INTERNAL_FUNCTION("B"."ID")<=INTERNAL_FUNCTION("A"."ID"))
       filter(INTERNAL_FUNCTION("B"."ID")<=INTERNAL_FUNCTION("A"."ID"))

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          3  recursive calls
         12  db block gets
         24  consistent gets
          7  physical reads
        612  redo size
      31824  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          2  sorts (memory)
          1  sorts (disk)
       1000  rows processed


SQL> with ave as (select avg(val) ave from t)
  2  select id, val, 
  3         ewma(ewma_value(val,0.3,ave)) over (order by id) ewma
  4  from t, ave
  5  order by id
  6  /

1000 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 314794421

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |  1000 | 39000 |     6   (0)| 00:00:01 |
|   1 |  WINDOW SORT          |      |  1000 | 39000 |     6   (0)| 00:00:01 |
|   2 |   NESTED LOOPS        |      |  1000 | 39000 |     6   (0)| 00:00:01 |
|   3 |    VIEW               |      |     1 |    13 |     3   (0)| 00:00:01 |
|   4 |     SORT AGGREGATE    |      |     1 |    13 |            |          |
|   5 |      TABLE ACCESS FULL| T    |  1000 | 13000 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL  | T    |  1000 | 26000 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
      31682  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Aggregate function is far far better.
What happens when number of rows increases?
---------------------------------------------------
|   Nb Rows | 1000 | 2000 |  3000 |  4000 |  5000 |
---------------------------------------------------
| 1st query | 1.75 | 7.40 | 16.96 | 31.78 | 51.39 |
| 2nd query | 0.04 | 0.09 |  0.14 |  0.17 |  0.21 |
---------------------------------------------------

Aggregate function is strictly linear when the first query is more than quadratic (increase like the square of number of rows).
(Note that an (unique) index on id column does not change anything as it is not used.)

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #351092 is a reply to message #351073] Mon, 29 September 2008 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now with model clause (in 10.2) we get:
SQL> select id, val, ewma
  2  from t
  3  model 
  4    unique dimension
  5    return all rows
  6    reference ave 
  7      on (select 0 id, avg(val) ave from t)
  8      dimension by (id)
  9      measures (ave)
 10    dimension by (id)
 11    measures (val, to_number(null) ewma)
 12    rules update sequential order
 13      ( ewma[ANY] order by id = :lambda * val[cv()] + (1-:lambda) * nvl(ewma[cv()-1],ave[0]) )
 14  order by id
 15  /
        ID        VAL    EWMA
---------- ---------- -------
         1          3    4.61
         2          3    4.13
         3          4    4.09
         4          9    5.56
         5          5    5.39
         6          6    5.58
         7          7    6.00
         8          4    5.40
         9          4    4.98
        10          8    5.89

10 rows selected.

And the performances array becomes (in seconds):
------------------------------------------------------------------------------
|   Nb Rows | 1000 | 2000 |  3000 |  4000 |  5000 |  10000 | 100000 | 1000000 |
------------------------------------------------------------------------------
| 1st query | 1.75 | 7.40 | 16.96 | 31.78 | 51.39 | 203.39 |        |         |
| 2nd query | 0.04 | 0.09 |  0.14 |  0.17 |  0.21 |   0.54 |   4.10 |   40.10 |
| 3rd query | 0.04 | 0.07 |  0.12 |  0.15 |  0.17 |   0.25 |   1.30 |   12.50 |
------------------------------------------------------------------------------

The power of MODEL!

Regards
Michel

[Updated on: Mon, 29 September 2008 12:23]

Report message to a moderator

icon14.gif  Re: Calculating a row value from the previous row value in a query statement [message #351182 is a reply to message #350586] Mon, 29 September 2008 13:03 Go to previous messageGo to next message
milaan66
Messages: 12
Registered: March 2007
Junior Member
Thanks guys for all your time and effort.
You made my day.

Regards,
Milaan
icon3.gif  Re: Calculating a row value from the previous row value in a query statement [message #360986 is a reply to message #350586] Mon, 24 November 2008 09:30 Go to previous messageGo to next message
milaan66
Messages: 12
Registered: March 2007
Junior Member
Hi guys,
I am trying to get the ewma with MODEL clause in 10g with the id column defined as varchar2 instead of number/integer. But it gives me an error stating "invalid number" and I am wondering why? The val column on which I am calculating the ewma is still a number and it shouldn't matter if the dimension column (id) is varchar2. I tried different ways but ended up with the same error. I think this error is generated for getting the first row ewma [cv()-1] function.

Any help?

Thanks,
Milaan
Re: Calculating a row value from the previous row value in a query statement [message #360994 is a reply to message #360986] Mon, 24 November 2008 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't post your data and your query we can't help.

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #360997 is a reply to message #360986] Mon, 24 November 2008 10:44 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
With that change an array indexed by integer became an array index by varchar2. There's no reason to do it, then don't do it.

If you need a varchar2 as output then submit a select with to_char(id).



Bye Alessandro
Re: Calculating a row value from the previous row value in a query statement [message #361009 is a reply to message #350586] Mon, 24 November 2008 12:38 Go to previous messageGo to next message
milaan66
Messages: 12
Registered: March 2007
Junior Member
I am working with the same data set and query (with the MODEL clause)as described in this thread. Really, the only difference being the "ID" column is defined as VARCHAR2 and actually have values as text. In other words, if ID column as text value in lieu of pure numbers, it (cv() function) does not work.

I hope I have made myself clear. If not, please let me know and I will post the data set that I am working with but that is the only difference.

Thanks,
Milaan
Re: Calculating a row value from the previous row value in a query statement [message #361012 is a reply to message #361009] Mon, 24 November 2008 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you post the same way I did?
I would be much clearer that anything you can say.

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #361014 is a reply to message #350586] Mon, 24 November 2008 14:02 Go to previous messageGo to next message
milaan66
Messages: 12
Registered: March 2007
Junior Member
Here you go... my appologies again if it does not indent properly as I don't know how to indent. If it does n't that please be assured that I am right now reading the forum guide to find if it mentions how to indent?

thank you
Milaan

SQL> create table t (id varchar2(20), val integer);

Table created.

SQL> insert into t values ('abc', 3);

1 row created.

SQL> insert into t values ('abcd', 5);

1 row created.

SQL> insert into t values ('abcde', 6)
2 /

1 row created.

SQL> insert into t values ('xyz', 3);

1 row created.

SQL> insert into t values ('a12345', 4);

1 row created.

SQL> insert into t values ('abc12', 3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

ID                VAL
---------- ----------
abc                 3
abcd                5
abcd                6
xyz                 3
a12345              4
abc12               3

6 rows selected.


SQL> var lambda number
SQL> exec :lambda := 0.3

PL/SQL procedure successfully completed.

select id, val, ewma
   from t
   model
     unique dimension
     return all rows
     reference ave
       on (select 0 id, avg(val) ave from t)
       dimension by (id)
       measures (ave)
     dimension by (id)
     measures (val, to_number(null) ewma)
     rules update sequential order
       ( ewma[1] order by id = :lambda * val[cv()] + (1-:lambda) * nvl(ewma[cv()-1],ave[0]) )
   order by id
/

       ( ewma[ANY] order by id = :lambda * val[cv()] + (1-:lambda) * nvl(ewma[cv()-1],ave[0]) )
                                                                              *
ERROR at line 13:
ORA-01722: invalid number


Is there a way to get ewma with the id as text?

Thanks,
Milaan

[Updated on: Mon, 24 November 2008 14:20]

Report message to a moderator

Re: Calculating a row value from the previous row value in a query statement [message #361015 is a reply to message #361014] Mon, 24 November 2008 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just introduced a new column as number:
SQL> select id, val, ewma
  2     from (select row_number() over (order by id) rn,
  3           id, val
  4           from t)
  5     model
  6       unique dimension
  7       return all rows
  8       reference ave
  9         on (select 0 rn, '' id, avg(val) ave from t)
 10         dimension by (rn)
 11         measures (id, ave)
 12       dimension by (rn)
 13       measures (id, val, to_number(null) ewma)
 14       rules update sequential order
 15         ( ewma[ANY] order by id = :lambda * val[cv()] + (1-:lambda) * nvl(ewma[cv()-1],ave[0]) )
 16  order by id
 17  /
ID                          VAL       EWMA
-------------------- ---------- ----------
a12345                        4          4
abc                           3        3.7
abc12                         3       3.49
abcd                          5      3.943
abcde                         6     4.5601
xyz                           3    4.09207

6 rows selected.

Regards
Michel
Re: Calculating a row value from the previous row value in a query statement [message #361016 is a reply to message #361015] Mon, 24 November 2008 15:03 Go to previous message
milaan66
Messages: 12
Registered: March 2007
Junior Member
Thanks Michel... it worked.
Best Regards,
Previous Topic: Random Time Between 2 dates
Next Topic: count in refcursor
Goto Forum:
  


Current Time: Sun Dec 04 11:00:18 CST 2016

Total time taken to generate the page: 0.06664 seconds