Simulate lag function in MySQL(在 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.order by
子句在这里很重要,就像它在常规中一样窗口功能.- 您可能还想对
company
使用延迟,以确保您计算的是同一company
的引号中的差异. - 你也可以用同样的方式实现行计数器
@cnt:=@cnt+1
order by
clause is important here just like it is in a regular window function.- You might also want to use lag for
company
just to be sure that you are computing difference in quotes of the samecompany
. - 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 中模拟滞后函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:在 MySQL 中模拟滞后函数
基础教程推荐
- SQL Server 2016更改对象所有者 2022-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01