在MySQL中使用变量赋值从最后一行中减去该值 [英] Subtracting the value from the last row using variable assignment in MySQL

问题描述

根据MySQL文档:

作为一般规则，永远不要为用户变量分配值，并且不要在同一条语句中读取该值.你可能会得到您期望的结果，但这不能保证.

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.

http://dev.mysql.com/doc/refman/5.6/en/user-variables.html

但是，在高性能MySQL 一书中，有两个使用该策略来提高查询性能的示例.

However, in the book High Perfomance MySQL there are a couple of examples of using this tactic to improve query performance anyway.

以下是反模式吗?如果是的话，是否有更好的方法在保持良好性能的同时编写查询?

Is the following an anti-pattern and if so is there a better way to write the query while maintaining good performance?

set @last = null;
select tick, count-@last as delta, @last:=count from measurement;

为澄清起见，我的目标是找到此行与最后一行之间的区别.我的表在tick上有一个主键，它是一个datetime列.

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.

更新:

尝试了Shlomi的建议后，我又恢复了原来的查询.事实证明，将case语句与聚合函数一起使用会产生意外的行为.参见例如:

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:

case when (@delta := (max(measurement.count) - @lastCount)) AND 0 then null
when (@lastCount := measurement.count) AND 0 then null
else @delta end

看来，mysql在第一次遍历结果时会评估不包含聚合函数的表达式，然后在第二遍(分组)遍历会评估聚合表达式.似乎是在第二遍过程中或之后评估案例表达式，并在该评估中使用第一遍过程中的预计算值.结果是第三行@delta始终是@delta的初始值(因为直到分组通过才进行赋值).我试图将组函数并入@delta行中，但无法使其表现出预期的效果.因此，最终我回到了没有此问题的原始查询.

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.

我仍然很想听听有关如何更好地处理这样的查询的更多建议.

I would still love to hear any more suggestions about how to better handle a query like this.

更新2:

很抱歉，对于这个问题没有回答，直到现在我还没有机会进一步调查.

Sorry for the lack of response on this question, I didn't have a chance to investigate further until now.

使用Shlomi的解决方案似乎有问题，因为在读取@last变量时我正在使用group by function，而在设置它时却没有.我的代码看起来像这样:

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:

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

MySQL似乎在第一遍处理不包含聚合函数的表达式，而在第二遍处理那些表达式.上面代码中的奇怪之处在于，即使 cumulative 评估为true MySQL，也必须在 ELSE 子句中看到 AVG 聚合函数，并决定在第二遍中评估整个内部 CASE 表达式.由于 @delta 是在没有聚合函数的表达式中设置的，因此它似乎是在第一次通过时设置的，而在第二次通过时，MySQL已完成对设置 @delta的行的评估和 @last .

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 cumulative evaluates to true MySQL must see the AVG aggregate function in the ELSE clause and decides to evaluate the whole inner CASE expression in the second pass. Since @delta 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 @delta and @last.

最终，我似乎也通过在第一个表达式中包含聚合函数找到了解决方法.像这样:

Ultimately I seem to have found a fix by including aggregate functions in the first expressions as well. Something like this:

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

由于我没有阅读源代码，所以我对MySQL所做的事情的理解纯粹是基于测试和推测，但是希望这会对其他可能遇到类似问题的人有所帮助.

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.

我将接受Shlomi的回答，因为这确实是一个很好的解决方案.请小心使用聚合函数的方法.

I am going to accept Shlomi's answer because it really is a good solution. Just be careful how you use aggregate functions.

推荐答案

我已经对此问题进行了深入研究，并对上述内容进行了一些改进.

I've researched this issue in depth, and wrote a few improvements on the above.

我在这篇文章中提供了解决方案，它使用可以预期其顺序的功能.另请考虑我的演讲去年.

I offer a solution in this post, which uses functions whose order can be expected. Also consider my talk last year.

诸如 CASE 之类的结构和诸如 COALESCE 之类的功能具有已知的基础行为(至少在更改之前，对吧?).

Constructs such as CASE and functions such as COALESCE have known underlying behavior (at least until this is changed, right?).

例如， CASE 子句按定义顺序逐个检查 WHEN 条件.

For example, a CASE clause inspects the WHEN conditions one by one, by order of definition.

考虑对原始查询进行重写:

Consider a rewrite of the original query:

select 
  tick,
  CASE
    WHEN (@delta := count-@last) IS NULL THEN NULL
    WHEN (@last:=count ) IS NULL THEN NULL
    ELSE @delta
  END AS delta
from 
  measurement,
  (select @last := 0) s_init
;

CASE 子句具有三个 WHEN 条件.它按顺序执行它们，直到遇到成功的第一个.我已经写了它们，使得前两个将总是失败.因此，它执行第一个，然后转为执行第二个，然后最后返回第三个.总是.

The CASE clause has three WHEN conditions. It executes them by order until it meets the first that succeeds. I've written them such that the first two will always fail. It therefore executes the first, then turns to execute the second, then finally returns the third. Always.

因此，我克服了期望评估顺序的问题，这是一个真实的问题，当您开始添加更复杂的子句(例如 GROUP BY ， DISTINCT ， ORDER BY 等.

I thus overcome the problem of expecting order of evaluation, which is a real and true problem, mostly evident when you start adding more complex clauses such as GROUP BY, DISTINCT, ORDER BY and such.

最后一点，我的解决方案与您在结果集的第一行中的解决方案不同-对于您的解决方案，它返回 NULL ，而对于我的解决方案，其返回值介于 0 和 count .如果我使用 NULL ，我将需要以其他方式更改 WHEN 条件-确保在 NULL上它们会失败值.

As a final note, my solution differs from yours in the first row on the result set -- with yours' it returns NULL, with mine it returns the delta between 0 and count. Had I used NULL I would have needed to change the WHEN conditions in some other way -- making sure they would fail on NULL values.

这篇关于在MySQL中使用变量赋值从最后一行中减去该值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！