How to Calculate Cumulative Sum in MySQL Without Using Analytic Functions?

Cumulative Sum in MySQL without SET

In MySQL, the absence of analytic functions like cumulative sum presents a challenge when attempting to calculate running totals. However, there are two approaches to emulating this functionality.

Correlated Subqueries

This approach utilizes a subquery within the main query to obtain the cumulative sum. However, it can be resource-intensive and complex, especially when dealing with complicated joins.

User Variables

Alternatively, MySQL's user variables can be leveraged to perform control break processing. This method involves sorting the results of the original query and wrapping it in an outer query.

In the outer query:

• User variables are initialized to ensure no previous values interfere.
• The wrapped query is executed, with the addition of an ORDER BY clause for sequential row processing.

• The following logic is applied:

  • If the current id and day values match those from the previous row, add the current amount to the cumulative subtotal.
  • Otherwise, reset the cumulative subtotal to zero and assign it the current amount.

The user variables are updated with the current id and day values after the calculation.

Example Query

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, @cumtotal := 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 column order needs to be changed:

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

This approach is suitable for versions of MySQL prior to 8.0 and can provide an efficient solution for calculating cumulative sums over a set of rows.

The above is the detailed content of How to Calculate Cumulative Sum in MySQL Without Using Analytic Functions?. For more information, please follow other related articles on the PHP Chinese website!