Thread: computing z-scores
You construct a z-score for a set of values by subtracting the average from the value and dividing the result by the standarddeviation. I know how to do this in a two-step procedure. First, I compute the average and standard deviation. Ina 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 goesbeyond my SQL competence. Martin Mueller
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
David J.
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
David J.
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 standarddeviation. I know how to do this in a two-step procedure. First, I compute the average and standard deviation. Ina 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 goesbeyond 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.
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.
On Thursday, May 24, 2018, David G. Johnston <david.g.johnston@gmail.com> 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_vFROM (VALUES (1),(2),(3)) vals (v);//-1, 0, 1David 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.
On Thursday, May 24, 2018, David G. Johnston <david.g.johnston@gmail.com> 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_vFROM (VALUES (1),(2),(3)) vals (v);//-1, 0, 1David 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.