MySQL same query result in multiple subqueries

Question

I am trying to optimize following query.

SELECT t3.*, 
        (SELECT SUM(t4.col_sum)
            FROM (...) t4
            WHERE t4.timestamp BETWEEN CONCAT(SUBSTR(t3.timestamp, 1, 11), "00:00:00") AND t3.timestamp)
        AS cum_sum
FROM (...) t3

Where (...) is a container for long query. It results 2 columns: timestamp and col_sum. I want to add third column to it by writing a query. That third column is a cumulative sum of col_sum.

The problem is I am putting same big query in two places (...)

Is there a way I can obtain a result and use the result in those two/multiple places (...)?

Gordon Linoff · Accepted Answer · 2015-03-26 14:28:47Z

1

One method is to use a temporary table.

Probably a more efficient method is to use variables to calculate a cumulative sum. It would be something like:

  select t.*,
         (@c := if(@t = left(t.timestamp, 11), @c + t.col_sum,
                   if(@t := left(t.timestamp, 11), 0, 0)
                  )
         ) as cumesum
  from (. . .) t cross join
       (select @t := '', @c := 0) vars
  order by t.timestamp;

The above query orders the rows by timestamp. The variable @t keeps track of the first 11 characters in the timestamp -- as I read your logic, you want to do the cumulative sum only within a group where this is constant.

The variable @c keeps track of the cumulative sum, resetting to zero when a new "first 11 characters" are encountered. The logic looks a bit complicated, but it is best to put all variable assignments in a single expression, because MySQL does not guarantee the order of evaluation of expressions.

edited Mar 26, 2015 at 14:28

answered Mar 26, 2015 at 12:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Umair A. Over a year ago

it works, can you add explanation as how it works? I have never used mysql variables before. I am looking into this now.

Collectives™ on Stack Overflow

MySQL same query result in multiple subqueries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related