Simulate lag function in MySQL

by

This is my favorite MySQL hack.

This is how you emulate the lag function:

SET @quot=-1;
select time,company,@quot lag_quote, @quot:=quote curr_quote
  from stocks order by company,time;
  • lag_quote holds the value of previous row’s quote. For the first row @quot is -1.
  • curr_quote holds the value of current row’s quote.

Notes:

  1. order by clause is important here just like it is in a regular
    window function.
  2. You might also want to use lag for company just to be sure that you are computing difference in quotes of the same company.
  3. You can also implement row counters in the same way @cnt:=@cnt+1

The nice thing about this scheme is that is computationally very lean compared to some other approaches like using aggregate functions, stored procedures or processing data in application server.

EDIT:

Now coming to your question of getting result in the format you mentioned:

SET @quot=0,@latest=0,company='';
select B.* from (
select A.time,A.change,IF(@comp<>A.company,1,0) as LATEST,@comp:=A.company as company from (
select time,company,quote-@quot as change, @quot:=quote curr_quote
from stocks order by company,time) A
order by company,time desc) B where B.LATEST=1;

The nesting is not co-related so not as bad (computationally) as it looks (syntactically) 🙂

Let me know if you need any help with this.

More Related Contents:

  1. How do I lag columns in MySQL?
  2. how to delete data from table A besides the customer service and director data in table B?
  3. Get records with max value for each group of grouped SQL results
  4. MYSQL OR vs IN performance
  5. SELECTING with multiple WHERE conditions on same column
  6. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  7. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  8. Getting “Lock wait timeout exceeded; try restarting transaction” even though I’m not using a transaction
  9. Cannot delete or update a parent row: a foreign key constraint fails
  10. Using union and order by clause in mysql
  11. Using an Alias in SQL Calculations
  12. Can MySQL replace multiple characters?
  13. OUTPUT Clause in MySQL
  14. Search all columns of a table using a single where condition with single keyword in mysql
  15. Using ORDER BY and GROUP BY together
  16. Sorting by date & time in descending order?
  17. Case statement in MySQL
  18. MySQL Pivot Table Column Data as Rows
  19. updating columns with a sequence number mysql
  20. MySQL, Concatenate two columns
  21. Select a column with a keyword name
  22. MySQL: Inner join vs Where [duplicate]
  23. Selecting from two tables with inner join and limit [duplicate]
  24. Use Composite Primary Key as Foreign Key
  25. Why the order of evaluation for expressions involving user variables is undefined?
  26. How to select data where a field has a min value in MySQL?
  27. How to SELECT by MAX(date)?
  28. get a recursive parent list
  29. How do you force mysql LIKE to be case sensitive? [duplicate]
  30. In what order are MySQL JOINs evaluated?

Leave a Comment