Changeset 6b9f38ffb400becb8ab312d34abd2981bca9fe87 for sql
 Timestamp:
 06/16/08 19:16:10 (7 years ago)
 gitparent:
 Files:

 sql/schema.sql (modified) (17 diffs)
Legend:
 Unmodified
 Added
 Removed
 Modified
 Copied
 Moved
sql/schema.sql
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_value426 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) VALUES446 (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) VALUES549 (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) VALUES649 (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) VALUES748 (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) VALUES847 (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