Simulate lag function in 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 |

How do I do a lag on this table in MySQL to print the difference in quotes, for example:

GOOGLE | 20
SAP    | 40  

Answer 1

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

Notes:

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.

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.