STDDEV_POP VS STDDEV_SAMP

Question

Can someone explain to me what is the difference of these two functions in postgres and when to you which?.

I have this data in my table and would like to compute the standard deviation of trips' sampling rate:

  user_id |      trip_id       |       lat        |       lon        | sampling_rate 
---------+--------------------+------------------+------------------+---------------
     126 | 125020080511025052 | 39.9531666666667 | 116.452566666667 |             7
     126 | 125020080511025052 |         39.95305 | 116.452683333333 |            16
     126 | 125020080511025052 | 39.9530666666667 | 116.452916666667 |            44
     126 | 125020080511025052 | 39.9530833333333 | 116.453183333333 |            40
     126 | 125020080511025052 |         39.95335 |        116.45365 |            21
     126 | 125020080511025052 | 39.9532833333333 | 116.453816666667 |            16
     126 | 125020080511025052 | 39.9533166666667 |        116.45405 |            13
     126 | 125020080511025052 | 39.9535666666667 | 116.454383333333 |            24
     126 | 125020080511025052 | 39.9537166666667 |         116.4546 |            16
     126 | 125020080511025052 | 39.9538333333333 | 116.454733333333 |            17

Durward

Which are "these two functions"? What do you expect to be returned? – Oriole

see the question subject please, answered already. – Durward

Answer 1

This is a statistics question rather than a programming or database question.

The short answer is that you use the sample standard deviation unless you have a reason to use the population standard deviation and understand why you are using population standard deviation instead.

The stddev() in Postgreql maps to stddev_samp().

This is a long read, but it covers the basics: https://statistics.laerd.com/statistical-guides/measures-of-spread-range-quartiles.php

As the number of observations in your sample go up, the sample standard deviation converges toward the population standard deviation.

Bloodred