All Forums Database
Moutusi 38 posts Joined 03/13
08 Sep 2014
Understanding Quantile

Hi,
I have created below table
Employee
 emp_id    emp_sal
 1                10
 2                20
 3                30
 4                40
 5                50
 6                60
 
 
After executing
sel emp_id, emp_sal,quantile(6,emp_sal) as A from employee;
 emp_id     emp_sal          A
 1                10                0
 2                20                1
 3                30                2
 4                40                3
 5                50                4
 6                60                5
 
sel emp_id, emp_sal,quantile(4,emp_sal) as A from employee;
  emp_id       emp_sal      A
    1                10            0
    2                20            0
   3                 30            1
   4                40             2
   5                50             2
   6               60             3
 
 
Please explain how this quantile no (A) is being calculated. Like in 2nd example why emp_sal 10,20 in same group but emp_sal 30 is different?
 

Thanks, Moutusi
nagendratrpth 25 posts Joined 03/14
09 Sep 2014

Hi Moutusi,
Actual calculation of Quantile funtion works as
(RANK() OVER (ORDER BY s) - 1) * q / COUNT(*) OVER()
please cross check the results of quantile(4,emp_sal) and (RANK() OVER (ORDER BY emp_sal) - 1) * 4 / COUNT(*) OVER() .
Thanks
Nagendra

You must sign in to leave a comment.