ReSQL.ru
     
powered by simpleCommunicator - 2.0.41     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Группировка строк по временным интервалам
7 сообщений из 7, страница 1 из 1
Группировка строк по временным интервалам
    #38391574
aleksandy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksandy
Гость
Имеется следующая таблица:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
create table test_data as
with test_series as (
  select
    generate_series(
      '2013-01-01 00:00:00.000'::timestamptz
    , '2014-01-01 00:00:00.000'::timestamptz
    , '1 minutes'
    ) tstamp
)
select
    ts.tstamp
  , (
      array[
        '123456789012'
      , '234567890123'
      , '345678901234'
      , '456789012345'
      , '567890123456'
      , '678901234567'
      , '789012345678'
      , '890123456789'
      ]
    )[trunc(random() * 8 + 1)]::text sid
  from test_series ts
;



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

Я сделал такой запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
select 
    t.sid
  , t.tstamp
  , case
      when t.ival <= cast('5 minutes' as interval)
      then t.ival
    end ival
  from (
    select 
        d.sid
      , d.tstamp 
      , d.tstamp - lag(d.tstamp) over (partition by d.sid order by d.tstamp) ival
      from test_data d
      where d.sid = '123456789012'
  ) t
  order by t.tstamp;



На выходе имею набор записей вида
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
     sid      |         tstamp         |   ival
--------------+------------------------+----------
 123456789012 | 2013-01-01 00:11:00+07 |
 123456789012 | 2013-01-01 00:13:00+07 | 00:02:00
 123456789012 | 2013-01-01 00:32:00+07 |
 123456789012 | 2013-01-01 00:38:00+07 |
 123456789012 | 2013-01-01 00:55:00+07 |
 123456789012 | 2013-01-01 01:00:00+07 | 00:05:00
 123456789012 | 2013-01-01 01:10:00+07 |
 123456789012 | 2013-01-01 01:12:00+07 | 00:02:00
 123456789012 | 2013-01-01 01:18:00+07 |
 123456789012 | 2013-01-01 01:39:00+07 |
 123456789012 | 2013-01-01 01:45:00+07 |
 123456789012 | 2013-01-01 01:46:00+07 | 00:01:00
 123456789012 | 2013-01-01 01:47:00+07 | 00:01:00
 123456789012 | 2013-01-01 01:50:00+07 | 00:03:00
 123456789012 | 2013-01-01 01:53:00+07 | 00:03:00
 123456789012 | 2013-01-01 01:58:00+07 | 00:05:00
 123456789012 | 2013-01-01 02:12:00+07 |
 123456789012 | 2013-01-01 02:14:00+07 | 00:02:00
 123456789012 | 2013-01-01 02:40:00+07 |
 123456789012 | 2013-01-01 02:50:00+07 |
 123456789012 | 2013-01-01 02:51:00+07 | 00:01:00
...


Дальнейший обсчет делаю в java-программе, из которой запрос и вызывается, но, как мне кажется, можно написать запрос, который бы возвращал такой набор данных:
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
     sid      |         tstamp         |   ival   | grp
--------------+------------------------+----------+-----
 123456789012 | 2013-01-01 00:11:00+07 |          | 1
 123456789012 | 2013-01-01 00:13:00+07 | 00:02:00 | 1
 123456789012 | 2013-01-01 00:32:00+07 |          | 2
 123456789012 | 2013-01-01 00:38:00+07 |          | 3
 123456789012 | 2013-01-01 00:55:00+07 |          | 4
 123456789012 | 2013-01-01 01:00:00+07 | 00:05:00 | 4
 123456789012 | 2013-01-01 01:10:00+07 |          | 5
 123456789012 | 2013-01-01 01:12:00+07 | 00:02:00 | 5
 123456789012 | 2013-01-01 01:18:00+07 |          | 6
 123456789012 | 2013-01-01 01:39:00+07 |          | 7
 123456789012 | 2013-01-01 01:45:00+07 |          | 8
 123456789012 | 2013-01-01 01:46:00+07 | 00:01:00 | 8
 123456789012 | 2013-01-01 01:47:00+07 | 00:01:00 | 8
 123456789012 | 2013-01-01 01:50:00+07 | 00:03:00 | 8
 123456789012 | 2013-01-01 01:53:00+07 | 00:03:00 | 8
 123456789012 | 2013-01-01 01:58:00+07 | 00:05:00 | 8
 123456789012 | 2013-01-01 02:12:00+07 |          | 9
 123456789012 | 2013-01-01 02:14:00+07 | 00:02:00 | 9
 123456789012 | 2013-01-01 02:40:00+07 |          | 10
 123456789012 | 2013-01-01 02:50:00+07 |          | 11
 123456789012 | 2013-01-01 02:51:00+07 | 00:01:00 | 11
...


Тогда через аналитику все можно было бы обсчитать на стороне БД и свернуть все результаты к виду
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
     sid      |         ts_start       |         ts_end         | grp
--------------+------------------------+------------------------+-----
 123456789012 | 2013-01-01 00:11:00+07 | 2013-01-01 00:13:00+07 | 1
 123456789012 | 2013-01-01 00:55:00+07 | 2013-01-01 01:00:00+07 | 4
 123456789012 | 2013-01-01 01:10:00+07 | 2013-01-01 01:12:00+07 | 5
 123456789012 | 2013-01-01 01:45:00+07 | 2013-01-01 01:58:00+07 | 8
 123456789012 | 2013-01-01 02:12:00+07 | 2013-01-01 02:14:00+07 | 9
 123456789012 | 2013-01-01 02:50:00+07 | 2013-01-01 02:51:00+07 | 11

Может какую-нибудь самописную функцию, которая бы мне пронумеровала группы? Дальше-то уже ничего сложного не остается.
...
Рейтинг: 0 / 0
09.09.2013, 22:58
| Ответить | Цитировать | Написать  
Группировка строк по временным интервалам
    #38391645
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Не совсем понял логику получения именно такого конечного результата, как ты показал, но на уровне "идеи как это сделать" могу предложить следующее:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
select sid, min(tstamp) as ts_start, max(tstamp) as ts_end,
       grp_id, count(1) as cnt
  from (
         select sid, tstamp, sog,
                -- Нумератор групп:
                sum(sog) over(partition by sid order by tstamp) as grp_id
           from (
                  select sid, tstamp,
                       -- Определение начала каждой группы:
                         case
                           when tstamp - 
                                lag(tstamp) over(partition by sid order by tstamp)
                                <= cast('5 minutes' as interval)
                             then null
                         else 1 
                         end as sog -- Start Of Group :)
                    from test_data
                ) v0
             ) v1
 group by sid, grp_id
 order by sid, min(tstamp)

on-line проверка на sqlfiddle.com

Если что, допилишь напильником до нужного состояния... ;)
...
Рейтинг: 0 / 0
10.09.2013, 06:02
| Ответить | Цитировать | Написать  
Группировка строк по временным интервалам
    #38391657
aleksandy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksandy
Гость
Добрый Э - Эх,

спасибо. Логика простая: имея начало и конец интервала, я могу найти цепочки, которые длиннее 5 минут и состоят из минимум 2 записей из test_data.
...
Рейтинг: 0 / 0
10.09.2013, 07:21
| Ответить | Цитировать | Написать  
Группировка строк по временным интервалам
    #38391658
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
aleksandy,

тогда в мой запрос всего-то нужно добавить HAVING count(1) > 1 ;)
...
Рейтинг: 0 / 0
10.09.2013, 07:33
| Ответить | Цитировать | Написать  
Группировка строк по временным интервалам
    #38391665
aleksandy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksandy
Гость
Добрый Э - Эх,

Да я в курсе, все уже допилено и почти работает. Ещё раз спасибо.
...
Рейтинг: 0 / 0
10.09.2013, 07:57
| Ответить | Цитировать | Написать  
Группировка строк по временным интервалам
    #38391677
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
aleksandy,

а почему "почти"? Давай уж допилим, чтобы работало железно.
...
Рейтинг: 0 / 0
10.09.2013, 08:21
| Ответить | Цитировать | Написать  
Группировка строк по временным интервалам
    #38391690
aleksandy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksandy
Гость
Добрый Э - Эх,

"почти", потому что дальнейшая обработка в java-программе ещё не до конца реализована. А то, что требовалось от скрипта, работает "на ура". Единственное, что меня сейчас настораживает, так это производительность на больших объемах. Но "это проблемы будущего Гомера..." :)
...
Рейтинг: 0 / 0
10.09.2013, 08:40
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Группировка строк по временным интервалам
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&gotonew=1&tid=1999176]:
 0ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 28ms
get topic data:
 13ms
get first new msg:
 8ms
get forum data:
 3ms
get page messages:
 47ms
get tp. blocked users:
 3ms
others: 12ms
total:  142ms
созд. / загр.: 142ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]