Início / dba / Perguntas / 61407
Accepted
Luan Huynh
Asked: 2014-03-21 19:37:01 +0800 CST

Como somar a soma anterior, por exemplo, N = (N-1) + (N-2) + ... + 1?

  • 772

Eu tenho um nome de tabela " TABLE_A ( id integer, no integer) ".

Quero somar "não" com grupo por "id" e "soma de não" atual = "soma de não" anterior

Aqui está o meu código:

1/ Criar tabela e inserir dados:

create table table_a (id int, no int);

insert into table_a values(1, 10);
insert into table_a values(1, 20);
insert into table_a values(1, 30);
insert into table_a values(2, 100);
insert into table_a values(2, 200);
insert into table_a values(2, 300);
insert into table_a values(3, 1);
insert into table_a values(3, 2);
insert into table_a values(3, 3);
insert into table_a values(3, 3);

2/ Resultado desejado:

id | sum_of_no
--------------
1  | 60
2  | 660
3  | 669

3/ Minhas soluções (ok):

with t_report_code_temp as
(   
select id, sum(no) as t_code
from table_a
group by id 
)
select a.id, sum(b.t_code)
from t_report_code_temp a 
join t_report_code_temp b on b.id <= a.id
group by a.id
order by 1

Minha pergunta:

Você poderia me dar a melhor maneira de resolver?

postgresql join

3 respostas

  1. De Craig Ringer, ypercube.

    Aqui meus testes:

    create table table_a (id int, no int);

insert into table_a  (1)
select a, a
from generate_series(1, 1000000) a

    Pergunta de Craig Ringer 

    with t_report_code_temp(id, t_code) as
(   
select id, sum(no)
from table_a
group by id 
)
SELECT
  id,
  sum(t_code) OVER (ORDER BY id ASC)
FROM t_report_code_temp;

1.000.000 rows -> 5.5s
2.000.000 rows -> 7s   (run (1) twice)

    consulta do ypercube

    SELECT
  id,
  SUM(SUM(no)) OVER (ORDER BY id ASC) AS sum_of_no
FROM table_a
GROUP BY id ;

1.000.000 rows -> 3.7s
2.000.000 rows -> 5.5s   (run (1) twice)

    Eu vejo a magia da função de janela. Obrigado !

    • 5

  2. Isso parece um candidato para funções de janela.

    Primeiro, calcule as somas per-id e, em seguida, faça uma soma contínua ordenada por ID para obter o resultado final desejado.

    with t_report_code_temp(id, t_code) as
(   
select id, sum(no)
from table_a
group by id 
)
SELECT
  id,
  sum(t_code) OVER (ORDER BY id ASC)
FROM t_report_code_temp;
    • 4
  3. Best Answer

    Melhorando (?) Na resposta de Craig Ringer. Menos código, mas não tenho certeza se é mais legível ou mais confuso:

    SELECT
  id,
  SUM(SUM(no)) OVER (ORDER BY id ASC) AS sum_of_no
FROM table_a
GROUP BY id 
ORDER BY id ;

    Testado no SQL-Fiddle

    Você está certo em seu comentário, quando uma função de janela (ou um agregado com OVER()) tem um ORDER BY, então a janela padrão é: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWque produz um total em execução com SUM().

    O fluxo lógico de execução é:

    FROM table_a            -- get all rows of table_a

-- WHERE                -- void here

GROUP BY id             -- make groups of rows, one for each value of "id"
      -- SUM(no) AS y   -- and calculate aggregates, like SUM(no)

-- HAVING               -- void here

SELECT                  -- calculate window functions and window aggregates 
  id,                   -- and any other function used in SELECT or ORDER BY 
  SUM(y) OVER (ORDER BY id ASC) AS sum_of_no

ORDER BY id             -- order the result set
    • 3

relate perguntas