文章/答案/技术大牛

发布

社区首页 >问答首页 >获取公共持续时间的MySQL查询

问获取公共持续时间的MySQL查询
EN

Stack Overflow用户

提问于 2017-05-14 04:23:27

回答 2查看 332关注 0票数 5

我无法解决这个查询问题，因为获取两个不同IP地址的公共持续时间，其中两个IP地址都处于OFF状态。

以下样本数据中的案例

1(简单情况)- IP地址"10.0.1.2“在00:10:10至00:20:00保持关闭，"10.0.1.3”在此期间也关闭，因此两者的关闭时间都是00:10:10至00:20:00。 2(问题)- IP地址"10.0.1.2“为13:00至13:25:00关闭，如果我们使用其他IP地址检查它，则为12:55:00至13:20:00关闭。所以，两者的一般持续时间是13:00到13:20:00。

样本数据：

ID   IP address  Status   Time
----------------------------------
1    10.0.1.2    OFF      00:10:00
1    10.0.1.2    ON       00:20:00
1    10.0.1.2    OFF      11:00:00
1    10.0.1.2    ON       11:20:00
1    10.0.1.2    OFF      13:00:00
1    10.0.1.2    ON       13:25:00
1    10.0.1.2    OFF      14:05:00
1    10.0.1.2    ON       14:10:00
1    10.0.1.2    OFF      15:35:00
1    10.0.1.2    ON       15:45:00
1    10.0.1.3    OFF      00:10:00
1    10.0.1.3    ON       00:20:00
1    10.0.1.3    OFF      11:05:00
1    10.0.1.3    ON       11:25:00
1    10.0.1.3    OFF      12:55:00
1    10.0.1.3    ON       13:20:00
1    10.0.1.3    OFF      17:10:00
1    10.0.1.3    ON       17:15:00
1    10.0.1.3    OFF      15:00:00
1    10.0.1.3    ON       16:45:00

输出：

ID   IP addresses       Status  Time
-----------------------------------------
1    10.0.1.3,10.0.1.2  OFF      00:10:00
1    10.0.1.3,10.0.1.2  ON       00:20:00
1    10.0.1.3,10.0.1.2  OFF      11:05:00
1    10.0.1.3,10.0.1.2  ON       11:20:00
1    10.0.1.3,10.0.1.2  OFF      13:00:00
1    10.0.1.3,10.0.1.2  ON       13:20:00
1    10.0.1.3,10.0.1.2  OFF      15:35:00
1    10.0.1.3,10.0.1.2  ON       15:45:00

mysql

sql

回答 2

Stack Overflow用户

回答已采纳

发布于 2017-05-14 09:24:50

这是给你的起跑线。

为了提高可读性，我将IP地址降为int，ip。
我把状态改为短信。它应该是布尔型的，如果MySQL没有这种情况，那么可能是char(1)或具有CHECK约束的int。
您需要考虑一些约束或唯一的索引来保证状态切换，并防止在它已经打开时打开它(打开多次)？
声明适当的索引以加快查询速度。否则就是二次复杂性。

CREATE TABLE foo (ip int NOT NULL, status text NOT NULL,
    ts time NOT NULL, PRIMARY KEY (ip, status, ts));

INSERT INTO foo VALUES
(2, 'OFF', '00:10:00'),
(2, 'ON',  '00:20:00'),
(2, 'OFF', '11:00:00'),
(2, 'ON',  '11:20:00'),
(2, 'OFF', '13:00:00'),
(2, 'ON',  '13:25:00'),
(2, 'OFF', '14:05:00'),
(2, 'ON',  '14:10:00'),
(2, 'OFF', '15:35:00'),
(2, 'ON',  '15:45:00'),
(3, 'OFF', '00:10:00'),
(3, 'ON',  '00:20:00'),
(3, 'OFF', '11:05:00'),
(3, 'ON',  '11:25:00'),
(3, 'OFF', '12:55:00'),
(3, 'ON',  '13:20:00'),
(3, 'OFF', '17:10:00'),
(3, 'ON',  '17:15:00'),
(3, 'OFF', '15:00:00'),
(3, 'ON',  '16:45:00');

假设您在MySQL中有公共表表达式CTE(除其他外，您也没有指定版本)。

如果您没有CTE，那么只需复制和替换对CTE的所有引用(在本例中是off)，并给它命名。最后一个示例将不使用WITH。

WITH off AS
(SELECT ip,
        ts "off_from",
        (SELECT ts FROM foo
         WHERE ip = a.ip AND a.ts <= ts AND status = 'ON'
         ORDER BY ts ASC LIMIT 1) "off_until"
 FROM foo a WHERE status = 'OFF'
)
SELECT * FROM off;

这给

 ip | off_from | off_until
----+----------+-----------
  2 | 00:10:00 | 00:20:00
  2 | 11:00:00 | 11:20:00
  2 | 13:00:00 | 13:25:00
  2 | 14:05:00 | 14:10:00
  2 | 15:35:00 | 15:45:00
  3 | 00:10:00 | 00:20:00
  3 | 11:05:00 | 11:25:00
  3 | 12:55:00 | 13:20:00
  3 | 17:10:00 | 17:15:00
  3 | 15:00:00 | 16:45:00

WITH off AS
(SELECT ip,
        ts "off_from",
        (SELECT ts FROM foo
         WHERE ip = a.ip AND a.ts <= ts AND status = 'ON'
         ORDER BY ts ASC LIMIT 1) "off_until"
 FROM foo a WHERE status = 'OFF'
)
SELECT *
FROM off x
INNER JOIN off y
ON  x.off_from <= y.off_from AND y.off_from < x.off_until
AND x.ip <> y.ip ;

 ip | off_from | off_until | ip | off_from | off_until
----+----------+-----------+----+----------+-----------
  2 | 00:10:00 | 00:20:00  |  3 | 00:10:00 | 00:20:00
  2 | 11:00:00 | 11:20:00  |  3 | 11:05:00 | 11:25:00
  3 | 00:10:00 | 00:20:00  |  2 | 00:10:00 | 00:20:00
  3 | 12:55:00 | 13:20:00  |  2 | 13:00:00 | 13:25:00
  3 | 15:00:00 | 16:45:00  |  2 | 15:35:00 | 15:45:00

并得到采取最小和最大的时间使用

WITH off AS
(SELECT ip,
        ts "off_from",
        (SELECT ts FROM foo
         WHERE ip = a.ip AND a.ts <= ts AND status = 'ON'
         ORDER BY ts ASC LIMIT 1) "off_until"
 FROM foo a WHERE status = 'OFF'
)
SELECT x.ip "ip_a", y.ip "ip_b",
       greatest( x.off_from, y.off_from ) "off_from",
       least( x.off_until, y.off_until ) "off_until"
FROM off x
INNER JOIN off y
ON  x.off_from <= y.off_from AND y.off_from < x.off_until
AND x.ip <> y.ip ;

屈服

 ip_a | ip_b | off_from | off_until
------+------+----------+-----------
    2 |    3 | 00:10:00 | 00:20:00
    2 |    3 | 11:05:00 | 11:20:00
    3 |    2 | 00:10:00 | 00:20:00
    3 |    2 | 13:00:00 | 13:20:00
    3 |    2 | 15:35:00 | 15:45:00

没有WITH (复制粘贴并命名CTE)。

SELECT x.ip "ip_a", y.ip "ip_b",
       greatest( x.off_from, y.off_from ) "off_from",
       least( x.off_until, y.off_until ) "off_until"
FROM
(SELECT ip,
        ts "off_from",
        (SELECT ts
         FROM foo
         WHERE ip = a.ip AND a.ts <= ts AND status = 'ON'
         ORDER BY ts ASC LIMIT 1) "off_until"
 FROM foo a WHERE status = 'OFF'
) x
INNER JOIN
(SELECT ip,
        ts "off_from",
        (SELECT ts
         FROM foo
         WHERE ip = a.ip AND a.ts <= ts AND status = 'ON'
         ORDER BY ts ASC LIMIT 1) "off_until"
 FROM foo a WHERE status = 'OFF'
) y
ON  x.off_from <= y.off_from
AND y.off_from < x.off_until
AND x.ip <> y.ip ;

对于使用LIMIT 1的内部select，请考虑(ip, status, ts)上的索引。

对于join，数据库管理系统可以使用ts上的索引。CTE (WITH子句)只实现一次虚拟表。这可能不适用于复制粘贴CTE几次(这里两次)。

这对你来说应该是个艰难的开端。到目前为止，这并不完美，也不是最好的解决方案。也许还有其他更好的。

票数 5

Stack Overflow用户

发布于 2017-05-25 09:22:14

一种方法是使用TIME_TO_SEC()以秒的形式获取时间，并计算存储过程中的差异：

Create table common_duration (
ip varchar (10),
start_time time,
end_time time
)

CREATE PROCEDURE `comm_time`()
    BEGIN
    DECLARE curs1 CURSOR FOR SELECT `IP`, TIME_TO_SEC(`time`) as time, STATUS FROM TABLE;
    DECLARE ip varchar(20);
    DECLARE iptime time;
    DECLARE ipstime time;
    DECLARE ipstatus varchar(10);
    OPEN curs1;
    FETCH curs1 INTO ip,iptime,ipstatus;
    if (status='ON')
    insert into `common_duration`(ip, start_time, end_time) values(ip, ipstime, iptime);
    else
    ipstime=iptime;
    endif;
    CLOSE curs1;
    SELECT t1.ip SEC_TO_TIME(t1.end_time-t1.start_time) as time_duration FROM `common_duration t1, `common_duration t2
    WHERE t1.time_duration= t2.time_duration
          AND t1.ip != t2.ip;
    End

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/43960355

复制

相似问题

问获取公共持续时间的MySQL查询
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取公共持续时间的MySQL查询EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取公共持续时间的MySQL查询
EN