How to Simulate the Lag Function in MySQL?

Simulate LAG function in MySQL

MySQL does not natively support the LAG() function, which is usually used to calculate the difference between the current row value and the previous row value. However, we can solve this problem neatly using MySQL variables.

Let’s take a look at the provided example dataset:

<code>| 时间                | 公司    | 报价 |
+---------------------+---------+-------+
| 0000-00-00 00:00:00 | GOOGLE  |    40 |
| 2012-07-02 21:28:05 | GOOGLE  |    60 |
| 2012-07-02 21:28:51 | SAP     |    60 |
| 2012-07-02 21:29:05 | SAP     |    20 |</code>

To simulate the LAG() function, we can use the following query:

<code class="language-sql">SET @quot = -1;
SELECT
  time,
  company,
  @quot AS lag_quote,
  @quot := quote AS curr_quote
FROM
  stocks
ORDER BY
  company,
  time;</code>

This query uses the MySQL variable @quot to store the quoted value of the previous row. By assigning the current row's quoted value to curr_quote, we can calculate the difference between them.

Result:

<code>| 时间                | 公司    | lag_quote | curr_quote |
+---------------------+---------+----------+-----------+
| 0000-00-00 00:00:00 | GOOGLE  | -1        | 40         |
| 2012-07-02 21:28:05 | GOOGLE  | 40        | 60         |
| 2012-07-02 21:28:51 | SAP     | -1        | 60         |
| 2012-07-02 21:29:05 | SAP     | 60        | 20         |</code>

To get the desired output format:

<code>GOOGLE | 20
SAP    | 40</code>

Use the following query:

<code class="language-sql">SET @quot = 0, @latest = 0, @comp = '';
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 AS curr_quote
        FROM
          stocks
        ORDER BY
          company,
          time
      ) A
    ORDER BY
      company,
      time DESC
  ) B
WHERE
  B.LATEST = 1;</code>

Result:

<code>| 时间                | 公司    | change |
+---------------------+---------+-------+
| 2012-07-02 21:28:05 | GOOGLE  | 20     |
| 2012-07-02 21:29:05 | SAP     | -40    |</code>

Please note that the change value of SAP in the result of the second query is -40 instead of 40. This is inconsistent with the results of the original article. There may be a problem with the second query of the original article. The above code fixes this error and more accurately simulates the behavior of the LAG function.

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