Oracle 运行乘法

我们都知道如何生成运行总计列

SELECT id, date, value, sum(value) 
 OVER (partition by id order by date) total 
 FROM dual 
 ORDER BY date

这会给我类似的东西

ID      DATE    VALUE    TOTAL
1       1/1/14  0.001    0.001
2       2/1/14  0.003    0.004
3       3/1/14  0.002    0.006

现在我想生成一个“运行乘法”，生成 0.001 * 0.004 * 0.006。我知道如果我只想要整个乘法的值可以通过类似的方法来完成

SELECT exp(sum(ln(value))) from dual

但这个方法不适用于oracle中的分区。也许有人有想法？

Edit

期望的结果是（不要介意数字，它们只是虚拟的，它们不会遇到溢出）。

ID      DATE    VALUE    TOTAL
1       1/1/14  0.001    0.001
2       2/1/14  0.003    0,000004
3       3/1/14  0.002    0,000000024

The exp(sum(ln()))只要您添加分析，该方法就有效sum()部分，不适合exp()。这将为您提供原始值的乘积：

WITH t AS (
  SELECT 1 AS id, DATE '2014-01-01' AS dat, 0.001 AS value FROM dual
  UNION ALL SELECT 2, DATE '2014-01-02', 0.003 FROM dual
  UNION ALL SELECT 3, DATE '2014-01-03', 0.002 FROM dual
)
SELECT id, dat, value, EXP(SUM(LN(value))
  OVER (PARTITION BY null ORDER BY dat))
  AS total 
FROM t
ORDER BY dat;

        ID DAT            VALUE      TOTAL
---------- --------- ---------- ----------
         1 01-JAN-14       .001       .001 
         2 02-JAN-14       .003    .000003 
         3 03-JAN-14       .002 .000000006 

这将为您提供运行总计的乘积：

WITH t AS (
  SELECT 1 AS id, DATE '2014-01-01' AS dat, 0.001 AS value FROM dual
  UNION ALL SELECT 2, DATE '2014-01-02', 0.003 FROM dual
  UNION ALL SELECT 3, DATE '2014-01-03', 0.002 FROM dual
),
u AS (
  SELECT id, dat, value, SUM(value) 
    OVER (PARTITION BY null ORDER BY dat) AS total 
  FROM t
)
SELECT id, dat, value, total, EXP(SUM(LN(total))
  OVER (PARTITION BY null ORDER BY dat)) AS product
FROM u
ORDER BY dat;

        ID DAT            VALUE      TOTAL    PRODUCT
---------- --------- ---------- ---------- ----------
         1 01-JAN-14       .001       .001       .001 
         2 02-JAN-14       .003       .004    .000004 
         3 03-JAN-14       .002       .006 .000000024 

显然使用你自己的表而不是 CTE；如果您尝试使用 ID 获取多个值的乘积/总和，请将其更改为partition by id. Using null是让这个与您的示例数据一起工作。