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

以下示例数据中的案例

1 (Simple case) - IP address "10.0.1.2" is remains OFF for 00:10:10 to 00:20:00 and "10.0.1.3" is also OFF in this duration, so the common duration of OFF for both is 00:10:10 to 00:20:00.

2 (Problematic)- IP address "10.0.1.2" is OFF for 13:00:00 to 13:25:00 and if we check it with other IP address, it is OFF for 12:55:00 to 13:20:00. So, the common duration in both are 13:00:00 to 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

最佳答案

这是给你的开胃菜。

• 为了便于阅读，我将 IP 地址缩减为 int，ip。
• 我将状态更改为文本。它应该是 bool 值，如果 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 的内部选择，考虑 (ip, status, ts) 上的索引。

对于连接，您的 DBMS 可能会使用 ts 上的索引。 CTE(WITH 子句)只会具体化虚拟表一次。这可能不适用于多次复制粘贴 CTE(此处为两次)。

这对您来说应该是一个粗略的开始。到目前为止，它还不是完美的或最佳的解决方案。可能还有其他更好的。

关于MySQL查询以获得共同的持续时间，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/43960355/