+------+--------+---------------------+
| name | status | timecode |
| U | ON | 2013-09-08 00:11:40 |
| A | OFF | 2013-09-08 00:12:29 |
| W1 | ON | 2013-09-08 00:35:29 |
| C | ON | 2013-09-08 01:35:37 |
| C | OFF | 2013-09-08 02:12:29 |
| G2 | ON | 2013-09-08 02:35:55 |
| C | ON | 2013-09-08 03:19:29 |
| J | ON | 2013-09-08 03:35:20 |
| W2 | ON | 2013-09-08 04:26:31 |
| M | ON | 2013-09-08 04:44:23 |
| A | ON | 2013-09-08 05:19:29 |
| C | OFF | 2013-09-08 05:48:50 |
| W1 | OFF | 2013-09-08 05:55:55 |
| M | OFF | 2013-09-08 06:32:29 |
| U | OFF | 2013-09-08 06:52:49 |
| J | OFF | 2013-09-08 08:12:29 |
| W2 | ON | 2013-09-08 08:19:29 |
| G2 | OFF | 2013-09-08 09:12:29 |
| W2 | OFF | 2013-09-08 09:26:39 |
| Ma | ON | 2013-09-08 09:35:20 |
| C | ON | 2013-09-08 09:57:28 |
| J | ON | 2013-09-08 10:19:29 |
| Gw | ON | 2013-09-08 10:35:20 |
| A | OFF | 2013-09-08 11:48:50 |
| G2 | ON | 2013-09-08 15:19:55 |
| W2 | OFF | 2013-09-08 15:28:17 |
| G2 | OFF | 2013-09-08 16:48:38 |
| W1 | ON | 2013-09-08 18:10:44 |
| Gw | OFF | 2013-09-08 20:12:29 |
| W1 | OFF | 2013-09-08 20:44:18 |
| Ma | OFF | 2013-09-08 21:12:29 |
| U | ON | 2013-09-08 23:15:59 |
+------+--------+---------------------+
以上是来自一组传感器的数据,这些传感器在一天中的不同时间打开和关闭。我想计算每个传感器每天打开多少小时。我知道如何获得两次之间的差异,但是如何将每个ON与OFF配对。有时,其中一个传感器可能会跳过读数并丢弃ON或OFF,因此必须丢弃它们
最好的选择是使用临时表、联接或变量吗?我需要在页面上打印结果。
SELECT x.*
, MIN(y.timecode) pair
, SEC_TO_TIME(TIME_TO_SEC(MIN(y.timecode))-TIME_TO_SEC(x.timecode)) diff
FROM my_table x
JOIN my_table y
ON y.name = x.name
AND y.status <> x.status
AND y.timecode > x.timecode
WHERE x.status = 'ON'
GROUP
BY x.name
, x.timecode;