|
|
Archives of the TeradataForum
Message Posted: Tue, 06 Mar 2007 @ 16:59:38 GMT
Subj: | | Re: Performance of a self join |
|
From: | | Dieter Noeth |
Goutham P wrote:
| Can you please explain what you meant by saying joining these tables together? In the derived table B, I am having the aggregation on only
fewer dimensional columns (dim 1 - dim 5) . Where as in the OLAP function used for table A, I am aggregating on different set of dimensional
columns (dim 1 - dim 4). I have to join these two tables as they both give different perspective of data. | |
Just do the OLAP calculation in the Derived Table, OLAP functions can work on aggregates:
select
A.*, value3
from A,
(
select
dim1 ,
dim2 ,
dim3 ,
dim4 ,
dim5 ,
metric1 / count(metric2) as value2 ,
Value2 / sum(Value2) over
(partition by dim1, dim2, dim3, dim4) as value3
from A
group by 1,2,3,4,5
) B
where A.dim1 = B.dim1
and A.dim2 = B.dim2
and A.dim3 = B.dim3
and A.dim4 = B.dim4
and A.dim5 = B.dim5
and A.dim6 = B.dim6
Dieter
| |