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

Ответ 1

Я использую отличную идею Tom H., но делаю это несколько иначе:

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

Запрос № 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

И затем для каждой строки мы можем найти самую длинную временную метку, которая меньше, чем наша временная метка с Query # 2:

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

Как только мы получим это, мы можем GROUP BY по вашему желанию.

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

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

Ответ 2

Теперь, когда я думаю, что я понимаю вашу проблему, основанный на ответе на комментарий 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: Изменены запросы для обработки точных совпадений по метке времени.

Ответ 3

Предупреждение: длинный ответ. Это должно работать и довольно аккуратно, за исключением одного шага в середине, где вы должны быть готовы запускать инструкцию 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
)

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

Чтобы сделать это, найдите следующий уровень ссылок. я имею в виду, поскольку в таблице Adjacency мы имеем 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

Итак,

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

возвращает

Neighbors
1,2,3
4

Уф!

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

Ответ 5

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

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