求一SQL通用中位数（需自编制）函数，同EXECL的PERCENTILE函数，高手请进！！！

[wkexuan]

PERCENTILE({1,2,3,4},0.3) 等于 1.9

祥情请见：http://community.csdn.net/Expert/topic/2982/2982020.xml?temp=.9180872　但上次结贴的时候搞错了，在EXECL中验证过Communism(星星)的算法逻辑是不正确的。

所以此次希望哪位大侠能够给出正确的算法逻辑并给出SQL代码。

谢谢！

[zjcxc]

还以为是星星的算法,结果不是,还是没搞清楚算法.

[swich]

up

[rea1gz]

这个需要查表的
很难实现，而且数学都还给老师了

[wkexuan]

怎么办？发动群众的智慧！！！

[zjcxc]

关键是不知道这个算法是怎么的,有人能说说原理也好啊.

[otoexpert]

我把计算过程描述给大家，具体的函数就请高手加工吧
CREATE TABLE #test
(id int identity(1,1) not null,
value int not null
)
insert into #test
select value from test --array, must be asc
DECLARE @se AS money
DECLARE @i AS int
DECLARE @j AS money
SET @se = 0.3 --k
SET @i = (SELECT ROUND((COUNT(*)-1)*@se,0,-1) FROM #test)
SET @j = (SELECT (COUNT(*)-1)*@se-ROUND((COUNT(*)-1)*@se,0,-1) FROM #test)
SELECT (1-@j)*(SELECT value from #test where id = @i+1)+@j*(SELECT value from #test where id = @i+2)--return value
DROP TABLE #test

[otoexpert]

稍作改动，这样更容易看出算法：
DECLARE @se AS money --k
DECLARE @arraycount AS int
DECLARE @i AS int
DECLARE @j AS money
CREATE TABLE #test
(id int identity(1,1) not null,
value int not null
)
insert into #test
select value from test --array, must be asc

SET @se = 0.3
SET @arraycount = (SELECT COUNT(*) FROM #test)
SET @i = ROUND((@arraycount-1)*@se,0,-1)
SET @j = (@arraycount-1)*@se-ROUND((@arraycount -1)*@se,0,-1)
SELECT (1-@j)*(SELECT value from #test where id = @i+1)+@j*(SELECT value from #test where id = @i+2)--return value
DROP TABLE #test

[zjcxc]

--借用楼上的计算公式来写计算函数

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 表
where Class=@Class 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

--测试

--测试数据
create table 表(Class int,Salary int,Median decimal(10,2))
insert 表 select 1,1000,NULL
union all select 1,2000,NULL
union all select 1,3000,NULL
union all select 1,4000,NULL
union all select 2,5000,NULL
union all select 2,6000,NULL
union all select 2,7000,NULL
union all select 2,8000,NULL
union all select 2,9000,NULL
union all select 2,10000,NULL
go

--调用函数进行更新
update 表 set Median=dbo.f_calc(0.3,Class)
go

--显示更新结果
select * from 表
go

--删除测试
drop table 表
drop function f_calc

/*--测试结果

Class Salary Median
----------- ----------- ------------
1 1000 1900.00
1 2000 1900.00
1 3000 1900.00
1 4000 1900.00
2 5000 6500.00
2 6000 6500.00
2 7000 6500.00
2 8000 6500.00
2 9000 6500.00
2 10000 6500.00

（所影响的行数为 10 行）
--*/

[Communism]

上次的算法弄错了，对不起，
我觉得应该是
select (max(Salary)-min(Salary))*你的PERCENTILE参数值 from 表 group by class

你试试吧，我觉得你的意思是这样吧 ：）

[zjcxc]

楼上,结果不对,你在excel中测试一下就知道了.