9

Consider the following table:

SELECT id, value FROM table ORDER BY id ASC;
+-----+---------+
| id  | value   |
+-----+---------+
| 12  | 158     |
| 15  | 346     |
| 27  | 334     |
| 84  | 378     |
| 85  | 546     |
+-----+---------+

The id column is auto-incremented but contains gaps. The value column is numeric.

I want to look at the increase in value over time by setting value in relation to the value two rows above. That is for row id=85 I want to set the value of row id=85 (546) in relation to the value of row id=27 (334). The value to be computed for row id=85 is hence 546/334=1.63473.

This is the result I want to achieve:

SELECT id, value, ...;
+-----+---------+---------------------+
| id  | value   | value/lag(value, 2) | (the syntax value/lag(value, 2) is made up)
+-----+---------+---------------------+
| 12  | 158     | NULL                |
| 15  | 346     | NULL                |
| 27  | 334     | 2.11392             | (334/158=2.11392)
| 84  | 378     | 1.09248             | (378/346=1.09248)
| 85  | 546     | 1.63473             | (546/334=1.63473)
+-----+---------+---------------------+

How do I perform such lagging in MySQL?

Please note that the id column contains gaps, so simply joining on the same table with t1.id = t2.id - 2 will not work.

pilcrow
  • 56,591
  • 13
  • 94
  • 135
knorv
  • 49,059
  • 74
  • 210
  • 294
  • There is a lag() function in the SQL standard but MySQL does not support this. –  Mar 30 '11 at 07:40

2 Answers2

9

Here is a solution that returns what you want in MySQL 

SET @a :=0;
SET @b :=2;
SELECT r.id, r.value, r.value/r2.value AS 'lag'
FROM
(SELECT if(@a, @a:=@a+1, @a:=1) as rownum, id, value FROM results) AS r
LEFT JOIN
(SELECT if(@b, @b:=@b+1, @b:=1) as rownum, id, value FROM results) AS r2
ON r.rownum = r2.rownum

MySQL 5.1 doesn't like a self join against a subquery so you have to count rows twice, so not as tidy or scalable as it might be, but it does make specifying the lag simple.

For readers that use Oracle instead this is much easier

SELECT id, value, value/lag(value, 2) over (order by id) as lag from results;
  • 3
    *For readers that use Oracle*: or PostgreSQL or DB2 or Teradata :) –  Mar 30 '11 at 10:42
0

As there are only two rows between the current one and the one from where you want to get the 'historical' data could you perphaps use variables to store the data temporarily using something like:

set @oldid0=999999;
set @oldid1=999999;
set @oldid2=999999;
select @oldid0:=@oldid1,@oldid1:=@oldid2,@oldid2:=id, value/@oldid0 from table order by id asc;

It is a very untidy solution but I think it will do the job. Maybe there is some way of preventing the variables from being displayed, I've not looked into it that far.

blankabout
  • 2,597
  • 2
  • 18
  • 29