sql中如何计算一列的几何平均数呢？

[cacu]

如题

如果输算术平均数是 select sum(col)/count(clo) from table


如果是几何平均数的话，能不能提供个自建函数呢？

[叶子]

declare @T table (col int)
insert into @T
select 3 union all
select 4 union all
select 5 union all
select 6

select 
	算术平均数=cast(sum(col)*1./count(col) as decimal(18,4)) ,
	几何平均数=cast(power(exp(sum(log(col))),1.0/count(col)) as decimal(18,4))
from @T

/*
算术平均数                                   几何平均数
--------------------------------------- ---------------------------------------
4.5000                                  4.3559
*/

[lycorisraya]

create table #test(id int identity(1,1),num int) 
insert into #test values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
select POWER(10.,SUM(LOG10(num))) as 聚合乘积,SQRT(POWER(10.,SUM(LOG10(num)))) as 几何平均数 from #test

[lycorisraya]

如果要提供自建函数的话，貌似需要写一个用户定义聚合函数（UDA），有点麻烦！还是等高手解决！

[开着拖拉机泡妞]

if OBJECT_ID('test') is not null
drop table test
go
create table test(
id int identity,
value int
)
go
insert test(value)
select 4 union all
select 1 union all
select 6 union all
select 2 union all
select 9 union all
select 4 union all
select 7 union all
select 5 union all
select 3 union all
select 2
go


declare @count numeric(18,2)
select @count=COUNT(1) from test
select power(cast(power(10*1.0,sum(log10(value))) as numeric(18,2)),
1/@count) as 几何平均数 from test
/*
几何平均数
---------------------
3.60
*/