Pie Charts in SQL – how pathetic can you get?

11

I have written a number of posts on alternative, far fetched usages of Table Functions. My all-time high thusfar was a post titled Table FUNctions: select a graph in SQL . Now, I think I have reached another level in irrelevancy – though I have to admit it gives me great personal satisfaction: this post tells you how to create pie-charts using SQL queries. Be warned: it is utterly useless. But great fun for the twisted minds I know some of you have too! Think Table Functions, some basic maths and too much fantasy.Displaying a pie chart as result of a normal SQL query

The story is really quite simple: you pass a query that selects labels and values as a cursor to a table function -table( cursor (select label, value from table))
In SQL*Plus, we execute these commands:

set linesize 150
set pagesize 500
set long 200

select substr(column_value,1,150) "Piechart Salaries per Job"
from table( pie_graph
            ( cursor(select job, sum(sal) from emp group by job)
            , 'Salaries per Job'
            ,30
            , 0.15
            )
          )
/

The function pie_graph that is invoked here looks as follows:

function pie_graph ( p_src in sys_refcursor -- a ref-cursor for a query that returns labels in the first column and values in the second columns
, p_title  in varchar2 default null
, p_radius in integer default 25 -- specifies the size of the piechart;
, p_threshold in number default 0.05 -- smallest percentage that warrants it owns pie-segment; all values below this threshold are taken together as Others
) return fixed_string_table

It relies on three Types being defined in the database:

  create type string_table is table of varchar2(2000);
create type values_tbl is table of number;
create type fixed_string_table as table of char(150);

Another example of using this function to create pie-charts:

select substr(column_value,1,150) "Piechart Objects per User"
from table( pie_graph
            ( cursor(select owner, count(*) from all_objects where owner not in ('PUBLIC','SYS') group by owner)
            , 'Objects per user'
            ,25
            , 0.15
            )
          )

The complete source code can be downloaded at the end of this article. It looks as follows (suggestions for improvements are very welcome!):

function pie_graph ( p_src in sys_refcursor -- a ref-cursor for a query that returns labels in the first column and values in the second columns
, p_title  in varchar2 default null
, p_radius in integer default 25 -- specifies the size of the piechart;
, p_threshold in number default 0.05 -- smallest percentage that warrants it owns pie-segment; all values below this threshold are taken together as Others
) return fixed_string_table
is
   /* required collection types

      create type string_table is table of varchar2(2000);
      create type values_tbl is table of number;
      create type fixed_string_table as table of char(150);


    select substr(column_value,1,150) "Piechart Salaries per Job"
    from table( pie_graph
                ( cursor(select job, sum(sal) from emp group by job)
                , 'Salaries per Job'
                ,30
                , 0.2
                )
              )
/

    */

   c_full_circle float := 2*3.14159;
   l_radius integer:=nvl(p_radius, 40);

   retval fixed_string_table:= fixed_string_table();
   l_labels string_table;
   l_values values_tbl;
   l_string char(1200);
   l_sum number:=0;
   l_sqr_radius integer:= power(l_radius,2);
   l_dial float:=0;
   l_percentage float;
   l_index integer;
   l_x integer;
   l_others integer:=0;
   l_others_label varchar2(1000):= ': ';
   l_others_value number:=0;

   procedure xy(x integer, y integer, marker char default '*')
   is
   l_string char(1200);
   begin
      l_string:= retval( l_radius - y+1);

      l_string :=  substr( l_string, 1 ,2*(l_radius + x))
                  ||marker
                  ||substr(l_string,2*(l_radius + x)+2)
            ;
      retval( l_radius - y+1):= substr(l_string,1,130);
   end xy;

   procedure create_piesegment
   ( p_percentage in float
   , p_marker in varchar2
   , p_label in varchar2)
   is
   begin
       -- move the dial half the distance (or angle) for this value
       -- to first print the Indicator (A, B, C etc.) for this value
       l_dial:= l_dial + 0.5 *p_percentage * c_full_circle;

       -- display the indicator in this pie-segment
        xy( round(6/10*l_radius*cos(l_dial))
          , round(6/10*l_radius*sin(l_dial))
          , p_marker);

       -- move the dial the other half of the distance (or angle) for this value
       l_dial:= l_dial + 0.5*p_percentage * c_full_circle;
       -- in 10 steps, draw the border for this segment
       for i in 0..10 loop
          -- print a dot at i/10 of the line from the center to the perimeter
          xy( round(i/10*l_radius*cos(l_dial))
            , round(i/10*l_radius*sin(l_dial))
            ,'.');
       end loop;
       -- extend the collection to return to add another line to the legend
       retval.extend;
       -- write the legend line for the current value (something like: A - 500 (23 %))
       retval(retval.last):= p_label;
  end;

begin
   -- retrieve all values and labels from the refcursor into local collections
   fetch p_src bulk collect into l_labels, l_values;
   close p_src;
   -- iterate through the values to calculate the sum of all values
   l_index:= l_values.first;
   loop
     l_sum:= l_sum + l_values(l_index);
     l_index:= l_values.next(l_index);
     if l_index is null then exit; end if;
   end loop;

   -- initialize the collection of fixed length strings to return
   for y in 1..2*l_radius+2 loop
     retval.extend;
     retval(y):= rpad(' ',140);
  end loop;
  retval(1):= p_title;
   -- draw center
   xy(0,0,'x');
   -- draw the outline of the chart
   for y in 0..l_radius  loop
      if (y mod 2 = 0)  -- to get a stepsize of 2 : every other line a marker
      then
        l_x:=  round(sqrt( l_sqr_radius - power(y,2)));
        xy(l_x,y); -- noon-3 o'clock
        xy(-l_x,y); -- 9 to 12
        xy(l_x,-y); -- 3 to 6
        xy(-l_x,-y); -- 6 to 9
      end if;
   end loop;

   -- draw the initial line, from the center horizontally to the right (3 o'clock)
   for i in 0..10 loop
      xy( round(i/10*l_radius*cos(l_dial))
        , round(i/10*l_radius*sin(l_dial))
        ,'.');
   end loop;

   -- iterate through the values and labels to draw all pie-segments
   l_index:= l_values.first;
   loop
     -- calculate the percentage for the current value
     l_percentage:= l_values(l_index)/l_sum;
     if l_percentage > p_threshold
     then
       create_piesegment
       ( p_percentage => l_percentage
       , p_marker => chr(ascii('A')+l_index-1-l_others)
       , p_label => chr(ascii('A')+l_index-1-l_others)||' = '||l_labels(l_index)||' - '||l_values(l_index)||' ('||round(l_percentage*1000)/10||' %)'
       );
     else -- under threshold, update others
       l_others:= l_others +1;
       l_others_value:= l_others_value + l_values(l_index);
       l_others_label:= l_others_label||l_labels(l_index)||' - '||l_values(l_index)||' ('||round(l_percentage*1000)/10||' %);';
     end if;
     l_index:= l_values.next(l_index);
     if l_index is null
     then
       exit;
     end if;
   end loop; -- over all values and labels
   if l_others_value <> 0
   then
     -- process the others
     l_percentage:= l_others_value/l_sum;
     create_piesegment
     ( p_percentage => l_percentage
     , p_marker => '#'
     , p_label => '# = Others '||l_others_value||' ('||round(l_percentage*1000)/10||' %)'||l_others_label||' - '
     );
   end if; -- l_others_value <>0
   return retval;
end;

Download the pie_graph function

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.

11 Comments

  1. He, couldn’t you also build in some nice text adventures, underneath those nice graphics ;-)

  2. Have you seen and appreciated the strict MVC architecture? The query itself is the Controller that instructs the Model to produce some data (that is the inner query passed in the first parameter as CURSOR expression while the VIEW, completely shielded from MODEL specifics is only concerned with producing a pretty picture.

  3. Wow! That beats the ol’ bargraph any day…
    SQL> column Bar format a20
    SQL> select rownum, lpad(‘>’, rownum, ‘-’) Bar from all_objects where rownum < 7
    2 /

    ROWNUM BAR
    ---------- --------------------
    1 >
    2 ->
    3 –>
    4 —>
    5 —->
    6 —–>

    6 rows selected.

  4. Something like poke 781,X:poke 782,Y:poke 783,0:sys 65520:Print”*” you mean? ;) Ok ok i admit, that’s for the Commodore, never did any ZX81 coding