CODEWITHSUNDEEP
mysql
Stephen Manderson
Stephen Manderson

Reputation: 53

How to select min & max values of subset groups

I'm trying to get my head round the following problem, I have a tracking device that updates its location and online state every minute. I'm trying to split this data up into separate journeys by grouping an online state into several small subsets of a select statement.

I know I can select the min & max and union these for the table as a whole but I'm unsure how to do this for each group where its online state is true.

Whilst I can achieve the desired result in code I need to move this operation to the server for over the wire performance reasons.

Could anyone help point me in the right direction to break this down into a sql statement?

[positions]
+-------------------+-----------------------+------+-----+---------+----------------+
| Field             | Type                  | Null | Key | Default | Extra          |
+-------------------+-----------------------+------+-----+---------+----------------+
| id                | int          unsigned | NO   | PRI | NULL    | auto_increment |
| device_id         | int          unsigned | NO   |     | NULL    |                |
| lat               | double                | NO   |     | 0       |                |
| lng               | double                | NO   |     | 0       |                |
| date_time         | datetime              | NO   |     | NULL    |                |
| online            | bit                   | NO   |     | 0       |                |
+-------------------+-----------------------+------+-----+---------+----------------+

Example data

id  device_id   lat     lng     date_time             online
14  1           0.1     0.1     2014-07-11 05:21:37   0
17  1           0.11    0.11    2014-07-11 05:22:37   1 
18  1           0.12    0.12    2014-07-11 05:23:37   1
24  1           0.13    0.13    2014-07-11 05:24:37   1
25  1           0.14    0.14    2014-07-11 05:25:37   1
26  1           0.14    0.14    2014-07-11 05:26:37   0
45  1           0.14    0.14    2014-07-11 05:27:37   0
47  1           0.14    0.14    2014-07-11 05:28:37   1
56  1           0.13    0.13    2014-07-11 05:29:37   1
67  1           0.12    0.12    2014-07-11 05:30:37   1
68  1           0.11    0.11    2014-07-11 05:31:37   1
78  1           0.11    0.11    2014-07-11 05:32:37   0

Desired result

StartDateTime        StartLat StartLng  FinishDateTime       FinishLat FinishLng
2014-07-11 05:22:37  0.11     0.11      2014-07-11 05:25:37  0.14      0.14
2014-07-11 05:28:37  0.14     0.14      2014-07-11 05:31:37  0.11      0.11

Thanks in advance, Steve

Upvotes: 2

Views: 1172

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

You need to characterize each group. The easiest way is to count the number of online = 0 before each row:

select device_id, min(date_time) as StartDateTime, max(date_time) as FinishDateTime,
       substring_index(group_concat(lat order by datetime asc), ',', 1) as StartLat,
       substring_index(group_concat(long order by datetime asc), ',', 1) as StartLong,
       substring_index(group_concat(lat order by datetime desc), ',', 1) as EndLat,
       substring_index(group_concat(long order by datetime desc), ',', 1) as EndLong
from (select e.*,
             (select count(*)
              from example e2
              where e2.device_id = e.device_id and
                    e2.date_time <= e.date_time and
                    e2.online = 0
             ) as grp
      from example e
      where e.online = 1
     ) e
group by device_id, grp;

This uses the substring_index()/group_concat() trick to get the first and last values.

select t.*,
       efirst.lat as firstLat, efirst.long as firstLong,
       ellast.lat as lastLat, elast.long as lastLong
from (select device_id, min(date_time) as StartDateTime, max(date_time) as FinishDateTime
      from (select e.*,
                   (select count(*)
                    from example e2
                    where e2.device_id = e.device_id and
                          e2.date_time <= e.date_time and
                          e2.online = 0
                   ) as grp
            from example e
            where e.online = 1
           ) e
      group by device_id, grp
     ) t join
     example efirst
     on efirst.device_id = t.device_id and efirst.date_time = FirstDateTime join
     example elast
     on elast.device_id = t.device_id and elast.date_time = FinishDateTime;

Upvotes: 1

Related Questions