Changeset 152d473243c4c0e838439fd654a698515e8288fc for sql
 Timestamp:
 05/14/08 15:31:43 (10 years ago)
 gitparent:
 Files:

 sql/schema.sql (modified) (18 diffs)
Legend:
 Unmodified
 Added
 Removed
 Modified
 Copied
 Moved
sql/schema.sql
r5de3ee0 r152d473 34 34 ); 35 35 36 37 CREATE TABLE stratcon.loading_dock_metric_text_s ( 38 sid integer NOT NULL, 39 whence timestamp NOT NULL, 40 name text NOT NULL, 41 value text, 42 PRIMARY KEY(whence,sid,name) 43 ); 44 45 CREATE TABLE stratcon.loading_dock_metric_text_s_change_log ( 46 sid integer NOT NULL, 47 whence timestamp NOT NULL, 48 name text NOT NULL, 49 value text, 50 PRIMARY KEY(whence,sid,name) 51 ); 52 36 53 CREATE TABLE stratcon.loading_dock_metric_numeric_s ( 37 54 sid integer NOT NULL, … … 42 59 ); 43 60 44 CREATE TABLE stratcon. loading_dock_metric_text_s(61 CREATE TABLE stratcon.rollup_matrix_numeric_5m ( 45 62 sid integer NOT NULL, 46 whence timestamp NOT NULL,47 63 name text NOT NULL, 48 value text, 49 PRIMARY KEY(whence,sid,name) 50 ); 51 52 CREATE TABLE stratcon.loading_dock_metric_text_s_change_log ( 64 rollup_time timestamp NOT NULL, 65 count_rows integer, 66 avg_value numeric, 67 min_value numeric, 68 max_value numeric, 69 PRIMARY KEY (rollup_time,sid,name) 70 ); 71 72 CREATE TABLE stratcon.rollup_matrix_numeric_20m ( 53 73 sid integer NOT NULL, 54 whence timestamp NOT NULL,55 74 name text NOT NULL, 56 value text, 57 PRIMARY KEY(whence,sid,name) 58 ); 59 75 rollup_time timestamp NOT NULL, 76 count_rows integer, 77 avg_value numeric, 78 min_value numeric, 79 max_value numeric, 80 PRIMARY KEY (rollup_time,sid,name) 81 ); 60 82 61 83 CREATE TABLE stratcon.rollup_matrix_numeric_60m( … … 65 87 count_rows integer, 66 88 avg_value numeric , 67 stddev_value numeric,68 89 min_value numeric , 69 90 max_value numeric , … … 76 97 count_rows integer, 77 98 avg_value numeric , 78 stddev_value numeric,79 99 min_value numeric , 80 100 max_value numeric , … … 87 107 count_rows integer, 88 108 avg_value numeric , 89 stddev_value numeric,90 109 min_value numeric , 91 110 max_value numeric , 92 111 PRIMARY KEY(rollup_time12,sid,name)); 93 112 94 CREATE TABLE stratcon.rollup_matrix_numeric_5m (95 sid integer NOT NULL,96 name text NOT NULL,97 rollup_time timestamp NOT NULL,98 count_rows integer,99 avg_value numeric,100 stddev_value numeric,101 min_value numeric,102 max_value numeric,103 PRIMARY KEY (rollup_time,sid,name)104 );105 106 CREATE TABLE stratcon.rollup_matrix_numeric_20m (107 sid integer NOT NULL,108 name text NOT NULL,109 rollup_time timestamp NOT NULL,110 count_rows integer,111 avg_value numeric,112 stddev_value numeric,113 min_value numeric,114 max_value numeric,115 PRIMARY KEY (rollup_time,sid,name)116 );117 118 113 CREATE TABLE stratcon.map_uuid_to_sid ( 119 114 id uuid NOT NULL, … … 375 370 FOR rec IN 376 371 SELECT sid , name,v_min_whence as rollup_time, 377 COUNT(1) as count_rows ,AVG(value) as avg_value, STDDEV(value) as stddev_value ,MIN(value) as min_value ,MAX(value) as max_value372 COUNT(1) as count_rows ,AVG(value) as avg_value,MIN(value) as min_value ,MAX(value) as max_value 378 373 FROM stratcon.loading_dock_metric_numeric_s 379 374 WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence '5 minutes'::interval … … 384 379 385 380 INSERT INTO stratcon.rollup_matrix_numeric_5m 386 (sid,name,rollup_time,count_rows,avg_value, stddev_value,min_value,max_value) VALUES387 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec. stddev_value,rec.min_value,rec.max_value);381 (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 382 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 388 383 389 384 END LOOP; … … 472 467 SELECT sid , name,v_min_whence as rollup_time, 473 468 SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 474 stddev(stddev_value) as stddev_value,475 469 MIN(min_value) as min_value ,MAX(max_value) as max_value 476 470 FROM stratcon.rollup_matrix_numeric_5m … … 482 476 483 477 INSERT INTO stratcon.rollup_matrix_numeric_20m 484 (sid,name,rollup_time,count_rows,avg_value, stddev_value,min_value,max_value) VALUES485 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec. stddev_value,rec.min_value,rec.max_value);478 (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 479 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 486 480 487 481 END LOOP; … … 569 563 FOR rec IN 570 564 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, 571 stddev(stddev_value) as stddev_value,572 565 MIN(min_value) as min_value ,MAX(max_value) as max_value 573 566 FROM stratcon.rollup_matrix_numeric_20m … … 577 570 578 571 INSERT INTO stratcon.rollup_matrix_numeric_60m 579 (sid,name,rollup_time,count_rows,avg_value, stddev_value,min_value,max_value) VALUES580 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec. stddev_value,rec.min_value,rec.max_value);572 (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 573 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 581 574 582 575 END LOOP; … … 662 655 FOR rec IN 663 656 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, 664 STDDEV(stddev_value) as stddev_value,665 657 MIN(min_value) as min_value ,MAX(max_value) as max_value 666 658 FROM stratcon.rollup_matrix_numeric_60m … … 671 663 672 664 INSERT INTO stratcon.rollup_matrix_numeric_6hours 673 (sid,name,rollup_time,count_rows,avg_value, stddev_value,min_value,max_value) VALUES674 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec. stddev_value,rec.min_value,rec.max_value);665 (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 666 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 675 667 676 668 END LOOP; … … 757 749 FOR rec IN 758 750 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, 759 STDDEV(stddev_value) as stddev_value,760 751 MIN(min_value) as min_value ,MAX(max_value) as max_value 761 752 FROM stratcon.rollup_matrix_numeric_6hours … … 766 757 767 758 INSERT INTO stratcon.rollup_matrix_numeric_12hours 768 (sid,name,rollup_time,count_rows,avg_value, stddev_value,min_value,max_value) VALUES769 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec. stddev_value,rec.min_value,rec.max_value);759 (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 760 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 770 761 771 762 END LOOP; … … 980 971 v_sql := 'select '  v_sid  ' as sid, '  quote_literal(in_name)  ' as name, '  981 972 's.rollup_time, d.count_rows, d.avg_value, '  982 'd. stddev_value, d.min_value, d.max_value ' 973 'd.min_value, d.max_value '  983 974 ' from '  984 975 '(select '  quote_literal(v_start_adj)  '::timestamp'  … … 1003 994 v_r_rollup_row.avg_value := 1004 995 (v_rollup_row.avg_value  v_l_rollup_row.avg_value) / v_interval; 1005 v_r_rollup_row.stddev_value :=1006 (v_rollup_row.stddev_value  v_l_rollup_row.stddev_value) / v_interval;1007 996 v_r_rollup_row.min_value := 1008 997 (v_rollup_row.min_value  v_l_rollup_row.min_value) / v_interval; … … 1012 1001 v_r_rollup_row.count_rows = NULL; 1013 1002 v_r_rollup_row.avg_value = NULL; 1014 v_r_rollup_row.stddev_value = NULL;1015 1003 v_r_rollup_row.min_value = NULL; 1016 1004 v_r_rollup_row.max_value = NULL;