itpub论坛回复: 分组求积

问题：

wenzai169：

从未遇到的SQL问题？

一表A结构大致如下
ID QTY
1 2
2 4
3 6
4 5
要求能够得到QTY字段的乘积2*4*6*5,

我的回答:

运用一下数学知识， 可以这样简化：

a * b * c = power(10, log(10, a) + log(10, b) + log(10, c)

这样， 楼主的问题可以用这样的单条sql来解决：

Select power(10, Sum(Log(10, qty))) From t

scott@O9I.US.ORACLE.COM> drop table t;

Table dropped.

scott@O9I.US.ORACLE.COM> create table t (id number, qty number);

Table created.

scott@O9I.US.ORACLE.COM> insert into t values(1, 2);

1 row created.

scott@O9I.US.ORACLE.COM> insert into t values(2, 4);

1 row created.

scott@O9I.US.ORACLE.COM> insert into t values(3, 6);

1 row created.

scott@O9I.US.ORACLE.COM> insert into t values(4, 5);

1 row created.

scott@O9I.US.ORACLE.COM> commit;

Commit complete.

scott@O9I.US.ORACLE.COM> Select power(10, Sum(Log(10, qty))) From t ;

POWER(10,SUM(LOG(10,QTY)))
--------------------------
240

scott@O9I.US.ORACLE.COM>

虽然举的例子没有用到分组功能， 不过加上group by,同样的方法依然可用。

(需要引用, 请注明出处: http://oldwain.itpub.net)

注意： 使用时要注意两次转换可能带来的精度问题。必要时也许加上trunc/round之类的函数更好一些

确实存在精度问题
SQL> select power(10,sum(log(10,q))) from qqq;

POWER(10,SUM(LOG(10,Q)))
------------------------
240

SQL> select power(10,sum(log(10,q)))-240 from qqq;

POWER(10,SUM(LOG(10,Q)))-240
----------------------------
-4.000E-36

解决方法是用round，不能用trunc，trunc不能四舍五入

遇到求积数据有0怎么办？log会报错的

是有这个问题。
其实不光0，有负数也会有问题。
好像没有什么太便捷的方法，最好还是事先判断一下：

* 如果有0存在，那么结果不必再计算，为0
* 如果有负数，那么计算负数的个数，从而得到结果的正负。在求积的时候参数用abs(colname)代替。将结果与正负值结合。

当然这些实现判断通过一系列的decode函数+子查询应该也可以实现，但会造成语句过于复杂。

哪位有更好的办法，欢迎指正。