Changeset 8894bdadccd8a4c542f45deafdd40965f568cd81

Show
Ignore:
Timestamp:
04/02/08 19:28:43 (6 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1207164523 +0000
git-parent:

[5789b0450d6a7a2018f9d3d6495a05b012b141b4]

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

updated functions for rollups

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/schema.sql

    ree0d0b7 r8894bda  
    5959   max_value numeric , 
    6060   PRIMARY KEY(rollup_time,sid,name)); 
     61    
     62CREATE TABLE stratcon.rollup_matrix_numeric_6hours( 
     63   sid integer not null, 
     64   name text not null,  
     65   rollup_time6 timestamp not null,  
     66   count_rows integer, 
     67   avg_value numeric , 
     68   stddev_value numeric, 
     69   min_value numeric , 
     70   max_value numeric , 
     71   PRIMARY KEY(rollup_time6,sid,name));    
     72 
     73CREATE TABLE stratcon.rollup_matrix_numeric_12hours( 
     74   sid integer not null, 
     75   name text not null,  
     76   rollup_time12 timestamp not null,  
     77   count_rows integer, 
     78   avg_value numeric , 
     79   stddev_value numeric, 
     80   min_value numeric , 
     81   max_value numeric , 
     82   PRIMARY KEY(rollup_time12,sid,name));       
    6183  
    6284CREATE TABLE stratcon.rollup_matrix_numeric_5m ( 
     
    109131 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_60m TO stratcon; 
    110132 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_5m TO stratcon; 
     133 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_6hours TO stratcon; 
     134 GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_12hours TO stratcon; 
    111135 GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon; 
    112136 ALTER TABLE stratcon.seq_sid OWNER TO stratcon; 
     
    264288END 
    265289$$ LANGUAGE plpgsql; 
     290 
    266291-- 1 hourl rollup 
    267292 
     
    275300  v_min_whence TIMESTAMPTZ; 
    276301  v_max_rollup_5 TIMESTAMPTZ; 
    277   
     302  v_whence TIMESTAMPTZ; 
    278303BEGIN 
    279304 
     
    283308  SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_60m  
    284309         INTO v_max_rollup_5;     
    285           
     310 
     311-- Insert Log for 6 Hour rollup 
     312    
     313   SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='6 hours' 
     314           INTO v_whence; 
     315      IF NOT FOUND THEN 
     316       INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'6 hours'); 
     317   END IF; 
     318    
     319    
    286320  IF v_min_whence <= v_max_rollup_5 THEN 
    287321   
     
    320354 
    321355 
     356-- 6 hours 
     357 
     358CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_6hours() 
     359RETURNS void 
     360AS $$ 
     361DECLARE 
     362  rec stratcon.rollup_matrix_numeric_6hours%rowtype; 
     363  v_sql TEXT; 
     364  v_min_whence TIMESTAMPTZ; 
     365  v_max_rollup_6 TIMESTAMPTZ; 
     366  v_whence TIMESTAMPTZ; 
     367  
     368BEGIN 
     369 
     370  SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours' 
     371         INTO v_min_whence; 
     372          
     373  SELECT max(date_trunc('H',rollup_time6)) FROM  stratcon.rollup_matrix_numeric_6hours  
     374         INTO v_max_rollup_6;     
     375 
     376-- Insert Log for 12 Hours rollup 
     377    
     378   SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='12 hours' 
     379           INTO v_whence; 
     380      IF NOT FOUND THEN 
     381       INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'12 hours'); 
     382   END IF; 
     383    
     384    
     385  IF v_min_whence <= v_max_rollup_6 THEN 
     386   
     387  DELETE FROM stratcon.rollup_matrix_numeric_6hours  
     388       WHERE rollup_time6= v_min_whence; 
     389 
     390  END IF; 
     391   
     392    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, 
     395                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
     396                         FROM stratcon.rollup_matrix_numeric_60m 
     397                           WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval 
     398                   GROUP BY sid,name 
     399        LOOP 
     400       
     401        
     402          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); 
     405           
     406     END LOOP; 
     407 
     408 
     409DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours'; 
     410 
     411RETURN; 
     412 
     413EXCEPTION 
     414    WHEN RAISE_EXCEPTION THEN 
     415       RAISE EXCEPTION '%', SQLERRM; 
     416    WHEN OTHERS THEN 
     417      RAISE NOTICE '%', SQLERRM; 
     418END 
     419$$ LANGUAGE plpgsql; 
     420 
     421 
     422-- 12 hours 
     423 
     424CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_12hours() 
     425RETURNS void 
     426AS $$ 
     427DECLARE 
     428  rec stratcon.rollup_matrix_numeric_12hours%rowtype; 
     429  v_sql TEXT; 
     430  v_min_whence TIMESTAMPTZ; 
     431  v_max_rollup_12 TIMESTAMPTZ; 
     432  v_whence TIMESTAMPTZ; 
     433  
     434BEGIN 
     435 
     436  SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours' 
     437         INTO v_min_whence; 
     438          
     439  SELECT max(date_trunc('H',rollup_time12)) FROM  stratcon.rollup_matrix_numeric_12hours  
     440         INTO v_max_rollup_12;     
     441 
     442/*-- Insert Log for 24 Hours rollup 
     443    
     444   SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='24 hours' 
     445           INTO v_whence; 
     446      IF NOT FOUND THEN 
     447       INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'24 hours'); 
     448   END IF; 
     449   */ 
     450    
     451  IF v_min_whence <= v_max_rollup_12 THEN 
     452   
     453  DELETE FROM stratcon.rollup_matrix_numeric_12hours  
     454       WHERE rollup_time12= v_min_whence; 
     455 
     456  END IF; 
     457   
     458    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, 
     461                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
     462                         FROM stratcon.rollup_matrix_numeric_6hours 
     463                           WHERE rollup_time6<= v_min_whence and rollup_time6> v_min_whence-'12 hour'::interval 
     464                   GROUP BY sid,name 
     465        LOOP 
     466       
     467        
     468          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); 
     471           
     472     END LOOP; 
     473 
     474 
     475DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours'; 
     476 
     477RETURN; 
     478 
     479EXCEPTION 
     480    WHEN RAISE_EXCEPTION THEN 
     481       RAISE EXCEPTION '%', SQLERRM; 
     482    WHEN OTHERS THEN 
     483      RAISE NOTICE '%', SQLERRM; 
     484END 
     485$$ LANGUAGE plpgsql; 
     486 
    322487 
    323488COMMIT;