是否有任何 MySQL 基准测试工具？[关闭]

Question

Brian Leach

Asked: 2015-09-11 22:32:38 +0800 CST2015-09-11 22:32:38 +0800 CST 2015-09-11 22:32:38 +0800 CST

识别 n 列的多个间隙和孤岛

772

在上一个问题中，我询问了一种识别一些差距和孤岛的方法。下表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列不存在的情况，尽管这是唯一的区别

1 个回答

Voted

tombom · Answer 1 · 2015-09-12T03:03:26+08:00

样本数据：

CREATE TABLE t
    (`ts` varchar(19), `EngOilP_sd` int, `CompOilLVL_sd` int)
;

INSERT INTO t
    (`ts`, `EngOilP_sd`, `CompOilLVL_sd`)
VALUES
    ('2015-06-24 20:28:07', 0, 0),
    ('2015-06-24 20:30:20', 1, 0),
    ('2015-06-24 20:36:47', 1, 1),
    ('2015-06-24 20:41:11', 1, 1),
    ('2015-06-24 20:43:29', 1, 1),
    ('2015-06-24 20:45:42', 1, 0),
    ('2015-06-24 20:47:51', 0, 0),
    ('2015-06-24 20:49:59', 0, 1),
    ('2015-06-24 20:52:01', 1, 1),
    ('2015-06-24 20:53:01', 1, 1),
    ('2015-06-24 20:54:01', 1, 1),
    ('2015-06-24 20:56:01', 1, 1),
    ('2015-06-24 20:57:01', 1, 1),
    ('2015-06-24 20:58:01', 1, 1),
    ('2015-06-24 21:02:01', 1, 1),
    ('2015-06-24 21:32:01', 0, 1),
    ('2015-06-24 21:52:01', 0, 0),
    ('2015-06-24 22:12:01', 0, 0),
    ('2015-06-24 22:20:01', 0, 0),
    ('2015-06-24 22:49:01', 0, 1),
    ('2015-06-24 23:52:01', 0, 1),
    ('2015-06-24 23:54:17', 0, 0)
;

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;

询问：

SELECT sd, TIMEDIFF(MAX(ts), MIN(ts)) AS duration, MIN(ts) AS startime, MAX(ts) AS endtime
FROM (
    SELECT 
    tp.*
    , @val_change := IF(@prev_val != tp.val, @val_change + 1, @val_change) AS vc
    , @prev_val := tp.val
    FROM
    t_pivot tp
    , (SELECT @prev_val := NULL, @val_change := 0) var_init_subquery
    ORDER BY sd, ts
) sq
WHERE val = 1
GROUP BY sd, vc;

结果：

|            sd |                  duration |            startime |             endtime |
|---------------|---------------------------|---------------------|---------------------|
| CompOilLVL_sd | January, 01 1970 00:06:42 | 2015-06-24 20:36:47 | 2015-06-24 20:43:29 |
| CompOilLVL_sd | January, 01 1970 00:42:02 | 2015-06-24 20:49:59 | 2015-06-24 21:32:01 |
| CompOilLVL_sd | January, 01 1970 01:03:00 | 2015-06-24 22:49:01 | 2015-06-24 23:52:01 |
|    EngOilP_sd | January, 01 1970 00:15:22 | 2015-06-24 20:30:20 | 2015-06-24 20:45:42 |
|    EngOilP_sd | January, 01 1970 00:10:00 | 2015-06-24 20:52:01 | 2015-06-24 21:02:01 |

看到它在sqlfiddle中实时工作

解释：

通过子句中的子查询，FROM我们正在初始化我们的变量。非常ORDER BY重要！原因如下：在SELECT子句中处理每一行。子句中的顺序SELECT也很重要。这里

, @val_change := IF(@prev_val != tp.val, @val_change + 1, @val_change) AS vc

@val_change如果列中的值val与“上一个”行不同（这就是为什么ORDER BY如此重要），我们会增加我们的变量。“上一行”在变量中@prev_val。当前行的值在这一行赋值给它

, @prev_val := tp.val

由于 MySQL 按照子句写入SELECT的顺序处理子句中的每一行，因此该变量保存子句前一行中前一行的值（或 NULL，当尚未处理任何行时）。SELECT@prev_valSELECT

现在我们所要做的就是GROUP BYsd 和@val_change外部查询中的 this 变量。

在此处的手册中阅读有关如何使用用户定义变量的更多信息。

警告：

这将导致全表扫描。

抱歉英语不好，希望它是可以理解的

更新问题：

查询仍然是相同的。你只需要调整视图t_pivot

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
    union all
    select 'Brake_sd', ts, Brake_sd from t;

包括Brake_sd.

看到它在sqlfiddle中实时工作

识别 n 列的多个间隙和孤岛

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

识别 n 列的多个间隙和孤岛

1 个回答

相关问题