Soma cumulativa sobre um conjunto de linhas no mysql

Eu tenho uma consulta complexa (contendo várias associações, uniões) que retorna um conjunto de linhas contendo id, dia, hora, quantidade. A saída da consulta é assim:

id day hr amount 1 1 1 10 1 1 2 25 1 1 3 30 1 2 1 10 1 2 2 40 1 2 2 30 2 1 1 10 2 1 2 15 2 1 3 30 2 2 1 10 2 2 2 20 2 2 2 30 

Preciso encontrar um total acumulativo para cada id, para cada hora do dia. A saída deve ser assim:

 id day hr amount cumulative total 1 1 1 10 10 1 1 2 25 35 1 1 3 30 65 1 2 1 10 10 1 2 2 40 50 1 2 2 30 80 2 1 1 10 10 2 1 2 15 25 2 1 3 30 55 2 2 1 10 10 2 2 2 20 30 2 2 2 30 60 

Minha consulta inicial que produz a primeira saída é assim:

 select id, day, hr, amount from ( //multiple joins on multiple tables)a left join (//unions on multiple tables)b on a.id=b.id; 

O que é consulta SQL para obter a sum cumulativa, conforme descrito na segunda saída? SET não deve ser usado na solução.

Obrigado.

O MySQL não fornece o tipo de function analítica que você usaria para obter uma “sum cumulativa” em execução, como as funções analíticas disponíveis em outro DBMS (como Oracle ou SQL Server).

Mas, é possível emular algumas funções analíticas, usando o MySQL.

Existem (pelo menos) duas abordagens viáveis:

Uma é usar uma subconsulta correlacionada para obter o subtotal. Essa abordagem pode ser dispendiosa em conjuntos grandes e complicada se os predicados na consulta externa forem complicados. Isso realmente depende de quão complicado é “múltiplas junções em várias tabelas”. (Infelizmente, o MySQL também não suporta CTEs.)

A outra abordagem é fazer uso das variables ​​de usuário do MySQL, para fazer algum processamento de quebra de controle. O “truque” aqui é os resultados de sua consulta classificada (usando um ORDER BY) e, em seguida, envolvendo sua consulta em outra consulta.

Vou dar um exemplo da última abordagem.

Devido à ordem em que o MySQL realiza operações, a coluna cumulative_total precisa ser calculada antes que o valor do id e do day da linha atual sejam salvos em variables ​​do usuário. É mais fácil colocar essa coluna primeiro.

A visualização em linha com alias como i (na consulta abaixo) está lá apenas para inicializar as variables ​​do usuário, apenas no caso de elas já estarem configuradas na session. Se aqueles já tiverem valores atribuídos, queremos ignorar seus valores atuais, e a maneira mais fácil de fazer isso é inicializá-los.

Sua consulta original é colocada entre parênteses e recebe um alias, c no exemplo abaixo. A única alteração na sua consulta original é a adição de uma cláusula ORDER BY, para que possamos ter certeza de que processamos as linhas da consulta em sequência.

A seleção externa verifica se o valor de id e day da linha atual “corresponde” à linha anterior. Se o fizerem, adicionamos o amount da linha atual ao subtotal cumulativo. Se eles não corresponderem, então redefinimos o subtotal cumulativo para zero e adicionamos a quantia da linha atual (ou, de maneira mais simples, basta atribuir a quantia da linha atual).

Depois de termos feito o cálculo do total cumulativo, salvamos os valores id e day da linha atual em variables ​​de usuário, para que estejam disponíveis quando processarmos a próxima linha.

Por exemplo:

 SELECT IF(@prev_id = c.id AND @prev_day = c.day ,@cumtotal := @cumtotal + c.amount ,@cumtotal := c.amount) AS cumulative_total , @prev_id := c.id AS `id` , @prev_day := c.day AS `day` , c.hr , c.amount AS `amount' FROM ( SELECT @prev_id := NULL , @prev_day := NULL , @subtotal := 0 ) i JOIN ( select id, day, hr, amount from ( //multiple joins on multiple tables)a left join (//unions on multiple tables)b on a.id=b.id ORDER BY 1,2,3 ) c 

Se for necessário retornar as colunas em uma ordem diferente, com o total cumulativo como a última coluna, uma opção é agrupar toda a instrução em um conjunto de parens e usar essa consulta como uma exibição sequencial:

 SELECT d.id , d.day , d.hr , d.amount , d.cumulative_total FROM ( // query from above ) d 

aqui vai, aqui está o seu total culminante …

 select f1.id, f1.day, f1.hr, f1.amount, sum(f2.amount) as culminative_total from foo f1 inner join foo f2 on (f1.day = f2.day and f1.id=f2.id) where f2.hr <= f1.hr group by f1.id, f1.day, f1.hour; 
Intereting Posts