Gauge charts in SQL or How to produce the world's ugliest speedometers

I may be at JavaOne 2007, the important work still has to go on. Hence the latest in a series of SQL powered charts and graphs: the Gauge or Speedometer. Using nifty graphical tricks and the SQL*Plus 3D Pack, I managed to create a query that can render visually compelling, not very interactive charts, such as the one below, showing the Salary Sum for the three departments in the EMP table:

Other valuable queries-turned-charts include the number of objects per object type in our database and of course the salary per employee in the EMP table. The possibilities are endless with this new innovative high end graphical solution.

See the stunning and grossly unfair distribution of wealth in Department 20:

And be illuminated with the wisdom of the number of objects per object type in my SCOTT schema – terribly exciting of course.

The only differences between the three queries used to produce these three pictures, are in the top inline view, called chart_query (the Model in terms of MVC for this query):

with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select ename  category      
  ,      'Employee '||empno||' '||ename category_label
  ,      sal value
  from   emp e
  where  deptno = 20  
)

with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select deptno  category      
  ,      'Department '||deptno||' '||(select dname from dept d where d.deptno = e.deptno) category_label
  ,      sum(sal) value
  from   emp e
  group
  by     deptno
)


with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select object_type category      
  ,      initcap(object_type) category_label
  ,      count(*) value
  from   user_objects
  group
  by     object_type
)
 

Well, that is not entirely true: there is a second query that is used to set the layout properties for the gauge, called gauge_dimensions: 

, gauge_dimensions as
( select 30 radius -- the radius of the gauge - can be changed
  ,      6  marker_count -- the number of markers for the gauge
  ,      0  low_limit
  ,      60 high_limit
  from   dual
)
 

We can set the radius of gauge, the upper and lower limit (or derive those from the actual values in the model-query) and specify the number of markers on the gauge.

The remainder of the SQL query is generic across all gauge-queries.

Is it pure SQL? Well… almost. To merge together the dials, I make use of a small PL/SQL function:

create or replace 
function merge_string_table_to_string
( p_tbl in string_table_collection
) return varchar2
is
  l_str varchar2(4000):='';
  l_pos number(4);
begin
  -- the string merge is for the * character
  -- that means that starting with p_tbl(1), all * from the other strings are applied to it
  l_str:= p_tbl(1);
  for i in least(2,p_tbl.count)..p_tbl.count loop
    -- find all * in p_tbl(i) and transfer them to l_str
    for j in 1..length(p_tbl(i)) loop
      if substr(p_tbl(i),j,1) <> ' '
      then
        l_str:= substr(l_str, 1, j -1 )||substr(p_tbl(i),j,1)||substr(l_str, j +1 );
      end if;
    end loop;
  end loop;
  return l_str;
end;
 

This function is based on a Nested Table type, that also needs to be setup:

create or replace type string_table_collection
as table of varchar2(4000)
/
 

For the rest, it is only SQL. And not even very spectacular SQL at that, apart perhaps from the use of a single Analytical Function to determine the position for the legend-marker in the chart (somewhere in the dial we need an indication of the category that particular dial represents).

Resources 

Download the script behind this article: Gauge.txt.

Note: using the Oracle ADF Faces Rich Components, we can render the first chart of this article (for Department 30) as: