counting values with almost the same time-CodePudding

Good afternoon. What is the essence of the matter, the train has a geotag that determines its position in space. Location data is entered into a table. It is required to count how many times the train was in a certain timezone. But the problem is that being in a certain time zone, the geotag leaves several records in the table by time. What query can be used to count the number of arrivals?

table

I created a query that counts how many times the train was at point 270 and at point 289. To do this, I rounded the time to hours, but the problem is that if the train arrived at the end of the hour, but left at the beginning of the next, the query counts it as two arrivals . Below I will attach the query itself and the output results.

Create temp table tmpTable_1 ON COMMIT DROP as
select addr,zone_id,DATE_PART('hour',time)*100 as IntTime from trac_path_rmp where time between '2022.04.06' and '2022.04.07';
Create temp table tmpTable_2 ON COMMIT DROP as select addr,zone_id,IntTime from tmpTable_1 where addr in (12421,12422,12423,12425) group by addr,zone_id,IntTime;
select addr,sum(case when zone_id=289 then 1 else 0 end) as "Zone 289", sum(case when zone_id=270 then 1 else 0 end)  as "Zone 270"  from tmpTable_2 group by addr order by addr;

result

CodePudding user response：

We can use LAG OVER() to get the timestamp of the previous row and only return the rows when there is at least a minutes difference. We could easily modify this: to 5 minutes for example.
We also keep the first row where LAG returns null.
We need to use hours and minutes because if we only use minutes we will get 0 time difference when there is exactly an hour between rows.
See dbFiddle link below.

;WITH CTE AS
(SELECT
*,
time_ - LAG(time_) OVER (ORDER BY id) AS dd
FROM table_name)
SELECT 
id,time_,addr,x,y,z,zone_id,type
FROM cte
WHERE DATE_PART('hours',dd)   60 * DATE_PART('minutes',dd) > 0 
OR dd IS null;

 id | time_               |  addr |       x |       y |       z | zone_id | type
--: | :------------------ | ----: | ------: | ------: | ------: | ------: | ---:
138 | 2022-04-06 19:19:11 | 12421 | 9793.50 | 4884.70 | -125.00 |     270 |    1
141 | 2022-04-06 20:37:23 | 12421 | 9736.00 | 4856.90 | -125.00 |     270 |    1
146 | 2022-04-06 22:58:15 | 12421 | 9736.00 | 4856.90 | -125.00 |     270 |    1

db<>fiddle here