Cumulative sum over a set of rows in mysql

Question

I have a complex query(containing multiple joins, unions) that returns a set of rows containing id, day, hr, amount. The output of the query looks like this:

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  

I need to find cumulative total for each id, for every hour of the day. The output should be like this:

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

My initial query that produces the first output looks like this:

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

What's sql query to get the cumulative sum as described in the second output? SET should not be used in the solution.

Thanks.

Answer 1

UPDATE

MySQL 8.0 introduces "window functions", functionality equivalent to SQL Server "window functions" (with partitioning and ordering provided by Transact-SQL OVER syntax), and Oracle "analytic functions".

MySQL Reference Manual 12.21 Window Functions https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

The answer provided here is an approach for MySQL versions prior to 8.0.

---

ORIGINAL ANSWER

MySQL doesn't provide the type analytic function you would use to get a running "cumulative sum", like the analytic functions available in other DBMS (like Oracle or SQL Server.)

But, it is possible to emulate some analytic functions, using MySQL.

There are (at least) two workable approaches:

One is to use a correlated subquery to get the subtotal. This approach can be expensive on large sets, and complicated if the predicates on the outer query are complicated. It really depends on how complicated that "multiple joins on multiple tables" is. (Unfortunately, MySQL also does not not support CTEs either.)

The other approach is to make use of MySQL user variables, to do some control break processing. The "trick" here is to the results from your query sorted (using an ORDER BY) and then wrapping your query in another query.

I'll give an example of the latter approach.

Because of the order that MySQL performs operations, the cumulative_total column needs to be computed before the value from id and day from the current row are saved into user variables. It's just easiest to put this column first.

The inline view aliased as i (in the query below) is just there to initialize the user variables, just in case these are already set in the session. If those already have values assigned, we want to ignore their current values, and the easiest way to do that is to initialize them.

Your original query gets wrapped in parenthesis, and is given an alias, c in the example below. The only change to your original query is the addition of an ORDER BY clause, so we can be sure that we process the rows from the query in sequence.

The outer select checks whether the id and day value from the current row "match" the previous row. If they do, we add the amount from the current row to the cumulative subtotal. If they don't match, then we reset the the cumulative subtotal to zero, and add the amount from the current row (or, more simply, just assign the amount from the current row).

After we have done the computation of the cumulative total, we save the id and day values from the current row into user variables, so they are available when we process the next row.

For example:

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

If it's necessary to return the columns in a different order, with cumulative total as the last column, then one option is to wrap that whole statement in a set of parens, and use that query as an inline view:

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