Databases
StackOverflow2013
Postsdbo
POSubtracting the value from the last row using variable assignment in MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSubtracting the value from the last row using variable assignment in MySQL
    primarykey
    data
    text
    <p>According to the MySQL documentation:</p> <blockquote> <p>As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.</p> <p><a href="http://dev.mysql.com/doc/refman/5.6/en/user-variables.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.6/en/user-variables.html</a></p> </blockquote> <p>However, in the book <em>High Perfomance MySQL</em> there are a couple of examples of using this tactic to improve query performance anyway.</p> <p>Is the following an anti-pattern and if so is there a better way to write the query while maintaining good performance?</p> <pre><code>set @last = null; select tick, count-@last as delta, @last:=count from measurement; </code></pre> <p>For clarification, my goal is to find the difference between this row and the last. My table has a primary key on tick which is a datetime column.</p> <p>Update:</p> <p>After trying Shlomi's suggestion, I have reverted back to my original query. It turns out that using a case statement with aggregate functions produces unexpected behavior. See for example:</p> <pre><code>case when (@delta := (max(measurement.count) - @lastCount)) AND 0 then null when (@lastCount := measurement.count) AND 0 then null else @delta end </code></pre> <p>It appears that mysql evaluates the expressions that don't contain aggregate functions on a first pass through the results, and then evaluates the aggregate expressions on a second (grouping) pass. It appears to evaluate the case expression during or after that second pass and use the precalculated values from the first pass in that evaluation. The result is that the third line @delta is always the initial value of @delta (because assignment didn't happen until the grouping pass). I attempted to incorporate a group function into the line with @delta but couldn't get it to behave as expected. So I ultimately when back to my original query which didn't have this problem.</p> <p>I would still love to hear any more suggestions about how to better handle a query like this.</p> <p>Update 2:</p> <p>Sorry for the lack of response on this question, I didn't have a chance to investigate further until now.</p> <p>Using Shlomi's solution it looks like I had a problem because I was using a group by function when I read my @last variable but not when I set it. My code looked something like this:</p> <pre><code>CASE WHEN (@delta := count - @last) IS NULL THEN NULL WHEN (@last:= count ) IS NULL THEN NULL ELSE (CASE WHEN cumulative THEN @delta ELSE avg(count) END) END AS delta </code></pre> <p>MySQL appears to process expressions that don't contain aggregate functions in a first pass and ones that do in a second pass. The strange thing in the code above is that even when <code>cumulative</code> evaluates to true MySQL must see the <code>AVG</code> aggregate function in the <code>ELSE</code> clause and decides to evaluate the whole inner <code>CASE</code> expression in the second pass. Since <code>@delta</code> is set in an expression without an aggregate function it seems to be getting set on the first pass and by the time the second pass happens MySQL is done evaluating the lines that set <code>@delta</code> and <code>@last</code>.</p> <p>Ultimately I seem to have found a fix by including aggregate functions in the first expressions as well. Something like this:</p> <pre><code>CASE WHEN (@delta := max(count) - @last) IS NULL THEN NULL WHEN (@last:= max(count) ) IS NULL THEN NULL ELSE (CASE WHEN cumulative THEN @delta ELSE avg(count) END) END AS delta </code></pre> <p>My understanding of what MySQL is doing is purely based on testing and conjecture since I didn't read the source code, but hopefully this will help others who might run into similar problems.</p> <p>I am going to accept Shlomi's answer because it really is a good solution. Just be careful how you use aggregate functions.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCode Commander
    1. USCode Commander
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload