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.
