MySQL GROUP BY DateTime +/- 3 секунды

Я использую прекрасную идею Тома Х., но здесь делаю это немного по-другому:

Вместо поиска всех строк, являющихся началом цепочек, мы можем найти все времена, являющиеся началом цепочек, а затем вернуться назад и найти строки, соответствующие времени.

Запрос № 1 здесь должен сказать вам, какие времена являются началом цепочек, находя, какие времена не имеют времени ниже них, но в пределах 3 секунд:

SELECT DISTINCT Timestamp
FROM Table a
LEFT JOIN Table b
ON (b.Timestamp >= a.TimeStamp - INTERVAL 3 SECONDS
    AND b.Timestamp < a.Timestamp)
WHERE b.Timestamp IS NULL

И затем для каждой строки мы можем найти самую большую временную метку начала цепочки, которая меньше нашей временной метки с помощью запроса № 2:

SELECT Table.id, MAX(StartOfChains.TimeStamp) AS ChainStartTime
FROM Table
JOIN ([query #1]) StartofChains
ON Table.Timestamp >= StartOfChains.TimeStamp
GROUP BY Table.id

Как только мы получим это, мы можем СГРУППИРОВАТЬ по нему, как вы хотели.

SELECT COUNT(*) --or whatever
FROM Table
JOIN ([query #2]) GroupingQuery
ON Table.id = GroupingQuery.id
GROUP BY GroupingQuery.ChainStartTime

Я не совсем уверен, что это достаточно отличается от ответа Тома Х, чтобы его можно было опубликовать отдельно, но похоже, что у вас возникли проблемы с реализацией, и я думал об этом, поэтому решил опубликовать снова. Удачи!

Теперь, когда я думаю, что понимаю вашу проблему, основываясь на вашем комментарии к OMG Ponies, я думаю, что у меня есть решение на основе набора. Идея состоит в том, чтобы сначала найти начало любой цепочки на основе названия. Начало цепочки будет определяться как любая строка, в которой нет совпадения в течение трех секунд до этой строки:

SELECT
    MT1.my_id,
    MT1.title,
    MT1.my_time
FROM
    My_Table MT1
LEFT OUTER JOIN My_Table MT2 ON
    MT2.title = MT1.title AND
    (
        MT2.my_time < MT1.my_time OR
        (MT2.my_time = MT1.my_time AND MT2.my_id < MT1.my_id)
    ) AND
    MT2.my_time >= MT1.my_time - INTERVAL 3 SECONDS
WHERE
    MT2.my_id IS NULL

Теперь можно предположить, что любые нецепные пускатели относятся к цепному пускателю, появившемуся перед ними. Поскольку MySQL не поддерживает CTE, вы можете захотеть поместить приведенные выше результаты во временную таблицу, так как это избавит вас от нескольких соединений с одним и тем же подзапросом ниже.

SELECT
    SQ1.my_id,
    COUNT(*)  -- You didn't say what you were trying to calculate, just that you needed to group them
FROM
(
    SELECT
        MT1.my_id,
        MT1.title,
        MT1.my_time
    FROM
        My_Table MT1
    LEFT OUTER JOIN My_Table MT2 ON
        MT2.title = MT1.title AND
        (
            MT2.my_time < MT1.my_time OR
            (MT2.my_time = MT1.my_time AND MT2.my_id < MT1.my_id)
        ) AND
        MT2.my_time >= MT1.my_time - INTERVAL 3 SECONDS
    WHERE
        MT2.my_id IS NULL
) SQ1
INNER JOIN My_Table MT3 ON
    MT3.title = SQ1.title AND
    MT3.my_time >= SQ1.my_time
LEFT OUTER JOIN
(
    SELECT
        MT1.my_id,
        MT1.title,
        MT1.my_time
    FROM
        My_Table MT1
    LEFT OUTER JOIN My_Table MT2 ON
        MT2.title = MT1.title AND
        (
            MT2.my_time < MT1.my_time OR
            (MT2.my_time = MT1.my_time AND MT2.my_id < MT1.my_id)
        ) AND
        MT2.my_time >= MT1.my_time - INTERVAL 3 SECONDS
    WHERE
        MT2.my_id IS NULL
) SQ2 ON
    SQ2.title = SQ1.title AND
    SQ2.my_time > SQ1.my_time AND
    SQ2.my_time <= MT3.my_time
WHERE
    SQ2.my_id IS NULL

Это выглядело бы намного проще, если бы вы могли использовать CTE или использовать временную таблицу. Использование временной таблицы также может повысить производительность.

Кроме того, будут проблемы с этим, если у вас будут точно совпадающие временные метки. Если это так, вам нужно немного изменить запрос, чтобы использовать комбинацию идентификатора и метки времени, чтобы различать строки с совпадающими значениями метки времени.

EDIT: запросы изменены для обработки точных совпадений по отметке времени.

Предупреждение: длинный ответ. Это должно работать, и это довольно аккуратно, за исключением одного шага посередине, когда вы должны быть готовы запускать оператор INSERT снова и снова, пока он ничего не сделает, поскольку мы не можем делать рекурсивные CTE-вещи в MySQL.

Я собираюсь использовать эти данные в качестве примера вместо ваших:

id    Timestamp
1     1:00:00
2     1:00:03
3     1:00:06
4     1:00:10

Вот первый запрос, который нужно написать:

SELECT a.id as aid, b.id as bid
FROM Table a
JOIN Table b 
ON (a.Timestamp is within 3 seconds of b.Timestamp)

Он возвращает:

aid     bid
1       1
1       2
2       1
2       2
2       3
3       2
3       3
4       4

Давайте создадим красивую таблицу для хранения тех вещей, которые не допускают дублирования:

CREATE TABLE
Adjacency
( aid INT(11)
, bid INT(11)
, PRIMARY KEY (aid, bid) --important for later
)

Теперь задача состоит в том, чтобы найти что-то вроде транзитивного замыкания этого отношения.

Для этого найдем следующий уровень ссылок. под этим я подразумеваю, что поскольку у нас есть 1 2 и 2 3 в таблице смежности, мы должны добавить 1 3:

INSERT IGNORE INTO Adjacency(aid,bid)
SELECT adj1.aid, adj2.bid
FROM Adjacency adj1
JOIN Adjacency adj2
ON (adj1.bid = adj2.aid)

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

Как только это закончится, у вас будет транзитивно-закрытое отношение, подобное этому:

aid     bid
1       1
1       2
1       3     --added
2       1
2       2
2       3
3       1     --added
3       2
3       3
4       4

А теперь к изюминке:

SELECT aid, GROUP_CONCAT( bid ) AS Neighbors
FROM Adjacency
GROUP BY aid

возвращает:

aid     Neighbors
1       1,2,3
2       1,2,3
3       1,2,3
4       4

So

SELECT DISTINCT Neighbors
FROM (
     SELECT aid, GROUP_CONCAT( bid ) AS Neighbors
     FROM Adjacency
     GROUP BY aid
     ) Groupings

возвращается

Neighbors
1,2,3
4

Вау!

Мне нравится ответ @Chris Cunningham, но вот еще один взгляд на него.

Во-первых, мое понимание вашей постановки задачи (поправьте меня, если я ошибаюсь):

Вы хотите просмотреть свой журнал событий как последовательность, упорядоченную по времени события, и разбить ее на группы, определив границу как интервал более 3 секунд между двумя соседними строками в последовательности.

Я работаю в основном в SQL Server, поэтому я использую синтаксис SQL Server. Это не должно быть слишком сложно перевести на MySQL SQL.

Итак, сначала наша таблица журнала событий:

--
-- our event log table
--
create table dbo.eventLog
(
  id       int          not null ,
  dtLogged datetime     not null ,
  title    varchar(200) not null ,

  primary key nonclustered ( id ) ,
  unique clustered ( dtLogged , id ) ,

)

Учитывая приведенное выше понимание постановки задачи, следующий запрос должен дать вам верхнюю и нижнюю границы ваших групп. Это простой вложенный оператор select с 2 group by для сворачивания вещей:

• Самый внутренний select определяет верхнюю границу каждой группы. Эта верхняя граница определяет группу.
• Внешний select определяет нижнюю границу каждой группы.

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

[отредактировано: верхняя граница — это наименьшее значение даты/времени, где интервал превышает 3 секунды]

select dtFrom = min( t.dtFrom ) ,
       dtThru =      t.dtThru
from ( select dtFrom = t1.dtLogged ,
              dtThru = min( t2.dtLogged )
       from      dbo.EventLog t1
       left join dbo.EventLog t2 on t2.dtLogged >= t1.dtLogged
                                and datediff(second,t1.dtLogged,t2.dtLogged) > 3
       group by t1.dtLogged
     ) t
group by t.dtThru

Затем вы можете извлечь строки из журнала событий и пометить их группой, к которой они принадлежат, следующим образом:

select *
from ( select dtFrom = min( t.dtFrom ) ,
              dtThru =      t.dtThru
       from ( select dtFrom = t1.dtLogged ,
                     dtThru = min( t2.dtLogged )
              from      dbo.EventLog t1
              left join dbo.EventLog t2 on t2.dtLogged >= t1.dtLogged
                                       and datediff(second,t1.dtLogged,t2.dtLogged) > 3
              group by t1.dtLogged
            ) t
       group by t.dtThru
     ) period
join dbo.EventLog t on t.dtLogged >=           period.dtFrom
                   and t.dtLogged <= coalesce( period.dtThru , t.dtLogged )
order by period.dtFrom , period.dtThru , t.dtLogged

Каждая строка помечается своей группой через возвращаемые столбцы dtFrom и dtThru. Вы можете проявить фантазию и присвоить целочисленный номер строки каждой группе, если хотите.

Простой запрос:

SELECT * FROM time_history GROUP BY ROUND(UNIX_TIMESTAMP(time_stamp)/3);