On Thursday, May 24, 2018, David G. Johnston <david.g.johns...@gmail.com> 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.
