Помогите с группировкой по времени

Здравствуйте! К сожалению с postgre только начал разбираться т.к. понадобились пространственные типы данных и столкнулся с некоторыми сложностями в запросах и очень нужна ваша помощь.


1. задача нужно сгруппировать записи по промежутку времени.
К примеру есть ряд записей у которых промежуток между временем 5-10 секунд, а есть более часа.
Нужно как-то сгруппировать данные промежуток между которых менее минуты.

2. задача так же со временем.
Так же есть точки добавляемые раз в 5 секунд. Но между ними могут быть промежутки в часы.
Нужно выбрать все точки между которыми меньше минуты промежуток.

Вот пример: мобильное устройство шлет координаты и едет по маршруту. Мне нужно по сути собрать все точки за определенный промежуток времени (к примеру месяц) и дальше уже делать расчеты. То бишь отрезки где больше минуты мне считать не нужно.

Выбирать кучу данных и считать на PHP это тупость, нужно делать это как-то на уровне SQL
Помогите пожалуйста, голова уже идет кругом. заранее спасибо.

1. Округли дату до минуты и группируй. как - http://postgresql.ru.net/node/214418
2. не понял что надо - пример чтоли придумай

GibsonG

Здравствуйте! К сожалению с postgre только начал разбираться т.к. понадобились пространственные типы данных и столкнулся с некоторыми сложностями в запросах и очень нужна ваша помощь. 1. задача нужно сгруппировать записи по промежутку времени. К примеру есть ряд записей у которых промежуток между временем 5-10 секунд, а есть более часа. Нужно как-то сгруппировать данные промежуток между которых менее минуты. 2. задача так же со временем. Так же есть точки добавляемые раз в 5 секунд. Но между ними могут быть промежутки в часы. Нужно выбрать все точки между которыми меньше минуты промежуток. Вот пример: мобильное устройство шлет координаты и едет по маршруту. Мне нужно по сути собрать все точки за определенный промежуток времени (к примеру месяц) и дальше уже делать расчеты. То бишь отрезки где больше минуты мне считать не нужно. Выбирать кучу данных и считать на PHP это тупость, нужно делать это как-то на уровне SQL Помогите пожалуйста, голова уже идет кругом. заранее спасибо.

Не совсем понял, интервалы переменной длины?
т.е. к примеру ваше значение 1 мин.

00:00 - 1
01:40 - 2
01:45 - 3
02:39 - 4
02:41 - 5
03.42 - 6
вам нужно ( ( 1 ), ( 2,3,4,5 ), ( 6 ) ) или ( ( 1 ), ( 2,3,4 ), ( 5 ), (6) )?

если вариант 1 то серверной процедурой, по другому не стоит.

NikolayV81,

или вариант 3:
( ( 1 ), ( 2, 3 ), ( 4, 5 ), (6 ) )?

GibsonG,

Почитай про оконные функции. В частности про LEAD / LAG.

GibsonG,

Как вариант - пошерсти оракловую ветку форума по слову start_of_group, чтобы понять, как к твоей задаче прикрутить LEAD / LAG

Извините что долго не отвечал. отвлекли по работе.

В общем постараюсь объяснить на одном из примеров более подробно.

По маршруту идет машина (полилиния). Если к примеру машина ушла с машрута я нахожу все точки не принадлежащие к маршруту.

время может быть такое

(первый уход)
2014-12-28 16:10:05
2014-12-28 16:10:10
2014-12-28 ---
2014-12-28 16:20:30

(второй уход)
2014-12-28 16:10:05
2014-12-28 16:10:10
2014-12-28 ---
2014-12-28 16:20:30

к примеру мне нужно посчитать кол-во уходов и время ухода.
То бишь тут нужны первые значения. сгрупированные по отрезкам.
Можно конечно выгрузить все в PHP и там искать, но это не есть хорошо.

Другой пример аналогичная ситуация, только выбирается не уход с маршрута а вхождение в какой-то полигон (многоугольник)

Добрый Э - Эх - да я немного читал про оконные функции, но прям сходу не смог понять как сгруппировать. С оконными не работал. Все что понял это выполнение агрегатных функций для каждой записи.

А уже далее по этим данным можно сгруппировать.
Буду читать дальше, спасибо за уточнение

GibsonG,

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

Ах... прям читаю и себя вижу годик назад. ))))
1. PHP нет не нужно.
2. PL/PgSQL вам в помощь, потому как поддерживает нативные типы данных.
3. Прохождение по всем записям. FOR rec IN "QUERY". И дальше IF (a-b)<1 минут THEN фиксируем начало отрезка ELSE фиксируем конец отрезка. Здесь можно много еще условий вставить, которые вам понадобятся в будущем (поверьте на слово). Это и количество координат можно ограничивать, и время общее просчитать и количество отрезков и кучу всего остального.
Можно конечно под каждую задачу запрос писать. Но без постагрегации данных - не обойтись.Та и в запросе без агрегаторов, разных CASE WHEN и тех же окон - не обойтись. А если запросы динамические на 70% то вам придется их готовить за базой, в том же PHP. И это лишает вас одного преимущества. Или даже двух. Кеширования планов и использования компилированых процедур.
В общем, я бы не советовал создавать абрукадабру в виде запросов и размазывать бизнес логику между слоями приложения под такие задачи. Потому как ожидаемого прироста скорости это не даст в итоге. А PLPG и тот же PostGis со своими Geometry,WithIN и другими плюшками у вас всегда под рукой.
Только одно но! Разбивайте такие запросами порциями (по времени или количеству координат). Любая конкатенация varchar внутри хранимки в цикле - зло. Простые арифметические операции летают. Ну и конечно же индексы, изучайте род и планы своих запросов. В общем Postgres ням ням... а сегодня еще PgBouncer прикрутил - не могу нарадоваться)))

GibsonG
По маршруту идет машина (полилиния). Если к примеру машина ушла с машрута я нахожу все точки не принадлежащие к маршруту.

Тогда выходит все несколько хитрее - если машина на одну точку отклонилась а на следующей вернулась, то это считается отдельным уходом? А какой длительности?

Я бы предложил для начала определить критерий принадлежности точки маршруту, затем отселектить последовательно ВСЕ точки с отдельной колонкой - признаком принадлежности (булевым). Должно получиться что-то вроде

moment | geodata | online
-----------------------------------------------
2014-12-28 16:10:05 | ....... | true
2014-12-28 16:10:10 | ....... | true
2014-12-28 16:10:15 | ....... | false
2014-12-28 16:10:20 | ....... | false
2014-12-28 16:10:25 | ....... | true
2014-12-28 16:10:30 | ....... | false
2014-12-28 16:10:35 | ....... | false
2014-12-28 16:10:40 | ....... | false
2014-12-28 16:10:45 | ....... | true

Для первого ухода интервал по идее должен быть 16:10:15 ... 16:10:25

Обработать это окнами может и можно, но имхо черезушно. Проще прогнать через функцию ( tstart out timestamp, tend out timestamp ), в которой что-то вроде

flag := true;
for r in select ..... loop
if flag and not r.online then
tstart := r.moment;
end if;
if not flag and r.online then
tend := r.moment;
return next;
end if;
flag := r.online;
end loop;