Re: computing z-scores

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Thursday, May 24, 2018, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

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.

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.