root/sql/sprocs/stratcon.window_robust_derive.sql

Revision efff2d97f875ae97b5b8083173c63b45af7e29df, 2.5 kB (checked in by Denish Patel <denish@omniti.com>, 5 years ago)

added stratcon.window_robust_derive

  • Property mode set to 100644
Line 
1 -- Function: stratcon.window_robust_derive(timestamp with time zone)
2
3 CREATE OR REPLACE FUNCTION stratcon.window_robust_derive(in_start_time timestamp with time zone)
4   RETURNS SETOF noit.metric_numeric_rollup_5m AS
5 $BODY$
6 declare
7   rec noit.metric_numeric_rollup_5m%rowtype;
8   r record;
9   rise numeric;
10   last_row_whence timestamp;
11   last_value numeric;
12   run numeric;
13 begin
14
15    rec.sid := null;
16    rec.name := null;
17    rec.count_rows := 0;
18    rec.avg_value := 0;
19    rise := 0;
20    run := 0;
21    rec.rollup_time = in_start_time;
22    for r in SELECT sid, name, whence,
23                    (whence > in_start_time - '5 minutes'::interval) as in_window,
24                    value
25               FROM noit.metric_numeric_archive
26              WHERE whence <= in_start_time
27                AND whence > in_start_time - ('5 minutes'::interval * 2)
28           order BY sid,name,whence
29   loop
30   if (rec.sid is not null and rec.name is not null) and
31      (rec.sid <> r.sid or rec.name <> r.name) then
32      if rec.count_rows > 0 then
33        rec.avg_value := rec.avg_value / rec.count_rows;
34        if run is not null and run > 0 then
35          rec.counter_dev := rise/run;
36        end if;
37        return next rec;
38      end if;
39      rec.avg_value := 0;
40      rec.count_rows := 0;
41      rec.counter_dev := null;
42      rise := 0;
43      run := 0;
44      last_value := null;
45      last_row_whence := null;
46   end if;
47   rec.sid := r.sid;
48   rec.name := r.name;
49   if r.in_window then
50     if r.value is not null then
51       rec.count_rows := rec.count_rows + 1;
52       rec.avg_value := rec.avg_value + coalesce(r.value,0);
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       end if;
62     end if;
63   end if;
64   if r.value is not null then
65     last_row_whence := r.whence;
66     last_value := r.value;
67   end if;
68   end loop;
69   if rec.count_rows > 0 then
70     rec.avg_value := rec.avg_value / rec.count_rows;
71     if run is not null and run > 0 then
72       rec.counter_dev := rise/run;
73     end if;
74     return next rec;
75   end if;
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.