Mysql按记录X之间的位置增量分组

Question

我需要帮助来编写MySQL查询。

我有满是日志的表，其中一列是unix时间戳。我希望(按组)对这些记录进行分组，以便它们之间在近距离时间(即5秒)内创建的事件在一个组中。

例如：

表：

timestamp
----------
1429016966
1429016964
1429016963
1429016960
1429016958
1429016957
1429016950
1429016949
1429016943
1429016941
1429016940
1429016938

变成这样的团体：

GROUP_CONCAT(timestamp)                                            | COUNT(*)
----------------------------------------------------------------------------- 
1429016966,1429016964,1429016963,1429016960,1429016958,1429016957  |    6
1429016950,1429016949                                              |    2                         
1429016943,1429016941,1429016940,1429016938                        |    4

当然，之后我可以在php中使用数据数组，但我认为mysql会更快地完成它。

Answer 1

我首先使用一个变量来获取每一行的位置，其中1是最高的时间列，结尾是最低的，如下所示：

SET @a := 0;

SELECT timeCol, @a := @a + 1 AS position
FROM myTable
ORDER BY timeCol DESC;

为了简单起见，我们将调用这个positionsTable，以便查询的其余部分更具可读性。创建该表后，我使用了一个“time_group”变量，该变量检查前一行是否在最后5秒内。如果是的话，我们保留相同的time_group。听起来很丑，看起来也有点丑，但它是这样的：

SELECT m.timeCol, m.position,
  CASE WHEN (SELECT p.timeCol FROM positionsTable p WHERE p.position = m.position - 1) <= m.timeCol + 5 
    THEN @time_group 
    ELSE @time_group := @time_group + 1 END AS timeGroup
FROM positionsTable m;

然后，使用它作为子查询，您可以对它们进行分组：

SELECT GROUP_CONCAT(timeCol), COUNT(*)
FROM(
  SELECT m.timeCol, m.position,
    CASE WHEN (SELECT p.timeCol FROM positionsTable p WHERE p.position = m.position - 1) <= m.timeCol + 5 
    THEN @time_group 
    ELSE @time_group := @time_group + 1 END AS timeGroup
  FROM positionsTable m) tmp
GROUP BY timeGroup;

下面是一个SQL Fiddle示例。

Answer 2

http://sqlfiddle.com/#!9/37d88/20

SELECT GROUP_CONCAT(t1.t) as `time`,
     COUNT(*)
  FROM (SELECT * 
        FROM  table1 
        ORDER BY t) as t1
GROUP BY CASE WHEN (@start+5)>=t THEN @start 
   ELSE @start:=t END