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 Thu, 25 September 2008 08:48
 milaan66 Messages: 12Registered: 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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Don't post in UPPER case.
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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
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.

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
 milaan66 Messages: 12Registered: 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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 joy_division Messages: 4747Registered: February 2005 Location: East Coast USA Senior Member

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
 milaan66 Messages: 12Registered: 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
 rajy_salim Messages: 204Registered: 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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 _jum Messages: 510Registered: 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
 rajy_salim Messages: 204Registered: 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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 milaan66 Messages: 12Registered: 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
 Alessandro Rossi Messages: 166Registered: 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:

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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Custom aggregate function will indeed do it but this is PL/SQL.

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
 Alessandro Rossi Messages: 166Registered: 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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 milaan66 Messages: 12Registered: 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
 Alessandro Rossi Messages: 166Registered: 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
/
```

[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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
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  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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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

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
 milaan66 Messages: 12Registered: March 2007 Junior Member
Thanks guys for all your time and effort.

Regards,
Milaan
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
 milaan66 Messages: 12Registered: 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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Alessandro Rossi Messages: 166Registered: 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
 milaan66 Messages: 12Registered: 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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 milaan66 Messages: 12Registered: 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
 Michel Cadot Messages: 64841Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 milaan66 Messages: 12Registered: 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 May 28 11:49:55 CDT 2017

Total time taken to generate the page: 0.14110 seconds