在MySQL中模拟延迟函数

Question

| 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中对该表执行滞后操作，以打印出行情差异，例如：

GOOGLE | 20
SAP    | 40  

- javanx

---

“company” 只有两个吗？还是它是可变的？ - Michael Berkowski

如果一家公司只有一行数据，您是否希望在结果中返回该公司？如果是，那么应该为它返回什么不同的差异？ - Andriy M

最后，这个问题具体是关于什么问题：如何返回每个公司的最后两行或如何计算两行之间的差异？我认为你不应该在一个问题中混合这两个问题。如果您确实需要解决这两个问题，最好分别提出两个单独的问题，这样您很可能会得到更高质量的答案。 - Andriy M

在你的例子中，为什么其中一家公司的结果没有变成负数？谷歌从40上涨到60，而SAP从60下降到20。或者你只想要绝对的变动，不考虑方向（在这种情况下，使用ABS(delta)）？ - eggyal

非常感谢您的帮助。我只是想举个例子，例如Google的增量为20，SAP的增量为-40，但现在它却相反了。 - javanx

Answer 1

这是我最喜欢的MySQL技巧。

这是模拟 lag 函数的方法：

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保存当前行的引用值。

注意：

1. order by子句在这里很重要，就像在常规窗口函数中一样。
2. 您可能还想对company使用lag，以确保计算相同company的引用差异。
3. 您还可以以同样的方式实现行计数器，例如@cnt:=@cnt+1

这个方案的好处是与其他方法（如使用聚合函数、存储过程或在应用程序服务器中处理数据）相比，计算非常简便。

编辑：

现在回到你提出的以所需格式获取结果的问题：

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;

嵌套并不相关，所以它在语法上看起来并不像它那么糟糕（计算上）:)

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

- Dojo

---

你好，我是 SQL Fiddle 的作者。你提到的错误信息实际上是我处理某些类型的 MySQL 查询时出现的 bug。感谢您在此留言，我意识到了这个问题，并已经找到了解决方案（例如，请查看这里：http://sqlfiddle.com/#!2/4f8a1/2）。谢谢！ - Jake Feasel

这对我有用！但我不理解机制。你能解释一下吗？ - David Rubinger

@符号表示它是会话变量。因此，@quot是一个变量。SET @quot=0将其初始化为0。想象一下查询的其余部分就像一个循环，正在遍历结果集。每当您执行@quot:=x时，当前行的x列的值被复制到@quot中。SET @quot=something和@quot:=something（注意冒号）之间有什么区别？我不知道，这就是他们想要的语法。当您使用SET时，它不需要冒号，当您只是做@variable_name = something时，它需要等号前面的冒号，所以它是@quot:=something。 - Dojo

请注意列/操作的顺序 @quot lag_quote, @quot:=quote curr_quote。首先，我只读取了 @quot 的值，并将该列命名为 lag_quote。由于我们尚未执行当前行的 @quot:=quote，因此 @quot 仍然保留先前分配的值，即上一行的 quote 值。然后，我们执行 @quot:=quote，其输出是 @quot 的更新值，即当前行的引用。我们将此列命名为 curr_quote。 - Dojo

注意：你必须保持变量的数据类型与要延迟的数据类型一致。例如，如果你想累加一个 FLOAT 字段，你必须将变量初始化为 @quot=0.0，否则它将无法工作。我花了一分钟才弄清楚这个问题！ - quickshiftin

Answer 2

从MySQL 8.0版本及以上开始，无需模拟LAG，它已经原生支持。

窗口函数：

返回分区内比当前行前N行的expr值。如果没有这样的行，则返回默认值。例如，如果N为3，则前两行的返回值为默认值。如果N或默认值缺失，则默认值分别为1和NULL。

SELECT
     company,
     quote,
     LAG(quote) OVER(PARTITION BY company ORDER BY time) AS prev_quote
FROM tab;

DBFiddle演示

- Lukasz Szozda

Answer 3

为了达到期望的结果，首先需要找到每个公司的最后和倒数第二个时间戳。使用以下查询非常简单：

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;

现在您需要将这个子查询与原始表连接以获得所需的结果：

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;

您可以在SQL Fiddle上观察结果。

这个查询仅使用了标准的SQL功能，应该可以在任何关系型数据库管理系统上运行。

- vyegorov

---

好的回答，特别是为那些准备技术面试的人，因为他们通常不鼓励使用窗口函数。 - Evan Zamir