Re: computing z-scores

On Thursday, May 24, 2018, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> 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.
>

I think I have misplaced a parenthesis though...order of operations needs
one added around the subtraction.

Note, this is not the correct list for questions like this. The -general
list is the one you want to be using.

David J.