Wednesday, August 15, 2012

Calculate Z-Score using SQL in Oracle

We can normalize data using Z-score, Z-score=(data value-mean)/standard deviation.
1. Data.
select num from  TBL_TEST order by num;
       NUM
----------
         1
         1
         2
         2
         2
         3
         4
         4
         5
2. Calculate Z-score.
with tbl_mean_std as
(
select avg(num) m, stddev(num) std from TBL_TEST
)
select num, (num-m)/std as z_score from TBL_TEST , tbl_mean_std  order by num;

       NUM    Z_SCORE
---------- ----------
         1 -1.1785113
         1 -1.1785113
         2 -.47140452
         2 -.47140452
         2 -.47140452
         3  .23570226
         4 .942809042
         4 .942809042
         5 1.64991582

2 comments:

rajput said...

In my opinion a z-score is the number of standard deviations that a value, x, is above or below the mean.If the value of x is less than the mean, the z score is negative.If the value of x is more than the mean, the z score is positive.If the value of x equals the mean,the z score is zero.
algebra solver

Jay Zhou, PhD. said...

That is a good interpretation of z-score. I think one benefit of z-score is that it makes variables of different scales comparable. Thank you for your input.
Jay