Simulate lag function in MySQL

Question

| time                | company | quote |
+---------------------+---------+-------+
| 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 |

How do I do a lag on this table in MySQL to print the difference in quotes, for example:

GOOGLE | 20
SAP    | 40  

Solution 1

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.

Solution 2

From MySQL 8.0 and above there is no need to simulate LAG. It is natively supported,

Window Function :

Returns the value of expr from the row that lags (precedes) the current row by N rows within its partition. If there is no such row, the return value is default. For example, if N is 3, the return value is default for the first two rows. If N or default are missing, the defaults are 1 and NULL, respectively.

SELECT
     company,
     quote,
     LAG(quote) OVER(PARTITION BY company ORDER BY time) AS prev_quote
FROM tab;

DBFiddle Demo

Solution 3

To achieve the desired result, first you need to find the last and next to last timestamps for each company. It is quite simple with the following query:

SELECT c.company, c.mts, max(l.ts) AS lts
  FROM (SELECT company, max(ts) AS mts FROM cq GROUP BY company) AS c
  LEFT JOIN cq l
    ON c.company = l.company AND c.mts > l.ts
 GROUP BY c.company, c.mts;

Now you have to join this subquery with the original table to get the desired results:

SELECT c.company, l.quote, coalesce(l1.quote, 0),
       (l.quote - coalesce(l1.quote, 0)) AS result
  FROM (SELECT c.company, c.mts, max(l.ts) AS lts
      FROM (SELECT company, max(ts) AS mts FROM cq GROUP BY company) AS c
      LEFT JOIN cq l
        ON c.company = l.company AND c.mts > l.ts
     GROUP BY c.company, c.mts) AS c
  LEFT JOIN cq AS l ON l.company = c.company AND l.ts = c.mts
  LEFT JOIN cq AS l1 ON l1.company = c.company AND l1.ts = c.lts;

You can observe results on SQL Fiddle.

This query is using only standard SQL capabilities and should work on any RDBMS.

Comments

[javanx]

I am getting error. DDL and DML statements are not allowed in the query panel for MySQL; only SELECT statements are allowed. Put DDL and DML in the schema panel.

[Dojo]

Though the error does not indicate this, try enabling "allowMultiQueries". This is a connector paramenter. For JDBC connector, see: dev.mysql.com/doc/refman/5.1/en/… . Are you able to run it successfully from MySQL client?

[Dojo]

You can also try executing the two statements individually but in the same session.

[Jake Feasel]

@javanx Hi, I'm the author of SQL Fiddle. The error message you mention was actually a bug in the way I was handling certain types of MySQL queries. Thanks to your message here, I recognized it as such and have worked out a solution that fixes it (see here, for example: sqlfiddle.com/#!2/4f8a1/2). Thanks!

[David Rubinger]

This worked for me! But I don't understand the mechanism. Could you explain?

[Dojo]

@symobol indicates that it is session variable. So @quot is a variable. SET @quot=0 initializes it to 0. Imagine the rest of the query like a for loop that is iterrating through the result set. whenever you do @quot:=x, the value of current row's x column is copied into @quot. What is the difference between SET @quot=something and @quot:=something (notice the colon)? I don't know, that's how they want the syntax to be. When you use SET it does not expect a colon, when you just do @variable_name = something, it needs a colon before equal sign. so its @quot:=something.

[Dojo]

Note the order colums/operations @quot lag_quote, @quot:=quote curr_quote. First I just read the value of @quot and name the column lag_quote. @quot is still holding the previously assigned value, i.e the previous row's quote value as we have not yet executed @quot:=quote for the current row. Then we do @quot:=quote whose output is the updated value of @quot i.e quote from the current row. And we name this column column as curr_quote

[quickshiftin]

NOTE You have to keep the data type of the variable consistent with the type of data you are trying to lag. EG, if you want to accumulate a FLOAT field, you must initialize your variable as @quot=0.0, otherwise it will not work. Took me a minute to figure this out!

[Evan Zamir]

Good answer, especially for people preparing for tech interviews since they often discourage using window functions.