主页 / user-73586

Brian Leach's questions

Martin Hope
Brian Leach
Asked: 2015-09-11 22:32:38 +0800 CST
  • 2

在上一个问题中，我询问了一种识别一些差距和孤岛的方法。下表t；

+---------------------+------------+---------------+
| ts                  | EngOilP_sd | CompOilLVL_sd |
+---------------------+------------+---------------+
| 2015-06-24 20:28:07 |          0 |             0 |
| 2015-06-24 20:30:20 |          1 |             0 |
| 2015-06-24 20:36:47 |          1 |             0 |
| 2015-06-24 20:41:11 |          1 |             0 |
| 2015-06-24 20:43:29 |          1 |             0 |
| 2015-06-24 20:45:42 |          0 |             0 |
| 2015-06-24 20:47:51 |          0 |             0 |
| 2015-06-24 20:49:59 |          0 |             1 |
| 2015-06-24 20:52:01 |          0 |             1 |
| 2015-06-24 20:54:17 |          0 |             0 |
+---------------------+------------+---------------+

下面的视图将给我值连续的数据孤岛true；

create view t_pivot as
    select 'EngOilP_sd' as sd, ts, EngOilP_sd as val from t
    union all
    select 'CompOilLVL_sd', ts, CompOilLVL_sd from t;

# explain
select sd, timediff(stop, start) as duration, start, stop
from (
    select x.sd, x.start, min(case y.val when 0 then y.ts end) as stop
    from (
        select sd, min(case val when 1 then ts end) as start
        from t_pivot
        group by sd
    ) as x
    join t_pivot as y
        on x.sd = y.sd
       and y.ts > x.start
    group by x.sd, x.start
) as z
order by sd desc;

给予

+---------------+----------+---------------------+---------------------+
| sd            | duration | start               | stop                |
+---------------+----------+---------------------+---------------------+
| EngOilP_sd    | 00:15:22 | 2015-06-24 20:30:20 | 2015-06-24 20:45:42 |
| CompOilLVL_sd | 00:04:18 | 2015-06-24 20:49:59 | 2015-06-24 20:54:17 |
+---------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)

现在，我很困惑如何查看下表中的 n 个差距

+---------------------+------------+---------------+---------------+
| ts                  | EngOilP_sd | CompOilLVL_sd | Brake_sd      |
+---------------------+------------+---------------+---------------+
| 2015-06-24 20:28:07 |          0 |             0 |             0 |
| 2015-06-24 20:30:20 |          1 |             0 |             0 |
| 2015-06-24 20:36:47 |          1 |             1 |             0 |
| 2015-06-24 20:41:11 |          1 |             1 |             1 |
| 2015-06-24 20:43:29 |          1 |             1 |             1 |
| 2015-06-24 20:45:42 |          1 |             0 |             1 |
| 2015-06-24 20:47:51 |          0 |             0 |             1 |
| 2015-06-24 20:49:59 |          0 |             1 |             0 |
| 2015-06-24 20:52:01 |          1 |             1 |             0 |
| 2015-06-24 20:53:01 |          1 |             1 |             0 |
| 2015-06-24 20:54:01 |          1 |             1 |             1 |
| 2015-06-24 20:56:01 |          1 |             1 |             1 |
| 2015-06-24 20:57:01 |          1 |             1 |             1 |
| 2015-06-24 20:58:01 |          1 |             1 |             1 |
| 2015-06-24 21:02:01 |          1 |             1 |             1 |
| 2015-06-24 21:32:01 |          0 |             1 |             1 |
| 2015-06-24 21:52:01 |          0 |             0 |             1 |
| 2015-06-24 22:12:01 |          0 |             0 |             0 |
| 2015-06-24 22:20:01 |          0 |             0 |             0 |
| 2015-06-24 22:49:01 |          0 |             1 |             1 |
| 2015-06-24 23:52:01 |          0 |             1 |             1 |
| 2015-06-24 23:54:17 |          0 |             0 |             0 |
+---------------------+------------+---------------+---------------+

我在这里寻找的结果集如下

+---------------+----------+---------------------+---------------------+
| sd            | duration | start               | stop                |
+---------------+----------+---------------------+---------------------+
| EngOilP_sd    | 00:15:22 | 2015-06-24 20:30:20 | 2015-06-24 20:45:42 |
| EngOilP_sd    | 00:10:00 | 2015-06-24 20:52:20 | 2015-06-24 21:02:42 |
| CompOilLVL_sd | 00:06:42 | 2015-06-24 20:36:47 | 2015-06-24 20:43:29 |
| CompOilLVL_sd | 00:42:02 | 2015-06-24 20:49:59 | 2015-06-24 21:32:01 |
| CompOilLVL_sd | 00:03:00 | 2015-06-24 20:49:59 | 2015-06-24 20:54:17 |
| Brake_sd      | 00:06:40 | 2015-06-24 20:41:11 | 2015-06-24 20:47:51 |
| Brake_sd      | 00:58:01 | 2015-06-24 20:54:01 | 2015-06-24 21:52:01 |
| Brake_sd      | 01:02:00 | 2015-06-24 22:49:01 | 2015-06-24 23:52:01 |
+---------------+----------+---------------------+---------------------+
8 rows in set (0.00 sec)

编辑最初，我没有Brake_sd问题中的列。用户@tombom 的回答正确地描述了Brake_sd列不存在的情况，尽管这是唯一的区别

mysql gaps-and-islands
Martin Hope
Brian Leach
Asked: 2015-08-26 14:09:25 +0800 CST
  • 5

我有一个具有以下结构的数据库

+---------------------+------------+---------------+
| timestamp           | EngOilP_sd | CompOilLVL_sd |
+---------------------+------------+---------------+
| 2015-06-24 20:28:07 |          0 |             0 |
| 2015-06-24 20:30:20 |          1 |             0 |
| 2015-06-24 20:36:47 |          1 |             0 |
| 2015-06-24 20:41:11 |          1 |             0 |
| 2015-06-24 20:43:29 |          1 |             0 |
| 2015-06-24 20:45:42 |          0 |             0 |
| 2015-06-24 20:47:51 |          0 |             0 |
| 2015-06-24 20:49:59 |          0 |             1 |
| 2015-06-24 20:52:01 |          0 |             1 |
| 2015-06-24 20:54:17 |          0 |             0 |
+---------------------+------------+---------------+

我想生成如下报告：

sd             duration      start                    stop
EngOilP_sd     15min 22s     2015-06-24 20:30:20      2015-06-24 20:45:42
CompOilLVL_sd  4min 18s      2015-06-24 20:49:59      2015-06-24 20:54:17

我应该补充一点，停止记录是第一个值 = 0 的记录并不重要，它可能是最后一个值 = 1 的记录

我怎样才能轻松地创建一个性能良好的查询来提供我需要的信息？我在应用程序中使用 Python，因此我不需要 SQL 以这种确切的形式输出报告，只需让我了解信息的内容即可。

mysql gaps-and-islands