Changeset 0dbb5eb9b2c9f102370e5d34d527dde28d3588d4

Timestamp:

03/27/08 20:05:16 (5 years ago)

Author:

Denish Patel <denish@omniti.com>

git-committer:

Denish Patel <denish@omniti.com> 1206648316 +0000

git-parent:

[03c905446907610b20f89a64a0d2810d134e52ec]

git-author:

Denish Patel <denish@omniti.com> 1206648316 +0000

Message:

updated functions for rollups

Files:

• sql/schema.sql (modified) (7 diffs)

sql/schema.sql

@@ -222 +222 @@
- 
-         
-
+=
-  
-  -- 5 MINUTES ROLLUP
@@ -244 +244 @@
-   -- 5 MINUTES ROLLUP
-
-
+
+
+
+
-     
-  -- HOURLY ROLLUP
@@ -273 +276 @@
-$$ LANGUAGE plpgsql;
-
-
---- 5 minutes rollup 
-
-
-CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m(v_min_whence timestamptz)
-ddddd
+
-AS $$
-DECLARE
@@ -296 +297 @@
-       LOOP
- 
-
-
-
-
-
-
-
-
+
+
+
+
+
- END LOOP;
-
@@ -317 +315 @@
-$$ LANGUAGE plpgsql;
-
-
-
-CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m_odd(v_min_whence timestamptz,v_max_rollup_5 timestamptz)
-RETURNS void
-AS $$
-DECLARE
- 
- rec stratcon.rollup_matrix_numeric_5m%rowtype;
- v_sql TEXT;
- 
-BEGIN
-
- DELETE FROM stratcon.rollup_matrix_numeric_5m 
- WHERE rollup_time >= date_trunc('minutes',v_min_whence);
- 
- FOR rec IN 
-                 SELECT sid , name, date_trunc('H',whence) + (round(extract('minute' from whence)/5)*5) * '1 minute'::interval as rollup_time,
-                       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
-                       FROM stratcon.loading_dock_metric_numeric_s
-                       WHERE WHENCE >= date_trunc('minutes',v_min_whence) AND WHENCE <= date_trunc('minutes',v_max_rollup_5)
-                 GROUP BY rollup_time,sid,name
-  
-        LOOP
- 
-    
-         v_sql:= 'INSERT INTO stratcon.rollup_matrix_numeric_5m'||                        
-         '(sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES '||
-         '('||rec.sid||', '||quote_literal(rec.name)||', '||quote_literal(rec.rollup_time)||', '||rec.count_rows||', '||rec.avg_value||', '||coalesce(rec.stddev_value,0)||
-         ', '||rec.min_value||', '||rec.max_value||')';
- 
- 
-           EXECUTE v_sql;
- 
-  END LOOP;
- 
-
-RETURN;
-
-EXCEPTION
-    WHEN RAISE_EXCEPTION THEN
-       RAISE EXCEPTION '%', SQLERRM;
-    WHEN OTHERS THEN
-      RAISE NOTICE '%', SQLERRM;
-END
-$$ LANGUAGE plpgsql;
-
-
-
-
-
-
---- Hourly rollup
-
@@ -380 +327 @@
-BEGIN
-    FOR rec IN 
-
-
+
+
+
-                         FROM stratcon.rollup_matrix_numeric_5m
-                           WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence)
-rollup_time
+date_trunc('hour',rollup_time)
-        LOOP
-
-
-
-
-
-
+
+
+
+
+
-     END LOOP;
-RETURN;
@@ -422 +369 @@
-
-COMMIT;
-