root/sql/sprocs/stratcon.running_derive.sql

Revision f70c252583c6c33599e13a35384a6b72c2d0d012, 2.5 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 4 years ago)

accidentally reverted these tables

  • Property mode set to 100644
Line 
1 DROP TYPE IF EXISTS stratcon.three_way_split;
2 CREATE TYPE stratcon.three_way_split AS (sid integer,  name text, rollup_time timestamp with time zone, value numeric, derivative numeric, counter numeric);     
3
4
5 CREATE OR REPLACE FUNCTION stratcon.running_derive(in_start_time timestamp with time zone)
6   RETURNS SETOF stratcon.three_way_split AS
7 $BODY$
8 declare
9   rec stratcon.three_way_split%rowtype;
10   r record;
11   rise numeric;
12   last_row_whence timestamp;
13   last_value numeric;
14   run numeric;
15   v_sql text;
16   v_in_minus_5 timestamptz := in_start_time - '5 minutes'::interval;
17   v_in_minus_10 timestamptz := in_start_time - '10 minutes'::interval;
18
19 begin
20
21    rec.sid := null;
22    rec.name := null;
23    rec.value := 0;
24    rise := 0;
25    run := 0;
26    rec.rollup_time = in_start_time;
27    v_sql := 'SELECT sid, name, whence,
28                    (whence > '||quote_literal(v_in_minus_5)||'::timestamptz) as in_window,
29                    value
30               FROM metric_numeric_archive
31              WHERE whence <= '||quote_literal(in_start_time)||'::timestamptz
32                AND whence > '||quote_literal(v_in_minus_10)||'::timestamptz
33           order BY sid,name,whence
34              ';
35
36   for r in EXECUTE v_sql
37   loop
38   if (rec.sid is not null and rec.name is not null) and
39      (rec.sid <> r.sid or rec.name <> r.name) then
40      rise := 0;
41      run := 0;
42      last_value := null;
43      last_row_whence := null;
44   end if;
45   rec.sid := r.sid;
46   rec.name := r.name;
47   if r.in_window then
48     rec.derivative := null;
49     rec.counter := null;
50     rec.value := null;
51     if r.value is not null then
52       rec.value = r.value;
53       if     last_row_whence is not null
54          and last_value is not null
55          and last_value <= r.value then
56         rise := rise + (r.value - last_value);
57         run := run + ((extract(epoch from r.whence) +
58                        (extract(milliseconds from r.whence)::integer % 1000)/1000.0) -
59                       (extract(epoch from last_row_whence) +
60                        (extract(milliseconds from last_row_whence)::integer % 1000)/1000.0));
61         if run is not null and rise is not null then
62           rec.derivative = rise / run;
63           if rise >= 0 then
64             rec.counter = rise / run;
65           end if;
66         end if;
67       end if;
68     end if;
69     return next rec;
70   end if;
71   if r.value is not null then
72     last_row_whence := r.whence;
73     last_value := r.value;
74   end if;
75   end loop;
76 return;
77 end;
78 $BODY$
79   LANGUAGE 'plpgsql' SECURITY DEFINER;
80  
81 GRANT EXECUTE ON FUNCTION stratcon.window_robust_derive(timestamp with time zone) TO stratcon;
82
Note: See TracBrowser for help on using the browser.