Hi all,
My question is about the Analytical functions in the model clause.
First the data base I am using:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
This is my sample query:
with test_data as
( select 1 id , 70 n from dual
union all select 2 id , 60 n from dual
union all select 3 id , 50 n from dual
union all select 4 id , 40 n from dual
union all select 5 id , 30 n from dual
union all select 6 id , 20 n from dual
union all select 7 id , 10 n from dual
)
select
*
from
test_data
model
dimension by (id)
measures (n,0 a, 0 b, 0 c, 0 d, 0 e, 0 f, 0 g,0 h)
rules
update
(
--aggregate sample
a[any] = max(n) [any]
,b[1] = max(n) [any]
--Analytic sample
,c[any] = first_value(n) over (order by id desc)
,d[any] = first_value(n) over (order by id asc)
,e[1] = first_value(n) over (order by id asc)
,f[1] = first_value(n) over (order by id desc)
,e[2] = first_value(n) over (order by id asc)
,f[2] = first_value(n) over (order by id desc)
,e[3] = first_value(n) over (partition by (1) order by id asc rows between unbounded preceding and unbounded following)
,f[3] = first_value(n) over (partition by (1) order by id desc rows between unbounded preceding and unbounded following)
,e[4] = first_value(n) over (partition by (1) order by id asc range between unbounded preceding and unbounded following)
,f[4] = first_value(n) over (partition by (1) order by id desc range between unbounded preceding and unbounded following)
,g[1] = first_value(n) over (order by id asc range between 10 preceding and 2 following)
,h[1] = first_value(n) over (order by id desc range between 10 preceding and 2 following)
,g[2] = first_value(n) over (order by id asc range between 10 preceding and 10 following)
,h[2] = first_value(n) over (order by id desc range between 10 preceding and 10 following)
,g[3] = first_value(n) over (order by id asc rows between 10 preceding and 2 following)
,h[3] = first_value(n) over (order by id desc rows between 10 preceding and 2 following)
,g[4] = first_value(n) over (order by id asc rows between 10 preceding and 10 following)
,h[4] = first_value(n) over (order by id desc rows between 10 preceding and 10 following)
)
order by
id
;
ID N A B C D E F G H
----- ----- ----- ----- ----- ----- ----- ----- ----- -----
1 70 70 70 10 70 70 70 70 70
2 60 70 0 10 70 60 60 60 60
3 50 70 0 10 70 50 50 50 50
4 40 70 0 10 70 40 40 40 40
5 30 70 0 10 70 0 0 0 0
6 20 70 0 10 70 0 0 0 0
7 10 70 0 10 70 0 0 0 0
In the aggregate sample columns (A and B):
A returns the max of N
B[1] returns also the max of N as I expected.
In the Analytical sample columns (C to G):
C[any] returns the first_value of N after sorting id asc
D[any] returns the first_value of N after sorting id desc
Until now everything is as I expect. However as seen in the following columns D to G.
If we specify a dimension (other than any) the window of the Analytical is limited to that dimension only.
I cannot find any documentation on this behavior.
Can anybody explain this?
Or is there another way to force the function to look at all records of the Model?
Regards,
Peter