Edit Question - KnowledgeHub

Title Body

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.

Tags (comma-separated) Cancel