Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables, table1 and table2. Let the two tables contain date, id and latency column.

I have a simple query that performs a join on the two tables and returns a set of rows:

Select table1.date,(table2.latency - table1.latency) as ans from table1, table2
where table1.id = table2.id order by ans;

I need to find the nth percentile row from the returned set of rows, lets say I need to find 90%, 99% and 99.9% percentile row from the data.

I need to display the data in a form like this:

    date       |   percentile  | ans
    01-12-1995 |    90         | 0.001563
    02-12-1999 |    99         | 0.0015
    05-12-2000 |    99.9       | 0.012

This is my first encounter to PostgreSQL. I am confused as to how should I proceed.

I was having a look at PERCENT_RANK() function. Please guide me in the correct direction.

share|improve this question
    
Please provide sample data and explain the order in which the percentile should be calculated. –  ypercube Dec 24 '13 at 12:21
    
tip use SQLfiddle to provide us with example data.. you should really elaborate your question more.. and it looks like you want to use date as an GROUP where columns percentile and ans (looks like an SUM) depends on –  Raymond Nijland Dec 24 '13 at 12:34
    
well i need the exact/closest row indicating that it is the 90 percentile value for the given set of data.so when i get the row, i want to display only that particular row. –  D3XT3R Dec 24 '13 at 12:39
    
You need to declare the version of Postgres you are working with. And, of course, an sqlfiddle or any kind of sample data would be welcome. –  Erwin Brandstetter Dec 24 '13 at 14:19

1 Answer 1

up vote 5 down vote accepted

Use the window function ntile() in a subquery (requires Postgres 8.4 or later).
Then select the segments you are interested in (corresponding to percentiles) and pick the row with the lowest value from it:

SELECT DISTINCT ON (segment)
       the_date, to_char((segment - 1)/ 10.0, '99.9') AS percentile, ans
FROM  (
    SELECT t1.the_date 
          ,ntile(1000) OVER (ORDER BY (t2.latency - t1.latency)) AS segment
          ,(t2.latency - t1.latency) AS ans
    FROM   table1 t1
    JOIN   table2 t2 ON t1.id = t2.id
   ) sub
WHERE  segment IN (601, 901, 991, 1000)
ORDER  BY segment, ans;

The Postgres-specific DISTINCT ON comes in handy for the last step. Detailed explanation in this related answer on SO:
Select first row in each GROUP BY group?

To get the 90, 99 and 99.9 percentile I picked the matching granularity with ntile(1000). And added a 60 percentile as per comment.

This algorithm picks the row at or above the exact value. You can add a line to the subquery with percent_rank() to get the exact relative rank of the select row in addition:

 percent_rank() OVER (ORDER BY (t2.latency - t1.latency)) AS pct_rank

Aside: I replaced the column name date with the_date since I am in the habbit of avoiding reserved SQL key words as identifiers, even if Postgres would permit them.

share|improve this answer
    
@D3XT3R: Actually my selection of segments was off. I fixed that and added the row above 60%. –  Erwin Brandstetter Dec 24 '13 at 14:52
    
so how do you base your selection of segments..i mean lets say.. i want to display percentile value of 100 percentile within the same algorith. as per your algorithm, i need to provide the segments of data i want to create, am i on the correct track? –  D3XT3R Dec 24 '13 at 14:56
    
Since I divided the rows into 1000 segments, the lowest value of the 601st (or the highest value of the 600th) would mark the 60% border ... –  Erwin Brandstetter Dec 24 '13 at 15:00
    
thanks erwin..how can i get the last row of the table i.e. the 100 percentile value? should i divide the rows into further more segments? –  D3XT3R Dec 24 '13 at 15:04
    
@D3XT3R: Use a descending order and add LIMIT 1 to get the last row in a simple query.. SELECT * FROM .. ORDER BY ans DESC LIMIT 1. Or, in the above query, to pick the row with the highest value from the segment, add DESC at the very end. You would pick the segments (600, 900, 990, 1000) in this case and adapt the to_char() expression accordingly. –  Erwin Brandstetter Dec 24 '13 at 15:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.