Início / dba / Perguntas / 15019
Accepted
Leigh Riffel
Asked: 2012-03-15 10:41:57 +0800 CST

Produto de n Linhas

  • 772

A partir desses dados (supondo que o número de linhas não seja conhecido antecipadamente):

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

Eu quero o produto da coluna c1 de todas as linhas. Algo como os resultados de SUM(c1) apenas, quero cada valor multiplicado pelos outros, em vez de adicionados. Nesse caso, seria 2 * 1 * 4 * 1 = 8. 

/*
X1
--
 8
*/

Os dados podem conter números negativos e zero, que podem ser simulados usando:

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

ou

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

Sei que isso pode ser feito com função de agregação personalizada, mas estou interessado em abordagens nativas.

oracle oracle-11g-r2

2 respostas

  1. Best Answer

    Para produtos agregados suficientemente pequenos, você pode usar o velho truque de somar os logaritmos e exponenciar o resultado

    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

    Como você está usando 11.2, é um pouco mais detalhado (embora alguém possa descobrir uma versão mais simples), mas você também pode usar expressões de tabela comuns recursivas

    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. Justin me inspirou a tentar uma versão usando a MODELcláusula. Este é meu primeiro uso da cláusula, então estou aberto a qualquer crítica construtiva. Criei três CTEs para testar números negativos e zero.

    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)
);

    Aqui está uma versão detalhada da solução de log que lida com valores negativos e zero.

    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
;

    Finalmente, aqui está um CTE recursivo semelhante ao Justins, mas um pouco mais simples.

    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

relate perguntas