StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>in addition to the normal use of <code>GROUP BY</code> and <code>AVG()</code> you may be interested in Quassnoi's answer to my question here:</p> <p><a href="https://stackoverflow.com/questions/1136597/group-by-for-continuous-rows-in-sql">GROUP BY for continuous rows in SQL</a></p> <p>He posted a very nice solution that also performs very well with many rows.</p> <p>Think of the speed as a state, and you want to aggregate all continous rows within a time period that have the same speed.</p> <p>Here is my attempt on rewriting your query using this method:</p> <pre><code>SELECT UNITID, /* we aggregate multiple rows, maybe you want to know which ones.. this one is optional */ CAST(GROUP_CONCAT(UUID SEPARATOR ', ') AS CHAR) AS UUIDS, /* is group field in the inner subquery, we can just use it in our select without an aggregate function */ SPEED, /* very important to select the lowest timestamp - this is the time when your unit has stopped moving .. first row with speed=0 */ MIN(TRACKINGTIME) AS TRACKINGTIME, /* we calc the average on latitude here */ TRUNCATE(AVG(LATITUDE),6) AS LATITUDE, /* same for longitude */ TRUNCATE(AVG(LONGITUDE),6) AS LONGITUDE, /* maybe you want to know how many rows with speed 0 are grouped together */ COUNT(UUID) AS AGGREGATE_COUNT FROM ( SELECT /* this increases the counter variable @r each time the state has changed.. when speed of the previous row was also "0" and is "0" in the current row, the counter is not increased. -- this is a virtual field we will use for GROUPing. @speed is used to remember the speed of the previous row for comparison in @r to determine if the speed has changed */ @r := @r + (@prev_unit != UNITID OR @prev_speed != 0 OR SPEED != 0) AS gn, @prev_speed := SPEED AS a_speed, @prev_unit := UNITID AS a_unit, tp.* FROM ( SELECT @r := 0, @prev_speed := 1, @prev_unit := '' ) vars, trackpoint_au tp ORDER BY UNITID, TRACKINGTIME ) q GROUP BY gn ORDER BY UNITID </code></pre> <p>Test data:</p> <pre><code>CREATE TABLE `trackpoint_au` ( `uuid` int(11) NOT NULL AUTO_INCREMENT, `latitude` decimal(10,0) NOT NULL, `longitude` decimal(10,0) NOT NULL, `speed` int(11) NOT NULL, `unitid` int(11) NOT NULL, `trackingtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`uuid`) ) ENGINE=MyISAM; INSERT INTO trackpoint_au (unitid, speed, latitude, longitude, trackingtime) VALUES (1, 0, 10, 10, NOW()), (1, 0, 20, 20, NOW()), (1, 1, 10, 10, NOW()), (1, 0, 10, 10, NOW()), (1, 0, 30, 30, NOW()), (2, 0, 10, 10, NOW()), (2, 0, 20, 20, NOW()), (3, 1, 10, 10, NOW()), (4, 0, 10, 10, NOW()), (4, 0, 20, 20, NOW()), (4, 1, 30, 30, NOW()), (4, 0, 60, 60, NOW()), (4, 0, 60, 60, NOW()); </code></pre> <p>Result:</p> <pre><code>+--------+--------+-------+---------------------+-----------+-----------+-----------------+ | UNITID | UUIDS | SPEED | TRACKINGTIME | LATITUDE | LONGITUDE | AGGREGATE_COUNT | +--------+--------+-------+---------------------+-----------+-----------+-----------------+ | 1 | 2, 1 | 0 | 2011-12-05 09:34:13 | 15.000000 | 15.000000 | 2 | | 1 | 3 | 1 | 2011-12-05 09:34:13 | 10.000000 | 10.000000 | 1 | | 1 | 4, 5 | 0 | 2011-12-05 09:34:13 | 20.000000 | 20.000000 | 2 | | 2 | 6, 7 | 0 | 2011-12-05 09:34:13 | 15.000000 | 15.000000 | 2 | | 3 | 8 | 1 | 2011-12-05 09:34:13 | 10.000000 | 10.000000 | 1 | | 4 | 9, 10 | 0 | 2011-12-05 09:34:13 | 15.000000 | 15.000000 | 2 | | 4 | 11 | 1 | 2011-12-05 09:34:13 | 30.000000 | 30.000000 | 1 | | 4 | 12, 13 | 0 | 2011-12-05 09:34:13 | 60.000000 | 60.000000 | 2 | +--------+--------+-------+---------------------+-----------+-----------+-----------------+ </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload