mysql - SQL 存储值，每日读数

Question

是否有可能以某种方式存储一个值，然后在需要时使用它。也许您对以下问题有任何其他解决方案。我有每小时的水读数，我想将其显示为一天的消耗量，但有时可能会丢失一些读数。

SELECT Readings.dte AS day,
       Ifnull(NextReadings.reading, (SELECT Max(consumption)
                                     FROM   meter_readings)) - Readings.reading AS Consumption
FROM   (SELECT Date(readdate)   AS dte,
               Min(consumption) AS Reading
        FROM   meter_readings
        GROUP  BY Date(readdate)) AS Readings
     LEFT JOIN 
       (SELECT Date(readdate)   AS dte,
               Min(consumption) AS Reading
        FROM   meter_readings
        GROUP  BY Date(readdate)) AS NextReadings
     ON NextReadings.dte = date_add(Readings.dte, INTERVAL +1 DAY)  

如果有

...
insert into meter_readings values ('2013-07-30 00:00:31',    143.860);
insert into meter_readings values ('2013-07-30 01:00:32',    143.870);
insert into meter_readings values ('2013-07-30 02:00:31',    143.870);
insert into meter_readings values ('2013-07-30 03:00:32',    143.870);
insert into meter_readings values ('2013-07-30 04:00:31',    143.870);

insert into meter_readings values ('2013-07-31 02:00:31',    143.890);

insert into meter_readings values ('2013-08-03 00:00:31',    143.900);
insert into meter_readings values ('2013-08-03 01:00:32',    143.920);
insert into meter_readings values ('2013-08-03 02:00:31',    143.920);

http://sqlfiddle.com/#!2/dd66cd/2 <--例如

如你所见，我:

July, 30 2013   0.03
July, 31 2013   0.03 
August, 03 2013 0.02

2013-07-31 的用法是 0.03 而这不是真的，因为 SELECT Max(consumption) FROM meter_readings) 。最好以某种方式存储最后一个正确值 (143.870) 然后使用而不是这个 Max(consumption)。 2013-07-31 的用法应为:143.890 - Variable，其中 Variable 为 143.870

Answer 1

我想你想要每天的最后一次阅读。使用此语法获取它:

SELECT M1.*

FROM meter_readings M1
LEFT JOIN meter_readings M2
ON Date(M1.ReadDate) = Date(M2.ReadDate)
AND M1.ReadDate < M2.ReadDate
WHERE M2.ReadDate IS NULL

或者获取每天的第一个读物:

SELECT M1.*

FROM meter_readings M1
LEFT JOIN meter_readings M2
ON Date(M1.ReadDate) = Date(M2.ReadDate)
AND M1.ReadDate > M2.ReadDate
WHERE M2.ReadDate IS NULL

并获取当天的使用情况。我不怎么用MySql。所以也许排名函数可以设计得更好。

SET @curRank1 = 0;
SET @curRank2 = 0;

SELECT
D1.ReadDate
,D1.Consumption AS D1Consumption
,D2.Consumption AS D2Consumption
,(D2.Consumption - D1.Consumption) AS UsageOfDay
FROM
(
  SELECT M1.*, @curRank1 := @curRank1 + 1 AS rank

FROM meter_readings M1
LEFT JOIN meter_readings M2
ON Date(M1.ReadDate) = Date(M2.ReadDate)
AND M1.ReadDate < M2.ReadDate
WHERE M2.ReadDate IS NULL
  ) D2
LEFT JOIN
(
  SELECT M1.*, @curRank2 := @curRank2 + 1 AS rank

FROM meter_readings M1
LEFT JOIN meter_readings M2
ON Date(M1.ReadDate) = Date(M2.ReadDate)
AND M1.ReadDate < M2.ReadDate
WHERE M2.ReadDate IS NULL
  ) D1
ON D2.rank = (D1.rank + 1)