Post reply

Percentile Calculation on Frequency field

  • 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.

  • March 14, 2011 at 11:20 am

    #1298474

    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!

  • March 14, 2011 at 11:24 am

    #1298478

    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]

  • March 14, 2011 at 11:44 am

    #1298491

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

  • March 14, 2011 at 11:46 am

    #1298493

    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]

  • March 14, 2011 at 11:50 am

    #1298498

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

  • March 14, 2011 at 12:54 pm

    #1298534

    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

  • March 14, 2011 at 1:02 pm

    #1298537

    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]

  • March 14, 2011 at 1:04 pm

    #1298538

    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.

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

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