在 MySQL 中模拟滞后函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11303532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Simulate lag function in MySQL
提问by javanx
| 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 |
How do I do a lag on this table in MySQL to print the difference in quotes, for example:
如何在 MySQL 中对这个表进行延迟以打印引号中的差异，例如：
GOOGLE | 20
SAP | 40
回答by Dojo
This is my favorite MySQL hack.
这是我最喜欢的 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_quoteholds the value of previous row's quote. For the first row @quot is -1.curr_quoteholds the value of current row's quote.
lag_quote保存前一行报价的值。第一行@quot 是-1。curr_quote保存当前行报价的值。
Notes:
笔记：
order byclause is important here just like it is in a regular window function.- You might also want to use lag for
companyjust 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
order by子句在这里很重要，就像在常规窗口函数中一样。- 您可能还想使用滞后
company来确保您计算的是相同company. - 你也可以用同样的方式实现行计数器
@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.
与使用聚合函数、存储过程或在应用程序服务器中处理数据等其他一些方法相比，这种方案的好处在于计算量非常精简。
EDIT:
编辑：
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.
如果您需要任何帮助，请告诉我。
回答by Lukasz Szozda
From MySQL 8.0 and above there is no need to simulate LAG. It is natively supported,
从 MySQL 8.0 及更高版本开始，无需模拟LAG. 它是本机支持的，
Returns the value of expr from the row that lags (precedes) the current row by N rows within its partition. If there is no such row, the return value is default. For example, if N is 3, the return value is default for the first two rows. If N or default are missing, the defaults are 1 and NULL, respectively.
窗口函数：
从分区内比当前行滞后（先于）N 行的行返回 expr 的值。如果没有这样的行，则返回值为默认值。例如，如果 N 为 3，则前两行的返回值是默认值。如果缺少 N 或默认值，则默认值分别为 1 和 NULL。
SELECT
company,
quote,
LAG(quote) OVER(PARTITION BY company ORDER BY time) AS prev_quote
FROM tab;
回答by vyegorov
To achieve the desired result, first you need to find the last and next to last timestamps for each company. It is quite simple with the following query:
要获得所需的结果，首先您需要找到每个公司的最后一个和倒数第二个时间戳。使用以下查询非常简单：
SELECT c.company, c.mts, max(l.ts) AS lts
FROM (SELECT company, max(ts) AS mts FROM cq GROUP BY company) AS c
LEFT JOIN cq l
ON c.company = l.company AND c.mts > l.ts
GROUP BY c.company, c.mts;
Now you have to join this subquery with the original table to get the desired results:
现在您必须将此子查询与原始表连接以获得所需的结果：
SELECT c.company, l.quote, coalesce(l1.quote, 0),
(l.quote - coalesce(l1.quote, 0)) AS result
FROM (SELECT c.company, c.mts, max(l.ts) AS lts
FROM (SELECT company, max(ts) AS mts FROM cq GROUP BY company) AS c
LEFT JOIN cq l
ON c.company = l.company AND c.mts > l.ts
GROUP BY c.company, c.mts) AS c
LEFT JOIN cq AS l ON l.company = c.company AND l.ts = c.mts
LEFT JOIN cq AS l1 ON l1.company = c.company AND l1.ts = c.lts;
You can observe results on SQL Fiddle.
您可以在SQL Fiddle上观察结果。
This query is using only standard SQL capabilities and should work on any RDBMS.
此查询仅使用标准 SQL 功能，应适用于任何 RDBMS。
