Changeset 6b9f38ffb400becb8ab312d34abd2981bca9fe87

Show
Ignore:
Timestamp:
06/16/08 19:16:10 (6 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1213643770 +0000
git-parent:

[bbebbfeff0cf986fbdb1cfdc15c07a9ea059dc2a]

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

remove min/max values from rollup tables/functions.

Files:

Legend:

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

    r601c99b r6b9f38f  
    6464    rollup_time timestamptz NOT NULL, 
    6565    count_rows integer, 
    66     avg_value numeric, 
    67     min_value numeric, 
    68     max_value numeric, 
     66    avg_value numeric 
    6967    PRIMARY KEY (rollup_time,sid,name) 
    7068); 
     
    7573    rollup_time timestamptz NOT NULL, 
    7674    count_rows integer, 
    77     avg_value numeric, 
    78     min_value numeric, 
    79     max_value numeric, 
     75    avg_value numeric 
    8076    PRIMARY KEY (rollup_time,sid,name) 
    8177); 
     
    9692   rollup_time timestamptz not null,  
    9793   count_rows integer, 
    98    avg_value numeric , 
    99    min_value numeric , 
    100    max_value numeric , 
     94   avg_value numeric  
    10195   PRIMARY KEY(rollup_time,sid,name)); 
    10296    
     
    106100   rollup_time timestamptz not null,  
    107101   count_rows integer, 
    108    avg_value numeric , 
    109    min_value numeric , 
    110    max_value numeric , 
     102   avg_value numeric  
    111103   PRIMARY KEY(rollup_time6,sid,name));    
    112104 
     
    117109   count_rows integer, 
    118110   avg_value numeric , 
    119    min_value numeric , 
    120    max_value numeric , 
    121111   PRIMARY KEY(rollup_time12,sid,name));       
    122112  
     
    434424 FOR rec IN  
    435425                SELECT sid , name,v_min_whence as rollup_time, 
    436                       COUNT(1) as count_rows ,AVG(value) as avg_value,MIN(value) as min_value ,MAX(value) as max_value 
     426                      COUNT(1) as count_rows ,AVG(value) as avg_value 
    437427                      FROM stratcon.loading_dock_metric_numeric_s 
    438428                      WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval 
     
    443433         
    444434        INSERT INTO stratcon.rollup_matrix_numeric_5m 
    445          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES  
    446          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
     435         (sid,name,rollup_time,count_rows,avg_value) VALUES  
     436         (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
    447437         
    448438   END LOOP; 
     
    536526 FOR rec IN  
    537527                SELECT sid , name,v_min_whence as rollup_time, 
    538                        SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    539                        MIN(min_value) as min_value ,MAX(max_value) as max_value 
     528                       SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 
    540529                       FROM stratcon.rollup_matrix_numeric_5m 
    541530                      WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence -'20 minutes'::interval 
     
    546535         
    547536        INSERT INTO stratcon.rollup_matrix_numeric_20m 
    548          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES  
    549          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
     537         (sid,name,rollup_time,count_rows,avg_value) VALUES  
     538         (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
    550539         
    551540   END LOOP; 
     
    638627   
    639628    FOR rec IN  
    640                 SELECT sid,name,date_hour(rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    641                          MIN(min_value) as min_value ,MAX(max_value) as max_value 
     629                SELECT sid,name,date_hour(rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 
    642630                         FROM stratcon.rollup_matrix_numeric_20m 
    643631                           WHERE date_hour(rollup_time)= v_min_whence 
     
    646634       
    647635          INSERT INTO stratcon.rollup_matrix_numeric_60m 
    648           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 
    649           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
     636          (sid,name,rollup_time,count_rows,avg_value) VALUES 
     637          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
    650638           
    651639     END LOOP; 
     
    736724   
    737725    FOR rec IN  
    738                 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, 
    739                          MIN(min_value) as min_value ,MAX(max_value) as max_value 
     726                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 
    740727                         FROM stratcon.rollup_matrix_numeric_60m 
    741728                           WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval 
     
    745732        
    746733          INSERT INTO stratcon.rollup_matrix_numeric_6hours 
    747           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 
    748           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
     734          (sid,name,rollup_time,count_rows,avg_value) VALUES 
     735          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
    749736           
    750737     END LOOP; 
     
    835822   
    836823    FOR rec IN  
    837                 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, 
    838                          MIN(min_value) as min_value ,MAX(max_value) as max_value 
     824                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 
    839825                         FROM stratcon.rollup_matrix_numeric_6hours 
    840826                           WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval 
     
    844830        
    845831          INSERT INTO stratcon.rollup_matrix_numeric_12hours 
    846           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 
    847           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
     832          (sid,name,rollup_time,count_rows,avg_value) VALUES 
     833          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
    848834           
    849835     END LOOP; 
     
    10611047 
    10621048  v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' || 
    1063            's.rollup_time, d.count_rows, d.avg_value, ' || 
    1064            'd.min_value, d.max_value ' || 
     1049           's.rollup_time, d.count_rows, d.avg_value ' || 
    10651050           ' from ' || 
    10661051           '(select ' || quote_literal(v_start_adj) || '::timestamp' || 
     
    10851070        v_r_rollup_row.avg_value := 
    10861071          (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval; 
    1087         v_r_rollup_row.min_value := 
    1088           (v_rollup_row.min_value - v_l_rollup_row.min_value) / v_interval; 
    1089         v_r_rollup_row.max_value := 
    1090           (v_rollup_row.max_value - v_l_rollup_row.max_value) / v_interval; 
    10911072      else 
    10921073        v_r_rollup_row.count_rows = NULL; 
    10931074        v_r_rollup_row.avg_value = NULL; 
    1094         v_r_rollup_row.min_value = NULL; 
    1095         v_r_rollup_row.max_value = NULL; 
     1075         
    10961076      end if; 
    10971077    else