在 MySQL 中模拟滞后函数

2022-01-30 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.

相关文章