How to simulate LEAD and LAG functions? [Mysql]

Asked

Viewed 587 times

7

I have recently been developing a query in which I came up with the need to compare rows of the same data set successively, I mean, from the same column, from the same table. I became aware of the LEAD() and LAG() functions of Mysql. However, I later checked that these are functions of the "window functions" type and that they may not be available for Mysql. Hence the question: it is possible to simulate its operation?

  1. Mysql and Window Functions
    Discussion about "Window Functions" and their unavailability in Mysql.

  2. Sqlfiddle (The query I’ve been developing):

    The query returns in separate columns, based on your value of record (binary), the times when the records were in 0 and 1. And from that, check the interval between them. For this "disregarding" the date provided by the timestamp and converting the period before it into HH:MM:SS format for hours only.

  3. Simulate LAG and LEAD Function:

    I found a similar post on Stackoverflow and Database Administrator, however I found that there was nothing about it here, other than I couldn’t apply the answer to my case. Dai saw the need to open a question.

  4. LEAD function - Mysql (Overview of the LEAD function)

    The LEAD() function is a window function which allows you to search a number of lines and access data from that line from the current line. Similar to the LAG() function, the LEAD() function is very useful for calculating the difference between the current line and the subsequent line 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 result similar to that:

        +----------+----------+---------------+----------+----------+---------------+
        | 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 a row is moved in reference to the left column t p/ v=n
  • The column lead diff v=n calculates the difference between the other two columns
mysql
  • 1

    You can update the question by showing an example of the output you want to get?

  • 1

    Done @Runo. Believes that it is of some use or simply have answered that I wish a result similar to the example?

  • 1

    Yes, your editing is very useful. Showing the result you want to get makes it easier for the community to help.

1 answer

6


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 connect the table to itself using a sequence. I think this query will return the desired 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 the Sqlfiddle to test

The first 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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.