Show
Ignore:
Timestamp:
04/16/08 21:05:40 (5 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1208379940 +0000
git-parent:

[78d7296483e39a19218cf1e50f7c4b4aaf0a27aa]

git-author:
Denish Patel <denish@omniti.com> 1208379940 +0000
Message:

modified rollup_time6/12 colums to rollup_time and changed functions accordingly

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • sql/schema.sql

    r8894bda rd67a38b  
    6363   sid integer not null, 
    6464   name text not null,  
    65    rollup_time6 timestamp not null,  
     65   rollup_time timestamp not null,  
    6666   count_rows integer, 
    6767   avg_value numeric , 
    …… 
    7474   sid integer not null, 
    7575   name text not null,  
    76    rollup_time12 timestamp not null,  
     76   rollup_time timestamp not null,  
    7777   count_rows integer, 
    7878   avg_value numeric , 
    …… 
    327327    FOR rec IN  
    328328                SELECT sid,name,date_trunc('hour',rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    329                          SQRT((SUM((count_rows-1)*(POWER(stddev_value,2)+POWER(avg_value,2)))/(SUM(count_rows)-1)))-(power(SUM(avg_value*count_rows)/SUM(count_rows),2)) as stddev_value, 
     329                         stddev(stddev_value) as stddev_value, 
    330330                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
    331331                         FROM stratcon.rollup_matrix_numeric_5m 
    …… 
    371371         INTO v_min_whence; 
    372372          
    373   SELECT max(date_trunc('H',rollup_time6)) FROM  stratcon.rollup_matrix_numeric_6hours  
     373  SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_6hours  
    374374         INTO v_max_rollup_6;     
    375375 
    …… 
    386386   
    387387  DELETE FROM stratcon.rollup_matrix_numeric_6hours  
    388        WHERE rollup_time6= v_min_whence; 
     388       WHERE rollup_time= v_min_whence; 
    389389 
    390390  END IF; 
    391391   
    392392    FOR rec IN  
    393                 SELECT sid,name,v_min_whence as rollup_time6,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    394                          SQRT((SUM((count_rows-1)*(POWER(stddev_value,2)+POWER(avg_value,2)))/(SUM(count_rows)-1)))-(power(SUM(avg_value*count_rows)/SUM(count_rows),2)) as stddev_value, 
     393                SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
     394                         STDDEV(stddev_value) as stddev_value, 
    395395                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
    396396                         FROM stratcon.rollup_matrix_numeric_60m 
    …… 
    401401        
    402402          INSERT INTO stratcon.rollup_matrix_numeric_6hours 
    403           (sid,name,rollup_time6,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 
    404           (rec.sid,rec.name,rec.rollup_time6,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     403          (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 
     404          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
    405405           
    406406     END LOOP; 
    …… 
    437437         INTO v_min_whence; 
    438438          
    439   SELECT max(date_trunc('H',rollup_time12)) FROM  stratcon.rollup_matrix_numeric_12hours  
     439  SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_12hours  
    440440         INTO v_max_rollup_12;     
    441441 
    …… 
    452452   
    453453  DELETE FROM stratcon.rollup_matrix_numeric_12hours  
    454        WHERE rollup_time12= v_min_whence; 
     454       WHERE rollup_time= v_min_whence; 
    455455 
    456456  END IF; 
    457457   
    458458    FOR rec IN  
    459                 SELECT sid,name,v_min_whence as rollup_time12,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    460                          SQRT((SUM((count_rows-1)*(POWER(stddev_value,2)+POWER(avg_value,2)))/(SUM(count_rows)-1)))-(power(SUM(avg_value*count_rows)/SUM(count_rows),2)) as stddev_value, 
     459                SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
     460                         STDDEV(stddev_value) as stddev_value, 
    461461                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
    462462                         FROM stratcon.rollup_matrix_numeric_6hours 
    463                            WHERE rollup_time6<= v_min_whence and rollup_time6> v_min_whence-'12 hour'::interval 
     463                           WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval 
    464464                   GROUP BY sid,name 
    465465        LOOP 
    …… 
    467467        
    468468          INSERT INTO stratcon.rollup_matrix_numeric_12hours 
    469           (sid,name,rollup_time12,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 
    470           (rec.sid,rec.name,rec.rollup_time12,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     469          (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 
     470          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
    471471           
    472472     END LOOP;