logo标签列表

MySQL查询以获取共同持续时间

mysqlsql
5
我无法解决这个查询问题，该问题涉及提取两个不同IP地址的共同离线时间。以下是示例数据：
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”与其他IP地址进行比较，则其在13:00:00至13:25:00处于关闭状态，并且如果我们将其与另一个IP地址进行比较，则它在12:55:00至13:20:00处于关闭状态。因此，两者的公共时间为13:00: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
2
是的，我们需要您在此方面所做的工作。请阅读http://stackoverflow.com/help/how-to-ask。 - Barmar
1
不是真的。我们想看看你尝试过的SQL解决方案。然后我们可以告诉你哪里出了问题，以及如何修复它。这样你就可以从错误中学习。如果你还没有尝试用SQL来解决问题，那么你似乎是在要求我们为你编写代码。 - Barmar
1
嗨，阿布希什，你能回复一下我上面的消息吗？我可以看到你正在从你的个人资料登录。 - halfer
1
@halfer：好的...我会添加我的SQL尝试。 - Abhishek Ginani
1
不要添加任何尝试，投票关闭。 - halfer
显示剩余7条评论
2个回答
5

以下是针对您的翻译：

  • 我将 IP 地址转换为整数以增加可读性，`ip`。
  • 我更改了状态文本。它应该是布尔类型，如果 MySQL 不支持，则可以使用 char(1) 或具有 CHECK 约束的整数。
  • 您需要考虑一些约束或唯一索引来保证状态转换，并防止在已开启时再次开启（多次开启）。
  • 声明适当的索引以加快查询速度。否则它将具有二次复杂度。
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)上建立索引。
对于连接操作，可能可以在ts上建立索引以被您的数据库管理系统使用。CTE（WITH子句）将只生成一次虚拟表。这可能不适用于多次复制粘贴CTE（此处为两次）。
这只是一个大致的起点。它远非完美或最佳解决方案。可能还有其他更好的解决方案。
提示：如果您想在列表后立即呈现代码块，并且该块不应缩进为最后一个列表元素的一部分，则在列表后放置一个<!-- HTML注释 -->以将其分开。但是，如果您想在列表元素内呈现代码块，则将其缩进两次（即使用八个空格而不是四个）。希望对您有所帮助！ - halfer
1
为了鼓励你的出色回答，我已经在这个问题上添加了赏金。也许这可以弥补那些似乎没有利用你帮助的 OP 的次数 :-)。 - halfer
1
@halfer 哇哦——非常非常感谢你！在我看来，思考在 SO 上发布的一些数据库问题需要相当长的时间来理解并解决。然而，点击接受按钮和点赞可以很快完成——但有时人们不会欣赏这里发布者所付出的努力 :( 至少为解决问题给予发布者某些虚拟（信用）积分。也许我应该更少地回答新用户的问题，而更喜欢有经验的用户（100 分以上）。那么我怎样才能获得奖金？我需要改变什么吗？再次非常感谢你！ - flutter
我认为，如果您认为提问者在问题上做出了出色的努力，并且他们已经展示了他们所尝试的内容，那么这将在很大程度上保护您免受帮助吸血鬼的侵扰。话虽如此，这并不是一种完美的策略：我在您的历史记录中看到您回答了一个1K+用户的问题，但仍然难以获得接受。不幸的是，这种情况时有发生，虽然我不喜欢人们不接受答案，但在这里，答案接受被视为可选项。 - halfer
赏金只能在下单后24小时后颁发，所以最早我可以在明天这个时候颁发。然而，我倾向于让赏金运行到期（7天），因为这会给一个好问题或好答案一些曝光。您不需要做任何事情（系统会在赏金开始过期时提醒我颁发它）。 - halfer
2

一种方法是使用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
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接