Обсуждение: computing z-scores

You construct a z-score for a set of values by subtracting the average from the value and dividing the result by the
standarddeviation. I know how to do this in a two-step procedure. First, I compute the average and standard deviation.
Ina 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
goesbeyond my SQL competence. 
 

Martin Mueller    

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
standarddeviation. I know how to do this in a two-step procedure. First, I compute the average and standard deviation.
Ina 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
goesbeyond 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.

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

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

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