問題描述

| 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.

這篇關于模擬 MySQL 中的滯后函數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！