298 | | v_sql:= 'INSERT INTO stratcon.rollup_matrix_numeric_5m'|| |
---|
299 | | '(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES '|| |
---|
300 | | '('||rec.sid||', '||quote_literal(rec.name)||', '||quote_literal(rec.rollup_time)||', '||rec.count_rows||', '||rec.avg_value||', '||coalesce(rec.stddev_value,0)|| |
---|
301 | | ', '||rec.min_value||', '||rec.max_value||')'; |
---|
302 | | |
---|
303 | | |
---|
304 | | EXECUTE v_sql; |
---|
305 | | |
---|
| 299 | INSERT INTO stratcon.rollup_matrix_numeric_5m |
---|
| 300 | (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES |
---|
| 301 | (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); |
---|
| 302 | |
---|
| 303 | |
---|
319 | | |
---|
320 | | |
---|
321 | | CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m_odd(v_min_whence timestamptz,v_max_rollup_5 timestamptz) |
---|
322 | | RETURNS void |
---|
323 | | AS $$ |
---|
324 | | DECLARE |
---|
325 | | |
---|
326 | | rec stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
327 | | v_sql TEXT; |
---|
328 | | |
---|
329 | | BEGIN |
---|
330 | | |
---|
331 | | DELETE FROM stratcon.rollup_matrix_numeric_5m |
---|
332 | | WHERE rollup_time >= date_trunc('minutes',v_min_whence); |
---|
333 | | |
---|
334 | | FOR rec IN |
---|
335 | | SELECT sid , name, date_trunc('H',whence) + (round(extract('minute' from whence)/5)*5) * '1 minute'::interval as rollup_time, |
---|
336 | | COUNT(1) as count_rows ,AVG(value) as avg_value,STDDEV(value) as stddev_value ,MIN(value) as min_value ,MAX(value) as max_value |
---|
337 | | FROM stratcon.loading_dock_metric_numeric_s |
---|
338 | | WHERE WHENCE >= date_trunc('minutes',v_min_whence) AND WHENCE <= date_trunc('minutes',v_max_rollup_5) |
---|
339 | | GROUP BY rollup_time,sid,name |
---|
340 | | |
---|
341 | | LOOP |
---|
342 | | |
---|
343 | | |
---|
344 | | v_sql:= 'INSERT INTO stratcon.rollup_matrix_numeric_5m'|| |
---|
345 | | '(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES '|| |
---|
346 | | '('||rec.sid||', '||quote_literal(rec.name)||', '||quote_literal(rec.rollup_time)||', '||rec.count_rows||', '||rec.avg_value||', '||coalesce(rec.stddev_value,0)|| |
---|
347 | | ', '||rec.min_value||', '||rec.max_value||')'; |
---|
348 | | |
---|
349 | | |
---|
350 | | EXECUTE v_sql; |
---|
351 | | |
---|
352 | | END LOOP; |
---|
353 | | |
---|
354 | | |
---|
355 | | RETURN; |
---|
356 | | |
---|
357 | | EXCEPTION |
---|
358 | | WHEN RAISE_EXCEPTION THEN |
---|
359 | | RAISE EXCEPTION '%', SQLERRM; |
---|
360 | | WHEN OTHERS THEN |
---|
361 | | RAISE NOTICE '%', SQLERRM; |
---|
362 | | END |
---|
363 | | $$ LANGUAGE plpgsql; |
---|
364 | | |
---|
365 | | |
---|
366 | | |
---|
367 | | |
---|
368 | | |
---|
369 | | |
---|
382 | | SELECT sid , name,date_trunc('hour',rollup_time) as rollup_time,SUM (count_rows) as count_rows ,AVG(avg_value) as avg_value, |
---|
383 | | STDDEV(stddev_value) as stddev_value ,MIN(min_value) as min_value ,MAX(max_value) as max_value |
---|
| 329 | 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, |
---|
| 330 | 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, |
---|
| 331 | MIN(min_value) as min_value ,MAX(max_value) as max_value |
---|
388 | | v_sql:= 'INSERT INTO stratcon.rollup_matrix_numeric_60m'|| |
---|
389 | | '(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES '|| |
---|
390 | | '('||rec.sid||', '||quote_literal(rec.name)||', '||quote_literal(rec.rollup_time)||', '||rec.count_rows||', '||rec.avg_value||', '||coalesce(rec.stddev_value,0)|| |
---|
391 | | ', '||rec.min_value||', '||rec.max_value||')'; |
---|
392 | | |
---|
393 | | EXECUTE v_sql; |
---|
| 336 | |
---|
| 337 | INSERT INTO stratcon.rollup_matrix_numeric_60m |
---|
| 338 | (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES |
---|
| 339 | (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); |
---|
| 340 | |
---|