MySQL GROUP BY DateTime +/- 3 秒

Question

假设我有一个包含 3 列的表：

• id (PK, int)
• timestamp (datetime)
• title (text)

我有以下记录：

1, 2010-01-01 15:00:00, Some Title
2, 2010-01-01 15:00:02, Some Title
3, 2010-01-02 15:00:00, Some Title

我需要对彼此相差 3 秒以内的记录进行 GROUP BY。对于此表，第 1 行和第 2 行将分组在一起。

这里有一个类似的问题： Mysql DateTime group by 15 mins

我也发现这个： http://www.artfulsoftware.com/infotree/queries.php#106

我不知道如何将这些方法转换为可以工作几秒钟的方法。 SO问题上的方法的问题在于，在我看来，它只适用于落在从已知点开始的时间段内的记录。例如，如果我要让 FLOOR() 以秒为单位，间隔 5 秒，时间 15:00:04 将与 15:00:01 分组，但不会以 15:00:06 分组。

这有道理吗？如果需要进一步说明，请告诉我。

编辑：对于数字集 {1, 2, 3, 4, 5, 6, 7, 50, 51, 60}，似乎最好将它们分组为 {1, 2 , 3, 4, 5, 6, 7}, {50, 51}, {60}，以便每个分组行取决于该行是否在前一行的 3 秒内。我知道这会改变一些事情，我很抱歉对此犹豫不决。

我正在尝试模糊匹配来自不同服务器的日志。服务器 #1 可能会记录一个项目“项目 #1”，而服务器 #2 将在服务器 #1 的几秒钟内记录同一项目“项目 #1”。我需要在两个日志行上执行一些聚合函数。不幸的是，由于服务器软件的性质，我只有继续下去的权利。

Answer 1

我正在使用 Tom H. 的绝妙想法，但这里的做法略有不同：

我们可以找到所有作为链开头的时间，而不是查找作为链开头的所有行，然后返回并找到与时间匹配的行。

这里的查询 #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

我不完全确定这与 Tom H 的答案是否足够不同，需要单独发布，但听起来你在实施方面遇到了麻烦，我正在考虑它，所以我想我会再次发布。祝你好运！

Answer 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 或使用临时表，这看起来会简单得多。使用临时表也可能有助于提高性能。

此外，如果您的时间戳完全匹配，也会出现问题。如果是这种情况，那么您将需要稍微调整查询以使用 id 和时间戳的组合来区分具有匹配时间戳值的行。

编辑：更改了查询以按时间戳处理精确匹配。

Answer 3

警告：答案很长。这应该可以工作，并且相当简洁，除了中间的一个步骤，您必须愿意一遍又一遍地运行 INSERT 语句，直到它不执行任何操作，因为我们无法在 MySQL 中执行递归 CTE 操作。

我将使用此数据作为示例，而不是您的：

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

所以

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

返回

Neighbors
1,2,3
4

唷！

Answer 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 列。如果您愿意，您可以想象并为每个组分配一个完整的行号。

Answer 5

简单查询：

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