Changeset 6b9f38ffb400becb8ab312d34abd2981bca9fe87
 Timestamp:
 06/16/08 19:16:10
(10 years ago)
 Author:
 Denish Patel <denish@omniti.com>
 gitcommitter:
 Denish Patel <denish@omniti.com> 1213643770 +0000
 gitparent:
[bbebbfeff0cf986fbdb1cfdc15c07a9ea059dc2a]
 gitauthor:
 Denish Patel <denish@omniti.com> 1213643770 +0000
 Message:
remove min/max values from rollup tables/functions.

Files:

Legend:
 Unmodified
 Added
 Removed
 Modified
 Copied
 Moved
r601c99b 
r6b9f38f 

64  64  rollup_time timestamptz NOT NULL, 

65  65  count_rows integer, 

66   avg_value numeric, 

67   min_value numeric, 

68   max_value numeric, 

 66  avg_value numeric 

69  67  PRIMARY KEY (rollup_time,sid,name) 

70  68  ); 

…  …  
75  73  rollup_time timestamptz NOT NULL, 

76  74  count_rows integer, 

77   avg_value numeric, 

78   min_value numeric, 

79   max_value numeric, 

 75  avg_value numeric 

80  76  PRIMARY KEY (rollup_time,sid,name) 

81  77  ); 

…  …  
96  92  rollup_time timestamptz not null, 

97  93  count_rows integer, 

98   avg_value numeric , 

99   min_value numeric , 

100   max_value numeric , 

 94  avg_value numeric 

101  95  PRIMARY KEY(rollup_time,sid,name)); 

102  96  

…  …  
106  100  rollup_time timestamptz not null, 

107  101  count_rows integer, 

108   avg_value numeric , 

109   min_value numeric , 

110   max_value numeric , 

 102  avg_value numeric 

111  103  PRIMARY KEY(rollup_time6,sid,name)); 

112  104  

…  …  
117  109  count_rows integer, 

118  110  avg_value numeric , 

119   min_value numeric , 

120   max_value numeric , 

121  111  PRIMARY KEY(rollup_time12,sid,name)); 

122  112  

…  …  
434  424  FOR rec IN 

435  425  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 

437  427  FROM stratcon.loading_dock_metric_numeric_s 

438  428  WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence '5 minutes'::interval 

…  …  
443  433  

444  434  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); 

447  437  

448  438  END LOOP; 

…  …  
536  526  FOR rec IN 

537  527  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 

540  529  FROM stratcon.rollup_matrix_numeric_5m 

541  530  WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence '20 minutes'::interval 

…  …  
546  535  

547  536  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); 

550  539  

551  540  END LOOP; 

…  …  
638  627  

639  628  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 

642  630  FROM stratcon.rollup_matrix_numeric_20m 

643  631  WHERE date_hour(rollup_time)= v_min_whence 

…  …  
646  634  

647  635  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); 

650  638  

651  639  END LOOP; 

…  …  
736  724  

737  725  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 

740  727  FROM stratcon.rollup_matrix_numeric_60m 

741  728  WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence'6 hour'::interval 

…  …  
745  732  

746  733  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); 

749  736  

750  737  END LOOP; 

…  …  
835  822  

836  823  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 

839  825  FROM stratcon.rollup_matrix_numeric_6hours 

840  826  WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence'12 hour'::interval 

…  …  
844  830  

845  831  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); 

848  834  

849  835  END LOOP; 

…  …  
1061  1047  

1062  1048  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 '  

1065  1050  ' from '  

1066  1051  '(select '  quote_literal(v_start_adj)  '::timestamp'  

…  …  
1085  1070  v_r_rollup_row.avg_value := 

1086  1071  (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; 

1091  1072  else 

1092  1073  v_r_rollup_row.count_rows = NULL; 

1093  1074  v_r_rollup_row.avg_value = NULL; 

1094   v_r_rollup_row.min_value = NULL; 

1095   v_r_rollup_row.max_value = NULL; 

 1075  

1096  1076  end if; 

1097  1077  else 
