| 189 | |
---|
| 190 | -- Generic rollup function (under progress) |
---|
| 191 | |
---|
| 192 | |
---|
| 193 | |
---|
| 194 | CREATE OR REPLACE FUNCTION stratcon.generic_rollup_metrix_numeric() |
---|
| 195 | RETURNS void |
---|
| 196 | AS $$ |
---|
| 197 | |
---|
| 198 | DECLARE |
---|
| 199 | |
---|
| 200 | v_min_whence TIMESTAMPTZ; |
---|
| 201 | v_max_rollup_5 TIMESTAMPTZ; |
---|
| 202 | v_max_rollup_60 TIMESTAMPTZ; |
---|
| 203 | |
---|
| 204 | BEGIN |
---|
| 205 | |
---|
| 206 | select min(whence) from stratcon.log_whence_s |
---|
| 207 | INTO v_min_whence; |
---|
| 208 | |
---|
| 209 | select max(rollup_time) from stratcon.rollup_matrix_numeric_5m |
---|
| 210 | INTO v_max_rollup_5; |
---|
| 211 | |
---|
| 212 | select max(rollup_time) from stratcon.rollup_matrix_numeric_60m |
---|
| 213 | INTO v_max_rollup_60; |
---|
| 214 | |
---|
| 215 | IF v_max_rollup_5 IS NULL THEN |
---|
| 216 | v_max_rollup_5:=timestamp '2008-01-01 00:00:00'; |
---|
| 217 | END IF; |
---|
| 218 | |
---|
| 219 | IF v_max_rollup_60 IS NULL THEN |
---|
| 220 | v_max_rollup_60:=timestamp '2008-01-01 00:00:00'; |
---|
| 221 | END IF; |
---|
| 222 | |
---|
| 223 | |
---|
| 224 | IF v_min_whence > v_max_rollup_5 THEN |
---|
| 225 | |
---|
| 226 | -- 5 MINUTES ROLLUP |
---|
| 227 | |
---|
| 228 | PERFORM stratcon.rollup_matrix_numeric_5m(v_min_whence); |
---|
| 229 | |
---|
| 230 | -- HOURLY ROLLUP |
---|
| 231 | |
---|
| 232 | IF date_trunc('H',v_min_whence)!= date_trunc('H',v_max_rollup_60) THEN |
---|
| 233 | |
---|
| 234 | PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence); |
---|
| 235 | |
---|
| 236 | END IF; |
---|
| 237 | |
---|
| 238 | -- DELETE FROM LOG TABLE |
---|
| 239 | |
---|
| 240 | DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence; |
---|
| 241 | |
---|
| 242 | ELSIF v_min_whence < v_max_rollup_5 THEN |
---|
| 243 | |
---|
| 244 | -- 5 MINUTES ROLLUP |
---|
| 245 | |
---|
| 246 | PERFORM stratcon.rollup_matrix_numeric_5m_odd(v_min_whence ,v_max_rollup_5); |
---|
| 247 | |
---|
| 248 | -- HOURLY ROLLUP |
---|
| 249 | |
---|
| 250 | DELETE FROM stratcon.rollup_matrix_numeric_60m |
---|
| 251 | WHERE date_trunc('hour',rollup_time) = date_trunc('hour',v_min_whence); |
---|
| 252 | |
---|
| 253 | PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence); |
---|
| 254 | |
---|
| 255 | -- DELETE FROM LOG TABLE |
---|
| 256 | |
---|
| 257 | DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence; |
---|
| 258 | |
---|
| 259 | ELSE |
---|
| 260 | |
---|
| 261 | RETURN; |
---|
| 262 | |
---|
| 263 | END IF; |
---|
| 264 | |
---|
| 265 | RETURN; |
---|
| 266 | |
---|
| 267 | EXCEPTION |
---|
| 268 | WHEN RAISE_EXCEPTION THEN |
---|
| 269 | RAISE EXCEPTION '%', SQLERRM; |
---|
| 270 | WHEN OTHERS THEN |
---|
| 271 | RAISE NOTICE '%', SQLERRM; |
---|
| 272 | END |
---|
| 273 | $$ LANGUAGE plpgsql; |
---|
| 274 | |
---|
| 275 | |
---|
| 276 | --- 5 minutes rollup |
---|
| 277 | |
---|
| 278 | |
---|
| 279 | CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m(v_min_whence timestamptz) |
---|
| 280 | RETURNS voidddddd |
---|
| 281 | AS $$ |
---|
| 282 | DECLARE |
---|
| 283 | |
---|
| 284 | rec stratcon.rollup_matrix_numeric_5m%rowtype; |
---|
| 285 | v_sql TEXT; |
---|
| 286 | |
---|
| 287 | BEGIN |
---|
| 288 | |
---|
| 289 | FOR rec IN |
---|
| 290 | SELECT sid , name, date_trunc('H',whence) + (round(extract('minute' from whence)/5)*5) * '1 minute'::interval as rollup_time, |
---|
| 291 | 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 |
---|
| 292 | FROM stratcon.loading_dock_metric_numeric_s |
---|
| 293 | WHERE WHENCE < date_trunc('minutes',v_min_whence) AND WHENCE >= date_trunc('minutes',v_min_whence)-'5 minutes'::interval |
---|
| 294 | GROUP BY rollup_time,sid,name |
---|
| 295 | |
---|
| 296 | LOOP |
---|
| 297 | |
---|
| 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 | |
---|
| 306 | END LOOP; |
---|
| 307 | |
---|
| 308 | |
---|
| 309 | RETURN; |
---|
| 310 | |
---|
| 311 | EXCEPTION |
---|
| 312 | WHEN RAISE_EXCEPTION THEN |
---|
| 313 | RAISE EXCEPTION '%', SQLERRM; |
---|
| 314 | WHEN OTHERS THEN |
---|
| 315 | RAISE NOTICE '%', SQLERRM; |
---|
| 316 | END |
---|
| 317 | $$ LANGUAGE plpgsql; |
---|
| 318 | |
---|
| 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 | |
---|
| 370 | --- Hourly rollup |
---|
| 371 | |
---|
| 372 | |
---|
| 373 | CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m(v_min_whence timestamptz) |
---|
| 374 | RETURNS void |
---|
| 375 | AS $$ |
---|
| 376 | DECLARE |
---|
| 377 | rec stratcon.rollup_matrix_numeric_60m%rowtype; |
---|
| 378 | v_sql TEXT; |
---|
| 379 | |
---|
| 380 | BEGIN |
---|
| 381 | FOR rec IN |
---|
| 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 |
---|
| 384 | FROM stratcon.rollup_matrix_numeric_5m |
---|
| 385 | WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence) |
---|
| 386 | GROUP BY rollup_time,sid,name |
---|
| 387 | LOOP |
---|
| 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; |
---|
| 394 | END LOOP; |
---|
| 395 | RETURN; |
---|
| 396 | |
---|
| 397 | EXCEPTION |
---|
| 398 | WHEN RAISE_EXCEPTION THEN |
---|
| 399 | RAISE EXCEPTION '%', SQLERRM; |
---|
| 400 | WHEN OTHERS THEN |
---|
| 401 | RAISE NOTICE '%', SQLERRM; |
---|
| 402 | END |
---|
| 403 | $$ LANGUAGE plpgsql; |
---|
| 404 | |
---|
| 405 | |
---|