Mysql – How to update values based on the value from the previous day

MySQLmysql-5.7query

I have a MySQL database temporary table with a bunch of meter readings in it against a system ID and a date (which combined make up the primary key).

Some entries have NULL for the meter reading, in which case they need to be updated to have the same meter reading as the previous day.

There is also a "diff" column which needs to have the difference between the reading for that day, and for the previous day.

I know how to update the tables using code, I am wondering if there is a good way to do it with SQL. Multiple queries is fine (I would guess that at a minimum the NULL readings would need to be updated before the diffs are calculated).

Here is an example schema with fake data, also in this sqlfiddle:

CREATE TABLE Readings (`SystemID` INT, `ReadingDate` DATE, `Reading` INT NULL, `Diff` INT NULL, PRIMARY KEY(`SystemID`,`ReadingDate`));

INSERT INTO Readings VALUES 
(1, '2021-02-01', 24, NULL), 
(1, '2021-02-02', 26, NULL), 
(1, '2021-02-03', 23, NULL), 
(1, '2021-02-04', NULL, NULL), 
(1, '2021-02-05', NULL, NULL), 
(1, '2021-02-06', 28, NULL), 
(1, '2021-02-07', 21, NULL),
(2, '2021-02-01', 124, NULL), 
(2, '2021-02-02', 126, NULL), 
(2, '2021-02-03', 123, NULL), 
(2, '2021-02-04', NULL, NULL), 
(2, '2021-02-05', NULL, NULL), 
(2, '2021-02-06', 128, NULL), 
(2, '2021-02-07', 121, NULL);

After running the updates, the values would be (INSERT format kept to make it easier to see):

(1, '2021-02-01', 24, NULL), 
(1, '2021-02-02', 26, 2), 
(1, '2021-02-03', 23, -3), 
(1, '2021-02-04', 23, 0), 
(1, '2021-02-05', 23, 0), 
(1, '2021-02-06', 28, 5), 
(1, '2021-02-07', 21, -7),
(2, '2021-02-01', 124, NULL), 
(2, '2021-02-02', 126, 2), 
(2, '2021-02-03', 123, -3), 
(2, '2021-02-04', 123, 0), 
(2, '2021-02-05', 123, 0), 
(2, '2021-02-06', 128, 5), 
(2, '2021-02-07', 121, -7);

EDIT: To further complicate things, this is a temporary table in the real application.

Best Answer

If you are on MySQL >= 8.0

You can take advantage of LATERAL derived tables.

SELECT
    Act.SystemID,
    Act.ReadingDate,
    Act.Reading,
    Act.Diff,
    Prev.Reading as PrevReading
FROM
    Readings AS Act,
LATERAL
    (SELECT   Reading
     FROM     Readings
     WHERE    SystemID = Act.SystemID
              AND ReadingDate < Act.ReadingDate
              AND Reading IS NOT NULL
     ORDER BY SystemID, ReadingDate DESC
     LIMIT 1) Prev
     
ORDER BY
    Act.SystemID, Act.ReadingDate;

In this case it returns the previous reading that is not null.

SystemID | ReadingDate | Reading | Diff | PrevReading
-------: | :---------- | ------: | ---: | ----------:
       1 | 2021-02-02  |      26 | null |          24
       1 | 2021-02-03  |      23 | null |          26
       1 | 2021-02-04  |    null | null |          23
       1 | 2021-02-05  |    null | null |          23
       1 | 2021-02-06  |      28 | null |          23
       1 | 2021-02-07  |      21 | null |          28
       2 | 2021-02-02  |     126 | null |         124
       2 | 2021-02-03  |     123 | null |         126
       2 | 2021-02-04  |    null | null |         123
       2 | 2021-02-05  |    null | null |         123
       2 | 2021-02-06  |     128 | null |         123
       2 | 2021-02-07  |     121 | null |         128

Now you can use this syntax to update all your rows in a single query:

UPDATE
    Readings Act,
    LATERAL
    (SELECT   Reading
     FROM     Readings 
     WHERE    SystemID = Act.SystemID
              AND ReadingDate < Act.ReadingDate
              AND Reading IS NOT NULL
     ORDER BY SystemID, ReadingDate DESC
     LIMIT 1) Prev    
SET
    Act.Reading = IF(Act.Reading IS NULL, Prev.Reading, Act.Reading),

    Act.Diff = CASE 
                   WHEN Act.Reading IS NULL THEN 0
                   ELSE Act.Reading - Prev.Reading 
               END;
           
SELECT * FROM Readings ORDER BY SystemID, ReadingDate;

And that is the result:

SystemID | ReadingDate | Reading | Diff
-------: | :---------- | ------: | ---:
       1 | 2021-02-01  |      24 | null
       1 | 2021-02-02  |      26 |    2
       1 | 2021-02-03  |      23 |   -3
       1 | 2021-02-04  |      23 |    0
       1 | 2021-02-05  |      23 |    0
       1 | 2021-02-06  |      28 |    5
       1 | 2021-02-07  |      21 |   -7
       2 | 2021-02-01  |     124 | null
       2 | 2021-02-02  |     126 |    2
       2 | 2021-02-03  |     123 |   -3
       2 | 2021-02-04  |     123 |    0
       2 | 2021-02-05  |     123 |    0
       2 | 2021-02-06  |     128 |    5
       2 | 2021-02-07  |     121 |   -7

db<>fiddle here

If you are on MySQL 5.7

You can create a function that returns the corresponding previous Reading:

CREATE FUNCTION PrevReading(sID int, rDate date)
RETURNS INT

BEGIN

   DECLARE val INT;

   SET val := (SELECT Reading
               FROM   Readings
               WHERE  SystemID = sID
                      AND ReadingDate < rDate
                      AND Reading IS NOT NULL
               ORDER BY 
                      SystemID, ReadingDate DESC
               LIMIT 1);

   RETURN val;

END;

And then update your table:

UPDATE
    Readings
SET
    Reading = IF(Reading IS NULL, PrevReading(SystemID, ReadingDate), Reading),
    Diff =    IF(Reading IS NULL, 0, Reading - PrevReading(SystemID, ReadingDate))

db<>fiddle here

Related Question