How Can I Efficiently Calculate Cumulative Sums in MySQL?

Cumulative Sum over Rows in MySQL

To determine the cumulative sum for each id, grouped by hour of day, a common approach is utilized in MySQL. Due to the absence of analytic functions in earlier versions of MySQL, two primary methods are employed: a correlated subquery or MySQL user variables.

Correlated Subquery Approach

This strategy involves a subquery that computes the cumulative total for each row, based on matching criteria. However, it can be resource-intensive for substantial datasets.

User Variable Approach

A more efficient method involves MySQL user variables and a wrapped query with an ORDER BY clause to ensure row processing sequence. The following steps outline this approach:

1. Initialize user variables to avoid interference from existing session values.
2. Embed the original query as an inline view with the ORDER BY clause.
3. Perform the cumulative sum calculation within a SELECT statement, comparing previous and current row values.
4. Assign the current row's values to user variables for the next iteration.

An example of this approach:

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
    (
      // Original query with multiple joins and unions
    ) a
    LEFT JOIN
    (
      // Unions on multiple tables
    ) b
    ON a.id = b.id
    ORDER BY 1, 2, 3
  ) c

This approach allows for efficient calculation of cumulative sums in MySQL versions prior to 8.0, when window functions were introduced.

The above is the detailed content of How Can I Efficiently Calculate Cumulative Sums in MySQL?. For more information, please follow other related articles on the PHP Chinese website!