模拟 MySQL 中的滞后函数

2021-11-20 00:00:00 sql sliding-window 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.

相关文章