Geek Answers Handbook

SQL Unexpected result of calling PERCENTILE_CONT function

Actually, I have an understanding of how PERCENTILE_CONT works, but I get a different result by calculating percentiles manually and calling the function. Here is the dataset:

305.7884804
350
373.3728865
384.2094838
410.8603441
414.9842786
455.3545205
550

To calculate 25%, summarize 350and 373.3728865, and then divide it by 2. And the result 361.68644325.

To calculate 50%, summarize 384.2094838and 410.8603441, and then divide it by 2. And the result 397.53491395.

To calculate 75%, summarize 414.9842786and 455.3545205, and then divide it by 2. And the result 435.16939955.

However, when I run this sql:

select 
percentile_cont(0.25) within group(order by YEAR_2_FTE ASC), 
percentile_cont(0.5) within group(order by YEAR_2_FTE ASC), 
percentile_cont(0.75) within group(order by YEAR_2_FTE ASC) from sr_database 
where firm_id=999;

I get this result:

25%: 367.529664875
50%: 397.53491395
75%: 425.076839075

?

+4
1

4- 5- 50- . 2- 3- 25- ( 75-) . :

enter image description here

8 4 , , 4 . 25- 75- 3- 6- .

Oracle , :

PERCENTILE_CONT . (P) (N) , . (RN) RN = (1+ (P * (N-1)). CRN = (RN) FRN = FLOOR (RN).

25- P = 0.25 N = 8 :

RN = (1+ 0.25*(8-1))
   = 2.75

So CRN = 3 FRN = 2

:

:

(CRN = FRN = RN),

(value of expression from row at RN)


(CRN - RN) * (value of expression for row at FRN) +

(RN - FRN) * (value of expression for row at CRN)

, "..." i.e:

Result = (3 - 2.75) * 350 + (2.75 - 2) * 373.3728856
       = 367.5296642

75- :

 RN = (1+ 0.75*(8-1))
    = 6.25
CRN = 7
FRN = 6
Result = (7 - 6.25) * 414.9842786 + (6.25 - 6) * 455.3545205
       = 425.076839075
+4

More articles:


All Articles