Postgresql группа по повторяющимся пунктам

Question

Я использую postgresql для хранения исторических данных, поступающих с платформы RTLS. Данные о местоположении не собираются непрерывно. Historical_movements реализован в виде одной таблицы следующим образом (это упрощенная таблица, но достаточная для представления варианта использования):

User   Area   EnterTime               ExitTime
John   room1  2018-01-01 10:00:00     2018-01-01 10:00:05
Doe    room1  2018-01-01 10:00:00     2018-01-01 10:10:00
John   room1  2018-01-01 10:05:00     2018-01-01 10:10:00
Doe    room1  2018-01-01 10:20:00     2018-01-01 10:30:00
John   room2  2018-01-01 11:00:00     2018-01-01 11:05:00
John   room2  2018-01-01 11:08:00     2018-01-01 11:15:00
John   room1  2018-01-01 12:00:00     2018-01-01 12:08:00
John   room1  2018-01-01 12:10:00     2018-01-01 12:20:00
John   room1  2018-01-01 12:25:00     2018-01-01 12:25:00
John   room3  2018-01-01 12:30:00     2018-01-01 12:35:00
John   room3  2018-01-01 12:40:00     2018-01-01 12:50:00

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

User  Area    EnterTime               ExitTime              ArregateTime
John  room1   2018-01-01 10:00:00     2018-01-01 10:10:00   00:10:00
John  room2   2018-01-01 11:00:00     2018-01-01 11:05:00   00:15:00
John  room1   2018-01-01 12:00:00     2018-01-01 12:25:00   00:25:00
John  room3   2018-01-01 12:30:00     2018-01-01 12:50:00   00:20:00
Doe   room1   2018-01-01 10:00:00     2018-01-01 10:30:00   00:30:00

Глядя на различные темы, я вполне уверен, что мне придется использовать лаг и разбиение по функциям, но не совсем понятно, как. Есть намеки? С наилучшими пожеланиями.

Answer 1

AggregateTime на самом деле не aggregate в вашем ожидаемом результате. Кажется, разница между max_time а также min_time для каждого block где каждый блок состоит из смежных строк с одинаковыми (users, area),

with block as(
    select users, area, entertime, exittime,     
         (row_number() over (order by users, entertime) -
          row_number() over (partition by users, area order by entertime)
         ) as grp
    from your_table
    order by 1,2,3
)
select users, area, entertime, exittime, (exittime - entertime) as duration
from (select users, area, grp, min(entertime) as entertime, max(exittime) as exittime
      from block
      group by users, area, grp
    ) t2
order by 5;

Я внес некоторые изменения в " Сброс номера строки в соответствии с изменением данных записи", чтобы прийти к решению.