How to Simulate a LAG Function in MySQL?

Simulate LAG function in MySQL

MySQL itself does not support the LAG function to calculate the value difference between consecutive rows. However, we can simulate the functionality of the LAG function in the following way.

Simulate LAG function

The following SQL statement simulates the LAG function in MySQL:

<code class="language-sql">SET @quot=-1;
select time,company,@quot lag_quote, @quot:=quote curr_quote
  from stocks order by company,time;</code>

Here, @quot is a user-defined variable used to store the quote of the previous row. For the first row, @quot is initialized to -1. curr_quoteSave the quote for the current line.

Customized results

While the above simulation provides hysteresis values, it does not present the results in the format specified in the question. To achieve this format, the following nested query can be used:

<code class="language-sql">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;</code>

This nested query calculates the quote differences and identifies the last row for each company, thus generating output in the required format.

The above is the detailed content of How to Simulate a LAG Function in MySQL?. For more information, please follow other related articles on the PHP Chinese website!