mysql - 根据时间戳计算并更新当前行和最后一行之间的功耗

Question

不幸的是，我的 SQL 技能非常基础。我希望有一个人可以帮助我。我想根据同一设备两行之间的时间差计算功耗(计数器读数)，以 Wh(瓦特/小时)为单位。

我有一个表 (mysql)，其中包含 deviceID、日期、CounterReading (Wh)，当前为空、ActivePower (Watt) 和一些其他列。在表“device_data”中大约。每分钟，设备都会插入一个新行，其中包含当前的电量使用情况(字段 activePower)。时间戳位于字段日期中。我想每小时运行一个脚本来计算计数器读数(Wh)并更新每一行。计算出的 Wh 需要添加到表中保存的最后一个值中。

我知道公式，但我没有技能编写正确的 SQL 查询来计算计数器读数并更新表。

Formula: Wh = 'timediff in sec' * activePower / 3600 Example: 55s * 9.1601 / 3600 = 0.139945972 Wh (4 digits after . should be enough).

计算出的 Wh 必须添加到 counterReading 的最后一个条目中。目前 CounterReading 字段为“NULL”，我想从 0 Wh 开始，直到今天为止进行总结。

有时 ActivePower 也可以为 0(= 电源插头后面的设备没有用电)。在这种情况下，CounterReading 值必须与最后一个条目相同。

下面是我的表“device_data”的示例，其中包含两个设备。

+--------------+---------------------+---------+--------+
|   DeviceId   |        DATE         | Counter | Active |
|              |                     | Reading | POWER  |
+--------------+---------------------+---------+--------+
| B4E62DE92CE9 | 2019-06-28 17:34:02 | NULL    | 9.1150 |
| B4E62DE92CE9 | 2019-06-28 17:35:01 | NULL    | 9.2129 |
| B4E62DE92CE9 | 2019-06-28 17:36:01 | NULL    | 9.1962 |
| B4E62DE92CE9 | 2019-06-28 17:37:01 | NULL    | 9.1741 |
| B4E62DE92CE9 | 2019-06-28 17:38:02 | NULL    | 9.1123 |
| B4E62DE92CE9 | 2019-06-28 17:39:02 | NULL    | 9.1256 |
| B4E62DE92CE9 | 2019-06-28 17:40:01 | NULL    | 9.2432 |
| B4E62DE92CE9 | 2019-06-28 17:41:02 | NULL    | 9.1924 |
| B4E62DE92CE9 | 2019-06-28 17:42:02 | NULL    | 9.2486 |
| B4E62DE92CE9 | 2019-06-28 17:43:01 | NULL    | 9.2040 |
| B4E62DE92CE9 | 2019-06-28 17:44:01 | NULL    | 9.0565 |
| B4E62DE92CE9 | 2019-06-28 17:45:01 | NULL    | 9.1879 |
| B4E62DE92CE9 | 2019-06-28 17:46:01 | NULL    | 9.2045 |
| B4E62DE92CE9 | 2019-06-28 17:47:02 | NULL    | 9.1986 |
| B4E62DE92CE9 | 2019-06-28 17:48:04 | NULL    | 9.1601 |
+--------------+---------------------+---------+--------+

这是我想要的最终结果的示例:

+--------------+-------------------+----------------+-------------+
|   DeviceId   |       DATE        | CounterReading | ActivePower |
+--------------+-------------------+----------------+-------------+
| B4E62DE92CE9 | 28.06.19 17:48:04 |         0.0000 |      9.1601 |
| B4E62DE92CE9 | 28.06.19 17:47:02 |         0.1559 |      9.1986 |
| B4E62DE92CE9 | 28.06.19 17:46:01 |         0.3093 |      9.2045 |
| B4E62DE92CE9 | 28.06.19 17:45:01 |         0.4599 |      9.1879 |
| B4E62DE92CE9 | 28.06.19 17:44:01 |         0.6083 |      9.0565 |
| B4E62DE92CE9 | 28.06.19 17:43:01 |         0.7591 |       9.204 |
| B4E62DE92CE9 | 28.06.19 17:42:02 |         0.9081 |      9.2486 |
| B4E62DE92CE9 | 28.06.19 17:41:02 |         1.0588 |      9.1924 |
| B4E62DE92CE9 | 28.06.19 17:40:01 |         1.2128 |      9.2432 |
| B4E62DE92CE9 | 28.06.19 17:39:02 |         1.3599 |      9.1256 |
| B4E62DE92CE9 | 28.06.19 17:38:02 |         1.5092 |      9.1123 |
| B4E62DE92CE9 | 28.06.19 17:37:01 |         1.6621 |      9.1741 |
| B4E62DE92CE9 | 28.06.19 17:36:01 |         1.8128 |      9.1962 |
| B4E62DE92CE9 | 28.06.19 17:35:01 |         1.9638 |      9.2129 |
| B4E62DE92CE9 | 28.06.19 17:34:02 |         2.1107 |       9.115 |
+--------------+-------------------+----------------+-------------+

如果有人能帮助我，那就太好了。

亲切的问候帕特里克

Answer 1

我现在能够计算瓦特/小时，因为我必须首先将日期转换为 UNIX_TIMESTAMP。我现在在 SQL 中努力求和 counterReading 始终是:counterRading + Wh 的最后一个值。

这里是我到目前为止的 SQL:

SELECT date,
       deviceID,
       activePower,
       counterReading,
       (UNIX_TIMESTAMP(date) - LAG(UNIX_TIMESTAMP(date)) OVER (ORDER BY date ASC)) * activePower / 3600 AS Wh
    FROM device_data
    WHERE deviceID = 'B4E62DE92E05';

我尝试过类似的事情

SELECT ...
    counterReading + (UNIX_TIMESTAMP(date) - LAG(UNIX_TIMESTAMP(date)) OVER (ORDER BY date ASC)) * activePower / 3600 AS Wh
    FROM ...

但这根本不起作用。这就是我得到的结果。

+---------------------+--------------+-------------+----------------+------------+
| date                | deviceID     | activePower | counterReading | Wh         |
+---------------------+--------------+-------------+----------------+------------+
| 2019-06-28 15:00:02 | B4E62DE92E05 |     80.2636 |           NULL |       NULL |
| 2019-06-28 15:01:01 | B4E62DE92E05 |     79.6895 |           NULL | 1.30602236 |
| 2019-06-28 15:02:01 | B4E62DE92E05 |     78.8532 |           NULL | 1.31422000 |
| 2019-06-28 15:03:01 | B4E62DE92E05 |     78.1088 |           NULL | 1.30181333 |
| 2019-06-28 15:04:01 | B4E62DE92E05 |     77.5467 |           NULL | 1.29244500 |
| 2019-06-28 15:05:01 | B4E62DE92E05 |     76.2649 |           NULL | 1.27108167 |
| 2019-06-28 15:06:01 | B4E62DE92E05 |     76.3540 |           NULL | 1.27256667 |
| 2019-06-28 15:07:01 | B4E62DE92E05 |     74.6706 |           NULL | 1.24451000 |
+---------------------+--------------+-------------+----------------+------------+