求用sql或存储过程实现excel函数 percentile

发布网友发布时间：2022-04-21 23:58

共2个回答

热心网友时间：2022-04-14 11:22

create table tb(Class int,Salary int,Median decimal(10,2))
insert tb select 1,50,NULL
union all select 2,150,NULL
union all select 3,100,NULL
union all select 4,200,NULL
union all select 5,200,NULL
union all select 6,300,NULL
union all select 7,350,NULL
union all select 8,400,NULL
union all select 9,400,NULL
union all select 10,500,NULL
go

create function f_calc(
@K decimal(3,2),
@Class int
)returns decimal(10,2)
as
begin
declare @re decimal(10,2)
if @K between 0 and 1
begin
declare @array table(id int identity(1,1),value int)
insert @array select Salary
from tb
order by Salary
set @re=(1-((@@rowcount-1)*@K-ROUND((@@rowcount-1)*@K,0,-1)))
*(select value from @array where id=ROUND((@@rowcount-1)*@K,0,-1)+1)
+((@@rowcount-1)*@K-ROUND((@@rowcount-1)*@K,0,-1))
*(SELECT value from @array where id=ROUND((@@rowcount-1)*@K,0,-1)+2)
end
return(@re)
end
go

select dbo.f_calc(0.5,0) ,dbo.f_calc(0.75,0) from tb
go
drop table tb
drop function f_calc

热心网友时间：2022-04-14 12:40

神马》