MySQL timediff для одного столбца для выбранных записей в таблице

В основном то, что я пытаюсь сделать, это получить продолжительность вызова на основе сигнализации 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

Идентификатор от первого BYE для данного CallID. CallID всегда один и тот же для каждого отдельного вызова. 00:00:04 должно быть разницей между date и 3.999876 разницей между micro_ts.

SQL должен возвращать одну запись для каждого callid. Сложность здесь в том, что я пытаюсь понять разницу между сообщениями, указывающими на начало/остановку вызова.

Я хочу, чтобы он возвращал продолжительность на основе «Первый метод = 200 после метода = 183 до первого BYE» или «Первый метод = 200 после метода = 180, и метод = 183 не найден до первого BYE»

Как я могу объединить эти критерии, чтобы вернуть то, что я хочу для таблицы, содержащей несколько вызовов?

Я пытался написать 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 раз, а затем ПОКА.

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

mysql sql select datediff timedelta
person Andy Thompson    schedule 05.04.2016    source источник
comment
Вы сказали, что работаете с предположением, что есть только 1 200 вызовов, но в обоих местах вы выделили 200, в строке ниже есть 200 от одного и того же callID. Можете ли вы сделать какие-либо предположения, например, что тот же идентификатор вызова не произойдет через минуту, но что ответ «пока» будет выполнен через ‹ 1 минуту?   -  person BugFinder    schedule 11.04.2016
comment
Привет @BugFinder. В моем примере SQL используется это предположение, но я хочу, чтобы это изменилось. Предполагается, что при ответе на вызов будет отображаться только 200 для каждого CallID. Это предположение не всегда будет верным. Я хочу, чтобы он возвращал продолжительность на основе Первый метод = 200 после метода = 183 до первого BYE или Первый метод = 200 после метода = 180, и метод = 183 не найден до первого BYE   -  person Andy Thompson    schedule 11.04.2016
comment
Позвольте мне попытаться перефразировать.. Продолжительность между 200 и BYE для уникальных CallID, когда 200 появляется после 183 или 180, если 183 отсутствует, и до BYE   -  person Andy Thompson    schedule 11.04.2016
comment
Как быстро можно повторно использовать callID?   -  person BugFinder    schedule 11.04.2016
comment
Согласно RFC 3261, его можно повторно использовать для каждого вызова, поскольку в RFC указано, что он должен быть уникальным в пространстве и времени для каждого отдельного вызова/сеанса. В этом конкретном случае он не будет использоваться повторно, но если у вас есть решение, которое может принимать комбинацию callID, from_user, to_user, date (все эти поля не включены в пример), это даже лучше. Я принимаю оба :)   -  person Andy Thompson    schedule 11.04.2016
comment
Итак, какие уникальные поля вы еще не упомянули.. И можем ли мы проиндексировать материал?   -  person BugFinder    schedule 12.04.2016
comment
@AndyThompson, твоя логика в отношении методов 200, 180, 183 непонятна. Можно ли его переформулировать следующим образом? Вызов начинается с первого вхождения двух последовательных строк с method=183, then method=200 ИЛИ method=180, then method=200. Другими словами, вызов начинается с первой строки, когда method=200 И непосредственно в предыдущей строке есть method in (180, 183). (порядок строк определяется столбцом id)   -  person Vladimir Baranov    schedule 12.04.2016
comment
Вызов начинается, если после 183 есть 200 или после 180, если 183 отсутствует. 180/183 может быть множество, но по крайней мере один должен существовать. Непосредственно предыдущий ряд не обязательно должен быть 180/183.   -  person Andy Thompson    schedule 12.04.2016
comment
@BugFinder Мы можем индексировать. Других уникальных полей, которые еще не упомянуты, нет.   -  person Andy Thompson    schedule 12.04.2016
comment
У меня есть несколько идей, но они кажутся невероятно грязными   -  person BugFinder    schedule 12.04.2016

Ответы (2)


arrow_upward
1
arrow_downward

Я попробовал, и могут возникнуть некоторые проблемы с приведением выражений 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) самостоятельно.

person Arth    schedule 12.04.2016
comment
Это работает как шарм — и дает правильную продолжительность, если вы измените end_date — start_date как duration1, чтобы включить timediff, а также @has_200 и @has_bye для _ms на micro_ts. Большое спасибо! - person Andy Thompson; 12.04.2016
comment
Я также хотел бы добавить, что это было хорошо написано и понятно для нас, неспециалистов. Чистое золото. - person Andy Thompson; 12.04.2016

arrow_upward
0
arrow_downward

У меня нет под рукой mySQL.. так что это может быть серьезной чепухой, но это то, что у меня в голове, я хотел записать это, прежде чем оно убежало - как я уже сказал, это кажется грязным

select callid, ,datediff(data,case when start1<=start2 then start1 else start2 end) as duration from 
   (select callid,call_from,call_to,min(starta) as start1,min(startb) as start2,min(data) from 
        (select callid,call_from,call_to,
          count(case when method = 180 THEN date END) starta,
          count(case when method = 183 THEN date END) startb,
          count(case when method = 200 THEN date END) data,
        from mydata) group by callid, call_from,call_to))

Это помогает? это дает вам идеи?

person BugFinder    schedule 12.04.2016