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

8

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<br />( select ename  category      <br />  ,      'Employee '||empno||' '||ename category_label<br />  ,      sal value<br />  from   emp e<br />  where  deptno = 20  <br />)<br /><br />with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category<br />( select deptno  category      <br />  ,      'Department '||deptno||' '||(select dname from dept d where d.deptno = e.deptno) category_label<br />  ,      sum(sal) value<br />  from   emp e<br />  group<br />  by     deptno<br />)<br /><br /><br />with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category<br />( select object_type category      <br />  ,      initcap(object_type) category_label<br />  ,      count(*) value<br />  from   user_objects<br />  group<br />  by     object_type<br />)<br />&nbsp;

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<br />( select 30 radius -- the radius of the gauge - can be changed<br />  ,      6  marker_count -- the number of markers for the gauge<br />  ,      0  low_limit<br />  ,      60 high_limit<br />  from   dual<br />)<br />&nbsp;

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

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

create or replace type string_table_collection<br />as table of varchar2(4000)<br />/<br />&nbsp;

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:

 

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

8 Comments

  1. Niall,

    You got it exactly right of course. It’s the fun of thinking it up, setting yourself the challenge and proving you can do it. It’s probably in the same category as running the marathon or climbing high mountains – not that I would want to compare my result with such achievement of course. But to do pointless things to have fun and/or meet the challenge you set yourself – that is basically it.

    Lucas

  2. Good one but me thinks someone got too much time on their hands and need to get a life. :D :D

    Laurent, sure it might be a good interview question but I would question the intent of the interviewer if such a question comes up in an interview.

  3. Niall Litchfield on

    Why would you waste time an effort on this. To exercise your brain. And let’s face producing completely pointless reports is so much more fun if it was you that came up with the desire for them in the first place. :)

  4. Aren’t charts to make things clearer by showing data in something the eye/brain can recognize in an instant? So….the above is a neat trick, but only for the purpose of showing off. What’s the use? Please enlighten me, because I’m totally missing the point why you would waste your time and talent on this.

  5. Duh………you might consider to sell some of your time to me ;-) Looks like you have plenty….
    Anyway, nice job.