Home » SQL & PL/SQL » SQL & PL/SQL » Percent_Rank Problem (Oracle SQL 10.2.0.4.0)
icon5.gif  Percent_Rank Problem [message #335298] Mon, 21 July 2008 14:47 Go to next message
SColvin
Messages: 2
Registered: July 2008
Location: Madison
Junior Member
I'm having a problem with the Percent_Rank function, wondering if anyone has some insight. SQL is:
SELECT
  A.val,
  A.rank_over,
  A.percent_rank_over,
  B.rank_4,
  B.percent_rank_4
FROM (
  SELECT
    val,
    RANK() OVER (ORDER BY val ASC) rank_over,
    PERCENT_RANK() OVER (ORDER BY val ASC) percent_rank_over
  FROM (
    SELECT            1 AS val FROM DUAL
    UNION ALL SELECT  2 FROM DUAL
    UNION ALL SELECT  3 FROM DUAL
    UNION ALL SELECT  4 FROM DUAL
    UNION ALL SELECT  5 FROM DUAL
    UNION ALL SELECT  6 FROM DUAL
    UNION ALL SELECT  7 FROM DUAL)
  GROUP BY val) A,
  (
  SELECT
    RANK(4) WITHIN GROUP (ORDER BY val ASC) AS rank_4,
    PERCENT_RANK(4) WITHIN GROUP (ORDER BY val ASC) AS percent_rank_4
  FROM (
    SELECT            1 AS val FROM DUAL
    UNION ALL SELECT  2 FROM DUAL
    UNION ALL SELECT  3 FROM DUAL
    UNION ALL SELECT  4 FROM DUAL
    UNION ALL SELECT  5 FROM DUAL
    UNION ALL SELECT  6 FROM DUAL
    UNION ALL SELECT  7 FROM DUAL)
) B
/


Results are:
    VAL  RANK_OVER PERCENT_RANK_OVER    RANK_4 PERCENT_RANK_4
------- ---------- ----------------- -------- --------------
      1          1                 0        4     .428571429
      2          2        .166666667        4     .428571429
      3          3        .333333333        4     .428571429
      4          4                .5        4     .428571429
      5          5        .666666667        4     .428571429
      6          6        .833333333        4     .428571429
      7          7                 1        4     .428571429


Can anyone tell me why PERCENT_RANK_OVER and PERCENT_RANK_4 are not the same for line 4 in the results? Am I incorrect in thinking they should be equal, did I make a mistake with my WITHIN GROUP clause? Any help appreciated.

Footnote: This is just a sample meant to show the issue, really I just want to query a dataset to rank a specific value, wondering why results are not as expected.
Re: Percent_Rank Problem [message #335311 is a reply to message #335298] Mon, 21 July 2008 16:38 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think if you re-read the link you will understand why the figures are different. To give you an idea formula is something like this.

Aggregate Function

percent_rank = (rank - 1) / (no.of_rows_in_a_group)

Analytic Function

percent_rank = (rank - 1) / (no_of_rows_in_a_partition - 1)

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions109.htm#SQLRF00686

Regards

Raj
Re: Percent_Rank Problem [message #335312 is a reply to message #335311] Mon, 21 July 2008 16:42 Go to previous message
SColvin
Messages: 2
Registered: July 2008
Location: Madison
Junior Member
Right, that confirms my observations, thanks for your help!
Previous Topic: need help in my code
Next Topic: Partitioning a table
Goto Forum:
  


Current Time: Wed Dec 07 04:58:23 CST 2016

Total time taken to generate the page: 0.19852 seconds