Как задержать столбцы в MySQL?

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

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 не сработает.


mysql sql data-analysis sliding-window
person knorv    schedule 30.03.2011    source источник
comment
В стандарте SQL есть функция lag(), но MySQL ее не поддерживает.   -  person a_horse_with_no_name    schedule 30.03.2011

Ответы (2)


arrow_upward
9
arrow_downward

Вот решение, которое возвращает то, что вы хотите в 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;
person Community    schedule 30.03.2011
comment
Для читателей, использующих Oracle: или PostgreSQL, или DB2, или Teradata :) - person a_horse_with_no_name; 30.03.2011

arrow_upward
0
arrow_downward

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

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

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

person blankabout    schedule 30.03.2011
comment
Lag(2) — это просто пример. Я ищу общее решение, которое позволило бы, скажем, Lag (365) :-) - person knorv; 30.03.2011
comment
А, я прочитал ваш вопрос и по глупости предположил, что вы имели в виду только отставание(2) - person blankabout; 30.03.2011