当前表如下所示：

SELECT * FROM mytable;
 user_id |     trip_id      |    lat    |    lon     | sampling_rate 
---------+------------------+-----------+------------+---------------
       2 | 1020081023055305 | 39.984094 | 116.319236 |              
       2 | 1020081023055305 | 39.984198 | 116.319322 |             1
       2 | 1020081023055305 | 39.984224 | 116.319402 |             5
       2 | 1020081023055305 | 39.984211 | 116.319389 |             5
       2 | 1020081023055305 | 39.984217 | 116.319422 |             5
       2 | 2020081023124523 | 39.927938 | 116.338967 |              
       2 | 2020081023124523 | 39.927527 | 116.338899 |             1
       2 | 2020081023124523 | 39.926516 | 116.338048 |            30
       2 | 2020081023124523 | 39.926496 | 116.338094 |             5
       2 | 2020081023124523 | 39.926498 |  116.33814 |             5
       5 | 4020081023175852 | 39.999974 | 116.327149 |              
       5 | 4020081023175852 | 40.000011 | 116.327161 |             6
       5 | 4020081023175852 | 40.000008 | 116.327135 |             5
       5 | 4020081023175852 | 40.000016 | 116.327126 |             5
       5 | 4020081023175852 | 40.000003 | 116.327098 |             5
       5 | 4020081023175852 | 40.000019 | 116.327071 |             5
       5 | 4020081023175852 | 40.000132 | 116.327086 |             5

然后我要计算平均值和标准差 sampling_rate 每次行程（注意 sampling_rate 每次行程开始时为0。

SELECT  AVG(sample_stat)AS mean_rate_val, STDDEV_POP(sample_stat) AS sampling_std
FROM (
SELECT COUNT(1) sample_stat FROM mytable GROUP BY trip_id
) sample_analysis

查询返回整个系统的平均值和标准偏差 sampling_rate 列。