Changeset 0dbb5eb9b2c9f102370e5d34d527dde28d3588d4

Show
Ignore:
Timestamp:
03/27/08 20:05:16 (7 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1206648316 +0000
git-parent:

[03c905446907610b20f89a64a0d2810d134e52ec]

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

updated functions for rollups

Files:

Legend:

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

    r03c9054 r0dbb5eb  
    222222  
    223223          
    224   IF v_min_whence > v_max_rollup_5 THEN 
     224  IF v_min_whence >= v_max_rollup_5 THEN 
    225225   
    226226  -- 5 MINUTES ROLLUP 
     
    244244   -- 5 MINUTES ROLLUP 
    245245 
    246      PERFORM stratcon.rollup_matrix_numeric_5m_odd(v_min_whence ,v_max_rollup_5); 
     246DELETE FROM stratcon.rollup_matrix_numeric_5m  
     247 WHERE rollup_time = date_trunc('minutes',v_min_whence); 
     248      
     249      PERFORM stratcon.rollup_matrix_numeric_5m(v_min_whence ,v_max_rollup_5); 
    247250      
    248251  -- HOURLY ROLLUP 
     
    273276$$ LANGUAGE plpgsql; 
    274277 
    275  
    276278--- 5 minutes rollup  
    277279 
    278  
    279280CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m(v_min_whence timestamptz) 
    280 RETURNS voidddddd 
     281RETURNS void 
    281282AS $$ 
    282283DECLARE 
     
    296297       LOOP 
    297298  
    298         v_sql:= 'INSERT INTO stratcon.rollup_matrix_numeric_5m'||                         
    299         '(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES '|| 
    300         '('||rec.sid||', '||quote_literal(rec.name)||', '||quote_literal(rec.rollup_time)||', '||rec.count_rows||', '||rec.avg_value||', '||coalesce(rec.stddev_value,0)|| 
    301         ', '||rec.min_value||', '||rec.max_value||')'; 
    302  
    303  
    304           EXECUTE v_sql; 
    305  
     299        INSERT INTO stratcon.rollup_matrix_numeric_5m 
     300         (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES  
     301         (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     302         
     303  
    306304 END LOOP; 
    307305 
     
    317315$$ LANGUAGE plpgsql; 
    318316 
    319  
    320  
    321 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m_odd(v_min_whence timestamptz,v_max_rollup_5 timestamptz) 
    322 RETURNS void 
    323 AS $$ 
    324 DECLARE 
    325   
    326  rec stratcon.rollup_matrix_numeric_5m%rowtype; 
    327  v_sql TEXT; 
    328   
    329 BEGIN 
    330  
    331  DELETE FROM stratcon.rollup_matrix_numeric_5m  
    332  WHERE rollup_time >= date_trunc('minutes',v_min_whence); 
    333   
    334  FOR rec IN  
    335                  SELECT sid , name, date_trunc('H',whence) + (round(extract('minute' from whence)/5)*5) * '1 minute'::interval as rollup_time, 
    336                        COUNT(1) as count_rows ,AVG(value) as avg_value,STDDEV(value) as stddev_value ,MIN(value) as min_value ,MAX(value) as max_value 
    337                        FROM stratcon.loading_dock_metric_numeric_s 
    338                        WHERE WHENCE >= date_trunc('minutes',v_min_whence) AND WHENCE <= date_trunc('minutes',v_max_rollup_5) 
    339                  GROUP BY rollup_time,sid,name 
    340    
    341         LOOP 
    342   
    343      
    344          v_sql:= 'INSERT INTO stratcon.rollup_matrix_numeric_5m'||                         
    345          '(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES '|| 
    346          '('||rec.sid||', '||quote_literal(rec.name)||', '||quote_literal(rec.rollup_time)||', '||rec.count_rows||', '||rec.avg_value||', '||coalesce(rec.stddev_value,0)|| 
    347          ', '||rec.min_value||', '||rec.max_value||')'; 
    348   
    349   
    350            EXECUTE v_sql; 
    351   
    352   END LOOP; 
    353   
    354  
    355 RETURN; 
    356  
    357 EXCEPTION 
    358     WHEN RAISE_EXCEPTION THEN 
    359        RAISE EXCEPTION '%', SQLERRM; 
    360     WHEN OTHERS THEN 
    361       RAISE NOTICE '%', SQLERRM; 
    362 END 
    363 $$ LANGUAGE plpgsql; 
    364  
    365  
    366  
    367  
    368  
    369  
    370317--- Hourly rollup 
    371318 
     
    380327BEGIN 
    381328    FOR rec IN  
    382                 SELECT sid , name,date_trunc('hour',rollup_time) as rollup_time,SUM (count_rows) as count_rows ,AVG(avg_value) as avg_value, 
    383                          STDDEV(stddev_value) as stddev_value ,MIN(min_value) as min_value ,MAX(max_value) as max_value 
     329                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, 
     330                         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, 
     331                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
    384332                         FROM stratcon.rollup_matrix_numeric_5m 
    385333                           WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence) 
    386                    GROUP BY rollup_time,sid,name 
     334                   GROUP BY date_trunc('hour',rollup_time),sid,name 
    387335        LOOP 
    388         v_sql:= 'INSERT INTO stratcon.rollup_matrix_numeric_60m'||                         
    389         '(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES '|| 
    390         '('||rec.sid||', '||quote_literal(rec.name)||', '||quote_literal(rec.rollup_time)||', '||rec.count_rows||', '||rec.avg_value||', '||coalesce(rec.stddev_value,0)|| 
    391         ', '||rec.min_value||', '||rec.max_value||')'; 
    392  
    393           EXECUTE v_sql; 
     336       
     337          INSERT INTO stratcon.rollup_matrix_numeric_60m 
     338          (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 
     339          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     340           
    394341     END LOOP; 
    395342RETURN; 
     
    422369 
    423370COMMIT; 
    424