主页 / dba / 问题 / 15019
Accepted
Leigh Riffel
Asked: 2012-03-15 10:41:57 +0800 CST

n 行的乘积

  • 772

根据此数据（假设行数事先未知）：

with q1 as (select mod(ora_hash(level),5) c1 from dual connect by level <=4)
select * from q1;
/*
C1
--
 2 
 1 
 4 
 1
*/

我想要所有行中 c1 列的乘积。类似于 SUM(c1) 的结果，我只希望每个值乘以其他值而不是相加。在这种情况下，这将是 2 * 1 * 4 * 1 = 8。 

/*
X1
--
 8
*/

数据可以包含负数和零，可以使用以下方法模拟：

with q1 as (select mod(ora_hash(level),5)-1 c1 from dual connect by level <=4)
select * from q1;

或者

with q1 as (select mod(ora_hash(level),5)-3 c1 from dual connect by level <=4)
select * from q1;

我知道这可以通过自定义聚合函数来完成，但我对本机方法很感兴趣。

oracle oracle-11g-r2

2 个回答

  1. Best Answer

    对于足够小的聚合产品，您可以使用对数求和并将结果取幂的老技巧

    SQL> ed
Wrote file afiedt.buf

  1  with q1
  2    as (select mod(ora_hash(level),5) c1
  3          from dual
  4       connect by level <=4)
  5  select exp(sum(ln(c1)))
  6*   from q1
SQL> /

EXP(SUM(LN(C1)))
----------------
               8

    由于您使用的是 11.2，它有点冗长（尽管有人可能会想出一个更简单的版本），但您也可以使用递归公用表表达式

    SQL> ed
Wrote file afiedt.buf

  1  with
  2  q1 as (select level l, mod(ora_hash(level),5) c1
  3           from dual
  4        connect by level <= 4),
  5  num(n, c1, running_product)
  6  as
  7  (
  8    select 1 as N,
  9           null as c1,
 10           1 as running_product
 11      from dual
 12    union all
 13    select N+1,
 14           q1.c1,
 15           (q1.c1)*running_product
 16      from num
 17           join q1 on (num.N = q1.l)
 18  )
 19  select running_product
 20    from (select num.*,
 21                 rank() over (order by N desc) rnk
 22            from num)
 23*  where rnk = 1
SQL> /

RUNNING_PRODUCT
---------------
              8
    • 5

  2. 贾斯汀启发我尝试使用该MODEL子句的版本。这是我第一次使用该条款，所以我愿意接受任何建设性的批评。我创建了三个 CTE 来测试负数和零。

    with q1 as (select level N, mod(ora_hash(level),5) c1 from dual connect by level <=4),
     q2 as (select N, c1-1 c1 from q1),
     q3 as (select N, c1-3 c1 from q1)
select c1 from q1
model return updated rows dimension by (N) measures (c1) 
rules iterate (999) until (c1[iteration_number+1] IS NULL) (
   c1[1] = c1[1] * NVL(c1[iteration_number+2],1)
)
union all
select c1 from q2
model return updated rows dimension by (N) measures (c1) 
rules iterate (999) until (c1[iteration_number+1] IS NULL) (
   c1[1] = c1[1] * NVL(c1[iteration_number+2],1)
)
union all
select c1 from q3
model return updated rows dimension by (N) measures (c1) 
rules iterate (999) until (c1[iteration_number+1] IS NULL) (
   c1[1] = c1[1] * NVL(c1[iteration_number+2],1)
);

    这是处理负值和零值的日志解决方案的充实版本。

    with q1 as (select mod(ora_hash(level),5)  c1 from dual connect by level <=4),
     q2 as (select c1-1 c1 from q1),
     q3 as (select c1-3 c1 from q1)
select 'q1',  case when sum(case when c1 < 0 then -1 
   when c1 > 0 then 1 else 0 end) >= 0 then 1 else -1 end *
   decode(min(abs(c1)),0,0,Round(exp(sum(ln(abs(nullif(c1,0))))))) x1  
   from q1
union all   
select 'q2', case when sum(case when c1 < 0 then -1 
   when c1 > 0 then 1 else 0 end) >= 0 then 1 else -1 end *
   decode(min(abs(c1)),0,0,Round(exp(sum(ln(abs(nullif(c1,0))))))) x1  
   from q2
union all   
select 'q3', case when sum(case when c1 < 0 then -1 
   when c1 > 0 then 1 else 0 end) >= 0 then 1 else -1 end *
   decode(min(abs(c1)),0,0,Round(exp(sum(ln(abs(nullif(c1,0))))))) x1  
   from q3
;

    最后，这是一个类似于 Justins 的递归 CTE，但稍微简单一些。

    with q1 as (select level N, mod(ora_hash(level),5) c1 from dual connect by level <=4),
     q2 as (select N, c1-1 c1 from q1),
     q3 as (select N, c1-3 c1 from q1),
     x1 (N, Product) as (
        select 1 N, 1 Product from dual
        union all
        select x1.N+1 N, q1.c1 * x1.Product Product from x1 
        join q1 on (x1.N = q1.N)
        ),
     x2 (N, Product) as (
        select 1 N, 1 Product from dual
        union all
        select x2.N+1 N, q2.c1 * x2.Product Product from x2
        join q2 on (x2.N = q2.N)
        ),
     x3 (N, Product) as (
        select 1 N, 1 Product from dual
        union all
        select x3.N+1 N, q3.c1 * x3.Product Product from x3
        join q3 on (x3.N = q3.N)
        )
select Product from (select N, Product, max(N) OVER () MaxN from x1) where N = MaxN
union all
select Product from (select N, Product, max(N) OVER () MaxN from x2) where N = MaxN
union all
select Product from (select N, Product, max(N) OVER () MaxN from x3) where N = MaxN;
    • 2

相关问题