Thread

1. computing z-scores

   Martin Mueller <martinmueller@northwestern.edu> — 2018-05-24T15:15:59Z

   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. 
   
   Martin Mueller    
   
   

2. Re: computing z-scores

   David G. Johnston <david.g.johnston@gmail.com> — 2018-05-24T15:22:26Z

   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.
   

3. Re: computing z-scores

   Ron <ronljohnsonjr@gmail.com> — 2018-05-24T15:26:44Z

   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
   
   
   -- 
   Angular momentum makes the world go 'round.
   
   
   

4. Re: computing z-scores

   David G. Johnston <david.g.johnston@gmail.com> — 2018-05-24T15:30:24Z

   On Thursday, May 24, 2018, Ron <ronljohnsonjr@gmail.com> wrote:
   
   > 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
   >
   
   That is syntactically correct but doesn't provide the correct answer.
   
   David J.
   

5. Re: computing z-scores

   David G. Johnston <david.g.johnston@gmail.com> — 2018-05-24T15:32:37Z

   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.