Changeset d67a38b6a56900457dc690b2dd62cbd081dca6aa for sql
 Timestamp:
 04/16/08 21:05:40 (10 years ago)
 gitparent:
 Files:

 sql/schema.sql (modified) (9 diffs)
Legend:
 Unmodified
 Added
 Removed
 Modified
 Copied
 Moved
sql/schema.sql
r8894bda rd67a38b 63 63 sid integer not null, 64 64 name text not null, 65 rollup_time 6timestamp not null,65 rollup_time timestamp not null, 66 66 count_rows integer, 67 67 avg_value numeric , … … 74 74 sid integer not null, 75 75 name text not null, 76 rollup_time 12timestamp not null,76 rollup_time timestamp not null, 77 77 count_rows integer, 78 78 avg_value numeric , … … 327 327 FOR rec IN 328 328 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, 329 SQRT((SUM((count_rows1)*(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,329 stddev(stddev_value) as stddev_value, 330 330 MIN(min_value) as min_value ,MAX(max_value) as max_value 331 331 FROM stratcon.rollup_matrix_numeric_5m … … 371 371 INTO v_min_whence; 372 372 373 SELECT max(date_trunc('H',rollup_time 6)) FROM stratcon.rollup_matrix_numeric_6hours373 SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_6hours 374 374 INTO v_max_rollup_6; 375 375 … … 386 386 387 387 DELETE FROM stratcon.rollup_matrix_numeric_6hours 388 WHERE rollup_time 6= v_min_whence;388 WHERE rollup_time= v_min_whence; 389 389 390 390 END IF; 391 391 392 392 FOR rec IN 393 SELECT sid,name,v_min_whence as rollup_time 6,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value,394 S QRT((SUM((count_rows1)*(POWER(stddev_value,2)+POWER(avg_value,2)))/(SUM(count_rows)1)))(power(SUM(avg_value*count_rows)/SUM(count_rows),2)) asstddev_value,393 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, 394 STDDEV(stddev_value) as stddev_value, 395 395 MIN(min_value) as min_value ,MAX(max_value) as max_value 396 396 FROM stratcon.rollup_matrix_numeric_60m … … 401 401 402 402 INSERT INTO stratcon.rollup_matrix_numeric_6hours 403 (sid,name,rollup_time 6,count_rows,avg_value,stddev_value,min_value,max_value) VALUES404 (rec.sid,rec.name,rec.rollup_time 6,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value);403 (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 404 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 405 405 406 406 END LOOP; … … 437 437 INTO v_min_whence; 438 438 439 SELECT max(date_trunc('H',rollup_time 12)) FROM stratcon.rollup_matrix_numeric_12hours439 SELECT max(date_trunc('H',rollup_time)) FROM stratcon.rollup_matrix_numeric_12hours 440 440 INTO v_max_rollup_12; 441 441 … … 452 452 453 453 DELETE FROM stratcon.rollup_matrix_numeric_12hours 454 WHERE rollup_time 12= v_min_whence;454 WHERE rollup_time= v_min_whence; 455 455 456 456 END IF; 457 457 458 458 FOR rec IN 459 SELECT sid,name,v_min_whence as rollup_time 12,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value,460 S QRT((SUM((count_rows1)*(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,459 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, 460 STDDEV(stddev_value) as stddev_value, 461 461 MIN(min_value) as min_value ,MAX(max_value) as max_value 462 462 FROM stratcon.rollup_matrix_numeric_6hours 463 WHERE rollup_time 6<= v_min_whence and rollup_time6> v_min_whence'12 hour'::interval463 WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence'12 hour'::interval 464 464 GROUP BY sid,name 465 465 LOOP … … 467 467 468 468 INSERT INTO stratcon.rollup_matrix_numeric_12hours 469 (sid,name,rollup_time 12,count_rows,avg_value,stddev_value,min_value,max_value) VALUES470 (rec.sid,rec.name,rec.rollup_time 12,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value);469 (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 470 (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 471 471 472 472 END LOOP;