上次的算法弄错了,对不起,
我觉得应该是
select (max(Salary)-min(Salary))*你的PERCENTILE参数值 from 表 group by class 你试试吧,我觉得你的意思是这样吧 :)
我觉得应该是
select (max(Salary)-min(Salary))*你的PERCENTILE参数值 from 表 group by class 你试试吧,我觉得你的意思是这样吧 :)
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
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
@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 行)
--*/