Rank by Decile Category - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Rank by Decile Category

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-04-11 : 10:39:27

Hi,

I have a set of data in a table which is sorted according to the data contained in one particular column. I would like to add an additional column wherein I rank the data according to deciles. Thus, I am not looking to merely rank the data (1, 2, 3, etc.) but divide my result set into 10 and then assign a decile ranking between 1 and 10 to each record.

To envision this, I currently have:

Item Value
a 0.28
b 0.03
c 0.04
d 0.09
e 0.10

And I want:

Item Value Decile
a 0.28 1
b 0.03 1
c 0.04 1
d 0.09 2
e 0.10 2

Of course, the decile categories of 1, 2, etc. would repeat for however many records are in that particular decile. Can anybody suggest how best I would go about doing this?

If there's an easy answer, a quick follow up question I have would be what if I had a massive table with hundreds of rows of records sorted by month (so the same tables above but with a Date record for each month over the past 10 years or so where the data changes every month). Is there a way I could rank the data into decile categories BY MONTH (I'm thinking I'll need to have a group by somewhere, but I'm not sure).

Any help would be much appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 11:14:40

Let me ask a couple questions to see if I'm understanding you.

if you have 100 unique values 1-100 then 1-10 would be 1, 2-20 would be 2 etc...
if you have 100 values (10 10s, 10 20s, 10 30s etc) then all the 10s would be 1, all the 20s would be 2 etc...
if you have 100 values (33 10s, 33 11s, 34 12s) how many for each of the 10 deciles?
if you have 100 values (1,2,3, and 97 98s) how many for each for each of the 10 deciles?
if you have 5 values (1,2,3,97,98) how many for each for each of the 10 deciles?

in other words are you "spreading" the set over all ten deciles or deviding the total range of values into 10 parts and then dropping each value into the appropriate bucket?

Be One with the Optimizer
TG

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-11 : 11:27:08

You need to determine your top/bottom values and loop to create the 10 decile intervals...and save this in a table....and join to the table and partition your data by the values.

re part 2....decile table structure would be start date, enddate, low value, upper value.

ps...I suspect .28 would be in a higher decile than .03 ....going by the other values!!!

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-04-11 : 11:28:39

That's a very good question. I'm looking to spread the set over ten deciles.

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-04-11 : 11:30:59

Hi AndrewMurphy, thanks for replying. You're right about the .28 value vs. 0.3.

I thank you for your suggestion, but is there a sample script you could point me to to loop and create 10 decile intervals? It's this part that I'm trying to learn how to do. Any hint you could give to start me off would be greatly appreciated.

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 13:36:13

How's this KidSQL?
2 different ways:
1) Spread evenly over all 10 deciles
2) Devide range into 10 even parts and bucket-ize the values


--Generate sample data
If Object_ID('tempdb.dbo.#deciles') > 0
	drop table #deciles
GO
set nocount on
declare @tb table (Item varchar(3), value decimal(3,2))
declare @i int
set @i = 1
while @i < 51
begin
	insert @tb (item, value)
	values (convert(varchar(3),@i), convert(decimal(3,2),rand()))
	set @i = @i + 1
end

--add to temp table #deciles
select item, value, identity(int,1,1) ord into #deciles from @tb order by value
GO


--To spread evenly over all 10 deciles
select	ord
	,value
	,ceiling(ord/decRange) decile
from	#deciles a
cross join (select max(ord)/10.0 decRange from #deciles) b
order by ord

--To devide range into 10 even parts and bucket-ize the values
select	ord
	,value
	,ceiling(value/decsize) decile
from	#deciles a
cross join (Select convert(decimal(3,2),(max(value)-min(value))/10.0) [decSize] from #deciles) b
order by ord

Be One with the Optimizer
TG

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-04-11 : 15:07:21

Thank you very much, TG. I'll give it a try and see.

marcellduplessis
Starting Member

4 Posts

Posted - 2009-02-27 : 00:53:03

Just take care to provide for equal values in the range.

Using identity to obtain a "Ranking" will give two equal values different ranks.

Ranks with identity:
Value IdentityRank
1 1
2 2
3 3
4 4
4 5
5 6
6 7

What you want:
Value IdentityRank
1 1
2 2
3 3
4 4
4 4
5 6
6 7

quote:

Originally posted by TG

How's this KidSQL?
2 different ways:
1) Spread evenly over all 10 deciles
2) Devide range into 10 even parts and bucket-ize the values


--Generate sample data
If Object_ID('tempdb.dbo.#deciles') > 0
	drop table #deciles
GO
set nocount on
declare @tb table (Item varchar(3), value decimal(3,2))
declare @i int
set @i = 1
while @i < 51
begin
	insert @tb (item, value)
	values (convert(varchar(3),@i), convert(decimal(3,2),rand()))
	set @i = @i + 1
end

--add to temp table #deciles
select item, value, identity(int,1,1) ord into #deciles from @tb order by value
GO


--To spread evenly over all 10 deciles
select	ord
	,value
	,ceiling(ord/decRange) decile
from	#deciles a
cross join (select max(ord)/10.0 decRange from #deciles) b
order by ord

--To devide range into 10 even parts and bucket-ize the values
select	ord
	,value
	,ceiling(value/decsize) decile
from	#deciles a
cross join (Select convert(decimal(3,2),(max(value)-min(value))/10.0) [decSize] from #deciles) b
order by ord

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources