Pie Charts in SQL – how pathetic can you get?

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.

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