Changeset 7e71217def419c242b081547a970d47a5bf3f20d

Show
Ignore:
Timestamp:
05/09/08 18:03:15 (7 years ago)
Author:
Theo Schlossnagle <jesus@omniti.com>
git-committer:
Theo Schlossnagle <jesus@omniti.com> 1210356195 +0000
git-parent:

[497022e7d61295bb0a4a5576ce5105d229818e83]

git-author:
Theo Schlossnagle <jesus@omniti.com> 1210356195 +0000
Message:

windowed retrieval functions

Files:

Legend:

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

    rd67a38b r7e71217  
    486486 
    487487 
     488create or replace function 
     489stratcon.fetch_varset(in_check uuid, 
     490                       in_name text, 
     491                       in_start_time timestamp, 
     492                       in_end_time timestamp, 
     493                       in_hopeful_nperiods int) 
     494returns setof stratcon.loading_dock_metric_text_s_change_log as 
     495$$ 
     496declare 
     497  v_sid int; 
     498  v_target record; 
     499  v_start_adj timestamp; 
     500  v_start_text text; 
     501  v_next_text text; 
     502  v_end_adj timestamp; 
     503  v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype; 
     504begin 
     505  -- Map out uuid to an sid. 
     506  select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check; 
     507  if not found then 
     508    return; 
     509  end if; 
     510 
     511  select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods); 
     512 
     513  select 'epoch'::timestamp + 
     514         ((floor(extract('epoch' from in_start_time) / 
     515                 extract('epoch' from v_target.period)) * 
     516           extract('epoch' from v_target.period)) || ' seconds') ::interval 
     517    into v_start_adj; 
     518 
     519  select 'epoch'::timestamp + 
     520         ((floor(extract('epoch' from in_end_time) / 
     521                 extract('epoch' from v_target.period)) * 
     522           extract('epoch' from v_target.period)) || ' seconds') ::interval 
     523    into v_end_adj; 
     524 
     525  for v_change_row in 
     526    select sid, 'epoch'::timestamp + 
     527         ((floor(extract('epoch' from whence) / 
     528                 extract('epoch' from v_target.period)) * 
     529           extract('epoch' from v_target.period)) || ' seconds') ::interval as whence, 
     530           name, value 
     531      from stratcon.loading_dock_metric_text_s_change_log 
     532     where sid = v_sid 
     533       and name = in_name 
     534       and whence <= v_start_adj 
     535  order by 'epoch'::timestamp + 
     536         ((floor(extract('epoch' from whence) / 
     537                 extract('epoch' from v_target.period)) * 
     538           extract('epoch' from v_target.period)) || ' seconds') ::interval desc 
     539     limit 1 
     540  loop 
     541    v_start_text := coalesce(v_change_row.value, '[unset]'); 
     542  end loop; 
     543 
     544  for v_change_row in 
     545    select v_sid as sid, whence, in_name as name, value from 
     546--    (select v_start_adj::timestamp + t * v_target.period::interval as whence 
     547--      from generate_series(1, v_target.nperiods) t) s  
     548-- left join 
     549    (select 'epoch'::timestamp + 
     550         ((floor(extract('epoch' from whence) / 
     551                 extract('epoch' from v_target.period)) * 
     552           extract('epoch' from v_target.period)) || ' seconds') ::interval as whence, 
     553           coalesce(value, '[unset]') as value 
     554      from stratcon.loading_dock_metric_text_s_change_log 
     555     where sid = v_sid 
     556       and name = in_name 
     557       and whence > v_start_adj 
     558       and whence <= v_end_adj) d 
     559--    using (whence) 
     560  order by whence asc 
     561  loop 
     562    v_next_text := v_change_row.value; 
     563    if v_change_row.value is not null and 
     564       v_start_text != v_change_row.value then 
     565      v_change_row.value := coalesce(v_start_text, '[unset]') || ' -> ' || coalesce(v_change_row.value, '[unset]'); 
     566    else 
     567      v_change_row.value := v_start_text; 
     568    end if; 
     569    if v_next_text is not null then 
     570      v_start_text := v_next_text; 
     571    end if; 
     572    return next v_change_row; 
     573  end loop; 
     574 
     575  return; 
     576end 
     577$$ language 'plpgsql'; 
     578 
     579 
     580create or replace function 
     581stratcon.choose_window(in_start_time timestamp, 
     582                       in_end_time timestamp, 
     583                       in_hopeful_nperiods int, 
     584                       out tablename text, 
     585                       out period interval, 
     586                       out nperiods int) 
     587returns setof record as 
     588$$ 
     589declare 
     590  window record; 
     591begin 
     592  -- Figure out which table we should be looking in 
     593  for window in 
     594    select atablename, aperiod, anperiods 
     595    from (select aperiod, iv/isec as anperiods, atablename, 
     596                 abs(case when iv/isec - in_hopeful_nperiods < 0 
     597                          then 10 * (in_hopeful_nperiods - iv/isec) 
     598                          else iv/isec - in_hopeful_nperiods 
     599                           end) as badness 
     600            from (select extract('epoch' from in_end_time) - 
     601                         extract('epoch' from in_start_time) as iv 
     602                 ) i, 
     603                 (   select 5*60 as isec, '5 minutes'::interval as aperiod, 
     604                            'rollup_matrix_numeric_5m' as atablename 
     605                  union all 
     606                     select 60*60 as isec, '1 hour'::interval as aperiod, 
     607                            'rollup_matrix_numeric_60m' as atablename 
     608                  union all 
     609                     select 6*60*60 as isec, '6 hours'::interval as aaperiod, 
     610                            'rollup_matrix_numeric_6hours' as atablename 
     611                  union all 
     612                     select 12*60*60 as isec, '12 hours'::interval as aperiod, 
     613                            'rollup_matrix_numeric_12hours' as atablename 
     614                 ) ivs 
     615         ) b 
     616 order by badness asc 
     617  limit 1 
     618  loop 
     619    tablename := window.atablename; 
     620    period := window.aperiod; 
     621    nperiods := window.anperiods; 
     622    return next; 
     623  end loop; 
     624  return; 
     625end 
     626$$ language 'plpgsql'; 
     627 
     628create or replace function 
     629stratcon.fetch_dataset(in_check uuid, 
     630                       in_name text, 
     631                       in_start_time timestamp, 
     632                       in_end_time timestamp, 
     633                       in_hopeful_nperiods int, 
     634                       derive boolean) 
     635returns setof stratcon.rollup_matrix_numeric_5m as 
     636$$ 
     637declare 
     638  v_sql text; 
     639  v_sid int; 
     640  v_target record; 
     641  v_interval numeric; 
     642  v_start_adj timestamp; 
     643  v_end_adj timestamp; 
     644  v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 
     645  v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 
     646  v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype; 
     647begin 
     648 
     649  -- Map out uuid to an sid. 
     650  select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check; 
     651  if not found then 
     652    return; 
     653  end if; 
     654 
     655  select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods); 
     656 
     657  select 'epoch'::timestamp + 
     658         ((floor(extract('epoch' from in_start_time) / 
     659                 extract('epoch' from v_target.period)) * 
     660           extract('epoch' from v_target.period)) || ' seconds') ::interval 
     661    into v_start_adj; 
     662 
     663  select 'epoch'::timestamp + 
     664         ((floor(extract('epoch' from in_end_time) / 
     665                 extract('epoch' from v_target.period)) * 
     666           extract('epoch' from v_target.period)) || ' seconds') ::interval 
     667    into v_end_adj; 
     668 
     669  if not found then 
     670    raise exception 'no target table'; 
     671    return; 
     672  end if; 
     673 
     674  v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' || 
     675           's.rollup_time, d.count_rows, d.avg_value, ' || 
     676           'd.stddev_value, d.min_value, d.max_value ' || 
     677           ' from ' || 
     678           '(select ' || quote_literal(v_start_adj) || '::timestamp' || 
     679                  ' + t * ' || quote_literal(v_target.period) || '::interval' || 
     680                       ' as rollup_time' || 
     681             ' from generate_series(1,' || v_target.nperiods || ') t) s ' || 
     682           'left join ' || 
     683           '(select * from stratcon.' || v_target.tablename || 
     684           ' where sid = ' || v_sid || 
     685             ' and name = ' || quote_literal(in_name) || 
     686             ' and rollup_time between ' || quote_literal(v_start_adj) || '::timestamp' || 
     687                                 ' and ' || quote_literal(v_end_adj) || '::timestamp) d' || 
     688           ' using(rollup_time)'; 
     689 
     690  for v_rollup_row in execute v_sql loop 
     691    if derive is true then 
     692      v_r_rollup_row := v_rollup_row; 
     693      if v_l_rollup_row.count_rows is not null and 
     694         v_rollup_row.count_rows is not null then 
     695        v_interval := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time); 
     696        v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2; 
     697        v_r_rollup_row.avg_value := 
     698          (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval; 
     699        v_r_rollup_row.stddev_value := 
     700          (v_rollup_row.stddev_value - v_l_rollup_row.stddev_value) / v_interval; 
     701        v_r_rollup_row.min_value := 
     702          (v_rollup_row.min_value - v_l_rollup_row.min_value) / v_interval; 
     703        v_r_rollup_row.max_value := 
     704          (v_rollup_row.max_value - v_l_rollup_row.max_value) / v_interval; 
     705      else 
     706        v_r_rollup_row.count_rows = NULL; 
     707        v_r_rollup_row.avg_value = NULL; 
     708        v_r_rollup_row.stddev_value = NULL; 
     709        v_r_rollup_row.min_value = NULL; 
     710        v_r_rollup_row.max_value = NULL; 
     711      end if; 
     712    else 
     713      v_r_rollup_row := v_rollup_row; 
     714    end if; 
     715    return next v_r_rollup_row; 
     716    v_l_rollup_row := v_rollup_row; 
     717  end loop; 
     718  return; 
     719end 
     720$$ language 'plpgsql'; 
     721 
    488722COMMIT;