Re: computing z-scores

On Thu, May 24, 2018 at 8:15 AM, Martin Mueller <
martinmueller(at)northwestern(dot)edu> wrote:

> You construct a z-score for a set of values by subtracting the average
> from the value and dividing the result by the standard deviation. I know
> how to do this in a two-step procedure. First, I compute the average and
> standard deviation. In a second run I use the formula and apply it to each
> value.
>
> Is there a way of doing this in a single-step procedure or can you chain
> the two parts together in one query? This goes beyond my SQL competence.
>

Window functions provide the easiest means to apply aggregated values to
individual rows.

SELECT v, (v - (AVG(v) OVER ()) / (stddev(v) OVER ())) AS z_v
FROM (
VALUES (1),(2),(3)
) vals (v);

//-1, 0, 1

​https://www.postgresql.org/docs/10/static/tutorial-window.html

David J.