CODEWITHSUNDEEP
postgresqlaggregate-functions
Andrew Fox
Andrew Fox

Reputation: 133

PostgreSQL Rolling Standard Deviation over time in single query

This may be an easily solvable question but I can't see an immediate solution. I am calling a PostgreSQL function which returns multiple columns, 2 of which are relevant to this question - a date column & a numeric field of return values. An example of the function call would be 

SELECT curr_date, return_val 
  FROM schema.function_name($1,$2);

With example output such as 

"2014-07-31";0.003767
"2014-08-07";-0.028531
"2014-08-14";0.020051
"2014-08-21";-0.003541
"2014-08-28";0.007766
"2014-09-04";-0.021926
"2014-09-11";0.026330
"2014-09-18";0.008137
"2014-09-25";-0.033303
"2014-10-02";0.030100
"2014-10-09";-0.012116
"2014-10-16";-0.017148

So on, so forth. The data will always return from this function with the dates ascending. What I would like to do is to use Postgres's stddev_samp function on every row, but only considering the return_value's from that row's date back in time. Something like:

SELECT curr_date, return_val,
     --stddev_samp(return_val) where curr_date <= curr_date of current row
  FROM schema.function_name($1,$2);

Naturally, if I calculated the sample deviation of the return_value's from 2014-07-31 to 2014-10-02 in the sample provided, it would differ slightly to calculating it using the result set from 2014-07-31 to any other date present. I know I could probably write another function which takes a numeric array as input and returns the standard deviation as output, and then call this in my query above, but I'm hoping someone may have a simpler approach which I'm just currently not seeing. If any other information is required, feel free to ask. I'm using version 10.7.

Upvotes: 2

Views: 1466

Answers (1)

S-Man
S-Man

Reputation: 23726

demo:db<>fiddle

Using window functions:

SELECT
    stddev_samp(return_val) OVER(ORDER BY curr_date)
FROM
    mytable

Upvotes: 2

Related Questions