How to simulate the LEAD and LAG functions? [MySQL]

Question

How to simulate the LEAD and LAG functions? [MySQL]

Navigation

#1 by (4 votes)

5

I have recently been developing a query in which I had the need to compare successively the rows of the same data set , I mean, from the same column, from the same table. In this, I became aware of the MySQL LEAD () and LAG () functions. However, I later verified that these are "window functions" functions and may not be available for MySQL. Ask the question: Can you simulate your operation?

MySQL and the Window Functions
Discussion about of "Window Functions" and their unavailability in MySQL.

SQLFiddle (The query I've been developing):

Query search to return in distinct columns, based on your value interval between them. To do this "disregard" the date provided by the timestamp and converting the period before in HH: MM: SS format to only hours.

Simulate LAG and LEAD Function:

I found a similar post in StackOverflow and Database Administrator, though I checked that there was nothing about it here, and I was not able to apply the answer to my case. Dai saw the need to open a question.

LEAD Function - MySQL (Summary on aa LEAD function)

The LEAD () function is a window function that allows you to search a number of rows and access data from that row from the current row. Similar to the LAG () function, the LEAD () function is very useful for calculating the difference between the current row and the subsequent row within the same result set.

Example:

mysql> SELECT
             t, val,
             LEAD(val)       OVER w AS 'lead',
             val - LEAD(val) OVER w AS 'lead diff'
           FROM series
           WINDOW w AS (ORDER BY t);
    +----------+------+------+--------- -+
    | t        | val  | lead | lead diff |
    +----------+------+------+-----------+
    | 12:00:00 |  100 |  125 |       -25 |
    | 13:00:00 |  125 |  132 |        -7 |
    | 14:00:00 |  132 |  145 |       -13 |
    | 15:00:00 |  145 |  140 |         5 |
    | 16:00:00 |  140 |  150 |       -10 |
    | 17:00:00 |  150 |  200 |       -50 |
    | 18:00:00 |  200 | NULL |      NULL |
    +----------+------+------+-----------+

EDITION 1:
I hope to achieve a similar result:

        +----------+----------+---------------+----------+----------+---------------+
        | t p/ v=1 | lead v=1 | lead diff v=1 | t p/ v=0 | lead v=0 | lead diff v=0 |
        +----------+----------+---------------+----------+----------+---------------+
        | 20.8806  | (null)   | (null)        | (null)   | (null)   | (null)        |    
        | 20.8764  | 20.8806  | -0,0042       | (null)   | (null)   | (null)        |    
        | 20.87    | 20.8764  | -0,0064       | (null)   | (null)   | (null)        |   
        | 20.8636  | 20.87    | -0,0064       | (null)   | (null)   | (null)        |  
        | 20.8508  | 20.8636  | -0,0128       | (null)   | (null)   | (null)        |  
        | 20.85    | 20.8508  | -0,0008       | (null)   | (null)   | (null)        | 
        | (null)   | (null)   | (null)        | 20.8333  | (null)   | (null)        |   
        | (null)   | (null)   | (null)        | 20.8303  | 20.8333  | -0,003        |
        | (null)   | (null)   | (null)        | 20.83    | 20.8303  | -0,003        |
        | (null)   | (null)   | (null)        | 20.8     | 20.83    | -0,03         |
        | (null)   | (null)   | (null)        | 20.5     | 20.8     | -0,3          |
        | (null)   | (null)   | (null)        | 20.4964  | 20.5     | -0,0036       |
        +----------+-----------+--------------+----------+----------+---------------+

The column lead v = n moves a line in reference to the column to its left tp / v = n

mysql

asked by anonymous 23.11.2018 / 18:09

1 answer

How to load a view into a layout? Logic error?

score 4 · Accepted Answer

There are several simple alternatives to simulate the LEAD () and LAG () functions in MySQL.

Using your Fiddle as a base

CREATE TABLE Teste
    ('Time_Stamp' varchar(20) ,'valor' int(2))
;

INSERT INTO Teste
    ('Time_Stamp','Valor')
VALUES
    ('2018-11-23 20:23:10','0'),
    ('2018-11-23 20:24:20','0'),
    ('2018-11-23 20:24:34','0'),
    ('2018-11-23 20:25:30','1'),
    ('2018-11-23 20:26:40','1'),
    ('2018-11-23 20:28:01','1'),
    ('2018-11-23 20:28:56','1'),
    ('2018-11-23 20:29:22','1'),
    ('2018-11-23 20:29:47','0'),
    ('2018-11-23 20:30:00','0'),
    ('2018-11-23 20:48:00','0'),
    ('2018-11-23 20:49:49','0'),
    ('2018-11-23 20:49:48','0'),
    ('2018-11-23 20:50:00','0'),
    ('2018-11-23 20:51:00','1'),
    ('2018-11-23 20:51:03','1'),
    ('2018-11-23 20:51:49','1'),
    ('2018-11-23 20:52:12','1'),
    ('2018-11-23 20:52:35','1'),
    ('2018-11-23 20:52:50','1')
;

An alternative is to bind the table to itself using a string. I think this query will return the intended result.

SELECT CASE WHEN T1.Valor = 1 THEN T1.Horas END AS 't p/ v=1',
       CASE WHEN T1.Valor = 1 THEN T_LEAD.Horas END AS 'lead v=1',
       CASE WHEN T1.Valor = 1 THEN T1.Horas - T_LEAD.Horas END AS 'lead diff v=1',
       CASE WHEN T1.Valor = 0 THEN T1.Horas END AS 't p/ v=0',
       CASE WHEN T1.Valor = 0 AND T_LEAD.Valor = 0 THEN T_LEAD.Horas END AS 'lead v=0',
       CASE WHEN T1.Valor = 0 AND T_LEAD.Valor = 0 THEN T1.Horas - T_LEAD.Horas END AS 'lead diff v=0'
  FROM 
  ( 
      SELECT Time_Stamp, 
             (HOUR(CAST(time_stamp AS TIME)) + (MINUTE(CAST(time_stamp AS TIME)) / 60) + SECOND(CAST(time_stamp AS TIME)) / 3600) AS Horas, 
             Valor, 
             @seq1 := @seq1 + 1 AS Seq 
        FROM Teste, (SELECT @seq1 := 0) r 
       ORDER BY Time_Stamp, Valor
  ) T1
  LEFT JOIN 
  ( 
     SELECT Time_Stamp, 
            (HOUR(CAST(time_stamp AS TIME)) + (MINUTE(CAST(time_stamp AS TIME)) / 60) + SECOND(CAST(time_stamp AS TIME)) / 3600) AS Horas, 
            Valor, 
            @seq3 := @seq3 + 1 AS Seq 
       FROM Teste, (SELECT @seq3 := 0) r 
      ORDER BY Time_Stamp, Valor
  ) T_LEAD
    ON T_LEAD.Seq - 1 = T1.Seq
 WHERE CAST(T1.time_stamp AS DATE) = '2018-11-23'
ORDER BY T1.time_stamp DESC;

Stay here SQLFiddle to test

The first few lines of the result look like this

t p/ v=1  lead v=1  lead diff v=1     t p/ v=0  lead v=0    lead diff v=0
20.8806   (null)    (null)            (null)    (null)      (null)
20.8764   20.8806   -0.0042           (null)    (null)      (null)
20.87     20.8764   -0.0064           (null)    (null)      (null)
20.8636   20.87     -0.0064           (null)    (null)      (null)
20.8508   20.8636   -0.0128           (null)    (null)      (null)
20.85     20.8508   -0.0008           (null)    (null)      (null)
(null)    (null)    (null)            20.8333   (null)      (null)
(null)    (null)    (null)            20.8303   20.8333     -0.003
(null)    (null)    (null)            20.83     20.8303     -0.0003
(null)    (null)    (null)            20.8      20.83       -0.03
(null)    (null)    (null)            20.5      20.8        -0.3
(null)    (null)    (null)            20.4964   20.5        -0.0036