Optimizing group by query with union

Question

I have a MySQL table looking like this:

I want to find a query that groups my table like this:

Details:

a_id = delimited area on a map

is_flag = 1-if sensor is in area/ 0 - if sensor not in area

Basically the first table describes in which area my sensor was at each timestamp.

The second table tells me the period that my sensor stayed in and out of each area.

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.

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

Here is my dbfiddle: https://www.db-fiddle.com/f/5pHiYKyx4yHoirRbGX4kP4/0

My query does what I need, but it takes to long for a whole day.

user_10138734 · Answer

WITH 
cte1 AS (SELECT CAST(JSON_UNQUOTE(`timestamp`) AS DATETIME) ts,
                areas_id,
                is_in_or_out
         FROM inouts),
cte2 AS (SELECT ts,
                areas_id,
                is_in_or_out,
                CAST(ROW_NUMBER() OVER (PARTITION BY areas_id ORDER BY ts ASC) AS SIGNED)
               -CAST(ROW_NUMBER() OVER (PARTITION BY areas_id ORDER BY is_in_or_out, ts ASC) AS SIGNED) AS grp
         FROM cte1)
SELECT areas_id, 
       ANY_VALUE(is_in_or_out) is_in_or_out,
       MIN(ts) min_ts,
       MAX(ts) max_ts
FROM cte2 
GROUP BY areas_id, 
         grp
ORDER BY areas_id, min_ts;

fiddle

PS1. Source data was altered slightly.

PS2. CAST is needed in MySQL, because ROW_NUMBER() produces bigint unsigned. May be replaced with 0.0 + ....

user_4523896 · Answer

this is syntax for sql server but should be the same in major dbms

with
x as (
    -- find start/end of each period
    select areas_id, is_in_or_out is_flag, timestamp t1
    , ISNULL(ABS(is_in_or_out - LAG(is_in_or_out, 1) over (partition by areas_id order by timestamp)), 1) T_START
    , ISNULL(ABS(is_in_or_out - LEAD(is_in_or_out, 1) over (partition by areas_id order by timestamp)), 1) T_END
    from inouts
),
y as (
    select *, LEAD(t1, 1) over (partition by areas_id order by t1) t2
    from x
    WHERE T_START<>0 OR T_END<>0
)
select areas_id, is_flag, t1 starttime, t2 endtime
from y
WHERE T_START<>0 
order by areas_id, t1

shoud do the trick

user_1440784 · Answer

Some more information (like example data and what query is failing) would help but it looks like you could just to a group by.

select a_id, is_flag, min(timestamp) as starttime, max(timestamp) as endtime
  from tablename
  group by a_id, is_flag

user_9601310 · Answer

What am I missing here? Have you possibly "overthought" things? The SQL below gives the same result set as your example db-fiddle (I tested on a copy), is very much simpler and runs much faster. It gives a row for each areas_id/is_in_or_out combination (per the GROUP BY). I don't quite understand why you need the UNIONs and ROW_NUMBER() OVERs to complicate the query. Hope this helps. Try it out for yourself and let me know if there's any issue!

SELECT areas_id,
       starttime,
       endtime,
       is_in_or_out
FROM   (SELECT areas_id,
               MIN(timestamp) starttime,
               MAX(timestamp) endtime,
               is_in_or_out
        FROM   inouts
        GROUP  BY is_in_or_out,
                  areas_id) x
ORDER  BY starttime;

P.S. I think MBeale's solution is also actually correct (although it misses the ORDER BY).

Optimizing group by query with union

4 Answers