| 356 | -- 6 hours |
---|
| 357 | |
---|
| 358 | CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_6hours() |
---|
| 359 | RETURNS void |
---|
| 360 | AS $$ |
---|
| 361 | DECLARE |
---|
| 362 | rec stratcon.rollup_matrix_numeric_6hours%rowtype; |
---|
| 363 | v_sql TEXT; |
---|
| 364 | v_min_whence TIMESTAMPTZ; |
---|
| 365 | v_max_rollup_6 TIMESTAMPTZ; |
---|
| 366 | v_whence TIMESTAMPTZ; |
---|
| 367 | |
---|
| 368 | BEGIN |
---|
| 369 | |
---|
| 370 | SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours' |
---|
| 371 | INTO v_min_whence; |
---|
| 372 | |
---|
| 373 | SELECT max(date_trunc('H',rollup_time6)) FROM stratcon.rollup_matrix_numeric_6hours |
---|
| 374 | INTO v_max_rollup_6; |
---|
| 375 | |
---|
| 376 | -- Insert Log for 12 Hours rollup |
---|
| 377 | |
---|
| 378 | SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='12 hours' |
---|
| 379 | INTO v_whence; |
---|
| 380 | IF NOT FOUND THEN |
---|
| 381 | INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'12 hours'); |
---|
| 382 | END IF; |
---|
| 383 | |
---|
| 384 | |
---|
| 385 | IF v_min_whence <= v_max_rollup_6 THEN |
---|
| 386 | |
---|
| 387 | DELETE FROM stratcon.rollup_matrix_numeric_6hours |
---|
| 388 | WHERE rollup_time6= v_min_whence; |
---|
| 389 | |
---|
| 390 | END IF; |
---|
| 391 | |
---|
| 392 | FOR rec IN |
---|
| 393 | SELECT sid,name,v_min_whence as rollup_time6,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, |
---|
| 394 | SQRT((SUM((count_rows-1)*(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, |
---|
| 395 | MIN(min_value) as min_value ,MAX(max_value) as max_value |
---|
| 396 | FROM stratcon.rollup_matrix_numeric_60m |
---|
| 397 | WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval |
---|
| 398 | GROUP BY sid,name |
---|
| 399 | LOOP |
---|
| 400 | |
---|
| 401 | |
---|
| 402 | INSERT INTO stratcon.rollup_matrix_numeric_6hours |
---|
| 403 | (sid,name,rollup_time6,count_rows,avg_value,stddev_value,min_value,max_value) VALUES |
---|
| 404 | (rec.sid,rec.name,rec.rollup_time6,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); |
---|
| 405 | |
---|
| 406 | END LOOP; |
---|
| 407 | |
---|
| 408 | |
---|
| 409 | DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours'; |
---|
| 410 | |
---|
| 411 | RETURN; |
---|
| 412 | |
---|
| 413 | EXCEPTION |
---|
| 414 | WHEN RAISE_EXCEPTION THEN |
---|
| 415 | RAISE EXCEPTION '%', SQLERRM; |
---|
| 416 | WHEN OTHERS THEN |
---|
| 417 | RAISE NOTICE '%', SQLERRM; |
---|
| 418 | END |
---|
| 419 | $$ LANGUAGE plpgsql; |
---|
| 420 | |
---|
| 421 | |
---|
| 422 | -- 12 hours |
---|
| 423 | |
---|
| 424 | CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_12hours() |
---|
| 425 | RETURNS void |
---|
| 426 | AS $$ |
---|
| 427 | DECLARE |
---|
| 428 | rec stratcon.rollup_matrix_numeric_12hours%rowtype; |
---|
| 429 | v_sql TEXT; |
---|
| 430 | v_min_whence TIMESTAMPTZ; |
---|
| 431 | v_max_rollup_12 TIMESTAMPTZ; |
---|
| 432 | v_whence TIMESTAMPTZ; |
---|
| 433 | |
---|
| 434 | BEGIN |
---|
| 435 | |
---|
| 436 | SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours' |
---|
| 437 | INTO v_min_whence; |
---|
| 438 | |
---|
| 439 | SELECT max(date_trunc('H',rollup_time12)) FROM stratcon.rollup_matrix_numeric_12hours |
---|
| 440 | INTO v_max_rollup_12; |
---|
| 441 | |
---|
| 442 | /*-- Insert Log for 24 Hours rollup |
---|
| 443 | |
---|
| 444 | SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='24 hours' |
---|
| 445 | INTO v_whence; |
---|
| 446 | IF NOT FOUND THEN |
---|
| 447 | INSERT INTO stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'24 hours'); |
---|
| 448 | END IF; |
---|
| 449 | */ |
---|
| 450 | |
---|
| 451 | IF v_min_whence <= v_max_rollup_12 THEN |
---|
| 452 | |
---|
| 453 | DELETE FROM stratcon.rollup_matrix_numeric_12hours |
---|
| 454 | WHERE rollup_time12= v_min_whence; |
---|
| 455 | |
---|
| 456 | END IF; |
---|
| 457 | |
---|
| 458 | FOR rec IN |
---|
| 459 | SELECT sid,name,v_min_whence as rollup_time12,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, |
---|
| 460 | SQRT((SUM((count_rows-1)*(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, |
---|
| 461 | MIN(min_value) as min_value ,MAX(max_value) as max_value |
---|
| 462 | FROM stratcon.rollup_matrix_numeric_6hours |
---|
| 463 | WHERE rollup_time6<= v_min_whence and rollup_time6> v_min_whence-'12 hour'::interval |
---|
| 464 | GROUP BY sid,name |
---|
| 465 | LOOP |
---|
| 466 | |
---|
| 467 | |
---|
| 468 | INSERT INTO stratcon.rollup_matrix_numeric_12hours |
---|
| 469 | (sid,name,rollup_time12,count_rows,avg_value,stddev_value,min_value,max_value) VALUES |
---|
| 470 | (rec.sid,rec.name,rec.rollup_time12,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); |
---|
| 471 | |
---|
| 472 | END LOOP; |
---|
| 473 | |
---|
| 474 | |
---|
| 475 | DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours'; |
---|
| 476 | |
---|
| 477 | RETURN; |
---|
| 478 | |
---|
| 479 | EXCEPTION |
---|
| 480 | WHEN RAISE_EXCEPTION THEN |
---|
| 481 | RAISE EXCEPTION '%', SQLERRM; |
---|
| 482 | WHEN OTHERS THEN |
---|
| 483 | RAISE NOTICE '%', SQLERRM; |
---|
| 484 | END |
---|
| 485 | $$ LANGUAGE plpgsql; |
---|
| 486 | |
---|