更新时间:2023-02-10 15:39:03
结合使用ROUND
、EXP
、SUM
和日志
SELECT ROUND(EXP(SUM(LOG([Col A]))),1)
FROM yourtable
SQL 小提琴:http://sqlfiddle.com/#!3/d43c8/2/0
解释
LOG
返回 col a ex 的对数.LOG([Col A])
返回
LOG
returns the logarithm of col a ex. LOG([Col A])
which returns
0
0.6931471805599453
1.0986122886681098
1.3862943611198906
然后您使用 SUM
将它们全部加在一起 SUM(LOG([Col A]))
返回
Then you use SUM
to Add them all together SUM(LOG([Col A]))
which returns
3.1780538303479453
然后使用返回的 EXP(SUM(LOG(['3.1780538303479453'])))
计算该结果的指数
Then the exponential of that result is calculated using EXP(SUM(LOG(['3.1780538303479453'])))
which returns
23.999999999999993
然后最后使用 ROUND
ROUND(EXP(SUM(LOG('23.9999999999999993'))),1)
进行四舍五入得到 24
Then this is finally rounded using ROUND
ROUND(EXP(SUM(LOG('23.999999999999993'))),1)
to get 24
简单的解决方法:
发生了无效的浮点运算.
An invalid floating point operation occurred.
当您的数据中有 0
时
SELECT ROUND(EXP(SUM(LOG([Col A]))),1)
FROM yourtable
WHERE [Col A] != 0
如果你只有 0
那么上面会给出 NULL
的结果.
If you only have 0
Then the above would give a result of NULL
.
当您的数据集中有负数时.
When you have negative numbers in your data set.
SELECT (ROUND(exp(SUM(log(CASE WHEN[Col A]<0 THEN [Col A]*-1 ELSE [Col A] END))),1)) *
(CASE (SUM(CASE WHEN [Col A] < 0 THEN 1 ELSE 0 END) %2) WHEN 1 THEN -1 WHEN 0 THEN 1 END) AS [Col A Multi]
FROM yourtable
示例输入:
1
2
3
-4
输出:
Col A Multi
-24