MYSQL/PHP -在值不变的情况下返回行平均值

除了GROUP BY和AVG()的正常使用之外，您可能对Quassnoi对我这里的问题的回答感兴趣:

SQL中连续行的GROUP BY

他发布了一个非常好的解决方案，在许多行中也表现得很好。

把速度想象成一种状态，并且您想要聚合在一段时间内具有相同速度的所有连续行。

下面是我尝试用这个方法重写你的查询:

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

当使用group by时，任何未使用复合函数的选定字段都应添加到group by语句中

编辑

将uuid添加到SELECT和GROUP BY

我宁愿提出不同的建议。如果当速度= 0时，Lat和Long的变化不显著，为什么要尝试得到平均值?对于这些值，您可以截断最后一位数字或四舍五入5或10。

这并不会减少行数。但是，您应该能够为Lat和Long获得一致的值。