Last week, someone asked me what a "perfect" dashboard for a network would look like. Trying to channel Tufte, I attempted to list all the variables that the viewer would care about. Since management uses a dashboard more often than other users, that'd be my audience. I reduced everything down to two variables: time and percentage of customers affected. Easy enough, right?

However, management probably wants to see the recent "in focus" and see how that flows into the long run (comparing the detailed now to the full history of the enterprise). That potentially large scale rules out a simple linear domain for the chart, and implies that a logarithmic domain would work better. something like the following chart (not pretty enough for a dashboard, but you get the idea;):

So let's create a postgresql function to transform numeric input into logarithmic buckets for grouping easily and efficiently:

create or replace function logarithmic_axis(numeric, numeric, numeric, numeric) returns numeric as $$ /* return a logarithmic chunked display of data from table XXX: with small width's, the time delta between periodic sampling dominates */ declare stamp alias for $1; -- current epochal time to bucketize init alias for $2; -- beginning of time range fin alias for $3; -- end of time range width alias for $4; -- how many buckets do we want delta numeric = 0.0; rate numeric = 0.0; exp numeric = 0.0; begin delta := fin - init; rate := pow(delta, (1 / width) ); delta := fin - stamp; if delta = 0 then -- catch the last row delta := 1; end if; exp := log(rate, cast(delta as numeric) ); exp := floor(exp); return (fin - floor( pow(rate, exp) ) ); end; $$ LANGUAGE plpgsql;

An example usage of this goes like (and is what generated the chart above):

select to_timestamp(logarithmic_axis( cast(extract(epoch from as numeric), (select cast(extract(epoch from min(day)) as numeric) from stocks), (select cast(extract(epoch from max(day)) as numeric) from stocks), 120)) as l, avg(price) from stocks group by l order by l;