MySQL - максимальное количество посещений по датам и периодам

У меня есть таблица посещений (id int, время начала и окончания, дата и время), и вы хотите отслеживать количество пиковых посещений.

Пример данных:

+------+---------------------+---------------------+
| id   | date-time-1         | date-time-2         |
+------+---------------------+---------------------+
| 1059 | 2016-07-04 19:13:00 | 2016-07-04 19:20:05 |
| 1060 | 2016-07-04 19:13:30 | 2016-07-04 19:14:25 |
| 1061 | 2016-07-04 19:14:39 | 2016-07-04 19:20:05 |
| 1062 | 2016-07-05 02:34:40 | 2016-07-05 02:45:23 |
| 1063 | 2016-07-05 02:34:49 | 2016-07-05 02:45:34 |
+------+---------------------+---------------------+

Ожидаемый результат должен быть:

+-------------------------------------------+-------+ 
| date-time-1         | date-time-2         | count | 
+-------------------------------------------+-------+ 
| 2016-07-04 19:13:00 | 2016-07-04 19:13:29 | 1     | 
| 2016-07-04 19:13:30 | 2016-07-04 19:14:25 | 2     | 
| 2016-07-04 19:14:26 | 2016-07-04 19:14:38 | 1     | 
| 2016-07-04 19:14:39 | 2016-07-04 19:20:05 | 2     | 
| 2016-07-04 19:20:06 | 2016-07-05 02:34:39 | 0     | 
| 2016-07-05 02:34:40 | 2016-07-05 02:34:48 | 1     | 
| 2016-07-05 02:34:49 | 2016-07-05 02:45:23 | 2     | 
| 2016-07-05 02:45:24 | 2016-07-05 02:45:34 | 1     | 
+------+------------------------------------+-------+
 Jorge Campos05 июл. 2016 г., 01:59
Должно быть как минимум одно второе отличие от одного периода к другому.
 Jorge Campos05 июл. 2016 г., 01:59
Lol @AshwinNair ты удалил твой Я удалил мой :)
 Jorge Campos05 июл. 2016 г., 02:16
Поскольку сейчас я ничего не делаю, я найду ответ. :)
 Ash05 июл. 2016 г., 02:00
Ага : ). Я позволю этому быть.
 Jorge Campos05 июл. 2016 г., 02:35
Если между первым рядом и вторым нет интервалов, то первый заканчивается на19:13:30 а вторая начинается с19:13:31 Ваш счет неверен, потому что для первого ряда есть 2 посещения, для второго 2 для третьего 3 и для последнего 2. Итак, я спрашиваю, вам нужен этот интервал, или вы отредактируете счет?
 Jorge Campos05 июл. 2016 г., 04:32
Редактировать свой вопрос с ним.
 Drew05 июл. 2016 г., 03:07
@JorgeCampos ожидаемые результаты не объясняются и, конечно, не попадают в минутные округленные временные отрезки. Требуется использование кольца-декодера Spiderman. Так что я бы не стал потеть, пока не пингнулся.
 Ash05 июл. 2016 г., 02:07
Это хороший вопрос ... голосование против отрицательного, ненужного понижения голосов.
 Jorge Campos05 июл. 2016 г., 03:16
LoL @Drew Я жду его ответа, я уже настроил sqlfiddle с результатом, который я упомянул.
 Federico Omoto05 июл. 2016 г., 03:36
@JorgeCampos, @AshwinNair, вы правы, если между первым рядом и вторым нет интервалов, так как первый заканчивается19:13:30 а вторая начинается с19:13:31 мой счет неверен.
 Ash05 июл. 2016 г., 01:58
Правильно. Я просто выяснил, даты и время для каждого посещения.

Ответы на вопрос(2)

select U.dt1 as date-time-1, DATE_ADD(U.dt2,INTERVAL -1 SECOND) as date-time-2, 
    (select count(id) from Visits where 
    (dt1 >= u.dt1 and dt1<U.dt2)  --(dt1)dt2
    or (dt1<u.dt1 and dt2>=u.dt2)   -- dt1()dt2
    --or (dt2 >= u.dt1 and dt2<U.dt2) -- dt1(dt2) (comment this line to get your result which I believe is incorrect)
    ) as count 
from (
    select A.dt1 as dt1, (
        select min(M.dt) from ( select min(dt2) as dt from Visits where dt2 > A.dt1 union select min(dt1) as dt from Visits where dt1 > A.dt1) M
    ) as dt2 from Visits A
union 
    select B.dt2 as dt1, (
        select min(M.dt) from ( select min(dt2) as dt from Visits where dt2 > b.dt2 union select min(dt1) as dt from Visits where dt1 > b.dt2) M 
    ) as dt2 from Visits b where B.dt2 <> (select max(dt2) from Visits)
) U 

Я прокомментировал проверку условий, чтобы увидеть, начинается ли посещение раньше диапазона и заканчивается в нем, чтобы получить тот же набор результатов, что и вы, но я считаю, что вы должны учитывать это.

 Ash05 июл. 2016 г., 09:55
Я изменил этот ответ.

чтобы сделать эту работу, вам нужно понять свои периоды и наложение между ними. В комментариях мы согласились с тем, что для правильной работы необходимо, чтобы во втором ряду была добавлена ​​хотя бы одна секунда к предыдущему концу. Чтобы понять, что я добавлю график того, какими будут ваши периоды, и прямо ниже периодов отvisits таблица, так что вы увидите, в конце концов, что время (так как все периоды одинаковы день и час, я оставлю только минуты и секунды на графике)

13:00        13:30         14:26      14:39
^            ^             ^          ^           
|------------||-----------||----------||-----------|
              |_ 13:31    |_ 14:25     |_ 14:40    |_ 20:05

--and in your table
13:00                                              20:05
^                                                  ^
|--------------------------------------------------|
             |------------|           14:39        20:05
             |_ 13:30     |_ 14:25    ^            ^
                                      |------------|

Для достижения такой таблицы периодов я создалVIEW Чтобы облегчить запрос, вот код к нему:

create or replace view vw_times as
  select dtstart as dt from visits
   UNION
  select dtend as dt from visits;

Цель этого представления состоит в том, чтобы идентифицировать все датыstarts а такжеends ваших данных периодов.

А вот запрос, который будет выдавать сценарии с такими периодами:

SELECT case when cnt>1 
           then date_add(dtstart,interval 1 second) 
           else dtstart 
           end as dtstart,
         dtend
  from (SELECT dtstart, 
               dtend, 
               @ct:[email protected]+1 as cnt
          FROM ( SELECT t1.dt as dtstart,
                        (select min(x.dt) 
                           from vw_times as x
                          where x.dt > t1.dt
                         ) as dtend
                   FROM vw_times t1,
                        (select @ct := 0) as cttab
                  ORDER BY t1.dt
                ) t2
          WHERE dtend is not null
        ) as t3

И от этого вы можетеLEFT JOIN Ваша таблица, чтобы найти перекрывающиеся периоды, как это:

SELECT times.dtstart, times.dtend, count(*)
  FROM (SELECT case when cnt>1 
                 then date_add(dtstart,interval 1 second) 
                 else dtstart 
                 end as dtstart,
               dtend
        from (SELECT dtstart, 
                     dtend, 
                     @ct:[email protected]+1 as cnt
                FROM ( SELECT t1.dt as dtstart,
                              (select min(x.dt) 
                                 from vw_times as x
                                where x.dt > t1.dt
                               ) as dtend
                         FROM vw_times t1,
                              (select @ct := 0) as cttab
                        ORDER BY t1.dt
                      ) t2
                WHERE dtend is not null
              ) as t3
       ) as times 
       LEFT JOIN visits v 
              ON (    times.dtstart >= v.dtstart
                  AND times.dtend <= v.dtend)
 GROUP BY times.dtstart, times.dtend

Это приведет к:

dtstart                      dtend                   count(*)
July, 04 2016 19:13:00       July, 04 2016 19:13:30     1
July, 04 2016 19:13:31       July, 04 2016 19:14:25     2
July, 04 2016 19:14:26       July, 04 2016 19:14:39     1
July, 04 2016 19:14:40       July, 04 2016 19:20:05     2

Смотрите это работает здесь:http://sqlfiddle.com/#!9/3509ff/10

РЕДАКТИРОВАТЬ

Поскольку вы добавили комментарий с окончательным результатом, последний запрос будет еще меньше:

SELECT times.dtstart, 
       case when times.dtend = vmax.maxend
            then date_add(times.dtend, interval 1 second)
            else times.dtend
            end as dtend, 
       count(*)
  FROM  (SELECT dtstart, 
               dtend
          FROM ( SELECT t1.dt as dtstart,
                        (select min(date_sub(x.dt, interval 1 second)) 
                           from vw_times as x
                          where x.dt > t1.dt
                        ) as dtend
                   FROM vw_times t1
                  ORDER BY t1.dt
               ) t2
        WHERE t2.dtend is not null
       ) as times 
       LEFT JOIN visits as v
              ON (    times.dtstart >= v.dtstart
                  AND times.dtend <= v.dtend)
       LEFT JOIN (select max(date_sub(v.dtend, interval 1 second)) as maxend
                    from visits v) vmax
              ON ( times.dtend = vmax.maxend )
 GROUP BY times.dtstart, 
          case when times.dtend = vmax.maxend
            then date_add(times.dtend, interval 1 second)
            else times.dtend
            end

Это приведет к:

dtstart                   dtend                 count(*)
July, 04 2016 19:13:00    2016-07-04 19:13:29    1
July, 04 2016 19:13:30    2016-07-04 19:14:24    2
July, 04 2016 19:14:25    2016-07-04 19:14:38    1
July, 04 2016 19:14:39    2016-07-04 19:20:05    2

Смотрите это здесь:http://sqlfiddle.com/#!9/3509ff/24

 Jorge Campos05 июл. 2016 г., 05:58
Хм, у последнего значения есть пропущенная секунда, я отредактирую завтра, уже поздно в моем конце. Это будет как первый.
 Federico Omoto06 июл. 2016 г., 03:12
Просто для ясности, я отредактировал свой оригинальный вопрос, оставив только отредактированную часть. Спасибо!
 Federico Omoto05 июл. 2016 г., 17:08
Существуют минимальные различия между результатами, полученными с помощью этого запроса, и желаемыми результатами. Посмотри пожалуйста:pastebin.com/HhS4h36D Большое спасибо за ваши усилия в этом!
 Jorge Campos06 июл. 2016 г., 02:05
Вы не поняли цель запроса. Требуемый набор результатов невозможен, поскольку вы добавляете данные, которых не существует, в набор.date-time-2 никогда не может быть одинаковыми существующими данными существующих строк, это всегда будут одни существующие данные минус одна секунда, за исключением последней строки. И первый запрос всегда один существующие данные минус одна секунда для поляdate-time-1 кроме первого результата. В запросе нет ничего плохого. Проанализируйте свои данные правильно, чтобы понять результат, который дают оба запроса.
 Federico Omoto05 июл. 2016 г., 05:53
Запроспочти отлично, но окончательный результат должен быть: 2016-07-04 19:13:00 до 2016-07-04 19:13:29: 1, 2016-07-04 19:13:30 до 2016-07-04 19 : 14: 25: 2, 2016-07-04 19:14:26 до 2016-07-04 19:14:38: 1, 2016-07-04 19:14:39 до 2016-07-04 19:20 : 05: 2
 Federico Omoto06 июл. 2016 г., 03:07
Извините, но я не вижу никаких проблем в моих рассуждениях. Ваш запрос дает почти идеальные результаты. Пожалуйста, посмотрите ваш запрос здесь с новыми двумя добавленными записями в таблицу:sqlfiddle.com/#!9/fec695/2 Первое различие, которое я вижу, находится в пятом ряду,count значение должно быть0, поскольку в этот промежуток времени посещений нет. Есть также на 1 секунду меньше разницы наdate-time-2 столбец для второго, четвертого и седьмого ряда. И, наконец, разница на 1 секунду меньшеdate-time-1 столбец для третьей, пятой и восьмой (последней) строки.
 Federico Omoto05 июл. 2016 г., 06:57
Это также поздно здесь, в моем конце :) Спасибо !!!
 Jorge Campos05 июл. 2016 г., 14:06
@FedericoOmoto Смотрите мое последнее редактирование. Я думаю, что теперь все в порядке. Пожалуйста, добавьте новые значения в команды вставки в предоставленном sqlfiddle, чтобы вы могли проверить это. Если что-то не так, дайте мне знать.
 Jorge Campos06 июл. 2016 г., 00:18
Запрос отлично работает с данными, которые вы представили. Можете ли вы показать реальные данные? Вы представили только эти три строки с идентификатором 1059, 1060, 1061. Для желаемого результата в нем должно быть больше данных. Покажите мне, чтобы я мог видеть, что не так.
 Federico Omoto06 июл. 2016 г., 01:28
Пожалуйста, посмотрите на мой отредактированный вопрос (РЕДАКТИРОВАТЬ). Эти строки являются реальными данными из моей таблицы. Обратите внимание, что я добавил идентификаторы 1062 и 1063. Ваш запрос имеет разницу в 1 секунду для некоторых результатов строки, а с дополнительными данными (идентификаторы 1062 и 1063) есть строка со счетчиком 1, когда он должен быть равен 0. Вы можете увидеть разницу между результатами вашего запроса и желаемыми результатами здесь:pastebin.com/HhS4h36D Еще раз спасибо за то, что нашли время посмотреть на это !!!
 Federico Omoto06 июл. 2016 г., 01:41
Если вы не можете увидеть мой отредактированный вопрос с новыми данными, вот пастбина с данными примера (строки с идентификатором 1059, 1060, 1061, 1062 и 1063), желаемыми результатами и результатами вашего запроса, чтобы вы могли сравнить их :pastebin.com/YkASfwrb Спасибо!

Ваш ответ на вопрос