Showing posts with label Calculate Z Score in Oracle. Show all posts
Showing posts with label Calculate Z Score in Oracle. Show all posts

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