How do I lag columns in MySQL?

by

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;

More Related Contents:

  1. Simulate lag function in MySQL
  2. SQL Query needed for fetch value from Two identical table but not fully identical table
  3. Error related to only_full_group_by when executing a query in MySql
  4. SQL split values to multiple rows
  5. How to reset AUTO_INCREMENT in MySQL
  6. How to declare a variable in MySQL?
  7. How to populate a table with a range of dates?
  8. MySQL Query GROUP BY day / month / year
  9. MySQL Cannot Add Foreign Key Constraint
  10. Grouping into interval of 5 minutes within a time range
  11. SQL counting all rows instead of counting individual rows
  12. Convert timestamp to date in MySQL query
  13. Update a column value, replacing part of a string
  14. MySQL Like multiple values
  15. In MySQL, should I quote numbers or not?
  16. MySQL add days to a date
  17. mysql dynamic query in stored procedure
  18. MySQL combine two columns into one column
  19. Check for x consecutive days – given timestamps in database
  20. MySQL Insert Into from one Database in another
  21. MySQL multiple columns in IN clause
  22. How to select one row randomly taking into account a weight?
  23. GROUP BY – do not group NULL
  24. Composite key as foreign key (sql)
  25. ALTER table – adding AUTOINCREMENT in MySQL
  26. MySQL Select minimum/maximum among two (or more) given values
  27. MySQL – why not index every field?
  28. MySQL: Insert datetime into other datetime field
  29. Selecting distinct 2 columns combination in mysql
  30. Why in SQL NULL can’t match with NULL?

Leave a Comment