MySQL 匹配两个表中的电话

Question

这几天我一直在尝试解决一个看似简单的问题，但无论我采取哪种尝试，我似乎都无法完美地解决它。

我有两个简单的表，每个表有 3 列:call_date、call_time、caller_id 所有这些都是不言自明的。

第一个表包含入站调用，第二个表包含出站调用。

接到呼入电话后，应在30分钟内调用相应的呼出电话。到目前为止一切顺利，没有问题，但这就是有点棘手的地方:

1. 入站表中同一 caller_id 可以有多个入站调用。
2. 只有与上一个相关调用相距至少 30 分钟的调用才被视为有效调用。因此，当有人调用四次时，假设在 08:00、08:15、08:31 和 09:31，则有 3 个有效调用:08:00、08:31 和 09:31。因此，在每次有效调用后，您必须再次等待 30 分钟才能使下一次调用有效。
3. 呼出表还可以不包含对同一号码的调用、一次或多次调用，所有调用都必须尽可能与来电匹配。

我尝试了各种方法来解决这个问题，但所有方法都不是 100% 准确，总是留下一个或多个调用不匹配或未标记为有效等，我的头开始有点旋转。

现在，我从入站调用表中构建了一个巨大的表，为 previous_calltime 和 next_calltime 添加了许多列，以便我可以计算时间戳之间的分钟数。我采取的第二种方法是创建单独的表，其中包含每个号码每天的第一个和最后一个调用等，但有些东西总是无法相加。

这是我的数据表示例，入站和出站是相同的。

+----+-----------+----------+----------+
| id | call_date | call_time|caller_id
+----+-----------+----------+----------+
|  1 | 2013-06-01| 08:00    | 12345
+----+-----------+----------+----------+
|  2 | 2013-06-01| 08:20    | 12345
+----+-----------+----------+----------+
|  3 | 2013-06-01| 08:30    | 12345
+----+-----------+----------+----------+
|  4 | 2013-06-01| 08:32    | 555-999
+----+-----------+----------+----------+
|  5 | 2013-06-01| 08:47    | 555-999
+----+-----------+----------+----------+

也许有人可以为我指明应该采取哪种方法的粗略方向。任何建议表示赞赏。

P.s.我在这里尝试 100% 基于 MySQL 的方法，但理论上我也可以使用 PHP，以防万一有人认为这在纯 SQL 中做起来很痛苦，而使用 PHP 可能会容易得多。

Answer 1

要使用的初始代码。

这(暂时)忽略日期，但应该为您提供有效调用的列表。不确定它会那么快，但使用它来填充有效调用的列表(或将入站调用标记为有效或无效)可能是一个主意。

使用上面的测试数据给出了我期望的结果:-

SELECT Sub1.*, 
        @valid := if(@PrevCallerId != Sub1.caller_id OR (TIME_TO_SEC(call_time) - @PrevValidCall) >= 1800, "valid", "invalid") AS valid_call,
        @PrevValidCall := if(@valid = "valid", TIME_TO_SEC(Sub1.call_time), @PrevValidCall),
        @PrevCallerId := Sub1.caller_id
FROM
(
    SELECT *
    FROM Inbound
    ORDER BY caller_id
) Sub1
CROSS JOIN (SELECT @PrevValidCall:= 0, @PrevCallerId := "", @valid := "") Sub2