Re: computing z-scores

[David G. Johnston]

On Thursday, May 24, 2018, David G. Johnston 
wrote:

> On Thu, May 24, 2018 at 8:15 AM, Martin Mueller <
> martinmuel...@northwestern.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.

Re: computing z-scores

[David G. Johnston]

On Thu, May 24, 2018 at 8:15 AM, Martin Mueller <
martinmuel...@northwestern.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.

computing z-scores

[Martin Mueller]

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. 

Martin Mueller