Как я могу отставать столбцы в MySQL?

Question

Рассмотрим следующую таблицу:

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

id столбец автоматически увеличивается, но содержит пробелы. value столбец числовой

Я хочу посмотреть на увеличение value со временем, установив value по отношению к value двумя рядами выше. Это для ряда id=85 Я хочу установить value ряда id=85 (546) по отношению к value ряда id=27 (334). Значение, которое будет вычислено для строки id=85 следовательно, 546/334=1,63473.

Вот результат, которого я хочу достичь:

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)
+-----+---------+---------------------+

Как мне выполнить такое отставание в MySQL?

Обратите внимание, что id столбец содержит пробелы, поэтому просто объединение в одной таблице с t1.id = t2.id - 2 не будет работать.

Answer 1

Вот решение, которое возвращает то, что вы хотите в 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 не любит самостоятельное объединение с подзапросом, поэтому вам приходится считать строки дважды, поэтому не так аккуратно или масштабируемо, как могло бы быть, но это делает определение лага простым.

Для читателей, которые используют Oracle вместо этого, это намного проще

SELECT id, value, value/lag(value, 2) over (order by id) as lag from results;

Answer 2

Поскольку существует только две строки между текущей и той, из которой вы хотите получить "исторические" данные, вы можете использовать переменные для временного хранения данных, используя что-то вроде:

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

Это очень неопрятное решение, но я думаю, оно подойдет. Может быть, есть какой-то способ предотвратить отображение переменных, я не стал вдаваться в подробности.