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 Thursday, May 24, 2018, Ron  wrote:

> On 05/24/2018 10:15 AM, Martin Mueller 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.
>>
>
> What about this?
>
> SELECT value, (value - AVG(value))/stddev(value) as zvalue
> FROM sometable
> WHERE some conditions
> GROUP by value
>

That is syntactically correct but doesn't provide the correct answer.

David J.

Re: computing z-scores

[Ron]

On 05/24/2018 10:15 AM, Martin Mueller 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.


What about this?

SELECT value, (value - AVG(value))/stddev(value) as zvalue
FROM sometable
WHERE some conditions
GROUP by value


--
Angular momentum makes the world go 'round.

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