Understanding Quantile

Database
Enthusiast

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?

Tags (2)
1 REPLY
Enthusiast

Re: Understanding Quantile

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