Changeset 03c905446907610b20f89a64a0d2810d134e52ec for sql

Timestamp:

03/26/08 22:02:44 (5 years ago)

Author:

Denish Patel <denish@omniti.com>

git-committer:

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

git-parent:

[bce2dd5b1ebb5d3628562c5f487797d4d7da1b46]

git-author:

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

Message:

updated functions for rollups

Files:

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

sql/schema.sql

@@ -48 +48 @@
-);
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
-    sid integer NOT NULL,
-    name text NOT NULL,
@@ -89 +101 @@
-   v_ex_sid integer;
-   v_new_sid integer;
-  v_sql text;
+
-BEGIN
-
@@ -99 +111 @@
-    INTO v_new_sid;
-
-
-
+
-   
-    RETURN v_new_sid;
@@ -176 +187 @@
-
-
+
+-- Generic rollup function (under progress)
+
+
+
+CREATE OR REPLACE FUNCTION stratcon.generic_rollup_metrix_numeric()
+RETURNS void
+AS $$
+
+DECLARE
+
+v_min_whence TIMESTAMPTZ;
+v_max_rollup_5 TIMESTAMPTZ;
+v_max_rollup_60 TIMESTAMPTZ;
+
+BEGIN
+
+  select min(whence) from stratcon.log_whence_s 
+         INTO v_min_whence;
+         
+  select max(rollup_time) from  stratcon.rollup_matrix_numeric_5m 
+         INTO v_max_rollup_5;         
+ 
+  select max(rollup_time) from  stratcon.rollup_matrix_numeric_60m 
+         INTO v_max_rollup_60; 
+         
+ IF v_max_rollup_5 IS NULL  THEN
+   v_max_rollup_5:=timestamp '2008-01-01 00:00:00';
+ END IF;
+ 
+ IF v_max_rollup_60 IS NULL  THEN
+    v_max_rollup_60:=timestamp '2008-01-01 00:00:00';
+  END IF;
+ 
+         
+  IF v_min_whence > v_max_rollup_5 THEN
+  
+  -- 5 MINUTES ROLLUP
+  
+     PERFORM stratcon.rollup_matrix_numeric_5m(v_min_whence);
+     
+     -- HOURLY ROLLUP
+     
+     IF  date_trunc('H',v_min_whence)!= date_trunc('H',v_max_rollup_60) THEN
+     
+       PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence);
+     
+     END IF;
+  
+  -- DELETE FROM LOG TABLE
+  
+   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence;
+  
+  ELSIF v_min_whence < v_max_rollup_5 THEN
+  
+   -- 5 MINUTES ROLLUP
+
+     PERFORM stratcon.rollup_matrix_numeric_5m_odd(v_min_whence ,v_max_rollup_5);
+     
+  -- HOURLY ROLLUP
+   
+   DELETE FROM stratcon.rollup_matrix_numeric_60m 
+       WHERE date_trunc('hour',rollup_time) = date_trunc('hour',v_min_whence);
+       
+         PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence);
+         
+  -- DELETE FROM LOG TABLE
+
+      DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence;
+      
+  ELSE
+  
+      RETURN;
+ 
+  END IF;
+ 
+RETURN;
+
+EXCEPTION
+    WHEN RAISE_EXCEPTION THEN
+       RAISE EXCEPTION '%', SQLERRM;
+    WHEN OTHERS THEN
+      RAISE NOTICE '%', SQLERRM;
+END
+$$ LANGUAGE plpgsql;
+
+
+--- 5 minutes rollup 
+
+
+CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m(v_min_whence timestamptz)
+RETURNS voidddddd
+AS $$
+DECLARE
+ 
+ rec stratcon.rollup_matrix_numeric_5m%rowtype;
+ v_sql TEXT;
+ 
+BEGIN
+
+ 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_min_whence)-'5 minutes'::interval
+                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;
+
+
+
+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
+
+
+CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m(v_min_whence timestamptz)
+RETURNS void
+AS $$
+DECLARE
+  rec stratcon.rollup_matrix_numeric_60m%rowtype;
+ v_sql TEXT;
+ 
+BEGIN
+    FOR rec IN 
+                SELECT sid , name,date_trunc('hour',rollup_time) as rollup_time,SUM (count_rows) as count_rows ,AVG(avg_value) as avg_value,
+                         STDDEV(stddev_value) as stddev_value ,MIN(min_value) as min_value ,MAX(max_value) as max_value
+                         FROM stratcon.rollup_matrix_numeric_5m
+                           WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence)
+                   GROUP BY rollup_time,sid,name
+        LOOP
+        v_sql:= 'INSERT INTO stratcon.rollup_matrix_numeric_60m'||                        
+        '(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;
+
+
--- GRANTS 
-
@@ -186 +414 @@
- GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric_s TO stratcon;
- GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s_change_log TO stratcon;
-UPDA
+DELE
- GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s TO stratcon;
-
+
+
- GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon;
- ALTER TABLE stratcon.seq_sid OWNER TO stratcon;