问MYSQL/PHP -返回行的平均值，而值没有变化？

EN

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

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());

+--------+--------+-------+---------------------+-----------+-----------+-----------------+
| 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 |
+--------+--------+-------+---------------------+-----------+-----------+-----------------+

while ($row = @mysql_fetch_assoc($result)){ 
    if( $row['SPEED']!=0){

        echo 'list average';
        clear list;
        // ADD TO XML DOCUMENT NODE
        echo '<marker ';
        echo 'unitid="' . $row['UNITID'] . '" ';
        echo 'lat="' . $row['LATITUDE'] . '" ';
        echo 'lng="' . $row['LONGITUDE'] . '" ';
        echo 'spd="' . $row['SPEED'] . '" ';
        echo 'time="' . $row['TRACKINGTIME'] . '" ';
        echo '/>';
    } else {
        //put data to a list 
    }
}

-- get average lat/lng for each unitid where speed is 0
select uuid, unitid, avg(lat), avg(lng)
from trackpoint_au
where speed=0
group by uuid, unitid