CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
arilwan
arilwan

Reputation: 3993

postgres: computing the standard deviation value for each trip

The current table looks like:

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

Then I want to compute the mean and standard deviation of the sampling_rate per trip(note that the value of the sampling_rate at the beginning of each trip is 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

The query returns mean and std_dev over whole sampling_rate column.

Upvotes: 0

Views: 397

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Then I want to compute the mean and standard deviation of the sampling_rate per trip

Is this what you want?

select user_id, trip_id, avg(sampling_rate), stddev_pop(sampling_rate)
from mytable
group by user_id, trip_id;

Upvotes: 1

Related Questions