Percentile function

Question

Percentile function

dva2007

SSCertifiable

Points: 7707
More actions
November 30, 2011 at 2:31 am

#263769

hi,
it seems SQL 2012 had new function called PERCENTILE_DISC() which will find out the percentile.
http://blog.sqlauthority.com/2011/11/22/sql-server-introduction-to-percentile_disc-analytic-functions-introduced-in-sql-server-2012/
Do we have alternative function in SQL 2008 to do the same?
thanks,
Vijay

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

As you said, it's an Analytic Function & introduced in Denali. We don’t have similar functions in SS2K8 but there are workarounds for it.

Please post your exact requirements. SSC would be happy to help you.

dva2007 SSCertifiable Points: 7707 More actions · Answer 2

Declare @TestScores table (StudentID int identity(1,1), Score int)

insert @TestScores (Score) Values (20)

insert @TestScores (Score) Values (03)

insert @TestScores (Score) Values (40)

insert @TestScores (Score) Values (45)

insert @TestScores (Score) Values (50)

insert @TestScores (Score) Values (20)

insert @TestScores (Score) Values (90)

insert @TestScores (Score) Values (20)

insert @TestScores (Score) Values (11)

insert @TestScores (Score) Values (30)

while @@ROWCOUNT > 0

insert @TestScores select t.Score from @TestScores t

cross join

(select max (StudentID) MaxRowNum from @TestScores) x

where

t.StudentID <= 10000 - x.MaxRowNum

select * From @TestScores

order by score

The highest score is 90 and based on the highest score this needs to be split by percentile (4 group quarterly group).

any score between 0 and 22.5 - group 1

22.6 and 45 - group 2

45 and 67.5 - group 3

and more than 67.6 - group 4

now the highest score could be different for different criteria. it could be 20 or 50 or 90 or 1000.

thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

One way is to use a CASE expression, something like this:

Declare @TestScores table (StudentID int identity(1,1), Score int)

DECLARE @Highscore decimal(4,2)

insert @TestScores (Score) Values (20)

insert @TestScores (Score) Values (03)

insert @TestScores (Score) Values (40)

insert @TestScores (Score) Values (45)

insert @TestScores (Score) Values (50)

insert @TestScores (Score) Values (20)

insert @TestScores (Score) Values (90)

insert @TestScores (Score) Values (20)

insert @TestScores (Score) Values (11)

insert @TestScores (Score) Values (30)

while @@ROWCOUNT > 0

insert @TestScores select t.Score from @TestScores t

cross join

(select max (StudentID) MaxRowNum from @TestScores) x

where

t.StudentID <= 10000 - x.MaxRowNum

SET @Highscore = 90

SELECT

StudentID

,Score

,CASE

WHEN Score >= 0 AND Score < @Highscore/4 THEN 'Group 1'

WHEN Score >= @Highscore/4 AND Score < @Highscore/2 THEN 'Group 2'

WHEN Score >= @Highscore/2 AND Score < @Highscore*3/4 THEN 'Group 3'

ELSE 'Group 4'

END AS ScoreGroup

FROM

@TestScores

Possinator Hall of Fame Points: 3422 More actions · Answer 4

2008 has the NTile function, sounds like it might be a solution.

http://msdn.microsoft.com/en-us/library/ms175126.aspx

[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

dva2007 (11/30/2011)
hi,
it seems SQL 2012 had new function called PERCENTILE_DISC() which will find out the percentile.
http://blog.sqlauthority.com/2011/11/22/sql-server-introduction-to-percentile_disc-analytic-functions-introduced-in-sql-server-2012/
Do we have alternative function in SQL 2008 to do the same?
thanks,
Vijay

If I'm understanding you correct, Yep... there sure is. It's called "NTILE" in SQL Server. Example follows... (dang it... just noticed "Burninator" suggested NTILE while I was typing. :-D)

--===== Create the test table. This is not a part of the solution.

DECLARE @TestScores TABLE (StudentID INT IDENTITY(1,1), Score INT)

;

--===== Use a high-speed pseudo-cursor to populate the test table

-- with INTEGERs from 0 to 90 in totally random order.

INSERT INTO @TestScores

(Score)

SELECT TOP 10000

Score = ABS(CHECKSUM(NEWID()))%91 --0 through 90... 91 is never reached

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

--===== Solve the problem.

SELECT StudentID, Score, NTILE(4) OVER (ORDER BY Score DESC)

FROM @TestScores

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Jeff, I don't think that's what Vijay was asking for. Your solution splits the scores into four evenly-sized groups. The requirement was for the scores to be split into groups according to the scores themselves - everything up to 22.5% in one group, everything between 22.5% and 45% in another, and so on.

John

dva2007 SSCertifiable Points: 7707 More actions · Answer 7

Thanks john and jeff for your input. Much appreciated.

One more problem is the datset i am designing is not on server 2008 so i had to use 2005 where NTILE is not available.

Also initially i thought i need to split the highest score in 4 category but then it has changed to split by even sized grouping. Therefore both solutions were required but i couldnt use NTILE.

I had to use dense_rank and then split in 4 different groups based on ranking of the data.

thanks,

Vijay

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

dva2007 (12/1/2011)
Thanks john and jeff for your input. Much appreciated.
One more problem is the datset i am designing is not on server 2008 so i had to use 2005 where NTILE is not available.

Absolutely not true. The code example I wrote and tested was on SQL Server 2005. Check the compatability level of your database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dva2007 SSCertifiable Points: 7707 More actions · Answer 9

yes, it is working in 2005. Thanks. I got it working with dense_rank but it is not the best way of writing if we have this function available. thanks very much.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

John Mitchell-245523 (12/1/2011)
Jeff, I don't think that's what Vijay was asking for. Your solution splits the scores into four evenly-sized groups. The requirement was for the scores to be split into groups according to the scores themselves - everything up to 22.5% in one group, everything between 22.5% and 45% in another, and so on.
John

Understood but I think it's the other way around. I believe the 22.5% came up only because 90 is the largest number and 90/4 is 22.5%. If the largest number was only 80, then he'd want it based on 80/4, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)