Show
Ignore:
Timestamp:
05/14/08 15:31:43 (6 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1210779103 +0000
git-parent:

[156a0c5971b4b5b55eca5ff8c6c3f2e835ca71ac]

git-author:
Denish Patel <denish@omniti.com> 1210779103 +0000
Message:

stddev_value removed.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/schema.sql

    r5de3ee0 r152d473  
    3434); 
    3535 
     36 
     37CREATE TABLE stratcon.loading_dock_metric_text_s ( 
     38    sid integer NOT NULL, 
     39    whence timestamp NOT NULL, 
     40    name text NOT NULL, 
     41    value text, 
     42    PRIMARY KEY(whence,sid,name) 
     43); 
     44 
     45CREATE TABLE stratcon.loading_dock_metric_text_s_change_log ( 
     46    sid integer NOT NULL, 
     47    whence timestamp NOT NULL, 
     48    name text NOT NULL, 
     49    value text, 
     50    PRIMARY KEY(whence,sid,name) 
     51); 
     52 
    3653CREATE TABLE stratcon.loading_dock_metric_numeric_s ( 
    3754    sid integer NOT NULL, 
     
    4259); 
    4360 
    44 CREATE TABLE stratcon.loading_dock_metric_text_s
     61CREATE TABLE stratcon.rollup_matrix_numeric_5m
    4562    sid integer NOT NULL, 
    46     whence timestamp NOT NULL, 
    4763    name text NOT NULL, 
    48     value text, 
    49     PRIMARY KEY(whence,sid,name) 
    50 ); 
    51  
    52 CREATE TABLE stratcon.loading_dock_metric_text_s_change_log ( 
     64    rollup_time timestamp NOT NULL, 
     65    count_rows integer, 
     66    avg_value numeric, 
     67    min_value numeric, 
     68    max_value numeric, 
     69    PRIMARY KEY (rollup_time,sid,name) 
     70); 
     71 
     72CREATE TABLE stratcon.rollup_matrix_numeric_20m ( 
    5373    sid integer NOT NULL, 
    54     whence timestamp NOT NULL, 
    5574    name text NOT NULL, 
    56     value text, 
    57     PRIMARY KEY(whence,sid,name) 
    58 ); 
    59  
     75    rollup_time timestamp NOT NULL, 
     76    count_rows integer, 
     77    avg_value numeric, 
     78    min_value numeric, 
     79    max_value numeric, 
     80    PRIMARY KEY (rollup_time,sid,name) 
     81); 
    6082 
    6183CREATE TABLE stratcon.rollup_matrix_numeric_60m( 
     
    6587   count_rows integer, 
    6688   avg_value numeric , 
    67    stddev_value numeric, 
    6889   min_value numeric , 
    6990   max_value numeric , 
     
    7697   count_rows integer, 
    7798   avg_value numeric , 
    78    stddev_value numeric, 
    7999   min_value numeric , 
    80100   max_value numeric , 
     
    87107   count_rows integer, 
    88108   avg_value numeric , 
    89    stddev_value numeric, 
    90109   min_value numeric , 
    91110   max_value numeric , 
    92111   PRIMARY KEY(rollup_time12,sid,name));       
    93112  
    94 CREATE TABLE stratcon.rollup_matrix_numeric_5m ( 
    95     sid integer NOT NULL, 
    96     name text NOT NULL, 
    97     rollup_time timestamp NOT NULL, 
    98     count_rows integer, 
    99     avg_value numeric, 
    100     stddev_value numeric, 
    101     min_value numeric, 
    102     max_value numeric, 
    103     PRIMARY KEY (rollup_time,sid,name) 
    104 ); 
    105  
    106 CREATE TABLE stratcon.rollup_matrix_numeric_20m ( 
    107     sid integer NOT NULL, 
    108     name text NOT NULL, 
    109     rollup_time timestamp NOT NULL, 
    110     count_rows integer, 
    111     avg_value numeric, 
    112     stddev_value numeric, 
    113     min_value numeric, 
    114     max_value numeric, 
    115     PRIMARY KEY (rollup_time,sid,name) 
    116 ); 
    117  
    118113CREATE TABLE stratcon.map_uuid_to_sid ( 
    119114    id uuid NOT NULL, 
     
    375370 FOR rec IN  
    376371                SELECT sid , name,v_min_whence as rollup_time, 
    377                       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 
     372                      COUNT(1) as count_rows ,AVG(value) as avg_value,MIN(value) as min_value ,MAX(value) as max_value 
    378373                      FROM stratcon.loading_dock_metric_numeric_s 
    379374                      WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval 
     
    384379         
    385380        INSERT INTO stratcon.rollup_matrix_numeric_5m 
    386          (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES  
    387          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     381         (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES  
     382         (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
    388383         
    389384   END LOOP; 
     
    472467                SELECT sid , name,v_min_whence as rollup_time, 
    473468                       SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    474                        stddev(stddev_value) as stddev_value, 
    475469                       MIN(min_value) as min_value ,MAX(max_value) as max_value 
    476470                       FROM stratcon.rollup_matrix_numeric_5m 
     
    482476         
    483477        INSERT INTO stratcon.rollup_matrix_numeric_20m 
    484          (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES  
    485          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     478         (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES  
     479         (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
    486480         
    487481   END LOOP; 
     
    569563    FOR rec IN  
    570564                SELECT sid,name,date_trunc('hour',rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    571                          stddev(stddev_value) as stddev_value, 
    572565                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
    573566                         FROM stratcon.rollup_matrix_numeric_20m 
     
    577570       
    578571          INSERT INTO stratcon.rollup_matrix_numeric_60m 
    579           (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 
    580           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     572          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 
     573          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
    581574           
    582575     END LOOP; 
     
    662655    FOR rec IN  
    663656                SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    664                          STDDEV(stddev_value) as  stddev_value, 
    665657                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
    666658                         FROM stratcon.rollup_matrix_numeric_60m 
     
    671663        
    672664          INSERT INTO stratcon.rollup_matrix_numeric_6hours 
    673           (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 
    674           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     665          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 
     666          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
    675667           
    676668     END LOOP; 
     
    757749    FOR rec IN  
    758750                SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
    759                          STDDEV(stddev_value) as stddev_value, 
    760751                         MIN(min_value) as min_value ,MAX(max_value) as max_value 
    761752                         FROM stratcon.rollup_matrix_numeric_6hours 
     
    766757        
    767758          INSERT INTO stratcon.rollup_matrix_numeric_12hours 
    768           (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES 
    769           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value); 
     759          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES 
     760          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value); 
    770761           
    771762     END LOOP; 
     
    980971  v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' || 
    981972           's.rollup_time, d.count_rows, d.avg_value, ' || 
    982            'd.stddev_value, d.min_value, d.max_value ' || 
     973           'd.min_value, d.max_value ' || 
    983974           ' from ' || 
    984975           '(select ' || quote_literal(v_start_adj) || '::timestamp' || 
     
    1003994        v_r_rollup_row.avg_value := 
    1004995          (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval; 
    1005         v_r_rollup_row.stddev_value := 
    1006           (v_rollup_row.stddev_value - v_l_rollup_row.stddev_value) / v_interval; 
    1007996        v_r_rollup_row.min_value := 
    1008997          (v_rollup_row.min_value - v_l_rollup_row.min_value) / v_interval; 
     
    10121001        v_r_rollup_row.count_rows = NULL; 
    10131002        v_r_rollup_row.avg_value = NULL; 
    1014         v_r_rollup_row.stddev_value = NULL; 
    10151003        v_r_rollup_row.min_value = NULL; 
    10161004        v_r_rollup_row.max_value = NULL;