Percentile Calculation on Frequency field

Question

Percentile Calculation on Frequency field

Ghanta

Hall of Fame

Points: 3999
More actions
March 14, 2011 at 11:10 am

#236738

Symbol Total FreqCnt
--===================================
NOK 6000000 3
NOK 1000000 2
NOK 3000000 2
NOK 400000 2
NOK 2000000 2
NOK 30000000 1
NOK 37000000 1
NOK 10000000 1
NOK 28000000 1
NOK 5000000 1
NOK 8000000 1
NOK 15000000 1
NOK 17000000 1
NOK 36000000 1
SEK 16000000 7
SEK 3000000 5
SEK 8000000 3
SEK 20000000 2
SEK 150000000 2
SEK 300000 1
SEK 17000000 1
SEK 10000000 1
SEK 170000000 1
SEK 26000000 1
SEK 6000000 1
SEK 190000000 1
SEK 37000000 1
SEK 60000000 1
SEK 5000000 1
Hi Guys I need to get 95 percentile for each Symbol using the Freqcnt field....
Example Output---
Symbol Total FreqCnt RunningTotal
SEK 16000000 7 7
SEK 3000000 5 12
SEK 8000000 3 15
SEK 20000000 2 17
SEK 150000000 2 19
SEK 300000 1 20
SEK 17000000 1 21
SEK 10000000 1 22
SEK 170000000 1 23
SEK 26000000 1 24
SEK 6000000 1 25
SEK 190000000 1 26
SEK 37000000 1 27
SEK 60000000 1 28
SEK 5000000 1 29
Total: 29
95 percent of Freqcnt for SEK is:27.55... So my query should output all records who freqCnt adds up to <= 27.55 while freqcnt is ordered in DESC.
From above, I should exclude the below two records:
SEK 60000000 1 28
SEK 5000000 1 29
I do not need RunningTotal field. Just the records that are in 95 percentile using FreqCnt for each Symbol. I tried using NTILE, but couldn't get the intended result... Thanks a lot in advance for the assistance.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

well, here's the data in consumable format...i'll try to look again at what you are asking, but i used up all my brain power converting your post to something usable....

with myCTE (Symbol,Total,FreqCnt)

AS

(

SELECT 'NOK','6000000','3' UNION ALL

SELECT 'NOK','1000000','2' UNION ALL

SELECT 'NOK','3000000','2' UNION ALL

SELECT 'NOK','400000','2' UNION ALL

SELECT 'NOK','2000000','2' UNION ALL

SELECT 'NOK','30000000','1' UNION ALL

SELECT 'NOK','37000000','1' UNION ALL

SELECT 'NOK','10000000','1' UNION ALL

SELECT 'NOK','28000000','1' UNION ALL

SELECT 'NOK','5000000','1' UNION ALL

SELECT 'NOK','8000000','1' UNION ALL

SELECT 'NOK','15000000','1' UNION ALL

SELECT 'NOK','17000000','1' UNION ALL

SELECT 'NOK','36000000','1' UNION ALL

SELECT 'SEK','16000000','7' UNION ALL

SELECT 'SEK','3000000','5' UNION ALL

SELECT 'SEK','8000000','3' UNION ALL

SELECT 'SEK','20000000','2' UNION ALL

SELECT 'SEK','150000000','2' UNION ALL

SELECT 'SEK','300000','1' UNION ALL

SELECT 'SEK','17000000','1' UNION ALL

SELECT 'SEK','10000000','1' UNION ALL

SELECT 'SEK','170000000','1' UNION ALL

SELECT 'SEK','26000000','1' UNION ALL

SELECT 'SEK','6000000','1' UNION ALL

SELECT 'SEK','190000000','1' UNION ALL

SELECT 'SEK','37000000','1' UNION ALL

SELECT 'SEK','60000000','1' UNION ALL

SELECT 'SEK','5000000','1'

)

SELECT TOP 95 PERCENT *

FROM MYCTE

ORDER BY FreqCnt DESC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 2

I think you need the additional column to store the intermediate running toal values. To calculate those values, have a look at this post.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ghanta Hall of Fame Points: 3999 More actions · Answer 3

hehehe Lowell this is not that I am asking... recharge ur brain :). Sorry for the poorly formated sample data...

LutzM SSC Guru Points: 107049 More actions · Answer 4

Ghanta (3/14/2011)
hehehe this is not that I am asking... recharge ur brain 🙂

What's wrong with my answer?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ghanta Hall of Fame Points: 3999 More actions · Answer 5

Sorry that was not for your response... I am reading the link u sent me.. that is good. Thanks!

Ghanta Hall of Fame Points: 3999 More actions · Answer 6

I think this should do it:

--Using Test table

Create Table TEST

(Symbol char(3),Total bigint,FreqCnt int, recordnum int identity (1, 1) )

Insert into TEST(Symbol, Total, FreqCnt)

SELECT 'NOK','6000000','3' UNION ALL

SELECT 'NOK','1000000','2' UNION ALL

SELECT 'NOK','3000000','2' UNION ALL

SELECT 'NOK','400000','2' UNION ALL

SELECT 'NOK','2000000','2' UNION ALL

SELECT 'NOK','30000000','1' UNION ALL

SELECT 'NOK','37000000','1' UNION ALL

SELECT 'NOK','10000000','1' UNION ALL

SELECT 'NOK','28000000','1' UNION ALL

SELECT 'NOK','5000000','1' UNION ALL

SELECT 'NOK','8000000','1' UNION ALL

SELECT 'NOK','15000000','1' UNION ALL

SELECT 'NOK','17000000','1' UNION ALL

SELECT 'NOK','36000000','1' UNION ALL

SELECT 'SEK','16000000','7' UNION ALL

SELECT 'SEK','3000000','5' UNION ALL

SELECT 'SEK','8000000','3' UNION ALL

SELECT 'SEK','20000000','2' UNION ALL

SELECT 'SEK','150000000','2' UNION ALL

SELECT 'SEK','300000','1' UNION ALL

SELECT 'SEK','17000000','1' UNION ALL

SELECT 'SEK','10000000','1' UNION ALL

SELECT 'SEK','170000000','1' UNION ALL

SELECT 'SEK','26000000','1' UNION ALL

SELECT 'SEK','6000000','1' UNION ALL

SELECT 'SEK','190000000','1' UNION ALL

SELECT 'SEK','37000000','1' UNION ALL

SELECT 'SEK','60000000','1' UNION ALL

SELECT 'SEK','5000000','1'

--======================

With MyCTE As (

SELECT Symbol, Total, FreqCnt

,(select sum(FreqCnt) from Test

where recordnum <= a.recordnum

and Symbol = a.Symbol )

RunningTotal,

(select 0.95 * cast(sum(FreqCnt) as float) from Test

where Symbol = a.Symbol) PercentileTotal

from Test a

)

select *

from MyCTE

where RunningTotal <= PercentileTotal

Order by Symbol, FreqCnt Desc

LutzM SSC Guru Points: 107049 More actions · Answer 7

How many rows do you have in that table? And how many rows per Symbol (approximately)?

Reason for asking: This code performs a triangular join that might seriously influence performance...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ghanta Hall of Fame Points: 3999 More actions · Answer 8

This is an ad-hoc data analysis so I was not bothered with that.. but please suggest what I can do to optimize it... if it doesn't take a lot of your time. I would like to learn. Thanks a lot for taking time in going through my request.