Mysql группа, где дельта между записями X

Question

Mysql группа, где дельта между записями X

Мне нужна помощь, чтобы написать запрос MySQL.

У меня есть таблица, полная журналов, где один из столбцов является Unix Timestamp.
Я хочу сгруппировать (GROUP BY) эти записи так, чтобы события, которые были сделаны за короткое время (то есть 5 секунд) между каждым из них, были в одной группе.

Например:

Таблица:

Станьте в такие группы:

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

Конечно, я могу потом работать с массивом данных в php, но я думаю, что MySQL сделает это быстрее.

0

delta group-by mysql php

Решение

Другие решения

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

0

Источник

Accepted Answer

Я начал с использования переменной, чтобы получить позицию каждой строки, где 1 — самый высокий столбец времени и заканчивается самым низким, например:

SET @a := 0;

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

Для простоты мы будем называть это positionsTable так что остальная часть запроса будет более читабельной. Создав эту таблицу, я использовал переменную time_group, которая проверяла, была ли предыдущая строка в течение последних 5 секунд. Если это так, мы сохраняем ту же группу времени. Звучит некрасиво и выглядит некрасиво, но это так:

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 пример.

0