|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/5/2009 2:55:34 PM
Posts: 5,
Visits: 18
|
|
i am trying to come up with a way to rank and have the ability to window using the rows function. i know that neither the rank nor the percent_rank functions allow you to specify the rows to window over.
so what i would want to do if using rows was possible in the rank statement:
rank() over (partition by column order by column )
does anybody know of a work around for this?
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: Yesterday @ 12:42:08 AM
Posts: 74,
Visits: 32
|
|
Hi,
WHat you could use is the PRECEEDING or PROCEEDING function call in the Partition to SELECT the # of rows you desire. Example, if you are ranking, then you can use a filter for Rank #1 and the Proceeding 10 rows to window over 10 rows only. I hope that makes sense, or I have completely lost your question. Get in touch if you need help.
Strive to success. Arun.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/5/2009 2:55:34 PM
Posts: 5,
Visits: 18
|
|
thanks. that is exactly what i want to do, but it is not allowed within the rank function. i just noticed the forum cut off some of my code. i wanted to bold it, but instead it got deleted. the *** part is what i want to do, but cant in teradata.
rank() over (partition by column order by column ***rows between 200 preceding and current row***)
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
Like Arun, I thought you were talking rubbish! But you are correct - you cannot use Rows on a Rank function.
The following will find the 200 preceding rows for every partition column in a subquery and then Rank them.
:-
Select PKCol, PartCol, RankCol
, Rank () Over (Partition By PartCol
Order By RankCol) As RowOrder
From (
Select PKCol, PartCol, RankCol
, Row_Number () Over(Partition By PartCol
Order By RankCol Desc
) As RowLimit
From TDUSER.T1
Qualify RowLimit LE 200 ) D1
Order By 2,4 Desc
;
I also did not think you could use an Olap function in a derived table and then use Olap on that. So a very good day - Thanks
Jim M
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/5/2009 2:55:34 PM
Posts: 5,
Visits: 18
|
|
thanks jim! so there were actually 2 parts to the question. i was hoping that would help with both, but alas i am still having an issue. the second part is more complicated, but is the same general problem. lets say i had the following data set; i want to order by time, then look back 5 rows and find the relative rank (create the fake_rank column) for each row. the order by time aspect is killing me and there is no real partition this time around. im not sure it is possible, but was hopeful someone here might have run into something similar. thanks again.
time/price/fake_rank (desc)
1/6/?
2/9/?
3/5/?
4/4/?
5/7/2
6/10/1
7/3/5
8/6/3
9/9/2
10/5/4
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
See below
:-
Create Volatile Table Tst1
("Time" Smallint Not Null
, Price Smallint Not Null)
Unique Primary Index ("Time")
On Commit Preserve Rows
;
Insert Into Tst1 Values (1,6);
Insert Into Tst1 Values (2,9);
Insert Into Tst1 Values (3,5);
Insert Into Tst1 Values (4,4);
Insert Into Tst1 Values (5,7);
Insert Into Tst1 Values (6,10);
Insert Into Tst1 Values (7,3);
Insert Into Tst1 Values (8,6);
Insert Into Tst1 Values (9,9);
Insert Into Tst1 Values (10,5);
Select "Time"
, Price
, Rank() Over (Order By Price Desc)
As Fake_Rank
From
(
Select "Time"
, Price
, Rank() Over (Order By "Time" Desc )
As Faker_Rank
From Tst1
Qualify Faker_Rank LE 5
) As D1
Order By 1
;
----------------------------------
Gives:
Time Price Fake_Rank
6 10 1
7 3 5
8 6 3
9 9 2
10 5 4
----------------------------------
Just a slight variation on the previous answer.
Note that Time 5 in your suggested dataset does not match - a typo in the dataset or you have not specified your rules fully?
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: Yesterday @ 12:42:08 AM
Posts: 74,
Visits: 32
|
|
Hi,
Yes you are right. You cannot use the PRECEEDING/PROCEEDING function on a rank. But you can use it on an AGGREGATE function call from the Partition function. What you can do too is to ensure your GROUPING columns get full row identifiers and then use the SUM function to get PRECEEDING/PROCEEDING number of rows from your call. I have done this before and it works fine AS LONG AS you make sure that you not overlap rows of wanted records by not specifying the full key identifier in your Partitioning function call. If you need any further help, get in touch. I don't know if I can currently give you exact Syntax as I do not have access to a Teradata Server to run test queries :). Take care and hope this helps.
Strive to success. Arun.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/5/2009 2:55:34 PM
Posts: 5,
Visits: 18
|
|
|
thanks guys. sort of combined the two solutions to get to what i was wanting. appreciate the help.
|
|
|
|