表中所选记录的一列上的 MySQL 时间差

Question

基本上我想做的是根据 SIP 信令获取通话持续时间。

我有一个包含如下所示记录的表，我正在尝试编写一个返回以下内容的 SELECT 语句:

id  callid  date                    micro_ts            method  duration1   duration2
------------------------------------------------------------------------------------
25  123     2016-04-05 00:00:25     1459814425000320    BYE     00:00:04    3.999876
46  234     2016-04-05 00:01:25     1459814485000000    BYE     00:00:04    4.000000

该 ID 来自给定 CallID 的第一个 BYE。每个单独调用的 CallID 始终相同。 00:00:04 应该是 date 之间的差异和 3.999876 micro_ts 之间的差异。

SQL 应为每个 callid 返回一条记录。这里棘手的部分是我试图找出指示调用开始/停止的消息之间的差异。

我希望它返回基于“第一个方法 = 200 之后方法 = 183 直到第一次 BYE”或“第一个方法 = 200 之后方法 = 180 并且在第一次 BYE 之前找不到方法 = 183”

如何组合这些条件来返回包含多个调用的表所需的内容？

我一直在尝试这样编写 SQL:

select * from (
select id, callid, micro_ts, method, min(date) as StartTime, max(date) as EndTime,
timediff(max(date), min(date)) as duration
from (select t.*,
             (select count(*)
              from tableA t2
              where t2.date <= t.date and
                    t2.method = 'BYE'
             ) as grp
      from tableA t
     ) t where t.method = '200'
group by callid, method
order by 3 desc) z;

我相信这是在假设所有调用只有 200 一次然后是 BYE 的情况下有效的。

id  callid  date                    micro_ts            method
---------------------------------------------------------------
1   123     2016-04-05 00:00:01     1459814401000025    INVITE
2   123     2016-04-05 00:00:02     1459814402000123    407
3   123     2016-04-05 00:00:03     1459814403000941    INVITE
4   123     2016-04-05 00:00:04     1459814404000392    INVITE
5   123     2016-04-05 00:00:05     1459814405000539    INVITE
6   123     2016-04-05 00:00:06     1459814406000101    404
7   123     2016-04-05 00:00:07     1459814407000007    INVITE
8   123     2016-04-05 00:00:08     1459814408000948    404
9   123     2016-04-05 00:00:09     1459814409000784    100
10  123     2016-04-05 00:00:10     1459814410000192    183
11  123     2016-04-05 00:00:11     1459814411000482    183
12  123     2016-04-05 00:00:12     1459814412000561    183
13  123     2016-04-05 00:00:13     1459814413000392    183
14  123     2016-04-05 00:00:14     1459814414000751    180
15  123     2016-04-05 00:00:15     1459814415000012    180
16  123     2016-04-05 00:00:16     1459814416000384    180
17  123     2016-04-05 00:00:17     1459814417000498    180
18  123     2016-04-05 00:00:18     1459814418000533    183
19  123     2016-04-05 00:00:19     1459814419000841    183
20  123     2016-04-05 00:00:20     1459814420000492    183
21  123     2016-04-05 00:00:21     1459814421000444    200         * FIRST 200 after 183
22  123     2016-04-05 00:00:22     1459814422000901    200         |
23  123     2016-04-05 00:00:23     1459814423000294    ACK         |
24  123     2016-04-05 00:00:24     1459814424000732    ACK         |
25  123     2016-04-05 00:00:25     1459814425000320    BYE         * FIRST BYE
26  123     2016-04-05 00:00:26     1459814426000020    BYE
27  123     2016-04-05 00:00:27     1459814427000391    200
28  123     2016-04-05 00:00:28     1459814428000123    200
29  234     2016-04-05 00:01:01     1459814461000000    INVITE
30  234     2016-04-05 00:01:02     1459814462000000    407
31  234     2016-04-05 00:01:03     1459814463000000    INVITE
32  234     2016-04-05 00:01:04     1459814464000000    INVITE
33  234     2016-04-05 00:01:05     1459814465000000    INVITE
34  234     2016-04-05 00:01:06     1459814466000000    404
35  234     2016-04-05 00:01:07     1459814467000000    INVITE
36  234     2016-04-05 00:01:08     1459814468000000    404
37  234     2016-04-05 00:01:09     1459814469000000    100
38  234     2016-04-05 00:01:10     1459814470000000    183
39  234     2016-04-05 00:01:11     1459814471000000    183
40  234     2016-04-05 00:01:12     1459814472000000    183
41  234     2016-04-05 00:01:13     1459814473000000    183
42  234     2016-04-05 00:01:14     1459814474000000    180
43  234     2016-04-05 00:01:15     1459814475000000    180
44  234     2016-04-05 00:01:16     1459814476000000    180
45  234     2016-04-05 00:01:17     1459814477000000    180
46  234     2016-04-05 00:01:21     1459814481000000    200         * FIRST 200 after 180 whern no 183 is present
47  234     2016-04-05 00:01:22     1459814482000000    200         |
48  234     2016-04-05 00:01:23     1459814483000000    ACK         |
49  234     2016-04-05 00:01:24     1459814484000000    ACK         |
50  234     2016-04-05 00:01:25     1459814485000000    BYE         * FIRST BYE
51  234     2016-04-05 00:01:26     1459814486000000    BYE
52  234     2016-04-05 00:01:27     1459814487000000    200
53  234     2016-04-05 00:01:28     1459814488000000    200

Answer 1

我已经尝试过，CASE 表达式可能存在一些转换问题，但这应该可以帮助您完成大部分工作:

SELECT id,
      callid
      start_date,
      start_ms,
      end_date,
      end_ms,
      end_date - start_date AS duration1,
      end_ms - start_ms AS duration2
 FROM (
   SELECT 
          @same_call := @callid = callid,
          @has_ended := @same_call AND (@has_ended OR @has_bye) AS has_ended,
          @has_18x := (@has_18x AND @same_call) OR method IN ('180','183'),
          @has_200 := (@has_200 AND @same_call) OR (@has_18x AND method = '200'),
          @has_bye := (@has_bye AND @same_call) OR (@has_200 AND method = 'BYE') AS has_bye,

          @start_date := CASE 
                           WHEN @has_200 THEN COALESCE(@start_date, `date`)
                         END AS start_date,
          @start_ms := CASE 
                         WHEN @has_200 THEN COALESCE(@start_ms, micro_ts)
                       END AS start_ms,
          @end_date := CASE 
                         WHEN @has_bye THEN COALESCE(@end_date, `date`)
                       END AS end_date,
          @end_ms := CASE 
                       WHEN @has_bye THEN COALESCE(@end_ms, micro_ts)
                     END AS end_ms,

          id,
          @callid := callid AS callid           

    FROM sip_signal

    JOIN (
      SELECT @callid := NULL,
             @has_ended := FALSE,
             @has_18x := FALSE,
             @has_200 := FALSE,
             @has_bye := FALSE,
             @start_date := NULL,
             @start_ms := NULL,
             @end_date := NULL,
             @end_ms := NULL                
         ) init

ORDER BY callid, micro_ts

      ) sip_call
WHERE has_bye 
  AND NOT has_ended

如果您想查看内部工作原理，只需运行内部 SELECT(+ JOIN 和 ORDER BY)即可。