Click here to Skip to main content
15,072,051 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

I have a mysql table that stores data about the presence of a sensor at each timestamp, in multiple areas.
If I have for example 3 areas, for a certain timestamp, my table will look like this:

timestamp    areas_id    is_in_or_out
 ts1             25          1
 ts1             26          0
 ts1             27          0
 ts2             25          1
 ts2             26          0
 ts2             27          0
 ts3             25          0
 ts3             26          0
 ts3             27          1


I am using the below query for each areas_id with union all in order to output in a single table, time periods of how my sensor traveled between areas and how much it stayed in/out each area.
SQL
select t.a_id, min(t.timestamp) starttime,max(t.timestamp) endtime, 
t.is_flag from(SELECT *,
ROW_NUMBER() OVER(ORDER BY a.timestamp) - ROW_NUMBER() OVER(PARTITION BY 
a.is_flag ORDER BY a.timestamp) as GRP
FROM tablename a where areas_id=25 ) t
group by is_flag , GRP, a_id 

The desired output:
starttime     endtime      areas_id      is_in_or_out
 ts1           ts2           25             1
 ts1           ts3           26             0
 ts1           ts2           27             0
 ts3           tsx           27             1

Here is my dbfiddle: DBFIDDLE
Everything works fine, except the execution time for a whole day.
Is there another way of doing this faster? Perhaps a faster querry?

Thank you.

What I have tried:

Here is my dbfiddle: DBFIDDLE
My query does what I need, but it takes to long for a whole day.
Posted
Updated 30-Aug-20 22:34pm

1 solution

Sorry, but the description of issue is not quite clear, because:
1) if you want to get min and max values for timestamp field, you don't need to write several subqueries (for each areas_id); the group by statement is doing it already,
2) if you want to get continuos time for each areas_id, then you need to read this: The SQL of Gaps and Islands in Sequences - Simple Talk[^]

I'd strngly recommend to use correct data type for date and time. Instead of varchar(21), use datetime or timestamp. See: MySQL :: MySQL 8.0 Reference Manual :: 11.2.2 The DATE, DATETIME, and TIMESTAMP Types[^]

I tried below query:
SQL
SELECT areas_id, MIN(timestamp) starttime, MAX(timestamp) endtime, is_in_or_out
FROM inouts
GROUP BY areas_id, is_in_or_out;

The result set is the same as yours, but the execution time is two times shorter. For such of small portion of data the difference is big!

Good luck!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900