September 10, 2008 at 10:06 am
We need to create a function for percentile so user can use it in their select statement. They want to take the percentile number as a parameter of the function as well as another parameter for groupname and they want to be able to do any percentile between 1 and 100.
for eg, for the following distribution:
groupA 10
groupA 25
groupA 36
groupA 47
groupA 55
groupA 66
groupA 78
groupA 89
groupA 92
groupA 100
groupB 10
groupB 20
groupB 30
...
the 75 percentile for groupA is 89.75. How to get this? There is a formula:
Ly=(n+1)*y/100
where y is the percentile number and n is the number in the distribution. so for 75 percentile the calculation is:
L75=(10+1)*75/100 which = 8.25
which means when the data is arranged in ascending order, the 75 percentile is the 8th data point from the left, plus 0.25*(distance between the 8th and 9th data values) so the 75 percentile is 89+0.25*(92-89) which = 89.75
I'm working on it now also, but figure out it would help if I post the question here so maybe we can get this solved faster if someone has done sth similar before.
Thanks!
September 10, 2008 at 2:10 pm
I already figured it out. Thx!
September 10, 2008 at 2:19 pm
If possible can you post what you came up? Others may have a similiar requirement.
😎
September 10, 2008 at 2:29 pm
ok, here it is:
--the easier way: round the position number
CREATE FUNCTION [dbo].[fn_getperentile](@groupname char(20), @percentile int)
RETURNS money
AS
BEGIN
DECLARE @ret money
DECLARE @cnt bigint
declare @position bigint
select @cnt = count(*) from test2 where groupname = @groupname
SELECT @position= round((@cnt+1)*@percentile/100,0)
SELECT @ret= price from
(select price, row_number() over(order by price asc) as rownumber
from test2
where groupname=@groupname)t
where rownumber=@position
RETURN @ret
END
--include the fraction
CREATE FUNCTION [dbo].[fn_getperentile](@groupname char(20), @percentile int)
RETURNS money
AS
BEGIN
DECLARE @ret money
DECLARE @cnt bigint
declare @position bigint
declare @fraction float
select @cnt = count(*) from test2 where groupname = @groupname
SELECT @position = (@cnt+1)*@percentile/100
SELECT @fraction = (@cnt+1)*@percentile/100.0 - @position
SELECT @ret= price from
(select price, row_number() over(order by price asc) as rownumber from test2 where groupname=@groupname) t
where rownumber=@position
SELECT @ret = @ret + (price - @ret) * @fraction from
(select price, row_number() over(order by price asc) as rownumber from test2 where groupname=@groupname) t
where rownumber=@position + 1
RETURN @ret
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply