在MySQL中模拟延迟功能 [英] Simulate lag function in MySQL

问题描述

| time                | company | quote |
+---------------------+---------+-------+
| 0000-00-00 00:00:00 | GOOGLE  |    40 |
| 2012-07-02 21:28:05 | GOOGLE  |    60 |
| 2012-07-02 21:28:51 | SAP     |    60 |
| 2012-07-02 21:29:05 | SAP     |    20 |

如何在MySQL中对该表进行处理以显示引号中的差异，例如:

How do I do a lag on this table in MySQL to print the difference in quotes, for example:

GOOGLE | 20
SAP    | 40  

推荐答案

这是我最喜欢的MySQL hack.

This is my favorite MySQL hack.

这是模拟滞后函数的方式:

This is how you emulate the lag function:

SET @quot=-1;
select time,company,@quot lag_quote, @quot:=quote curr_quote
  from stocks order by company,time;

• lag_quote保留上一行报价的值.对于第一行，@ quot是-1.
• curr_quote保存当前行的报价.
• lag_quote holds the value of previous row's quote. For the first row @quot is -1.
• curr_quote holds the value of current row's quote.

注意:

就像在常规中一样，
1. order by子句在这里很重要 窗口功能.
2. 您可能还想对company使用滞后，只是为了确保您计算的是同一company的引号中的差异.
3. 您还可以采用与@cnt:=@cnt+1
相同的方式来实现行计数器
1. order by clause is important here just like it is in a regular window function.
2. You might also want to use lag for company just to be sure that you are computing difference in quotes of the same company.
3. You can also implement row counters in the same way @cnt:=@cnt+1

与该方案相比，与使用聚合函数，存储过程或在应用程序服务器中处理数据等其他方法相比，该方案在计算上非常精益.

The nice thing about this scheme is that is computationally very lean compared to some other approaches like using aggregate functions, stored procedures or processing data in application server.

现在开始以您提到的格式获取结果的问题:

Now coming to your question of getting result in the format you mentioned:

SET @quot=0,@latest=0,company='';
select B.* from (
select A.time,A.change,IF(@comp<>A.company,1,0) as LATEST,@comp:=A.company as company from (
select time,company,quote-@quot as change, @quot:=quote curr_quote
from stocks order by company,time) A
order by company,time desc) B where B.LATEST=1;

嵌套不是相互关联的，因此(在计算上)不像在语法上那么糟糕:)

The nesting is not co-related so not as bad (computationally) as it looks (syntactically) :)

如果您需要任何帮助，请告诉我.

Let me know if you need any help with this.

这篇关于在MySQL中模拟延迟功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！