Postgres Pro
Downloads
Home   >   mailing lists

Re: computing z-scores - Mailing list pgsql-general

From David G. Johnston
Subject Re: computing z-scores
Date
Msg-id CAKFQuwYFeL_KBYMmBpk=vJ0A0GkS6YWpwGbAHu8=V=70Lu8q-A@mail.gmail.com
Whole thread Raw
In response to Re: computing z-scores  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
Tree view
On Thursday, May 24, 2018, David G. Johnston <david.g.johnston@gmail.com> 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.

pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: computing z-scores
Next
From: Paul Jungwirth
Date:
Subject: Performance opportunities for write-only audit tables?