Postgresql group by recurring items

Question

I'm using postgresql to store historical data coming from an RTLS platform. Position data is not collected continuosly. The historical_movements is implemented as a single table as follow (it is a simplified table but enough to present the use case):

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

I'm looking at a way to make a query showing the user staying in the various rooms, aggregating the data related to the same room and computing the overall staying time, as follows

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

Looking at various threads I'm quite sure I'd have to use lag and partition by functions but it's not clear how. Any hints? Best regards.

Hi, I think the problem is explained and I don't know how to make the query, if not enough I'm sorry or that — Dan Grow, Feb 18 '18 at 23:40

jimmu · Answer 1 · 2018-02-19T13:34:23.433

0

AggregateTime isn't really an aggregate in your expected result. It seems to be a difference between max_time and min_time for each block where each block is set of contiguous rows with same (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;

I made some changes to 'Resetting Row number according to record data change' to arrive at the solution.

edited Feb 19 '18 at 13:34

answered Feb 18 '18 at 21:57

jimmu

1,025
1
10
15

Hi, I've created a sqlfiddle with my table creation and your suggested query, http://sqlfiddle.com/#!15/0d8db/2/0 if you have a look to the 'block' part, the grp column doesn't change when badge b passes from area a to area b, perhaps I'm making any mistake. thanks – Dan Grow Feb 19 '18 at 00:38
grp number does not change but the combination of column `(badge, area, grp)` changes from `(B,A,3)` to `(B, B, 3)` which you care about. Isn't that sufficient for your problem? – jimmu Feb 19 '18 at 00:55
Hi, I wasn't able to test the whole query (removing the comments) because I have an error ERROR: syntax error at or near "select" Position: 293 Any hints? thank you very much – Dan Grow Feb 19 '18 at 08:21
@DanGrow working example: http://sqlfiddle.com/#!15/0d8db/6/0 I realized that the expected output you provided is not consistent. Second row `John, room2` has exittime that should be `2018-01-01 11:15:00` instead if I understand your question correctly. – jimmu Feb 19 '18 at 13:05

Postgresql group by recurring items

1 Answers1