Archives of the TeradataForum
Message Posted: Thu, 09 Aug 2007 @ 15:57:31 GMT
Subj: | | Re: High skew while doing rank |
|
From: | | Mohommod.Khan |
As mentioned by Mike try using a qualify to eliminate additional data. You will notice high spool issue with these OLAP. The other approach is
you can rewrite this to use the Teradata Rank using 'group by' and see if it helps.
In your case you can try:
Select activity_prd_id ,
site_id,
user_id,
activity_count ,
rank( activity_count ) As activity_count_rank
>From tb_test
Group By activity_prd_id , site_id
QUALIFY rank( activity_count ) <= (Some condition) ;
The solution is to use : Row_Num which is ANSII like the Ansii Rank. However the Row_Number does not return duplicate value like
tie.
So this should work:
Select activity_prd_id ,
site_id,
user_id,
activity_count ,
ROW_NUMBER () over (Partition By activity_prd_id , site_id
Order By activity_count Desc ) activity_count_rank
>From tb_test
QUALIFY rank( activity_count ) <= (Some condition) ;
Good luck!
|